ダウンロード SourceCode (1.6 KB)
Rows between 2 following and unbounded following
応用して今度は、Rows between 2 following and unbounded followingなcount(*)とminとmaxとsumを代用してみます。サンプルを見てみましょう。
select sortKey,Val,
count(*) over(order by sortKey
Rows between 2 following
and unbounded following) as cnt,
min(Val) over(order by sortKey
Rows between 2 following
and unbounded following) as minVal,
max(Val) over(order by sortKey
Rows between 2 following
and unbounded following) as maxVal,
sum(Val) over(order by sortKey
Rows between 2 following
and unbounded following) as sumVal
from OracleCompOlap
order by sortKey;
| sortKey | Val | cnt | minVal | maxVal | sumVal |
| 1 | 1 | 10 | 0 | 9 | 50 |
| 3 | 4 | 9 | 0 | 9 | 45 |
| 5 | 5 | 8 | 0 | 8 | 36 |
| 7 | 9 | 7 | 0 | 7 | 28 |
| 8 | 8 | 6 | 0 | 7 | 26 |
| 9 | 2 | 5 | 3 | 7 | 26 |
| 10 | 0 | 4 | 3 | 7 | 21 |
| 12 | 5 | 3 | 3 | 6 | 14 |
| 13 | 7 | 2 | 5 | 6 | 11 |
| 14 | 3 | 1 | 6 | 6 | 6 |
| 15 | 5 | 0 | null | null | null |
| 18 | 6 | 0 | null | null | null |
本稿の「1. Rows 2 Preceding」と「2. Rows between current row and unbounded following」を踏まえて、答えは下記となります。なお、array_agg関数は、集約の内訳を表示するのに便利なので使用してます。
select sortKey,Val,
Lead(WKcnt,2,0::bigint) over(order by sortKey) as cnt,
Lead(WKminVal,2) over(order by sortKey) as minVal,
Lead(WKmaxVal,2) over(order by sortKey) as maxVal,
Lead(WKsumVal,2) over(order by sortKey) as sumVal,
Lead(WKVals,2) over(order by sortKey) as Vals
from (select sortKey,Val,
count(*) over(order by sortKey desc) as WKcnt,
min(Val) over(order by sortKey desc) as WKminVal,
max(Val) over(order by sortKey desc) as WKmaxVal,
sum(Val) over(order by sortKey desc) as WKsumVal,
array_agg(Val) over(order by sortKey desc) as WKVals
from OracleCompOlap) a
order by sortKey;
| sortKey | Val | cnt | minVal | maxVal | sumVal | Vals |
| 1 | 1 | 10 | 0 | 9 | 50 | {6,5,3,7,5,0,2,8,9,5} |
| 3 | 4 | 9 | 0 | 9 | 45 | {6,5,3,7,5,0,2,8,9} |
| 5 | 5 | 8 | 0 | 8 | 36 | {6,5,3,7,5,0,2,8} |
| 7 | 9 | 7 | 0 | 7 | 28 | {6,5,3,7,5,0,2} |
| 8 | 8 | 6 | 0 | 7 | 26 | {6,5,3,7,5,0} |
| 9 | 2 | 5 | 3 | 7 | 26 | {6,5,3,7,5} |
| 10 | 0 | 4 | 3 | 7 | 21 | {6,5,3,7} |
| 12 | 5 | 3 | 3 | 6 | 14 | {6,5,3} |
| 13 | 7 | 2 | 5 | 6 | 11 | {6,5} |
| 14 | 3 | 1 | 6 | 6 | 6 | {6} |
| 15 | 5 | 0 | null | null | null | null |
| 18 | 6 | 0 | null | null | null | null |
SQLのイメージは下記となります。order by sortKey Rows between 2 following and unbounded followingに対応する紫線と黄緑線と青線を引いてます。
この記事は参考になりましたか?
- 分析関数の衝撃連載記事一覧
-
- PostgreSQLの分析関数の衝撃(8) ――RowsとRangeの代用
- PostgreSQLの分析関数の衝撃(7) ――window関数の変わった使用例
- PostgreSQLの分析関数の衝撃6 (window関数の応用例)
- この記事の著者
-
山岸 賢治(ヤマギシ ケンジ)
趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。
※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です
この記事は参考になりましたか?
この記事をシェア

