clickhouselisted

# ClickHouse ## MergeTree Engine Selection - `MergeTree` -- default for raw event/fact tables. - `ReplacingMergeTree` -- deduplication by ORDER BY key; duplicates removed only at merge time (queries may still see them until parts merge; `FINAL` forces it at query cost). - `AggregatingMergeTree` -- stores partial aggregate states (`AggregateFunction(...)` columns), usually fed by a materialized view. ```sql CREATE TABLE events ( date Date, market_id String, volume UInt64, created_at DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(date) ORDER BY (date, market_id); ``` Partitioning: by month or day, DATE-typed key, avoid high partition counts. ORDER BY: most-filtered columns first; column order affects both index usefulness and compression. ## State/Merge Combinators and Materialized Views Aggregate states are written with `-State` functions and read back with the matching `-Merge` function. The MV target table declares `AggregateFunction` columns: ```sql CREATE TABLE market_stats_hourly ( hour DateTime, market_id String, total_volume AggregateFunction(sum, UInt64), total_trades AggregateFunction(count, UInt32), unique_users AggregateFunction(uniq, String) ) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(hour) ORDER BY (hour, market_id); CREATE MATERIALIZED VIEW market_stats_hourly_mv TO market_stats_hourly AS SELECT toStartOfHour(timestamp) AS hour, market_id, sumState(amount) AS total_volume, countState()