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;
問題をアレンジして重複を除いた累計を求めてみましょう。
まずは、テーブルのデータと、出力結果を考えます。
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)
となります。
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のイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/1594/fig02c.gif)
最後に
今回は総集編として、前編、中編、後編、完結編に関連した内容を扱いました。次回の応用編では、分析関数の変わった使い方を紹介する予定です。
参考資料
- OracleSQLパズル 『最小の空き番号を取得その2』
本稿の「1. 旅人算の感覚を応用する(2人旅人算)」の類題と別解を置いてます。
- OracleSQLパズル 『最大のリージョンを求める(境界なし)』
本稿の「2. 旅人算の感覚を応用する(3人旅人算)」の類題と別解を置いてます。
- OracleSQLパズル 『minus allとintersect all』
OracleSQLパズル 『集合(重複要素を許可)の包含関係を調べる』本稿の「3. minus allとintersect allを模倣」の類題と別解を置いてます。 - OracleSQLパズル 『distinctオプションとorder by指定の分析関数を代用』
本稿の「4. 重複を除いた累計」の類題と別解を置いてます。