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

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


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

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

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



# 自分の回答
▽記述

```sql
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
```

▽処理の内容



```sql
SELECT SUM(売上額)AS 売上高,MONTH(売上日),sub.商品ID
FROM 売上テーブル
```
　売上額の総額とdateから月のみ、商品ID(外部結合後のテーブル)を選択

```sql
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を関連付ける


```sql
WHERE 売上日 BETWEEN'2017-01-01'AND'2017-12-31'
```
　外部結合で抽出していないTOP3外の抽出条件を、売上日を2017年内と指定


```sql
GROUP BY MONTH(売上日),sub.商品ID
```
　売上月と商品IDでグループ化


```sql
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|

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


# 正答例のひとつ

▽記述例

```sql
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 月,順位
```

▽処理の内容


```sql
SELECT 月,順位,ISNULL(MAX(商品名),'その他') AS 商品名,SUM(sub.売上額) AS 売上高
```
　月と（売上高の）順位
　MAX値をとる商品名のNULL値を「その他」に変換して商品名とする
　sub（結合後のテーブル）での売上額を合計する



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

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

```sql
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年を指定する
→　その他の商品が抽出できる

```sql
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](https://qiita-image-store.s3.amazonaws.com/0/205951/c9f3cf8a-8291-a887-06c5-b67fc78aea00.png)

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


　
　

――今回の記事の参考リンク
[【SQL変換関数】ISNULL　\-　NULL値を変換する　（SQL Server） – SQL Master　データベースエンジニアとLinuxエンジニアのための情報まとめ](http://www.sql-master.net/articles/SQL300.html)
[SQL講座 LEFT JOIN とRIGHT JOIN（表の外部結合）](http://www.pursue.ne.jp/jouhousyo/SQLDoc/select22.html)
[分析関数 ROW\_NUMBER の使用例 \- オラクル・Oracleをマスターするための基本と仕組み](https://www.shift-the-oracle.com/sql/functions/row_number-practice.html)