Mysqlで行列変換してみる

## 概要

DBテーブルのデータの持ち方を変えてrowデータをcolumnに出したい(`出力したいクエリ結果`)、
要望があった際に調べて作成した手順とクエリを記載

### テーブルとクエリで出力する内容

- 1日に、どこのメーカーから何時になんの商品が何個入ったかの`stock`テーブルを用意
- 最終的に`出力したいクエリ結果`テーブルのように、縦に持っていたデータを横のデータに整形して出力する(pivot)。

## テーブル

- stockテーブル

|stock_time|maker_name|item|item_count|
|---|---|---|---|---|
|2021/08/01　10:00:00|メーカーA|チョコ|10|
|2021/08/01　15:00:00|メーカーA|チョコ|5|
|2021/08/01　18:00:00|メーカーA|チョコ|3|
|2021/08/01　10:00:00|メーカーA|アイス|5|
|2021/08/01　15:00:00|メーカーA|アイス|10|
|2021/08/01　10:00:00|メーカーB|チョコ|2|
|2021/08/01　15:00:00|メーカーB|チョコ|4|
|2021/08/01　18:00:00|メーカーB|チョコ|6|
|2021/08/01　10:00:00|メーカーB|アイス|2|
|2021/08/01　15:00:00|メーカーB|アイス|3|
|2021/08/01　10:00:00|メーカーB|クッキー|10|
|2021/08/01　15:00:00|メーカーB|クッキー|20|


- 出力したいクエリ結果

|stock_time|maker_name|チョコ|アイス|クッキー|
|---|---|---|---|---|---|---|---|
|2021/08/01|メーカーA|3|2|0|
|2021/08/01|メーカーB|3|2|2|


## 手順

### 1. 納入履歴テーブルで、日ごと、メーカ、商品ごとにGroupByをするクエリを作成

```sql
SELECT
	DATA_FORMAT (`stock_time`,'%Y-%m-%d'),
	`maker_name`,
	`item`,
	COUNT(`item`)
FROM
	`stock`
GROUP BY
	DATE_FORMAT(`stock_time`, '%Y-%m-%d'), -- h:m:sの　timeのデータがGroupByする際に不要なためDATA_FORMATで整形
	`maker_name`,
	`item`;
```

以下テーブルが出力される

|stock_time|maker_name|item|item_count|
|---|---|---|---|---|
|2021/08/01|メーカーA|チョコ|3|
|2021/08/01|メーカーA|アイス|2|
|2021/08/01|メーカーB|チョコ|3|
|2021/08/01|メーカーB|アイス|2|
|2021/08/01|メーカーB|クッキー|2|

### 2. 各商品の納入回数を横データにするためにCASE文を使用して商品名ごとにカラムを作る

```sql
SELECT
	DATE_FORMAT(`stock_time`, '%Y-%m-%d'),
	`maker_name`,
	-- `item`,　横列に追加されるので不要になるカラム
	-- COUNT(`maker_name`) count,　横列に追加されるので不要になる
-- 上で作成したクエリに追加: itemカラム`WHEN`で指定したitemだったら、横列に`AS`で命名したセルに`THEN`で指定したものを表示する　
	CASE item
	WHEN "アイス" THEN
		count(item)
	ELSE
		0
	END AS "アイス",
	CASE item
	WHEN "チョコ" THEN
		count(item)
	ELSE
		0
	END AS "チョコ",
	CASE item
	WHEN "クッキー" THEN
		count(item)
	ELSE
		0
	END AS "クッキー"
-- ↑ここまでが、縦データを横列につけるクエリ
FROM
	`stock`
GROUP BY
	DATE_FORMAT(`stock_time`, '%Y-%m-%d'),
	`maker_name`,
	`item`;
```

以下のようなテーブルが作成される

|stock_time|maker_name|チョコ|アイス|クッキー|
|---|---|---|---|---|---|---|
|2021/08/01|メーカーA|<font color="Red">2</fonr>|0|0|
|2021/08/01|メーカーA|0|<font color="Red">2</fonr>|0|
|2021/08/01|メーカーB|<font color="Red">3</fonr>|0|0|
|2021/08/01|メーカーB|0|<font color="Red">2</fonr>|0|
|2021/08/01|メーカーB|0|0|<font color="Red">2</fonr>|

最終的にほしいのは上記テーブルの入荷日時、メーカ毎の、`商品名`columnを足し上げたもの。(赤の数字)
- メーカーAのチョコの入荷回数：　2 + 0
- メーカーAのアイスの入荷回数：　0 + 2
- メーカーAのクッキーの入荷回数：　0 + 0
...


- 出力したいクエリ結果

|stock_time|maker_name|item|チョコ|アイス|クッキー|
|---|---|---|---|---|---|---|---|
|2021/08/01|メーカーA|チョコ|3|2|0|
|2021/08/01|メーカーB|チョコ|3|2|2|

### 3. 2で作成したクエリをサブクエリとして、使用し、SUM関数で足し上げる

```sql
SELECT
	s.stock_time, -- サブクエリで出したsのstock_timeを出力するため`s.xxx`
	s. `maker_name`,
	SUM(`チョコ`) `チョコ`,
	SUM(`アイス`) `アイス`,
	SUM(`クッキー`) `クッキー`
FROM (
	SELECT
		DATE_FORMAT(`stock_time`, '%Y-%m-%d') stock_time,
		`maker_name`,
		CASE item
		WHEN "アイス" THEN
			count(item)
		ELSE
			0
		END AS "アイス",
		CASE item
		WHEN "チョコ" THEN
			count(item)
		ELSE
			0
		END AS "チョコ",
		CASE item
		WHEN "クッキー" THEN
			count(item)
		ELSE
			0
		END AS "クッキー"
	FROM
		`stock`
		-- h:m:sの　timeのデータが不要なためDATA_FORMATで整形
	GROUP BY
		DATE_FORMAT(`stock_time`, '%Y-%m-%d'),
		`maker_name`,
		`item`) AS s -- サブクエリで出力したテーブルをsとする
GROUP BY
	s.stock_time,
	s.maker_name;
```

`出力したいクエリ結果`が作成される

|stock_time|maker_name|item|チョコ|アイス|クッキー|
|---|---|---|---|---|---|---|---|
|2021/08/01|メーカーA|チョコ|3|2|0|
|2021/08/01|メーカーB|チョコ|3|2|2|


## やったことのまとめ

- CASEを使用して、追加したいcolumn条件を記載
- サブクエリを使用して、もととなるデータを抽出する