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をするクエリを作成

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文を使用して商品名ごとにカラムを作る

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|2|0|0|
|2021/08/01|メーカーA|0|2|0|
|2021/08/01|メーカーB|3|0|0|
|2021/08/01|メーカーB|0|2|0|
|2021/08/01|メーカーB|0|0|2|

最終的にほしいのは上記テーブルの入荷日時、メーカ毎の、商品名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関数で足し上げる

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条件を記載
• サブクエリを使用して、もととなるデータを抽出する