結論

ウインドウフレーム内での絞り込みが色々できる。

Window関数の拡張について

PostgreSQL 11の細かいことは全て篠田の虎の巻 10巻を読むのじゃ。 PostageSQLの新機能で大事なことは全て篠田の虎の巻で学んだ（マジ

で何ができるかというと以下の2点

ウインドウフレームのグルーピングがRow(行）以外も増えた
元々RANGEがあったけどint使えるようになったのでやっと実用的になった
ウインドウフレーム内のLIMITとしてEXCLUDE句が出来た

EXCLUDE句の種類は以下の通り。

EXCLUDE CURRENT ROW
- 自分の行を除外する
EXCLUDE GROUP
- 自分と同じ値の行を除外する
EXCLUDE TIES
- 重複した値の行を除外する
EXCLUDE NO OTHERS
- ウインドウフレーム以外の値を省略する

ちなみに ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING の場合は 行単位 BETWEEN 自分のレコードから指定した単位の1つ上 PRECEDING AND 自分のレコードから指定した単位の一つ下 FOLLOWING となる。

これによって次のようになる。

-- OVER ~ の振る舞いを確認
demo=# WITH t(id, value) AS (VALUES (1, 1), (2, 1), (3, 3), (4, 5), (5, 5), (6, 5), (7, 6))
SELECT
  id,
  value,
  array_agg(id) OVER ROWS as row_id,
  array_agg(value)  OVER ROWS as row_value,
  array_agg(id) OVER RANGE as renge_id,
  array_agg(value)  OVER RANGE as renge_value,
  array_agg(id) OVER GROUPS as groups_id,
  array_agg(value)  OVER GROUPS  as groups_value
FROM 
  t WINDOW obj AS (ORDER BY value),
  ROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  RANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  GROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING);

 id | value | row_id  | row_value | renge_id  | renge_value |   groups_id   | groups_value
----+-------+---------+-----------+-----------+-------------+---------------+---------------
  1 |     1 | {1,2}   | {1,1}     | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  2 |     1 | {1,2,3} | {1,1,3}   | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  3 |     3 | {2,3,4} | {1,3,5}   | {3}       | {3}         | {1,2,3,4,5,6} | {1,1,3,5,5,5}
  4 |     5 | {3,4,5} | {3,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  5 |     5 | {4,5,6} | {5,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  6 |     5 | {5,6,7} | {5,5,6}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  7 |     6 | {6,7}   | {5,6}     | {4,5,6,7} | {5,5,5,6}   | {4,5,6,7}     | {5,5,5,6}
(7 rows)

-- EXCLUDE句を確認
demo=# WITH t(value) AS (VALUES (1), (1), (3), (5), (5), (5), (6))
SELECT
  value,
  array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      EXCLUDE CURRENT ROW) AS current_row,
  array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      EXCLUDE GROUP) AS group,
  array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      EXCLUDE TIES) AS ties,
  array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      EXCLUDE NO OTHERS) AS no_others
FROM t
WINDOW obj AS (ORDER BY value);

 value | current_row | group |  ties   | no_others
-------+-------------+-------+---------+-----------
     1 | {1}         |       | {1}     | {1,1}
     1 | {1,3}       | {3}   | {1,3}   | {1,1,3}
     3 | {1,5}       | {1,5} | {1,3,5} | {1,3,5}
     5 | {3,5}       | {3}   | {3,5}   | {3,5,5}
     5 | {5,5}       |       | {5}     | {5,5,5}
     5 | {5,6}       | {6}   | {5,6}   | {5,5,6}
     6 | {5}         | {5}   | {5,6}   | {5,6}
(7 rows)

移動平均で売上を集計したり、ページ別のユーザ動線の集計などで便利。でRDSはPostgreSQL 11がまだPreviewだし、Aurora for PostgreSQLはまだ10対応すら終わってない。早く11対応してくれ～～～。

以上現場の声でした。

そーだいなるらくがき帳

そーだいが自由気侭に更新します。

PostgreSQL 11のWindow関数は除外指定できる

結論

Window関数の拡張について