分析関数の衝撃

分析関数の衝撃5（総集編）

CodeZineに掲載されたSQLを分析関数で記述する 5

山岸賢治[著]

更新日: 2008/10/07
公開日: 2008/09/25

ポスト

ダウンロードソースコード (3.0 KB)

4. 重複を除いた累計

　最後に、重複を除いた累計を求めるSQLです。

　中編の「4. 累計を求める」では、下記のselect文で、累計を求めました。

累計を求める

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,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オプションを使った場合はorder byを指定できないので、文法エラー（ORA-30487: ORDER BY not allowed here）となります。

ORA-30487が発生

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)
order by SortKey;

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

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

1+0=1
5+0+0+0=5
1+2+0+0=3+0+0=3

　SQLのイメージは下記です。

SQLのイメージ

最後に

　今回は総集編として、前編、中編、後編、完結編に関連した内容を扱いました。次回の応用編では、分析関数の変わった使い方を紹介する予定です。

参考資料

OracleSQLパズル『最小の空き番号を取得その2』
本稿の「1. 旅人算の感覚を応用する（2人旅人算）」の類題と別解を置いてます。
OracleSQLパズル『最大のリージョンを求める（境界なし）』
本稿の「2. 旅人算の感覚を応用する（3人旅人算）」の類題と別解を置いてます。
OracleSQLパズル『minus allとintersect all』
OracleSQLパズル『集合（重複要素を許可）の包含関係を調べる』
本稿の「3. minus allとintersect allを模倣」の類題と別解を置いてます。
OracleSQLパズル『distinctオプションとorder by指定の分析関数を代用』
本稿の「4. 重複を除いた累計」の類題と別解を置いてます。