ITコーディネータのシュウです。
埼玉県加須市にある、加須花崎水上公園の散歩道です。プールと自然観察園の間のほうの道で撮った写真になります。水上公園ということで、夏のプールが注目されますが、お花見やバーベキュー、また自然観察園ではいろいろな木々やウッドデッキ、野鳥の観察小屋などもあり、季節によっていろいろな表情が見れるので、私はとても気に入っています。
＜本日の題材＞
複数行のデータを集計して横展開
今回は、テーブルレイアウト上、キー毎に複数行あるデータを、横に並べて集計しながら1行で表示させたい場合のSQLについて取り上げてみたいと思います。（以前、カンマ区切りで横並びにするケースについて、SQL Serverの場合にFOR XML句を取り上げたことがありますが、今回はよく使う例としてCASE文を使用します）
例えば、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケース。(SQL Serverで検証)
DB上は以下のようにデータが登録されています。
SELECT
部品コード
, SUBSTRING(年月日,1,6) AS 年月
, SUM(数量) AS 仕入数量
FROM 部品発注表
GROUP BY 部品コード, SUBSTRING(年月日,1,6)
ORDER BY 部品コード, SUBSTRING(年月日,1,6);
これを、部品コード毎で、月を横に並べて仕入数量を表示させたい場合、以下のようなSQLを実行します。
SELECT
A.部品コード
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '04' THEN A.数量 ELSE 0 END) AS "4月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '05' THEN A.数量 ELSE 0 END) AS "5月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '06' THEN A.数量 ELSE 0 END) AS "6月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '07' THEN A.数量 ELSE 0 END) AS "7月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '08' THEN A.数量 ELSE 0 END) AS "8月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '09' THEN A.数量 ELSE 0 END) AS "9月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '10' THEN A.数量 ELSE 0 END) AS "10月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '11' THEN A.数量 ELSE 0 END) AS "11月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '12' THEN A.数量 ELSE 0 END) AS "12月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '01' THEN A.数量 ELSE 0 END) AS "1月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '02' THEN A.数量 ELSE 0 END) AS "2月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '03' THEN A.数量 ELSE 0 END) AS "3月"
FROM [dbo].[部品発注表] A
JOIN [dbo].[年度マスタ] B ON SUBSTRING(A.年月日,1,6) = B.年月
WHERE B.年度 = '2014'
GROUP BY A.部品コード
ORDER BY A.部品コード;
このように、部品コード毎に、4月～3月まで月毎の仕入数量を横並びで表示させることができました。
上記では年度マスタを以下のように設定しています。
SELECT * FROM [dbo].[年度マスタ]
ORDER BY 年度,表示順;
ORACLEでも同様に行うことができますが、上記のSUBSTRING関数は、ORACLEではSUBSTR関数となります。そこだけ注意すれば同様に実行できます。
また、上記の例は、数量の場合でしたが、文字項目を横並びにしたい場合もあると思います。
例えば、下記のように、日本の様々なランキングをデータとして持つテーブルがあるとします。
SELECT
A.区分NO
, B.区分名
, A.名称
, A.数値
, B.単位
FROM dbo.ランキング A
JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
ORDER BY A.区分NO, A.NO;
※データはもっとたくさんありますが、表示は上記まで
これらの都市人口や川の長さ、湖の面積、山の高さなどの区分ごとに、ランキングの1位から10位までを横並びで表示させたいとします。
まず、各区分毎にランキング情報を抽出するのは、以下：
SELECT
A.区分NO
, B.区分名
, A.名称
, RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
, A.数値
, B.単位
FROM dbo.ランキング A
JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
ORDER BY A.区分NO, RANK順位;
※データはもっとたくさんありますが、表示は上記まで
これを各区分毎に横並びで表示するのは、例えば以下のSQLのように、MAX関数などを使用して集約したかたちにします。
SELECT
C.区分NO
, C.区分名
, MAX(CASE C.RANK順位 WHEN 1 THEN C.名称 ELSE '' END)
AS "1位"
, MAX(CASE C.RANK順位 WHEN 2 THEN C.名称 ELSE '' END)
AS "2位"
, MAX(CASE C.RANK順位 WHEN 3 THEN C.名称 ELSE '' END)
AS "3位"
, MAX(CASE C.RANK順位 WHEN 4 THEN C.名称 ELSE '' END)
AS "4位"
, MAX(CASE C.RANK順位 WHEN 5 THEN C.名称 ELSE '' END)
AS "5位"
, MAX(CASE C.RANK順位 WHEN 6 THEN C.名称 ELSE '' END)
AS "6位"
, MAX(CASE C.RANK順位 WHEN 7 THEN C.名称 ELSE '' END)
AS "7位"
, MAX(CASE C.RANK順位 WHEN 8 THEN C.名称 ELSE '' END)
AS "8位"
, MAX(CASE C.RANK順位 WHEN 9 THEN C.名称 ELSE '' END)
AS "9位"
, MAX(CASE C.RANK順位 WHEN 10 THEN C.名称 ELSE '' END)
AS "10位"
FROM
(SELECT
A.区分NO
, B.区分名
, A.名称
, RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
, A.数値
FROM dbo.ランキング A
JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
) C
GROUP BY C.区分NO, C.区分名
ORDER BY C.区分NO;
※最近の統計では、最上川と木曽川はどちらも229kmで同じ7位となっているようですが、以前は最上川が7位、木曽川が8位とされていたようです。今回は、取りあえずそちらを使っています。
上記は、ORACLEでもまったく同様に実行することができます。
今日は以上まで
にほんブログ村