【KARTE Datahub 実践】エンジニアが解説する機能紹介と大規模SQL実装の裏話

-- 指標20個分のCTEを用意する
-- 3ヶ月連続購入者の6月の閲覧ページと総数
WITH p6_view_events AS (

  SELECT
    ve.uri_url AS p6_view_uri_url,
    COUNT(*) AS p6_view_count
  FROM view_events_all ve
  JOIN purchase_flag_6_8 pf ON ve.user_id = pf.user_id
  WHERE pf.purchase_flag_6_8 = '6〜8月までの3ヶ月連続購入'
  AND sync_date >= TIMESTAMP('2025-06-01') AND sync_date < TIMESTAMP('2025-07-01')
  GROUP BY ve.uri_url
  ORDER BY p6_view_count DESC
  LIMIT 100
),

-- 3ヶ月連続購入者の7月の閲覧ページと総数
p7_view_events AS (
  SELECT
    ve.uri_url AS p7_view_uri_url,
    COUNT(*) AS p7_view_count
  FROM view_events_all ve
  JOIN purchase_flag_6_8 pf ON ve.user_id = pf.user_id
  WHERE pf.purchase_flag_6_8 = '6〜8月までの3ヶ月連続購入'
  AND sync_date >= TIMESTAMP('2025-07-01') AND sync_date < TIMESTAMP('2025-08-01')
  GROUP BY ve.uri_url
  ORDER BY p7_view_count DESC
  LIMIT 100
),
-- 中略 

-- UNION ALLで各CTEテーブルを結合するためにダミーカラムを生成し、結合する
SELECT
  p6_view_uri_url,
  p6_view_count,
  CAST(NULL AS STRING) AS p7_view_uri_url,
  CAST(NULL AS NUMERIC) AS  p7_view_count,
  CAST(NULL AS STRING) AS p8_view_uri_url,
  CAST(NULL AS NUMERIC) AS p8_view_count,
  CAST(NULL AS STRING) AS e7_exit_page,
  CAST(NULL AS NUMERIC) AS e7_exit_count,
  CAST(NULL AS STRING) AS e8_exit_page,
  CAST(NULL AS NUMERIC) AS e8_exit_count,
  CAST(NULL AS STRING) AS init_utm_campaign,
  CAST(NULL AS NUMERIC) AS init_utm_user_count,
  CAST(NULL AS STRING) AS init_medium,
  CAST(NULL AS NUMERIC) AS init_medium_user_count,
  CAST(NULL AS STRING) AS init_content,
  CAST(NULL AS NUMERIC) AS init_content_user_count,
  CAST(NULL AS STRING) AS init_source,
  CAST(NULL AS NUMERIC) AS init_source_user_count,
  CAST(NULL AS STRING) AS init_term,
  CAST(NULL AS NUMERIC) AS init_term_user_count,
  CAST(NULL AS STRING) AS july_utm_campaign,
  CAST(NULL AS NUMERIC) AS july_utm_user_count,
  CAST(NULL AS STRING) AS july_medium,
  CAST(NULL AS NUMERIC) AS july_medium_user_count,
  CAST(NULL AS STRING) AS july_content,
  CAST(NULL AS NUMERIC) AS july_content_user_count,
  CAST(NULL AS STRING) AS july_source,
  CAST(NULL AS NUMERIC) AS july_source_user_count,
  CAST(NULL AS STRING) AS july_term,
  CAST(NULL AS NUMERIC) AS july_term_user_count,
  CAST(NULL AS STRING) AS august_utm_campaign,
  CAST(NULL AS NUMERIC) AS august_utm_user_count,
  CAST(NULL AS STRING) AS august_medium,
  CAST(NULL AS NUMERIC) AS august_medium_user_count,
  CAST(NULL AS STRING) AS august_content,
  CAST(NULL AS NUMERIC) AS august_content_user_count,
  CAST(NULL AS STRING) AS august_source,
  CAST(NULL AS NUMERIC) AS august_source_user_count,
  CAST(NULL AS STRING) AS august_term,
  CAST(NULL AS NUMERIC) AS august_term_user_count
FROM p6_view_events

UNION ALL

SELECT
  CAST(NULL AS STRING) AS p6_view_uri_url,
  CAST(NULL AS NUMERIC) AS p6_view_count,
  p7_view_uri_url,
  p7_view_count,
  CAST(NULL AS STRING) AS p8_view_uri_url,
  CAST(NULL AS NUMERIC) AS p8_view_count,
  CAST(NULL AS STRING) AS e7_exit_page,
  CAST(NULL AS NUMERIC) AS e7_exit_count,
  CAST(NULL AS STRING) AS e8_exit_page,

-- 中略

Copy