SQL演習 特定期間内のデータ集計とトップ３の抽出

SQLの演習として以下の例題を問いてみる。

例題：A商店の売上テーブルから、今年１年を通して売れている商品のTOP3とその月ごとの売上、またTOP3外の商品すべてを合算した売上を抽出するSQL文を書く

A商店の売上テーブルと各データ型↓

売上日	商品ID	商品名	売上額
date	nchar(3)	nchar(10)	money

自分の回答

▽記述

SELECT SUM(売上額)AS 売上高,MONTH(売上日),sub.商品ID
FROM 売上テーブル
LEFT OUTER JOIN (
  SELECT TOP 3 商品ID,MAX(商品名) AS 商品名,SUM(売上額) AS 売上高
  FROM 売上テーブル
  WHERE 売上日 BETWEEN'2017-01-01'AND'2017-12-31'
  GROUP BY 商品ID
  OEDER BY 売上高 DESC
) AS sub ON 売上テーブル.商品ID = sub.商品ID
WHERE 売上日 BETWEEN'2017-01-01'AND'2017-12-31'
GROUP BY MONTH(売上日),sub.商品ID
ORDER BY MONTH(売上日) ASC ,売上高 DESC

▽処理の内容

SELECT SUM(売上額)AS 売上高,MONTH(売上日),sub.商品ID
FROM 売上テーブル

　売上額の総額とdateから月のみ、商品ID(外部結合後のテーブル)を選択

LEFT OUTER JOIN (
SELECT TOP 3 商品ID,MAX(商品名) AS 商品名,SUM(売上額) AS 売上高
FROM 売上テーブル
WHERE 売上日 BETWEEN'2017-01-01'AND'2017-12-31'
GROUP BY 商品ID
OEDER BY 売上高 DESC
) AS sub ON 売上テーブル.商品ID = sub.商品ID

　売上テーブルの外部結合で商品ID、最大値を取る商品名、売上高を選択
　条件は2017年中
　商品IDでグループ化
　グループ化したあとの売上高を降順にする
　→　以上の処理で年間売上TOP3の商品を抽出

　 外部結合で利用したテーブルをsubとし、元のテーブルと商品IDを関連付ける

WHERE 売上日 BETWEEN'2017-01-01'AND'2017-12-31'

　外部結合で抽出していないTOP3外の抽出条件を、売上日を2017年内と指定

GROUP BY MONTH(売上日),sub.商品ID

　売上月と商品IDでグループ化

ORDER BY MONTH(売上日) ASC ,売上高 DESC

　売上月昇順で並び替え、そのなかで売上高の高い順（降順）に並び替え

▽上記の文での表示例

売上高	月	商品ID
20000	1	NULL
5000	1	010
2000	1	020
1000	1	030
～～～	～～～	～～～
23000	12	NULL
7000	12	010
4000	12	020
2000	12	030

この状態であると商品名が表示されず、その他の商品が毎月の最初に表示されてしまう。

正答例のひとつ

▽記述例

SELECT 月,順位,ISNULL(MAX(商品名),'その他') AS 商品名,SUM(sub.売上額) AS 売上高
FROM(
  SELECT ISNULL(ranks.順位,99999) AS 順位,ranks.商品名,MONTH(売上日) AS 月,dx.売上額
  FROM 売上テーブル AS dx
  LEFT OUTER JOIN (
    SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY SUM(売上額) DESC) AS 順位,
    商品ID,MAX(商品名) AS 商品名
    FROM 売上テーブル
    WHERE 売上日 >='2017/01/01' AND 売上日<='2017/12/31'
    GROUP BY 商品ID
  ) AS ranks ON dx.商品ID = ranks.商品ID
  WHERE dx.売上日 >='2017/01/01' AND dx.売上日<='2017/12/31'
) AS sub
GROUP BY 月,順位
ORDER BY 月,順位

▽処理の内容

SELECT 月,順位,ISNULL(MAX(商品名),'その他') AS 商品名,SUM(sub.売上額) AS 売上高

　月と（売上高の）順位
　MAX値をとる商品名のNULL値を「その他」に変換して商品名とする
　sub（結合後のテーブル）での売上額を合計する

FROM(
 SELECT ISNULL(ranks.順位,99999) AS 順位,ranks.商品名,MONTH(売上日) AS 月,dx.売上額
 FROM 売上テーブル AS dx

ranksテーブルの順位カラムがNULL値の場合、順位カラムに99999と表示する
ranksテーブルの商品名、売上月、売上テーブルをdxとして売上額、これらをカラムとする

LEFT OUTER JOIN (
  SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY SUM(売上額) DESC) AS 順位,
  商品ID,MAX(商品名) AS 商品名
  FROM 売上テーブル
  WHERE 売上日 >='2017/01/01' AND 売上日<='2017/12/31'
  GROUP BY 商品ID
) AS ranks ON dx.商品ID = ranks.商品ID__
WHERE dx.売上日 >='2017/01/01' AND dx.売上日<='2017/12/31'
) AS sub

売上額を降順に並べ替えてROW_NUMBERで連番を振り、上位3レコードを抽出
商品ID、商品名カラムの最大値を売上テーブルから選択
条件は売上日が2017年であること
商品IDでグループ化
上記の一連の命令をranksとし、売上テーブルの商品IDと関連付ける
→　これで順位の割り振られた年間売上１～３位の商品が抽出できる
上位3商品以外のその他の抽出条件として、2017年を指定する
→　その他の商品が抽出できる

GROUP BY 月,順位
ORDER BY 月,順位

月、順位の順でグループ化し、並べ替え（省略されているので昇順になる）

　

▽上記の文での表示例

月	順位	商品名	売上高
1	1	A商品	5000
1	2	B商品	2000
1	3	C商品	1000
1	99999	その他	20000
～～～	～～～	～～～	～～～
12	1	A商品	7000
12	2	B商品	4000
12	3	C商品	2000
12	99999	その他	23000

改善のための考え方

• 必要な処理をブロックとして捉える

目的のデータを取り出すために特定の処理を必要とするものを、それぞれブロックとしてひとまとまりにする。すると、処理の流れや必要なことがイメージしやすくなる。SQLを読み解く際、記述する際に有効。

　
　

――今回の記事の参考リンク
【SQL変換関数】ISNULL　-　NULL値を変換する　（SQL Server） – SQL Master　データベースエンジニアとLinuxエンジニアのための情報まとめ
SQL講座 LEFT JOIN とRIGHT JOIN（表の外部結合）
分析関数 ROW_NUMBER の使用例 - オラクル・Oracleをマスターするための基本と仕組み