お疲れ様です。@naokiurです。
djangocongressに参加させて頂きました。
ためになる、興味深い発表を拝見しました。
素晴らしい機会を本当にありがとうございます。
拝見した、Make Query Great Again!
に触発されて、
Djangoのウィンドウ関数について調べました。
環境
- MacBook Pro (Retina 13-inch、Early 2015)
- macOS High Sierra 10.13.6
- IntelliJ IDEA CE 2019.1
- Python 3.7.3
- Django 2.2
- Django sqlite3 3.27.2
実施したこと
- ウィンドウ関数を調べる
- Djangoでの記述方法を調べる
そもそもウィンドウ関数とは
sqlite3のウィンドウ関数は、PostgreSQLのウィンドウ関数を元にしているとのことなので、
PostgreSQLのドキュメントを確認しました。
3.5. ウィンドウ関数
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。
これは集約関数により行われる計算の形式と似たようなものです。
とは言っても、通常の集約関数とは異なり、
ウィンドウ関数の使用は単一出力行に行をグループ化しません。
行はそれぞれ個別の身元を維持します。
裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
- 集約関数と似ているもの
- 単一出力行に行をグループ化しない
SELECT結果のあるグループごとに対して、
計算し、その情報を行ごとに付与することができる、
と理解しました。
前提
プログラムで使用されていたテーブルを、
流用させて頂きます。
django model
from django.db import models
# Create your models here.
class SushiTopping(models.Model):
""" タコ、サワラ、シメサバなどの具材 """
name = models.CharField(max_length=255)
class Meta:
db_table = 'sushi_topping'
class Menu(models.Model):
""" お品書き """
name = models.CharField(max_length=255, unique=True)
price = models.IntegerField(default=0)
sushi_toppings = models.ManyToManyField(SushiTopping)
class Meta:
db_table = 'menu'
class Sale(models.Model):
""" 売上 """
sales_date = models.DateTimeField()
menu = models.ForeignKey(Menu, on_delete=models.PROTECT)
class Meta:
db_table = 'sale'
テーブル
- menu
- id
- name
- price
- sushitopping
- id
- name
- menu_sushi_toppings
- id
- menu_id
- sushitopping_id
- sale
- id
- menu_id
- sales_date
ユースケース
こんなものをサンプルとして考えました。
- 売上の推移を見たい
Aが売れ、Bが売れ…と続き、
その時点での売上がいくらだったか、
というのを出力してみたいと思います。
出力イメージは以下です。
イメージ
yyyy/mm/dd hh:mm:ss メニュー名 金額 そのときの売上合計額
例
2019/05/18 10:00:00 A 100 100
2019/05/18 11:00:00 B 300 400
2019/05/18 12:00:00 A 100 500
...
SQLで書く
SELECT
datetime(sale.sales_date, '+9 hours'),
menu.name,
menu.price,
sum(menu.price) OVER(ORDER BY sale.sales_date)
FROM
sale
INNER JOIN
menu
ON
sale.menu_id = menu.id
;
全体の推移を出力するため、
PARTITION BY句は不要でした。
結果
2019-05-18 17:10:52|マグロ|100|100
2019-05-18 17:13:58|マグロ|100|200
2019-05-18 17:14:11|サーモン|80|280
2019-05-18 17:14:18|三種盛り|1000|1280
2019-05-18 17:14:26|サーモン|80|1360
2019-05-18 17:14:31|マグロ|100|1460
おお…！
一番右のカラムに、全体の推移が出力されました…！
Djangoで書く
公式を確認したところ、
Windowというfunctionを用いて、 QuerySet.annotate()を呼び出すようです。
sales_transition = Sale.objects.annotate(
sales_process=Window(
# 外部キーが指定されているテーブルは、
# `__`で各カラムにアクセスすることができる
expression=Sum('menu__price'),
order_by=['sales_date']
)
)
# 確認用
[
print('{} {} {} {}'.format(sales.sales_date, sales.menu.name, sales.menu.price, sales.sales_process) )
for sales in sales_transition
]
結果
2019-05-18 08:10:52+00:00 マグロ 100 100
2019-05-18 08:13:58+00:00 マグロ 100 200
2019-05-18 08:14:11+00:00 サーモン 80 280
2019-05-18 08:14:18+00:00 三種盛り 1000 1280
2019-05-18 08:14:26+00:00 サーモン 80 1360
2019-05-18 08:14:31+00:00 マグロ 100 1460
おお…！ SQLと同様の結果を得ることができました…！
ちなみに実行しているSQLは…？
.query()で確認しました。
SELECT
"sale"."id",
"sale"."sales_date",
"sale"."menu_id",
SUM("menu"."price") OVER (ORDER BY "sale"."sales_date") AS "sales_process"
FROM
"sale"
INNER JOIN
"menu"
ON ("sale"."menu_id" = "menu"."id")
自分で書いたSQLとほぼ同じものが出力されていました！
結果
Djangoでウィンドウ関数を試すことができました。
PARTITION BY句を用いることで、
データ分析や集計結果のための、
様々な出力結果を取得することができそうです。