分析関数の衝撃

PostgreSQLの分析関数の衝撃5
（Row_Number関数の応用例）

SQLで旅人算の感覚

山岸賢治[著]

2009/10/16 14:00

ポスト

ダウンロード SourceCode (1.3 KB)

4. 重複を除いた累計

　最後に、重複を除いた累計を求めるSQLです。『PostgreSQLの分析関数の衝撃2』の「4. 累計を求める」では、下記のselect文で、累計を求めました。

累計を求めるSQL

select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date) as onhand_amt
  from Accounts
order by prc_date;

　問題をアレンジして重複を除いた累計を求めてみましょう。まずは、テーブルのデータと、出力結果を考えます。

Visit

SortKey	Visiter
1	A
2	B
3	A
4	C
5	B
6	B
7	A
8	D
9	E

　各行のSortKeyまでのVisiterの数を数えますが、重複を除いて数えます。たとえば、SortKey=5の行なら、VisiterはA,B,A,C,Bですが、重複を除き、AとBとCでVisiterの数は、3となります。SortKey=8の行なら、VisiterはA,B,A,C,B,A,Dですが、重複を除き、AとBとCとDでVisiterの数は、4となります。

出力結果

SortKey	Visiter	cnt
1	A	1
2	B	2
3	A	2
4	C	3
5	B	3
6	B	3
7	A	3
8	D	4
9	E	5

　下記のselect文は文法エラー（DISTINCT is not implemented for window functions）となります。

文法エラーになるSQL

select SortKey,Visiter,
count(distinct Visiter) over(order by SortKey) as cnt
  from Visit;

　代替案として、SortKeyの昇順にVisiterを見ていった時に、同じVisiterが複数回登場したとしても、最初の1回のみを数えればいいと考えて、答えは下記となります。

答え

select SortKey,Visiter,sum(willSum) over(order by SortKey) as cnt
from (select SortKey,Visiter,
      case Row_Number() over(partition by Visiter order by SortKey)
      when 1 then 1 else 0 end as willSum
        from Visit) a
order by SortKey;

　単純case式で、Row_Number関数の値の結果が1なら1、そうでなければ0とした値を求め、willSumとしてます。そして、window関数のsum関数でwillSumの累計を求めてます。

　0は加算の単位元なので、0を加算しても値は変化しないのです。例としては、下記の数式となります。

0は加算の単位元