グルーヴノーツ コンサルタントの吉村です。
これまでのブログとは打って変わってデータ統合・データ加工に関するお話です。

これはシリーズ化して何本か書いていこうと思います。

---

1.縦横変換・横縦変換とは

縦横変換・横縦変換というのが一般的に正式な名前化と言われるとちょっと自信がありませんが、
よく「どうやってやったらいいですか？」と相談を受けますし、この言葉でだいたい伝わるのでこのまま進めます。
ちなみに英語ではPivot/Unpivotと言うみたいです。Excel/スプレッドシートのピボットテーブルがあるのでこちらはピンと来るかもしれないですね。

 

縦横変換

言葉通り縦に持っているデータを横にします。下図のイメージの変換ですね。
左の都道府県毎かつ月毎に12ヶ月分を持っているデータを、右の都道府県ごとで列に12ヶ月を持つデータに変換するようなパターンです。

 

横縦変換

続いて先程の逆で横に持っているデータを縦にします。下図のイメージの変換ですね。
左の都道府県ごとで列に12ヶ月を持っているデータを、右の都道府県毎かつ月毎に12ヶ月分を持つデータに変換するようなパターンです。

このような縦→横と横→縦のクエリーによるデータ加工の考え方と書き方を説明していきます。
最近のデータベースによってはPivot/Unpivotの関数があったりしますが、
BigQueryのようにまだ無いデータベースもあるので引き出しは多いに越したことありません。

---

2.縦横変換の考え方とクエリーの書き方

まずは縦→横にデータを変換する際の考え方です。
先程の例で月の部分を縦→横なので、月の値ごとにハンドリングを行います。

具体的には下図のような2ステップで変換を行います。

 

ステップ1：月ごとに値を持つ列をズラす

都道府県毎に12レコードの月のデータがあります。この月毎の12件を「◯月だったら」と条件を付けて取り扱います。

month=01月だったらvalueをセットし、そうでない場合は何もセットしない。

というような条件をこの例であれば12ヶ月分実施します。すると1ステップ後のような斜めに値が入った表ができあがります。

 

ステップ2：1件に集約する

先程の右表のように列ごとに該当月の値しかセットされていない状態の表ができあがれば後は集約するだけです。

集約には数値であればMaxでもSumでもできますし、文字であればMaxを使います。
すると2ステップ後の都道府県毎に1件のレコードができあがります。

 

クエリの書き方

続いてクエリの書き方です。

まずステップ1の部分ですが、 case にて month = "01月" と条件を指定し、
一致する場合には value をセットし一致しない場合には null をセットしています。
これを12ヶ月分実施すると、前図のステップ1を経たの表の状態となります。

	select
	  prefecture,
	  month,
	  case when month = "01月" then value else null end as jan,
	  case when month = "02月" then value else null end as feb,
	  case when month = "03月" then value else null end as mar,
	  case when month = "04月" then value else null end as apl,
	  case when month = "05月" then value else null end as may,
	  case when month = "06月" then value else null end as jun,
	  case when month = "07月" then value else null end as jul,
	  case when month = "08月" then value else null end as aug,
	  case when month = "09月" then value else null end as sep,
	  case when month = "10月" then value else null end as oct,
	  case when month = "11月" then value else null end as nov,
	  case when month = "12月" then value else null end as dec
	from blog.vertical_table;

続いてステップ2の部分ですが、集約を行いますので month の列を削除して、
集計関数である sum を各項目に指定するとともに group by 1を指定するだけです。
これだけで縦→横へのデータの変換を行うことができます。

	select
	  prefecture,
	  sum(case when month = "01月" then value else null end) as jan,
	  sum(case when month = "02月" then value else null end) as feb,
	  sum(case when month = "03月" then value else null end) as mar,
	  sum(case when month = "04月" then value else null end) as apl,
	  sum(case when month = "05月" then value else null end) as may,
	  sum(case when month = "06月" then value else null end) as jun,
	  sum(case when month = "07月" then value else null end) as jul,
	  sum(case when month = "08月" then value else null end) as aug,
	  sum(case when month = "09月" then value else null end) as sep,
	  sum(case when month = "10月" then value else null end) as oct,
	  sum(case when month = "11月" then value else null end) as nov,
	  sum(case when month = "12月" then value else null end) as dec
	from blog.vertical_table
	group by 1;

---

3.横縦変換の考え方とクエリーの書き方

続いては横→縦にデータを変換する際の考え方です。
先程の例で月の部分を横→縦なので、同じように月の値ごとにハンドリングしていきます。
ただ横→縦はレコード数を増やす（1件→12件）のでちょっと発想の転換が必要です。

具体的には下図のような2ステップで変換を行います。
ここでは、テーブル結合が発生している点がポイントです。

 

ステップ1：1件を必要なレコード件数に増幅する

都道府県毎に1レコードずつ存在し、1レコードの中に12ヶ月のデータを列で保有しています。
まずこの1レコードを12レコードにします。
12レコードにするには12レコード持ってるテーブルとCROSS JOINすることでレコードを増幅させることができます。

 

ステップ2：複数列に持ってるデータを1列にまとめる

先程の右表のように列ごとに該当月の値しかセットされていない状態の表ができあがれば後は集約するだけです。
先程の下表のように12レコードにすることができれば、後はmonthの値ごとに列をまとめていくことで都道府県毎の12レコードにすることができます。

 

クエリの書き方

続いてクエリの書き方です。

まずステップ1の部分ですが、with句の select month from unnest(generate_array(1,12) にて
1〜12の値が入った仮想テーブル（前述の②の表）が生成されます。
それと都道府県ごとのテーブルをCROSS JOINすることで12倍されたデータが組み立てられます。

	with t as (select month from unnest(generate_array(1,12)) as month)
	select
		month,
	  prefecture,
	  jan,
	  feb,
	  mar,
		apl
	  may,
	  jun,
	  jul,
	  aug,
	  sep,
	  oct,
	  nov,
	  dec
	from blog.horizontal_table
	cross join t;

続いてステップ2の部分ですが、12個の列を1列に黄色い部分をまとめますので case を使い month = 1なら1月の列を、
というように12列分の条件式を記載し1列にしてしまいます。
これだけで横→縦へのデータの変換を行うことができます。
今回は12ヶ月の月のデータですが、商品分類などであればcaseの際に商品A,B,C,その他のようなまとめ方もすることができます。

	with t as (select month from unnest(generate_array(1,12)) as month)
	select
	  prefecture,
	  month,
	  case
	    when month =  1 then jan
	    when month =  2 then feb
	    when month =  3 then mar
	    when month =  4 then apl
	    when month =  5 then may
	    when month =  6 then jun
	    when month =  7 then jul
	    when month =  8 then aug
	    when month =  9 then sep
	    when month = 10 then oct
	    when month = 11 then nov
	    when month = 12 then dec
	  end as value
	from blog.horizontal_table
	cross join t;

---

いかがでしたでしょうか？
知ってしまえばなんてことのないデータ加工方法ですが、知らないと「なるほど」となったのではないでしょうか？
この手のデータ加工や蓄積などの記事をシリーズ的に書いていこうと思うのでぜひ引き続き読んでいただければと思います。

---

※本ブログの内容や紹介するサービス・機能は、掲載時点の情報です。