大規模テーブル集計をSQLで高速化する方法｜パーティションとフィルタ戦略

-- ✅ パーティションプルーニングが効く
WHERE time >= 1704067200              -- Unixタイムスタンプで直接比較
WHERE TD_TIME_RANGE(time, '2024-01-01', '2024-12-31')  -- TD固有の関数
WHERE time >= TD_TIME('2024-01-01')   -- TD_TIME関数で変換

-- ❌ プルーニングが効かない（関数でラップしている）
WHERE FROM_UNIXTIME(time) >= '2024-01-01'  -- timeを変換してから比較
WHERE YEAR(FROM_UNIXTIME(time)) = 2024     -- timeに関数を適用

-- ❌ 遅い・コストが高い：FROM_UNIXTIME で time をラップしている
SELECT
    customer_id,
    SUM(total_amount)  AS total_sales
FROM orders
WHERE FROM_UNIXTIME(time) >= '2024-01-01'
AND   FROM_UNIXTIME(time) <  '2025-01-01'
GROUP BY customer_id;


-- ✅ 速い・安い：TD_TIME_RANGE でパーティションを直接指定
SELECT
    customer_id,
    SUM(total_amount)  AS total_sales
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST')
GROUP BY customer_id;

-- TD_TIME を使ったパーティション絞り込み
WHERE time >= TD_TIME('2024-01-01', 'JST')
AND   time <  TD_TIME('2025-01-01', 'JST')

-- ❌ 全カラムを読む（スキャン量が最大）
SELECT *
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST')
AND status = 'completed';


-- ✅ 必要なカラムだけ読む（スキャン量を削減）
SELECT
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST')
AND status = 'completed';

-- ❌ JOINしてからWHEREで絞る（大量データを結合してから捨てる）
SELECT
    o.customer_id,
    c.rank,
    SUM(o.total_amount)  AS total_sales
FROM orders     o
JOIN customers  c  ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
AND   TD_TIME_RANGE(o.time, '2024-01-01', '2025-01-01', 'JST')
AND   c.rank = 'gold';


-- ✅ サブクエリまたはCTEで先に絞ってからJOINする
WITH completed_orders AS (
    -- まず orders を絞り込む
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'completed'
    AND TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST')
),
gold_customers AS (
    -- customers も先に絞り込む
    SELECT customer_id, rank
    FROM customers
    WHERE rank = 'gold'
)
SELECT
    o.customer_id,
    c.rank,
    SUM(o.total_amount)  AS total_sales
FROM completed_orders   o
JOIN gold_customers     c  ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.rank;

-- ① 日次バッチで「月次顧客別集計テーブル」を作成する（重い処理を1回だけ）
CREATE TABLE monthly_customer_summary AS
SELECT
    DATE_TRUNC('month', order_date)  AS sales_month,
    customer_id,
    COUNT(order_id)                  AS order_count,
    SUM(total_amount)                AS total_spent,
    AVG(total_amount)                AS avg_order_value,
    MAX(order_date)                  AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY
    DATE_TRUNC('month', order_date),
    customer_id;

-- ② 後続の分析クエリは軽量な monthly_customer_summary を参照する
-- RFM分析
SELECT
    customer_id,
    DATE_DIFF('day', MAX(last_order_date), CURRENT_DATE)  AS recency,
    SUM(order_count)                                       AS frequency,
    SUM(total_spent)                                       AS monetary
FROM monthly_customer_summary
WHERE sales_month >= DATE_ADD('month', -12, DATE_TRUNC('month', CURRENT_DATE))
GROUP BY customer_id;

-- COUNT DISTINCTの正確値（重い）
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST');

-- APPROX_DISTINCT（HyperLogLog）で近似値（速い・誤差は1〜2%程度）
SELECT APPROX_DISTINCT(customer_id) AS approx_unique_customers
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST');

-- 正確なパーセンタイル（重い）
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median
FROM orders;

-- APPROX_PERCENTILE（速い）
SELECT APPROX_PERCENTILE(total_amount, 0.5) AS approx_median
FROM orders
WHERE TD_TIME_RANGE(time, '2024-01-01', '2025-01-01', 'JST');

-- ✅ 小さいテーブルを broadcast してシャッフルコストを削減
SELECT /*+ broadcast(c) */
    o.customer_id,
    c.rank,
    SUM(o.total_amount) AS total_sales
FROM orders     o
JOIN customers  c  ON o.customer_id = c.customer_id
WHERE TD_TIME_RANGE(o.time, '2024-01-01', '2025-01-01', 'JST')
AND o.status = 'completed'
GROUP BY o.customer_id, c.rank;