グループ単位の通し番号とグループ内の連番の付け方

いきさつ

前々から分析関数なるものの存在は知っていましたが、あまり使い道を分かっていませんでした。 でも、関数の理解が深まれば中々使えることを最近になって分かってきました。

参考例

------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2016.07.03 作成
------------------------------------------------------------------
SELECT V_1.GROUP_ID
     , V_1.MEISAI_ID
     , DENSE_RANK() OVER(ORDER BY V_1.GROUP_ID) AS GROUP_NO
     , ROW_NUMBER() OVER(PARTITION BY V_1.GROUP_ID ORDER BY V_1.MEISAI_ID) AS MEISAI_NO
     , LISTAGG(V_1.VALUE, ',') WITHIN GROUP(ORDER BY V_1.VALUE ASC) AS VALUE_LIST
     , SUM(SUM(V_1.VALUE)) OVER(PARTITION BY V_1.GROUP_ID) AS GROUP_SUMMARY
     , SUM(SUM(V_1.VALUE)) OVER() AS TOTAL_SUMMARY
  FROM ( SELECT 'A01' AS GROUP_ID, '001' AS MEISAI_ID, 1 AS VALUE FROM DUAL UNION ALL
         SELECT 'A01',             '002',              2          FROM DUAL UNION ALL
         SELECT 'A01',             '003',              3          FROM DUAL UNION ALL
         SELECT 'A01',             '003',              4          FROM DUAL UNION ALL
         SELECT 'A02',             '001',              5          FROM DUAL UNION ALL
         SELECT 'A02',             '002',              6          FROM DUAL UNION ALL
         SELECT 'A02',             '003',              7          FROM DUAL UNION ALL
         SELECT 'A03',             '001',              8          FROM DUAL UNION ALL
         SELECT 'A03',             '001',              9          FROM DUAL UNION ALL
         SELECT 'A03',             '001',             10          FROM DUAL ) V_1
 GROUP BY
       V_1.GROUP_ID
     , V_1.MEISAI_ID
 ORDER BY
       V_1.GROUP_ID
     , V_1.MEISAI_ID
;

実行結果

GROUP_ID MEISAI_ID GROUP_NO MEISAI_NO VALUE_LIST GROUP_SUMMARY TOTAL_SUMMARY
-------- --------- -------- --------- ---------- ------------- -------------
A01      001              1         1 1                     10            55
A01      002              1         2 2                     10            55
A01      003              1         3 3,4                   10            55
A02      001              2         1 5                     18            55
A02      002              2         2 6                     18            55
A02      003              2         3 7                     18            55
A03      001              3         1 8,9,10                27            55

使い道

�@グループ単位に通し番号を振りたい場合

DENSE_RANK() OVER(ORDER BY [グループ項目名を指定])

�Aグループ内で連番を振りたい場合

ROW_NUMBER() OVER(PARTITION BY [グループ項目名を指定] ORDER BY [連番を振りたい順位を指定])

�Bグループ内で項目をカンマ区切りで指定したい場合

LISTAGG([カンマ区切りで表示したい項目名], ',') WITHIN GROUP(ORDER BY [カンマ区切りで表示したい順位を指定] ASC)
※重複は除去できないので、重複を除去したい場合は自前でストアド・ファンクションを作成するか、 一度インラインビュー化して重複を削除してから上記関数を使用する。
SELECT A, LISTAGG(B, ',') ... FROM (SELECT DISTINCT A,B FROM X) GROUP BY A;

�C縦計(小計)を明細に持ちたい場合

SUM(SUM([集計したい項目名を指定])) OVER(PARTITION BY [グループ項目名を指定])

�D縦計(合計)を明細に持ちたい場合

SUM(SUM([集計したい項目名を指定])) OVER()