ClickHouseNotes

第 04 章：SQL 与数据类型

zjc 于 2026-01-04 发布

这是《ClickHouse 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 ClickHouse 支持丰富的 SQL 能力和类型系统，但它不是 OLTP 数据库。理解类型、可空性、数组、嵌套、函数和参数化查询，是写出高性能且可控语句的前提。

4.1 数据库与表管理

CREATE DATABASE IF NOT EXISTS analytics;

SHOW TABLES FROM analytics;

DESCRIBE TABLE analytics.events_local;

SHOW CREATE TABLE analytics.events_local;

删除表：

DROP TABLE analytics.events_local;

清空分区比 DELETE 更高效：

ALTER TABLE analytics.events_local
DROP PARTITION '202608';

4.2 数值类型

类型 说明
UInt8 / UInt16 / UInt32 / UInt64 无符号整数
Int8 / Int16 / Int32 / Int64 有符号整数
Float32 / Float64 浮点数
Decimal32 / Decimal64 / Decimal128 定点数
UInt128 / UInt256 / Int256 超大整数，慎用于普通业务

金额优先使用 Decimal：

SELECT
    toDecimal64(19.99, 2) AS price,
    price * 3 AS total;

不要用 Float64 存资金余额。浮点适合比率、评分、科学计算，不适合精确金额。

4.3 时间类型

类型 说明
Date 日期
Date32 更大日期范围
DateTime 秒级时间
DateTime64 亚秒精度

建表：

CREATE TABLE time_demo
(
    d Date,
    dt DateTime,
    dt64 DateTime64(3, 'Asia/Shanghai')
)
ENGINE = Memory;

常用函数：

SELECT
    now() AS now_time,
    today() AS today_date,
    toDate('2026-08-25') AS d,
    toDateTime('2026-08-25 10:00:00', 'Asia/Shanghai') AS dt,
    formatDateTime(dt, '%Y-%m-%d %H:%i:%s') AS formatted;

ClickHouse 的 DateTime 默认精度是秒。需要毫秒时应显式使用 DateTime64(3)。

4.4 字符串与枚举

类型 说明
String 变长字符串
FixedString(N) 定长字符串
Enum8 / Enum16 枚举
UUID 全局唯一标识
LowCardinality(String) 低基数字典编码

Enum 示例：

CREATE TABLE order_demo
(
    order_id String,
    status Enum8('CREATED' = 1, 'PAID' = 2, 'CANCELLED' = 3)
)
ENGINE = Memory;

LowCardinality 适合状态、平台、国家、渠道等低基数字段：

CREATE TABLE event_demo
(
    event_type LowCardinality(String),
    platform LowCardinality(String)
)
ENGINE = Memory;

不建议把用户 ID、URL、trace id 这类高基数字段设置成 LowCardinality，否则字典本身会带来额外成本。

4.5 Nullable

CREATE TABLE nullable_demo
(
    id UInt64,
    remark Nullable(String)
)
ENGINE = Memory;

查询：

SELECT
    id,
    isNull(remark) AS is_null,
    coalesce(remark, 'empty') AS safe_value
FROM nullable_demo;

Nullable 会带来额外存储和计算成本。默认值通常比空值更适合分析表：

场景 建议
未知城市 UInt32 默认 0，维度表标记未知
无备注 空字符串
无支付金额 Decimal64 默认 0
事件确实无值且需区分 Nullable

4.6 数组、Map 和 Nested

数组：

SELECT
    [1, 2, 3] AS ids,
    length(ids) AS n,
    arrayJoin(ids) AS expanded_id;

Map：

SELECT
    map('os', 'iOS', 'network', '5G') AS props,
    props['os'] AS os;

Nested：

CREATE TABLE sku_demo
(
    order_id String,
    items Nested(
        sku_id UInt64,
        quantity UInt32,
        price Decimal64(2)
    )
)
ENGINE = Memory;

展开：

SELECT
    order_id,
    arrayJoin(items.sku_id) AS sku_id
FROM sku_demo;

复杂类型灵活，但会降低压缩和过滤效率。高频过滤和聚合字段应提升为普通列。

4.7 JSON 类型

新版 ClickHouse 提供原生 JSON 能力，不同版本行为差异较大。兼容性要求高的链路常用 String 保存 JSON，再用函数解析：

SELECT
    JSONExtractString(payload, 'city') AS city,
    JSONExtractUInt(payload, 'user_id') AS user_id
FROM (
    SELECT '{"user_id":1001,"city":"SH"}' AS payload
);

如果使用原生 JSON 类型，应先确认版本、写入格式、路径访问语法和schema 演进策略。

4.8 查询语句

SELECT
    city_id,
    count() AS pv,
    countDistinct(user_id) AS uv,
    round(sum(amount), 2) AS gmv
FROM analytics.events_local
WHERE event_date = today()
  AND event_type IN ('view', 'pay')
GROUP BY city_id
HAVING pv > 100
ORDER BY gmv DESC
LIMIT 20;

条件表达式：

SELECT
    multiIf(
        amount >= 1000, 'high',
        amount >= 100, 'middle',
        'low'
    ) AS level
FROM analytics.events_local;

去重估算：

SELECT uniqExact(user_id) AS exact_uv, uniq(user_id) AS approx_uv
FROM analytics.events_local;

uniq 更快但近似；uniqExact 精确但成本更高。

4.9 JOIN 能力与边界

SELECT
    e.city_id,
    c.city_name,
    count() AS pv
FROM analytics.events_local AS e
LEFT JOIN analytics.city_dim AS c ON e.city_id = c.city_id
GROUP BY e.city_id, c.city_name;

ClickHouse 支持多种 JOIN 算法，但大表 JOIN 的资源消耗仍要重视。优化思路：

  1. 小维表走 Dictionary；
  2. 常用汇总先物化；
  3. 事实表冗余必要维度字段；
  4. 过滤条件下推；
  5. 避免在超大表之间做高频在线 JOIN。

4.10 系统表

常用系统表：

系统表 用途
system.tables 表元数据
system.parts part 状态
system.merges 合并任务
system.query_log 查询日志
system.processes 正在执行的查询
system.clusters 集群节点
system.replicas 副本状态
system.disks 磁盘信息

查看当前查询：

SELECT
    query_id,
    elapsed,
    read_rows,
    memory_usage,
    query
FROM system.processes
ORDER BY elapsed DESC;

终止查询：

KILL QUERY WHERE query_id = 'query-id';

4.11 常见 SQL 错误

错误 原因 处理
Type mismatch 类型不兼容 显式转换
Unknown identifier 字段或别名作用域错误 检查 SELECT、WHERE、HAVING
Memory limit exceeded 结果或状态过大 过滤、分片、限制内存
Too many parts 小写入过多 改为大批量写入
Readonly user 用户权限不足 授权或换用户

本章小结

类型系统决定了存储效率、压缩率和计算成本。数值与时间要明确精度，字符串要区分低基数与高基数，Nullable 和复杂类型要克制使用。SQL 层面应优先利用分区过滤、预聚合、字典和系统表，把查询成本控制在可预测范围内。

思考题

  1. 为什么金额不应使用 Float64？
  2. LowCardinality 为什么不适合 trace id？
  3. Nullable 有什么代价？
  4. uniq 和 uniqExact 的差异是什么？
  5. 大表 JOIN 有哪些常见优化方向？