벤치마크 쿼리

Trino 성능을 측정하기 위한 쿼리 예제입니다. perf 스케일(5천만 거래)에서 실행을 권장합니다.

대량 집계 -- 전체 거래 통계

SELECT
    status,
    channel,
    COUNT(*)                       AS txn_count,
    CAST(SUM(amount) AS BIGINT)    AS total_amount,
    CAST(AVG(amount) AS BIGINT)    AS avg_amount,
    MIN(amount)                    AS min_amount,
    MAX(amount)                    AS max_amount
FROM iceberg.card.transactions
GROUP BY status, channel
ORDER BY txn_count DESC;

윈도우 함수 -- 고객별 누적 매출

SELECT
    customer_id,
    txn_datetime,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY CAST(txn_datetime AS TIMESTAMP)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM iceberg.card.transactions
WHERE status = 'approved'
LIMIT 1000;

다중 JOIN -- Federation 크로스 카탈로그

SELECT
    c.customer_grade,
    m.category,
    COUNT(*)                       AS txn_count,
    CAST(SUM(t.amount) AS BIGINT)  AS total_amount
FROM iceberg.card.transactions t
JOIN sourcedb.public.customers c ON t.customer_id = c.customer_id
JOIN iceberg.card.merchants m ON t.merchant_id = m.merchant_id
WHERE t.status = 'approved'
GROUP BY c.customer_grade, m.category
ORDER BY total_amount DESC
LIMIT 50;

서브쿼리 -- 평균 이상 거래 고객

SELECT
    customer_id,
    COUNT(*)                       AS txn_count,
    CAST(AVG(amount) AS BIGINT)    AS avg_amount
FROM iceberg.card.transactions
WHERE status = 'approved'
GROUP BY customer_id
HAVING AVG(amount) > (
    SELECT AVG(amount) FROM iceberg.card.transactions WHERE status = 'approved'
)
ORDER BY avg_amount DESC
LIMIT 100;

MCC 코드별 DISTINCT 카운트

SELECT
    mcc_code,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT merchant_id) AS unique_merchants,
    COUNT(*)                    AS txn_count
FROM iceberg.card.transactions
WHERE mcc_code IS NOT NULL
GROUP BY mcc_code
ORDER BY txn_count DESC;