【SQL】横持ち⇔縦持ちのテーブル変換のやり方をまとめてみた

こんにちは、ミナピピン(@python_mllover)です。今回はBigquery(SQL)で横持ちデータを縦持ちに変換する方法を紹介していきたいと思います。

Contents

縦持ちと横持ち
- 縦持ちデータ
- 横持ちデータ
縦持ちデータを横持ちデータに変換する

横持ちデータを縦持ちデータに変換する

縦持ちと横持ち

縦持ちデータ

縦持ちデータというのが↓のようなやつ

テーブル名：tate_data

名前	趣味名	趣味順
山本	野球	趣味1
山本	サッカー	趣味2
山本	テニス	趣味3
田中	野球	趣味1
田中	テニス	趣味2
田中	将棋	趣味3
鈴木	サッカー	趣味1
鈴木	ゴルフ	趣味2
鈴木	水泳	趣味3
佐藤	テニス	趣味1
佐藤	水泳	趣味2
佐藤	将棋	趣味3

横持ちデータ

テーブル名：yoko_data

名前	趣味1	趣味2	趣味3
山本	野球	サッカー	テニス
田中	野球	テニス	将棋
鈴木	サッカー	ゴルフ	水泳
佐藤	テニス	水泳	将棋

横持ちのデータは可読性が高いですが、データ処理がしにくいです。逆に縦持ちはデータが一列なので処理が行いやすいという利点があります。

縦持ちデータを横持ちデータに変換する

縦持ちデータを横持ちデータに変換するのは合計回数とかならGROUP BYで集計してCASEで各趣味名ごとに集計すればいいだけです。ですが、上記みたいな例だとPARTITION・OVER辺りの集計関数を使用する必要があります。

-- 縦持ちデータを横持ちデータに変換する
SELECT
  FIRST_VALUE(趣味順) 
    OVER (PARTITION BY 名前 ORDER BY 趣味順
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 趣味1
  NTH_VALUE(趣味順, 2) 
    OVER (PARTITION BY 名前 ORDER BY 趣味順
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 趣味2,
  LAST_VALUE(趣味順) 
    OVER (PARTITION BY 名前 ORDER BY 趣味順
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 趣味3
FROM
  tate_data

→分析関数

横持ちデータを縦持ちデータに変換する

横持ちデータの縦持ちへの変換はCROSS JOINとCASE句とUNION句を用いることで簡単にできます

SELECT
  a.user_id,
  CASE WHEN b.index = 1 THEN '趣味1'
       WHEN b.index = 2 THEN '趣味2'
       ELSE '趣味3' END AS 趣味順,
FROM
  yoko_data AS a 
CROSS JOIN
  (
    SELECT
      1 AS index
    UNION ALL
    SELECT
      2 AS index

    UNION ALL
     SELECT
       3 AS index
  ) AS b

まず縦持ちデータは、各ユーザーIDに対して、属性ごとのレコードがあるため、横持ちデータの各レコードを属性数分コピーしてCROSSJOINで元の縦持ちデータに結合します。縦持ちデータは、属性ごとに別のレコードとして指標を取得する必要があるため、CASE文を使って、indexごとに取得する指標を分けます。