ClickHouse Cheat Sheet

CREATE TABLE events(    event_date Date,    event_time DateTime,    user_id UInt64,    event_type LowCardinality(String),    properties String)ENGINE = MergeTreePARTITION BY toYYYYMM(event_date)ORDER BY (event_type, user_id, event_time)TTL event_date + INTERVAL 90 DAYSETTINGS index_granularity = 8192;

-- Approximate distinct count (fast, low memory)SELECT uniqCombined(user_id) FROM events WHERE event_date >= today() - 7;-- Array-based funnel-style aggregationSELECT event_type, count() AS cntFROM eventsWHERE event_date >= today() - 1GROUP BY event_typeORDER BY cnt DESCLIMIT 10;-- Quantiles in one passSELECT quantiles(0.5, 0.95, 0.99)(response_ms) FROM requests;-- FINAL forces merge-time dedup for ReplacingMergeTree readsSELECT * FROM users FINAL WHERE id = 42;

CREATE TABLE events_by_hour(    hour DateTime,    event_type LowCardinality(String),    cnt AggregateFunction(count))ENGINE = AggregatingMergeTreeORDER BY (event_type, hour);CREATE MATERIALIZED VIEW events_by_hour_mv TO events_by_hour ASSELECT    toStartOfHour(event_time) AS hour,    event_type,    countState() AS cntFROM eventsGROUP BY hour, event_type;-- Query the rollupSELECT hour, event_type, countMerge(cnt) FROM events_by_hour GROUP BY hour, event_type;