CREATE TABLE events_monthly
(
    event_date Date,
    user_id UInt64,
    event_type String,
    data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- 分区键值格式: 202601, 202602, ...

CREATE TABLE logs_daily
(
    log_date Date,
    log_time DateTime,
    level LowCardinality(String),
    message String
)
ENGINE = MergeTree()
PARTITION BY log_date
ORDER BY (log_date, log_time);

-- 分区键值格式: 2026-05-01, 2026-05-02, ...

CREATE TABLE metrics_by_type
(
    metric_date Date,
    metric_type LowCardinality(String),
    metric_name String,
    value Float64
)
ENGINE = MergeTree()
PARTITION BY (metric_type, toYYYYMM(metric_date))
ORDER BY (metric_date, metric_name);

-- ✅ 命中分区裁剪（使用了分区键）
SELECT count() FROM events_monthly
WHERE event_date >= '2026-05-01' AND event_date < '2026-06-01';
-- 执行计划: Selected 1/12 partitions

-- ✅ 使用 toYYYYMM() 函数也能触发裁剪
SELECT count() FROM events_monthly
WHERE toYYYYMM(event_date) = 202605;

-- ❌ 无法裁剪（条件不涉及分区键）
SELECT count() FROM events_monthly WHERE user_id = 123;

-- 查看分区信息
SELECT
    partition,
    count() AS parts_count,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE table = 'events_monthly' AND active = 1
GROUP BY partition
ORDER BY partition;

-- ❌ 按小时分区：一年产生 8760 个分区
-- 每个分区可能有多个 parts，系统维护成本极高
PARTITION BY toStartOfHour(event_time)

-- ❌ 按 user_id 分区：可能产生上亿个分区
PARTITION BY user_id

-- ✅ 经验法则
-- 单个分区数据量建议: 100MB ~ 10GB
-- 分区总数建议: < 1000
-- 每个分区的 parts 数: < 300

-- 场景 1: 分析系统，主要按时间和用户查询
CREATE TABLE analytics
(
    event_date Date,
    user_id UInt64,
    event_type LowCardinality(String),
    url String,
    duration UInt32
)
ENGINE = MergeTree()
ORDER BY (event_date, user_id, event_type);

-- 场景 2: 用户画像系统，主要按用户查询
CREATE TABLE user_profiles
(
    user_id UInt64,
    profile_date Date,
    metric_name LowCardinality(String),
    metric_value Float64
)
ENGINE = MergeTree()
ORDER BY (user_id, profile_date, metric_name);

-- 场景 3: 日志系统，主要按时间和级别查询
CREATE TABLE logs
(
    log_time DateTime,
    level LowCardinality(String),
    service LowCardinality(String),
    trace_id String,
    message String
)
ENGINE = MergeTree()
ORDER BY (log_time, level, service);

-- 针对 ORDER BY (event_date, user_id, event_type) 设计

-- ✅ 前缀匹配：性能最好（可以利用稀疏索引快速定位）
SELECT * FROM analytics WHERE event_date = '2026-05-11';
SELECT * FROM analytics WHERE event_date = '2026-05-11' AND user_id = 123;
SELECT * FROM analytics WHERE event_date = '2026-05-11' AND user_id = 123 AND event_type = 'click';

-- ⚠️ 跳过前缀：需要扫描更多数据
SELECT * FROM analytics WHERE user_id = 123;
SELECT * FROM analytics WHERE event_type = 'click';

-- 💡 通过跳数索引优化跳过前缀的查询
ALTER TABLE analytics ADD INDEX idx_user_id user_id TYPE bloom_filter GRANULARITY 4;

-- 最佳实践：分区键是排序键的前缀
CREATE TABLE best_practice
(
    event_date Date,
    user_id UInt64,
    event_type LowCardinality(String),
    data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)    -- 分区键: event_date 的月份
ORDER BY (event_date, user_id);      -- 排序键: event_date 在最前面

-- 这样设计的好处:
-- 1. 按月分区 → 分区裁剪
-- 2. 排序键以 event_date 开头 → 每个分区内数据按日期有序
-- 3. 按日期范围查询时，先裁剪分区，再利用排序键快速定位

-- 按类型分区，按时间排序
CREATE TABLE mixed_partition
(
    event_date Date,
    event_type LowCardinality(String),
    user_id UInt64,
    data String
)
ENGINE = MergeTree()
PARTITION BY event_type                    -- 按类型分区
ORDER BY (event_date, user_id);           -- 按时间+用户排序

-- 查询: 先裁剪到某个类型的分区，然后在分区内按日期范围过滤
SELECT * FROM mixed_partition
WHERE event_type = 'purchase'
  AND event_date >= '2026-05-01'
  AND event_date < '2026-06-01';

-- 默认索引粒度: 每 8192 行一个索引标记
-- 查询时，ClickHouse 先通过索引标记定位到可能包含数据的 granule
-- 然后只读取这些 granule 的数据

-- 查看 granule 信息
SELECT
    partition,
    name AS part_name,
    rows,
    marks,                           -- 索引标记数
    formatReadableSize(primary_key_bytes_in_memory) AS pk_memory
FROM system.parts
WHERE table = 'best_practice' AND active = 1;

-- 查看索引使用情况
SELECT
    query,
    read_rows,
    read_bytes,
    result_rows,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%best_practice%'
ORDER BY event_time DESC
LIMIT 10;

-- 启用自适应索引粒度（默认已启用）
CREATE TABLE adaptive_granularity
(
    event_date Date,
    user_id UInt64,
    data String    -- 大文本列
)
ENGINE = MergeTree()
ORDER BY (event_date, user_id)
SETTINGS
    index_granularity_bytes = 10485760,  -- 目标 granule 大小: 10MB
    min_bytes_for_wide_part = 10485760;

-- 当单行数据较大时，granule 行数会自动减少
-- 当单行数据较小时，granule 行数接近默认的 8192

-- 查看所有分区
SELECT
    partition,
    count() AS parts_count,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    min(min_date) AS min_date,
    max(max_date) AS max_date
FROM system.parts
WHERE table = 'events_monthly' AND active = 1
GROUP BY partition
ORDER BY partition;

-- DETACH 分区（将分区从表中移除，数据文件仍在磁盘上）
ALTER TABLE events_monthly DETACH PARTITION '202604';

-- ATTACH 分区（重新附加）
ALTER TABLE events_monthly ATTACH PARTITION '202604';

-- ATTACH PART（附加单个数据部分）
ALTER TABLE events_monthly ATTACH PART 'all_1_1_0';

-- DROP 分区（永久删除！）
ALTER TABLE events_monthly DROP PARTITION '202601';

-- 从另一张表复制分区
ALTER TABLE events_monthly ATTACH PARTITION '202603' FROM events_backup;

-- FREEZE 分区（创建快照备份）
ALTER TABLE events_monthly FREEZE PARTITION '202605';

-- MOVE 分区到另一个表
ALTER TABLE events_monthly MOVE PARTITION '202604' TO TABLE events_archive;

-- FETCH PARTITION: 从远程副本下载分区（需要 ReplicatedMergeTree）
-- ALTER TABLE events_monthly FETCH PARTITION '202605' FROM '/clickhouse/tables/events';

-- MOVE PARTITION: 在同一服务器的不同表之间移动分区
ALTER TABLE events_monthly MOVE PARTITION '202604' TO TABLE events_monthly_archive;

-- 分区数量检查
SELECT
    database,
    table,
    count(DISTINCT partition) AS partition_count
FROM system.parts
WHERE active = 1
GROUP BY database, table
HAVING partition_count > 500
ORDER BY partition_count DESC;

-- 监控指标数据：日写入 10 亿行
CREATE TABLE monitoring.metrics
(
    timestamp DateTime64(3),
    host LowCardinality(String),
    metric_name LowCardinality(String),
    labels Map(String, String),
    value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)    -- 按天分区（数据量大）
ORDER BY (host, metric_name, timestamp) -- 先按主机和指标名，再按时间
TTL timestamp + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

-- 查询: 获取某台主机最近 1 小时的 CPU 使用率
SELECT
    timestamp,
    value
FROM monitoring.metrics
WHERE host = 'web-server-01'
  AND metric_name = 'cpu_usage'
  AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY timestamp;
-- 利用: 分区裁剪 + 排序键前三列快速定位

-- 用户行为埋点数据
CREATE TABLE user_events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    session_id String,
    event_type LowCardinality(String),
    page_url String,
    referrer String,
    user_agent String,
    ip IPv4,
    extra Map(String, String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time)
TTL event_date + INTERVAL 12 MONTH DELETE;

-- 典型查询 1: 某用户某天的行为轨迹
SELECT
    event_time,
    event_type,
    page_url
FROM user_events
WHERE event_date = '2026-05-11'
  AND user_id = 12345
ORDER BY event_time;

-- 典型查询 2: 某天的页面访问 Top10
SELECT
    page_url,
    count() AS pv,
    uniq(user_id) AS uv
FROM user_events
WHERE event_date = '2026-05-11'
GROUP BY page_url
ORDER BY pv DESC
LIMIT 10;

CREATE TABLE ecom.orders
(
    order_date Date,
    order_id UInt64,
    user_id UInt64,
    shop_id UInt64,
    product_id UInt64,
    product_name String,
    category_id LowCardinality(UInt32),
    quantity UInt32,
    amount Decimal128(2),
    status Enum8('pending'=1, 'paid'=2, 'shipped'=3, 'delivered'=4, 'cancelled'=5),
    created_at DateTime,
    updated_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, shop_id, user_id, order_id)
PRIMARY KEY (order_date, shop_id);    -- 主键只需前两列，减少索引内存

-- 查询 1: 某店铺某月销售统计
SELECT
    shop_id,
    count() AS orders,
    sum(amount) AS revenue,
    uniq(user_id) AS customers,
    sum(amount) / uniq(user_id) AS arpu
FROM ecom.orders
WHERE order_date >= '2026-05-01' AND order_date < '2026-06-01'
  AND shop_id = 1001
  AND status NOT IN ('cancelled')
GROUP BY shop_id;

-- 查询 2: 某用户的历史订单
SELECT
    order_date,
    order_id,
    product_name,
    amount,
    status
FROM ecom.orders
WHERE user_id = 12345
ORDER BY order_date DESC
LIMIT 20;