SQL OVER句の分析関数で効率よくデータを集計する
分析関数を使用すれば、効率よく簡単にデータを集計することができます。
例えば、社員一覧と社員の所属する部署の所属人数を取得したい場合、分析関数を使わない場合は下記のように取得していました。
SELECT
T1.社員名
,T2.部署名
,(
SELECT COUNT(*)
FROM 社員マスタ S1
WHERE S1.部署コード = T2.部署コード
) 所属人数
FROM
社員マスタ T1
INNER JOIN 部署マスタ T2
ON T1.部署コード = T2.部署コード| 社員名 | 部署名 | 所属人数 |
|---|---|---|
| テスト社員1 | 営業部 | 10 |
| テスト社員2 | 営業部 | 10 |
| テスト社員3 | 開発部 | 30 |
| テスト社員4 | 営業部 | 10 |
やり方はいろいろあると思いますが、社員名と部門名は社員単位の値なのに対して、所属人数は部署単位の値なので、複雑な副問い合わせやグループ化などが必要になってくると思います。
分析関数を使用すれば下記のようにシンプルに取得することができます。
SELECT
T1.社員名
,T2.部署名
,COUNT(*) OVER(PARTITION BY T2.部署コード) 所属人数
FROM
社員マスタ T1
INNER JOIN 部門マスタ T2
ON T1.部署コード = T2.部署コード集計関数COUNTの後にOVERを指定して、分析関数として集計します。
OVERの中のPARTITION BYとORDER BYで分析方法を指定することができます。
今回はCOUNTで件数を取得したいだけなのでORDER BYは指定していません。
ROW_NUMBER()で行番号を取得したい場合はORDER BYで、ソート順を指定する必要があります。
今回は部署ごとの集計なのでPARTITION BYで部署コードを集計単位として指定します。部署コードが同じデータは全て同じ値が出力されるはずです。
PARTITION BYを指定しなかった場合は、社員全員の合計人数が出力されます。こちらの場合は全データ同じ値が出力されるはずです。
なので、ページング処理などで、データ全体の件数と、１ページ分のデータが必要な場合、別々にSQLを実行せずに１回のSQLで両方取得することができます。
SELECT
T1.行番号
,T1.社員コード
,T1.社員名
,T1.全件数
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY T1.社員コード) 行番号
,COUNT(*) OVER() 全件数
,T1.社員コード
,T1.社員名
FROM
社員マスタ T1
) T1
WHERE
T1.行番号 BETWEEN 11 AND 20
ORDER BY T1.行番号| 行番号 | 社員コード | 社員名 | 全件数 |
|---|---|---|---|
| 6 | 6 | テスト社員6 | 1500 |
| 7 | 7 | テスト社員7 | 1500 |
| 8 | 8 | テスト社員8 | 1500 |
| 9 | 9 | テスト社員9 | 1500 |
| 10 | 10 | テスト社員10 | 1500 |
PARTITION BYを指定すれば、列ごとに集計単位を変えることができるので、例えば、社員一覧を取得するSQL(社員単位)で、部署内で最年長の年齢・所属人数（部署単位）や全社員で最年長の年齢・所属人数（単位なし）をシンプルに１回のSQLで取得することができます。