04 รันคิวรีวิเคราะห์ตลาด

-- Approximate count of distinct timestamps (HyperLogLog)
SELECT uniq(datetime) AS distinct_ts FROM forex;

-- Exact count — precise, but scans every value
SELECT uniqExact(datetime) AS distinct_ts FROM forex;

-- Adaptive + tunable accuracy vs memory
SELECT uniqCombined(datetime) AS distinct_ts FROM forex;

-- One row: an array of the 8 most actively quoted pairs (by tick count)
SELECT topK(8)(pair) AS most_active FROM forex;

-- One row per day: gold (XAU/USD) open, high, low, close — a candlestick chart
SELECT
    toDate(datetime)      AS day,
    argMin(bid, datetime) AS open,    -- bid at the day's first tick
    max(bid)              AS high,
    min(bid)              AS low,
    argMax(bid, datetime) AS close    -- bid at the day's last tick
FROM forex
WHERE base = 'XAU' AND quote = 'USD'
GROUP BY day
ORDER BY day;

-- One row per pair: median and 99th-percentile bid/ask spread (tighter = more liquid)
SELECT
    pair,
    round(quantile(0.5)(ask - bid), 6)  AS median_spread,
    round(quantile(0.99)(ask - bid), 6) AS p99_spread
FROM forex
GROUP BY pair
ORDER BY median_spread ASC;

-- One row per pair: avg spread during the active window vs quiet hours
SELECT
    pair,
    count()                                                           AS ticks,
    round(avgIf(ask - bid, toHour(datetime) BETWEEN 7 AND 20), 6)     AS spread_active,
    round(avgIf(ask - bid, toHour(datetime) NOT BETWEEN 7 AND 20), 6) AS spread_quiet
FROM forex
GROUP BY pair
ORDER BY ticks DESC;

-- One row per pair: the latest quoted bid and the timestamp it was seen
SELECT
    pair,
    argMax(bid, datetime) AS last_bid,
    max(datetime)         AS as_of
FROM forex
GROUP BY pair
ORDER BY pair;

-- Optimized: base+quote ARE the leading sort key — the index skips to that pair
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';

-- Non-optimized: bid is NOT in the sort key — no index, scans all 26.5M ticks.
-- (cache off so the full scan shows on every run)
SELECT count() FROM forex WHERE bid > 1.5
SETTINGS use_query_condition_cache = 0;

-- Optimized: the primary index skips straight to the XAU/USD rows
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';

-- Non-optimized: bid isn't in the sort key, so nothing can be skipped
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE bid > 1.5;