BigQuery SQL

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

この記事は約4分で読めます。

 

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

 

縦持ちと横持ち

 

縦持ちデータ

 

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

 

テーブル名: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ごとに取得する指標を分けます。

 

 

 

 

 

 


プログラミング・スクレイピングツール作成の相談を受け付けています!

クラウドワークス・ココナラ・MENTAなどでPython・SQL・GASなどのプログラミングに関する相談やツール作成などを承っております!

過去の案件事例:

  • Twitter・インスタグラムの自動化ツール作成
  • ウェブサイトのスクレイピングサポート
  • ダッシュボード・サイト作成
  • データエンジニア転職相談

これまでの案件例を見る

キャリア相談もお気軽に!文系学部卒からエンジニア・データサイエンティストへの転職経験をもとに、未経験者がどう進むべきかのアドバイスを提供します。


スポンサーリンク
/* プログラミング速報関連記事一覧表示 */
ミナピピンの研究室

コメント

タイトルとURLをコピーしました