環境

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

参考にさせて頂きました