postgresにおけるウインドウ関数の実行タイミングについて

Question

sqlの実行タイミングについて不明点がございます。まず
テーブルは以下のような設計となっております。

DROP TABLE IF EXISTS item_purchase_anlytics;
create table item_purchase_anlytics(
item_name char(16),
item_category char(16),
action_date date,
place char(8),
user_id char(8),
price int
);
insert into item_purchase_anlytics values
  ('サバ','food','2019-03-29','Store A','UID0001',280),
  ('しょう油','food','2019-03-29','Store A','UID0001',280),
  ('サバ','food','2019-03-30','Store A','UID0001',350),
  ('サバ','food','2019-03-30','Store A','UID0001',350),
  ('卵','food','2019-03-30','Store A','UID0001',200),
  ('しょう油','food','2019-03-30','Store A','UID0001',200),
  ('鶏肉','food','2019-04-01','Store A','UID0001',450),
  ('卵','food','2019-04-04','Store A','UID0001',280),
  ('卵','food','2019-04-06','Store A','UID0001',280),
  ('卵','food','2019-04-09','Store A','UID0001',280),
  ('サバ','food','2019-04-10','Store A','UID0004',280),
  ('しょう油','food','2019-04-10','Store A','UID0001',280),
  ('卵','food','2019-04-10','Store A','UID0001',280),
  ('卵','food','2019-04-12','Store A','UID0001',280),
  ('卵','food','2019-04-12','Store A','UID0001',280),
  ('鶏肉','food','2019-04-12','Store A','UID0001',450),
  ('サバ','food','2019-04-13','Store A','UID0001',280),
  ('しょう油','food','2019-04-13','Store A','UID0001',280),
  ('深皿','zakka','2019-04-14','Store A','UID0003',780),
  ('しょう油','food','2019-04-14','Store A','UID0003',280),
  ('深皿','zakka','2019-04-14','Store A','UID0003',980),
  ('卵','food','2019-04-15','Store A','UID0001',280),
  ('サバ','food','2019-04-17','Store A','UID0001',280),
  ('サバ','food','2019-04-18','Store A','UID0001',280),
  ('深皿','zakka','2019-04-23','Store A','UID0002',980);

実行したsqlは以下になります。

以下のようなsqlを実行しました。

SELECT
    action_date,
    SUM(price) AS amount_price, 　　　　　-- ①
    AVG(SUM(price)) OVER( 　　　　　　　　--　②
        ORDER BY
            action_date 
        ROWS BETWEEN 6 preceding 
            AND CURRENT ROW
    ) AS moving_average
FROM
    item_purchase_anlytics
GROUP BY
    action_date
ORDER BY
    action_date
;

