Zenn
🔍

SQLで定数定義

2024/05/23に公開
SQL
BigQuery
小ネタ
TROCCO
tech

はじめに

複数のログデータを結合する時、あらかじめ日付などの条件で絞ること、クエリのパフォーマンス向上が見込める。あと、troccoのカスタム変数を入れるときに一箇所に設定したいモチベーションがある

  • troccoのカスタム変数

https://documents.trocco.io/docs/custom-variables

実装

※クエリはイメージです。

WITH 
const AS (
  -- Define constants
  SELECT
    CAST('2023-09-01' AS TIMESTAMP) AS start_date,
    CAST('2023-10-01' AS TIMESTAMP) AS end_date
),

google_adgroup_data AS (
  SELECT 
    segments_date AS date,
    ad_group_name AS google_ad_group_name,
    metrics_impressions AS google_impressions,
    metrics_clicks AS google_clicks,
    metrics_cost_micros AS google_cost_micros
  FROM `analytics-prd-pjt.dwh_ads.google_adgroup_report`, const
  WHERE date_trunc(segments_date, day) BETWEEN start_date AND end_date
),

yahoo_adgroup_data AS (
  SELECT 
    segments_date AS date,
    ad_group_name AS yahoo_ad_group_name,
    metrics_impressions AS yahoo_impressions,
    metrics_clicks AS yahoo_clicks,
    metrics_cost_micros AS yahoo_cost_micros
  FROM `analytics-prd-pjt.dwh_ads.yahoo_adgroup_report`, const
  WHERE date_trunc(segments_date, day) BETWEEN start_date AND end_date
)

SELECT
  g.date,
  g.google_ad_group_name,
  y.yahoo_ad_group_name,
  g.google_impressions,
  y.yahoo_impressions,
  g.google_clicks,
  y.yahoo_clicks,
  g.google_cost_micros,
  y.yahoo_cost_micros
FROM google_adgroup_data g
FULL OUTER JOIN yahoo_adgroup_data y
ON g.date = y.date AND g.google_ad_group_name = y.yahoo_ad_group_name

troccoのカスタム変数を入れる場合

WITH 
const AS (
  -- Define constants
  SELECT
    CAST('$start_date$' AS TIMESTAMP) AS start_date,
    CAST('$end_date$' AS TIMESTAMP) AS end_date
)

アドホックに分析（BIのフィルター的な）

WITH
const AS (
  -- Define constants
  SELECT '10000' AS company_id -- 空欄でもエラーになりません
)

SELECT *
FROM `analytics-prd-pjt.dwh_ads.google_adgroup_report` AS report, const
WHERE
  1 = 1
  AND CAST(report.company_id AS STRING) LIKE CONCAT('%',CAST(const.company_id AS STRING),'%')

おわりに

きっと、もっと良い方法があるでしょう。
ご教授お願いします。
日付以外も使えます。小ネタでした。

Discussion