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

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序(シーケンス)生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
→グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

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

参考例

------------------------------------------------------------------
-- 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()


Copyright(c) 2014-2017 pakkin. All Rights Reserved.