実行結果を確認したところ
①についてはGROUP BYしたグループごとに合計値を取得しているようなのですが
問題は②についてです。
通常ウインドウ関数についてはselect時に実行するつまり今回で言うとところのGROUP BYした後の最後のselect文の実行時に実行されるという認識です。
よって②のSUM(price)はGROUP BY　action_dateでグループした後のacton_dateごとでグループした後の範囲の中での合計値を取得していると思います。
「AVG(SUM(price)) OVER(・・・」については実行結果を確認するとaction_dateを並べ変えてかつその合計値つまり①の実行結果を
出力した後のある行の6行前の平均値を取得しているように見えます。

ウィンドウ関数の実行タイミングはsqlのウインドウ関数以外のselect文が実行された後のデータに対してウインドウ関数が実行されるという認識で正しいでしょうか？

実行タイミングが読めないでウインドウ関数の実行タイミングがよくわからないのでどなたか教えていただきたいです。

Answer 1

ウィンドウ関数の実行タイミングはsqlのウインドウ関数以外のselect文が実行された後のデータに対して
ウインドウ関数が実行されるという認識で正しいでしょうか？

はい。仰るとおりです。
ただし、
省略可能な引数をスキップした場合や上位で指定する句の記述を変更した場合の動作は
少し注意なさったほうがいいでしょう。

以下に掲載するSQL文の結果がすぐに想像できて
全ての列において実際の結果も合致していたら...けっこう凄いと思います。

SELECT
    ROW_NUMBER() OVER() As rid,
    action_date,
    STRING_AGG(cast(price as varchar), ',') As array_to_price,
    AVG(MAX(price)) OVER(
        ORDER BY
            action_date DESC
        ROWS BETWEEN 6 preceding 
            AND CURRENT ROW
    ) AS moving_average
FROM
    item_purchase_anlytics
GROUP BY
    action_date
ORDER BY
    action_date
;

• row_number関数は

射影1のため、値が昇順で表示されるかと思いきや
戻り値がパーティション内での行番号という性質と引数が無指定のため
射影4のORDER BY action_date DESCの結果に影響を受け、
射影2のaction_dateが昇順に並んでいるのにもかかわらず、戻り値が降順となります

• 射影4のwindow関数で指定しているFRAME句(->OVER句->ORDER BY句)は

OVER句(->ORDER BY句)でaction_date列の並びを降順に指定しているため、
前のレコードではなく、後ろのレコードを6行分参照します

■結果

rid	action_date	array_to_price	moving_average	moving_averageの内部動作	演算対象のrid
14	2019/3/29	[280,280]	314.2857143	(280 + 280 + 280 + 280 + 450 + 350 + 280) / 7	8 から 14
13	2019/3/30	[350,350,200,200]	338.5714286	(450 + 280 + 280 + 280 + 280 + 450 + 350) / 7	7 から 13
12	2019/4/1	[450]	328.5714286	(280 + 450 + 280 + 280 + 280 + 280 + 450) / 7	6 から 12
11	2019/4/4	[280]	404.2857143	(980 + 280 + 450 + 280 + 280 + 280 + 280) / 7	5 から 11
10	2019/4/6	[280]	404.2857143	(280 + 980 + 280 + 450 + 280 + 280 + 280) / 7	4 から 10
9	2019/4/9	[280]	404.2857143	(280 + 280 + 980 + 280 + 450 + 280 + 280) / 7	3 から 9
8	2019/4/10	[280,280,280]	404.2857143	(280 + 280 + 280 + 980 + 280 + 450 + 280) / 7	2 から 8
7	2019/4/12	[280,280,450]	504.2857143	(980 + 280 + 280 + 280 + 980 + 280 + 450) / 7	1 から 7
6	2019/4/13	[280,280]	513.3333333	(980 + 280 + 280 + 280 + 980 + 280) / 6	1 から 6
5	2019/4/14	[780,280,980]	560	(980 + 280 + 280 + 280 + 980) / 5	1 から 5
4	2019/4/15	[280]	455	(980 + 280 + 280 + 280) / 4	1 から 4
3	2019/4/17	[280]	513.3333333	(980 + 280 + 280) / 3	1 から 3
2	2019/4/18	[280]	630	(980 + 280) / 2	1 から 2
1	2019/4/23	[980]	980	(980) / 1	1 のみ

Comments

[Carlos_is_only]

表まで作成していただいて・・・
詳しいご説明誠にありがとうございます。

[Carlos_is_only]

詳しいご説明をありがとうございました。
また新にSQLの質問をさせていただいたのでお時間が許せば是非そちらもご覧いただけると嬉しいです。

Answer 2

ウィンドウ関数の実行タイミングはsqlのウインドウ関数以外のselect文が実行された後のデータに対してウインドウ関数が実行されるという認識で正しいです。

クエリの実行計画を見るとウインドウ関数が最後に実行されることが分かります。「データ検索→グルーピング→ソート→ウインドウ関数」の順で実行されます。

クエリの実行計画は「explain select(…)」で取得できます。

Comments

[Carlos_is_only]

なるほど、クエリ実行計画を確認する方法がありましたね
ご説明ありがとうございます。

[Carlos_is_only]

詳しいご説明をありがとうございました。
また新にSQLの質問をさせていただいたのでお時間が許せば是非そちらもご覧いただけると嬉しいです。