CREATE TABLE my_table
(
    event_date Date,
    user_id UInt64,
    event_type LowCardinality(String),
    url String,
    duration UInt32,
    extra Map(String, String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)       -- 分区键
ORDER BY (event_date, user_id, event_type) -- 排序键
PRIMARY KEY (event_date, user_id)       -- 主键（可选，默认等于 ORDER BY）
SAMPLE BY user_id                       -- 采样键（可选）
TTL event_date + INTERVAL 12 MONTH      -- 数据过期时间（可选）
SETTINGS index_granularity = 8192;      -- 索引粒度

-- 查看表的所有活跃数据部分
SELECT
    partition,
    name           AS part_name,
    rows,
    formatReadableSize(bytes_on_disk) AS disk_size,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS compress_ratio,
    modification_time
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'my_table'
  AND active = 1
ORDER BY partition, modification_time;

-- 查看部分合并操作
SELECT
    event_time,
    event_type,
    table,
    partition_id,
    name AS part_name,
    rows,
    formatReadableSize(size_in_bytes) AS size
FROM system.part_log
WHERE database = currentDatabase()
  AND table = 'my_table'
ORDER BY event_time DESC
LIMIT 20;

-- ✅ 按月分区（最常用，适合日志和事件数据）
PARTITION BY toYYYYMM(event_date)

-- ✅ 按天分区（数据量极大时）
PARTITION BY event_date

-- ✅ 按类型 + 时间混合分区
PARTITION BY (event_type, toYYYYMM(event_date))

-- ⚠️ 不推荐：分区过多（如按小时），会导致大量 part 文件
PARTITION BY toStartOfHour(event_time)

-- ⚠️ 不推荐：无分区，所有数据在同一个分区
-- （不设置 PARTITION BY）

-- 查询时指定分区条件，ClickHouse 自动裁剪不相关分区
-- 只扫描 2026 年 5 月的分区
SELECT count()
FROM my_table
WHERE event_date >= '2026-05-01' AND event_date < '2026-06-01';
-- 执行计划中可以看到 "Selected 1/12 partitions"

-- 使用分区键过滤
SELECT *
FROM system.parts
WHERE table = 'my_table' AND active = 1;

-- 分离分区（数据从表中移除，但文件仍在磁盘上）
ALTER TABLE my_table DETACH PARTITION '202601';

-- 重新附加分区
ALTER TABLE my_table ATTACH PARTITION '202601';

-- 删除分区（不可恢复！）
ALTER TABLE my_table DROP PARTITION '202601';

-- 从另一个表复制分区
ALTER TABLE my_table ATTACH PARTITION '202601' FROM another_table;

-- 卸载分区到指定目录
ALTER TABLE my_table FREEZE PARTITION '202601';

-- 查看分区列表
SELECT DISTINCT partition
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'my_table'
  AND active = 1
ORDER BY partition;

-- 排序键列的选择顺序至关重要：
-- 1. 第一列：最常用的等值/范围查询过滤条件
-- 2. 第二列：次常用的过滤条件或 GROUP BY 条件
-- 3. 后续列：按查询模式逐步添加

-- ❌ 错误示例：低基数列放在最前面
CREATE TABLE logs_bad (
    event_date Date,
    status String,    -- 只有几个值，放在第一位过滤效果差
    user_id UInt64,
    url String
) ENGINE = MergeTree()
ORDER BY (status, event_date, user_id);

-- ✅ 正确示例：高选择性列在前
CREATE TABLE logs_good (
    event_date Date,
    status String,
    user_id UInt64,
    url String
) ENGINE = MergeTree()
ORDER BY (event_date, user_id, status);

-- 针对 ORDER BY (event_date, user_id, status) 设计的表

-- ✅ 高效查询：匹配排序键前缀
SELECT * FROM logs_good WHERE event_date = '2026-05-11';
SELECT * FROM logs_good WHERE event_date = '2026-05-11' AND user_id = 123;
SELECT * FROM logs_good WHERE event_date = '2026-05-11' AND user_id = 123 AND status = 'error';

-- ⚠️ 一般查询：跳过前缀列
SELECT * FROM logs_good WHERE user_id = 123;
SELECT * FROM logs_good WHERE status = 'error';

-- 💡 可以使用二级索引（跳数索引）优化跳过前缀的查询

-- 场景：按 (date, user_id, event_id) 排序，但主键只需 (date, user_id)
-- 主键索引更小，内存占用更少
CREATE TABLE events
(
    event_date Date,
    user_id UInt64,
    event_id UInt64,
    payload String
)
ENGINE = MergeTree()
ORDER BY (event_date, user_id, event_id)
PRIMARY KEY (event_date, user_id);
-- 主键索引只需存储 (date, user_id) 的稀疏索引
-- 查询时仍可利用完整的排序键进行过滤

-- 列级 TTL：30 天后将 detail 列置为默认值
CREATE TABLE logs_with_ttl
(
    event_date Date,
    user_id UInt64,
    summary String,
    detail String TTL event_date + INTERVAL 30 DAY,
    tags Array(String) TTL event_date + INTERVAL 30 DAY
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL event_date + INTERVAL 12 MONTH DELETE;  -- 行级 TTL：12 个月后删除

-- 动态修改 TTL
ALTER TABLE logs_with_ttl MODIFY TTL event_date + INTERVAL 6 MONTH;

-- TTL 表达式可以很灵活
CREATE TABLE tiered_storage
(
    event_date Date,
    data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY event_date
TTL
    event_date + INTERVAL 30 DAY TO VOLUME 'hot',    -- 30 天后移到热存储
    event_date + INTERVAL 90 DAY TO VOLUME 'cold',   -- 90 天后移到冷存储
    event_date + INTERVAL 365 DAY DELETE;             -- 365 天后删除

-- 查看 TTL 信息
SELECT
    database,
    table,
    partition,
    name,
    rows,
    modification_time,
    ttl_info
FROM system.parts
WHERE table = 'logs_with_ttl' AND active = 1;

<!-- config.xml 中定义存储策略 -->
<storage_configuration>
    <disks>
        <ssd>
            <path>/data/ssd/clickhouse/</path>
        </ssd>
        <hdd>
            <path>/data/hdd/clickhouse/</path>
        </hdd>
        <s3>
            <type>s3</type>
            <endpoint>https://my-bucket.s3.amazonaws.com/data/</endpoint>
            <access_key_id>YOUR_KEY</access_key_id>
            <secret_access_key>YOUR_SECRET</secret_access_key>
        </s3>
    </disks>
    <policies>
        <tiered>
            <volumes>
                <hot>
                    <disk>ssd</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                </hot>
                <cold>
                    <disk>hdd</disk>
                </cold>
                <archive>
                    <disk>s3</disk>
                </archive>
            </volumes>
        </tiered>
    </policies>
</storage_configuration>

-- 创建表时指定存储策略
CREATE TABLE tiered_table
(
    event_date Date,
    data String
)
ENGINE = MergeTree()
ORDER BY event_date
SETTINGS storage_policy = 'tiered';

-- 创建表时设置参数
CREATE TABLE optimized_table
(
    event_date Date,
    user_id UInt64,
    data String
)
ENGINE = MergeTree()
ORDER BY (event_date, user_id)
SETTINGS
    index_granularity = 4096,              -- 更细的索引粒度
    min_bytes_for_wide_part = 0,           -- 始终使用宽格式
    max_parts_to_merge_at_once = 50;       -- 减少单次合并的 part 数

-- 自动去重：相同排序键的行只保留最新版本
CREATE TABLE users_latest
(
    user_id UInt64,
    name String,
    email String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)  -- 按 updated_at 判断最新版本
ORDER BY user_id;

INSERT INTO users_latest VALUES
    (1, 'Alice', '[email protected]', '2026-01-01 10:00:00'),
    (2, 'Bob',   '[email protected]', '2026-01-01 10:00:00');

-- 插入同一 user_id 的新版本
INSERT INTO users_latest VALUES
    (1, 'Alice', '[email protected]', '2026-05-11 10:00:00');

-- ⚠️ 去重发生在 merge 期间，查询时可能返回重复数据
-- 使用 FINAL 强制实时去重（有性能开销）
SELECT * FROM users_latest FINAL;

-- 或者使用 GROUP BY 取最新
SELECT
    user_id,
    argMax(name, updated_at) AS name,
    argMax(email, updated_at) AS email,
    max(updated_at) AS updated_at
FROM users_latest
GROUP BY user_id;

-- 自动聚合数值列
CREATE TABLE page_views
(
    event_date Date,
    url String,
    views UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree((views, unique_users))  -- 对这些列自动求和
ORDER BY (event_date, url);

INSERT INTO page_views VALUES
    ('2026-05-11', '/home', 100, 50),
    ('2026-05-11', '/home', 200, 80),
    ('2026-05-11', '/about', 50, 30);

-- ⚠️ merge 后数据会被聚合，但 merge 前查询需要手动聚合
SELECT
    event_date,
    url,
    sum(views) AS total_views,
    sum(unique_users) AS total_unique
FROM page_views
GROUP BY event_date, url
ORDER BY total_views DESC;

-- 配合物化视图实现复杂预聚合
CREATE TABLE mv_user_stats
(
    event_date Date,
    user_id UInt64,
    event_count AggregateFunction(count, UInt64),
    sum_amount AggregateFunction(sum, Decimal128(2)),
    uniq_urls AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_date, user_id);

-- 创建物化视图自动填充
CREATE MATERIALIZED VIEW mv_user_stats_view
TO mv_user_stats
AS
SELECT
    event_date,
    user_id,
    countState()      AS event_count,
    sumState(amount)   AS sum_amount,
    uniqState(url)     AS uniq_urls
FROM raw_events
GROUP BY event_date, user_id;

-- 查询时需要使用合并函数
SELECT
    event_date,
    user_id,
    countMerge(event_count)    AS events,
    sumMerge(sum_amount)       AS total_amount,
    uniqMerge(uniq_urls)       AS unique_urls
FROM mv_user_stats
GROUP BY event_date, user_id;

-- 使用 Sign 列标记行的正负状态
CREATE TABLE user_balance
(
    user_id UInt64,
    balance Decimal128(2),
    sign Int8   -- 1 = 有效行, -1 = 抵消行
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;

INSERT INTO user_balance VALUES
    (1, 100.00, 1),   -- 初始余额 100
    (1, 100.00, -1),  -- 抵消旧数据
    (1, 150.00, 1);   -- 新余额 150

-- 查询需要使用 FINAL 或手动折叠
SELECT
    user_id,
    sum(balance * sign) AS current_balance
FROM user_balance
GROUP BY user_id
HAVING sum(sign) > 0;

-- 在 CollapsingMergeTree 基础上增加版本号
-- 解决了并发写入导致的折叠顺序问题
CREATE TABLE user_profile
(
    user_id UInt66,
    name String,
    score UInt32,
    sign Int8,
    version UInt32
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY user_id;

INSERT INTO user_profile VALUES
    (1, 'Alice', 100, 1, 1),    -- 版本 1
    (1, 'Alice', 100, -1, 1),   -- 抵消版本 1
    (1, 'Alice', 150, 1, 2);    -- 版本 2

-- 快速创建表参考模板

-- 1. 通用事件表
CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    properties Map(String, String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_type);

-- 2. 维度表（去重）
CREATE TABLE dim_users (
    user_id UInt64,
    name String,
    email String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

-- 3. 统计表（预聚合）
CREATE TABLE stats_daily (
    stat_date Date,
    metric_name LowCardinality(String),
    dimensions Map(String, String),
    value AggregateFunction(sum, Float64)
) ENGINE = AggregatingMergeTree()
ORDER BY (stat_date, metric_name);