【Oracle】近似クエリ（Approximate Query Processing）完全ガイド｜APPROX_COUNT_DISTINCT・APPROX_PERCENTILE で大規模データを高速集計する

-- APPROX_COUNT_DISTINCT: COUNT(DISTINCT) の近似値を返す
-- 大規模テーブルで COUNT(DISTINCT) より大幅に速い

-- 正確な COUNT(DISTINCT)（大規模テーブルでは重い）
SELECT COUNT(DISTINCT customer_id) AS exact_count
FROM orders;

-- 近似 COUNT(DISTINCT)（同等の結果をはるかに速く返す）
SELECT APPROX_COUNT_DISTINCT(customer_id) AS approx_count
FROM orders;
-- 数億件でも数秒以内に結果を返すことが多い

-- GROUP BY との組み合わせ
SELECT
    product_category,
    COUNT(*) AS order_count,
    APPROX_COUNT_DISTINCT(customer_id) AS unique_customers,
    APPROX_COUNT_DISTINCT(product_id)  AS unique_products
FROM orders
WHERE order_date >= DATE '2024-01-01'
GROUP BY product_category
ORDER BY unique_customers DESC;

-- 月別のユニークユーザー数を集計する（ダッシュボード向け）
SELECT
    TRUNC(order_date, 'MM')           AS month,
    APPROX_COUNT_DISTINCT(customer_id) AS monthly_unique_customers
FROM orders
GROUP BY TRUNC(order_date, 'MM')
ORDER BY month;

-- ステップ 1: 日次サマリーテーブルに中間集計（スケッチ）を格納する
CREATE TABLE daily_customer_sketch AS
SELECT
    TRUNC(order_date, 'DD')               AS order_day,
    product_category,
    APPROX_COUNT_DISTINCT_DETAIL(customer_id) AS cust_sketch   -- BLOB として格納
FROM orders
GROUP BY TRUNC(order_date, 'DD'), product_category;

-- ステップ 2: スケッチから月次・週次を再集計する（生データへの再スキャン不要）
SELECT
    TRUNC(order_day, 'MM')                   AS month,
    product_category,
    APPROX_COUNT_DISTINCT_AGG(cust_sketch)    AS monthly_unique_customers
FROM daily_customer_sketch
GROUP BY TRUNC(order_day, 'MM'), product_category
ORDER BY month, product_category;
-- 生データに戻らずに月次集計が可能 → 大幅な高速化

-- APPROX_PERCENTILE: パーセンタイル値の近似値を返す
-- 構文: APPROX_PERCENTILE(percentile_value) WITHIN GROUP (ORDER BY col)

-- 中央値（50パーセンタイル）を取得する
SELECT
    department_id,
    APPROX_PERCENTILE(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    APPROX_PERCENTILE(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary,
    APPROX_PERCENTILE(0.99) WITHIN GROUP (ORDER BY salary) AS p99_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- 正確な PERCENTILE_DISC との比較
SELECT
    APPROX_PERCENTILE(0.5)  WITHIN GROUP (ORDER BY salary) AS approx_median,
    PERCENTILE_DISC(0.5)    WITHIN GROUP (ORDER BY salary) AS exact_median,
    APPROX_PERCENTILE(0.95) WITHIN GROUP (ORDER BY salary) AS approx_p95,
    PERCENTILE_CONT(0.95)   WITHIN GROUP (ORDER BY salary) AS exact_p95
FROM employees;
-- 大規模データでは APPROX_PERCENTILE が大幅に速い

-- APPROX_MEDIAN（Oracle 19c 以降）: APPROX_PERCENTILE(0.5) と同等
-- SELECT APPROX_MEDIAN(salary) FROM employees GROUP BY department_id;

-- DET パラメータ: 誤差限界を指定する（デフォルト: 'ERROR_RATE '0.5''）
SELECT
    APPROX_PERCENTILE(0.95, 'ERROR_RATE 0.1')
        WITHIN GROUP (ORDER BY response_time_ms) AS p95_fast
FROM web_request_logs
WHERE request_date >= DATE '2024-01-01';
-- ERROR_RATE: パーセンタイルランクの最大誤差（0.1 = 0.1 ％ 以内）
-- 誤差を小さくするほど処理時間がかかる（デフォルトの 0.5 が通常は十分）