前年比・前月比の使い所と、巨大テーブルでもクエリを重くしない設計

SELECT  month,  revenue,  LAG(revenue, 1)  OVER (ORDER BY month) AS revenue_prev_month,  LAG(revenue, 12) OVER (ORDER BY month) AS revenue_prev_year,  SAFE_DIVIDE(revenue, LAG(revenue, 1)  OVER (ORDER BY month)) - 1 AS mom,  SAFE_DIVIDE(revenue, LAG(revenue, 12) OVER (ORDER BY month)) - 1 AS yoyFROM monthly_revenueORDER BY month;

-- models/daily_kpi.sql{{ config(materialized='incremental', unique_key='date') }}
SELECT  DATE(event_timestamp) AS date,  COUNT(DISTINCT user_id) AS dau,  SUM(amount) AS revenue,  COUNT(*) AS event_countFROM {{ ref('events_partitioned') }}{% if is_incremental() %}  -- 増分実行: 過去2日分だけを再計算（遅延データを許容）  WHERE DATE(event_timestamp) >= (SELECT MAX(date) FROM {{ this }}) - 1{% endif %}GROUP BY date

CREATE MATERIALIZED VIEW project.dataset.daily_kpi_mv ASSELECT  DATE(event_timestamp) AS date,  APPROX_COUNT_DISTINCT(user_id) AS dau_approx,  SUM(amount) AS revenueFROM project.dataset.events_partitionedGROUP BY date;
-- レポート側はこれを使うだけSELECT  date, dau_approx, revenue,  LAG(revenue, 365) OVER (ORDER BY date) AS revenue_prev_yearFROM project.dataset.daily_kpi_mvWHERE date >= CURRENT_DATE() - 30;

-- 日次 sketch を作って永続化CREATE OR REPLACE TABLE daily_user_hll ASSELECT  DATE(event_timestamp) AS date,  HLL_COUNT.INIT(user_id) AS user_hllFROM events_partitionedGROUP BY date;
-- 月次 ユニークユーザー（再集計）はマージするだけSELECT  DATE_TRUNC(date, MONTH) AS month,  HLL_COUNT.MERGE(user_hll) AS mau_approxFROM daily_user_hllGROUP BY month;