Flask-SQLAlchemyでGroup Byクエリを使いこなす!売上分析・ユーザー分析の実践例
Flask-SQLAlchemyで複数の集計を行うGroup Byの実装
方法1: func.sum()
と func.count()
のような集計関数を直接使用する
この方法は、単純な集計を行う場合に便利です。例えば、次のコードは、Product
テーブルの各カテゴリの合計価格と商品数を取得します。
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格と商品数を取得
results = db.session.query(
Product.category,
func.sum(Product.price),
func.count(Product.id),
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.func_sum_price}")
print(f"商品数: {result.func_count_id}")
方法2: sqlalchemy.orm.query.Query.group_by()
メソッドを使用する
この方法は、より複雑な集計を行う場合に便利です。例えば、次のコードは、各カテゴリの合計価格、平均価格、商品数を取得します。
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格、平均価格、商品数を取得
results = db.session.query(
Product.category,
func.sum(Product.price).label("total_price"),
func.avg(Product.price).label("average_price"),
func.count(Product.id).label("product_count"),
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.total_price}")
print(f"平均価格: {result.average_price}")
print(f"商品数: {result.product_count}")
上記の方法のいずれを使用する場合でも、group_by()
メソッドは、集計を行う列を指定するために使用されます。func.sum()
や func.count()
などの集計関数は、select_from()
メソッドではなく、group_by()
メソッド内で呼び出す必要があります。
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格と商品数を取得
results = db.session.query(
Product.category,
func.sum(Product.price),
func.count(Product.id),
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.func_sum_price}")
print(f"商品数: {result.func_count_id}")
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格、平均価格、商品数を取得
results = db.session.query(
Product.category,
func.sum(Product.price).label("total_price"),
func.avg(Product.price).label("average_price"),
func.count(Product.id).label("product_count"),
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.total_price}")
print(f"平均価格: {result.average_price}")
print(f"商品数: {result.product_count}")
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格と、そのカテゴリの中で最も高額な商品の名前を取得
subquery = db.session.query(
Product.category,
func.max(Product.price).label("max_price"),
).group_by(Product.category)
results = db.session.query(
Product.category,
func.sum(Product.price),
Product.name,
).join(subquery, Product.category == subquery.c.category).filter(
Product.price == subquery.c.max_price
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.func_sum_price}")
print(f"最高額商品名: {result.name}")
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格が1000円以上のカテゴリのみを取得
results = db.session.query(
Product.category,
func.sum(Product.price).label("total_price"),
).group_by(Product.category).having(func.sum(Product.price) >= 1000).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.total_price}")
方法4: sqlalchemy.orm.aliased()
関数を使用する
aliased()
関数は、クエリ内の列に別名を付けるために使用できます。これは、同じ列に対して複数の集計関数を使用する場合に役立ちます。例えば、次のコードは、各カテゴリの合計価格と平均価格を取得します。
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格と平均価格を取得
results = db.session.query(
Product.category,
func.sum(Product.price).label("total_price"),
func.avg(aliased(Product.price)).label("average_price"),
).group_by(Product.category).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.total_price}")
print(f"平均価格: {result.average_price}")
order_by()
メソッドは、結果をソートするために使用できます。これは、集計結果を特定の順序で表示する場合に役立ちます。例えば、次のコードは、各カテゴリの合計価格が高い順に結果をソートします。
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
category = db.Column(db.String(80))
price = db.Column(db.Float)
# 各カテゴリの合計価格が高い順に結果をソート
results = db.session.query(
Product.category,
func.sum(Product.price).label("total_price"),
).group_by(Product.category).order_by(func.sum(Product.price).desc()).all()
# 結果を処理
for result in results:
print(f"カテゴリ: {result.category}")
print(f"合計価格: {result.total_price}")
sqlalchemy