0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

【BigQuery】カンマ区切り文字列を分割して縦持ちにする(SPLIT/UNNEST/CROSS JOIN)

Last updated at Posted at 2024-02-12

やりたいこと

カラムcustomerに顧客名、カラムordersにカンマ区切りで注文商品名が格納されているテーブルを想定します。カンマ区切りの注文商品名を分割して縦持ちのデータに変換する方法を述べます。

image.png

結論

SPLITでカンマ区切り文字列を配列に変換します。
その後、UNNESTとCROSS JOINを組み合わせて配列の要素をテーブルの行に変換します。

WITH
  test_table AS (
  /*
    テスト用テーブルの作成
  */
    SELECT 'Aさん' AS customer, 'コーヒー,チョコレート' AS orders
    UNION ALL
    SELECT 'Bさん' AS customer, '紅茶,クッキー' AS orders
    UNION ALL
    SELECT 'Cさん' AS customer, 'コーヒー' AS orders
    UNION ALL
    SELECT 'Dさん' AS customer, 'コーヒー,チョコレート,クッキー' AS orders
    UNION ALL
    SELECT 'Eさん' AS customer, NULL AS orders
  )

SELECT
  customer
  ,split_order

FROM
  test_table

CROSS JOIN
  UNNEST(SPLIT(orders,',')) AS split_order

BigQuery公式:配列内の要素をテーブル内の行に変換する
BigQuery公式:SPLIT関数

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?