SQL
SQLServer

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

More than 1 year has passed since last update.

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

例題:A商店の売上テーブルから、今年1年を通して売れている商品の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と関連付ける
→ これで順位の割り振られた年間売上1~3位の商品が抽出できる
上位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

改善のための考え方

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

Untitled (1).png

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

 
 

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