Djangoのウィンドウ関数を試しました。

お疲れ様です。@naokiurです。

[djangocongress](https://djangocongress.jp/)に参加させて頂きました。
ためになる、興味深い発表を拝見しました。
素晴らしい機会を本当にありがとうございます。

拝見した、[Make Query Great Again!](https://www.slideshare.net/dattun/django-congress-jp-2019-make-query-great-again-slide-share)
に触発されて、
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
```Python
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で書く
[公式](https://docs.djangoproject.com/ja/2.2/ref/models/expressions/#django.db.models.expressions.Window)を確認したところ、
`Window`というfunctionを用いて、 `QuerySet.annotate()`を呼び出すようです。


```Python
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()`で確認しました。

```SQL
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句を用いることで、
データ分析や集計結果のための、
様々な出力結果を取得することができそうです。


# 参考にさせて頂きました
* [Make Query Great Again!](https://www.slideshare.net/dattun/django-congress-jp-2019-make-query-great-again-slide-share)
* https://docs.djangoproject.com/ja/2.2/ref/models/expressions/#django.db.models.expressions.Window
* https://www.postgresql.jp/document/9.6/html/tutorial-window.html