Day 6｜SQL、数据类型与函数体系：从兼容到正确建模

前五天，我们完成了 Doris 的定位、架构、部署形态与第一个实验集群。今天开始进入数据工程阶段。

会写 SELECT 只是起点。一套长期稳定的数据平台，还需要回答更具体的问题：金额为什么选 DECIMAL，全球事件时间为什么要区分 DATETIME 与 TIMESTAMPTZ，标签应该放进 ARRAY 还是拆成明细表，动态 JSON 什么时候适合 VARIANT，精确去重与近似去重如何取舍，窗口函数和 LATERAL VIEW 又分别解决什么问题。

本日学习目标

完成今天的学习后，你应当能够：

1. 分清 MySQL 协议兼容、SQL 语法兼容和执行语义兼容；
2. 按照业务语义、精度、范围、查询方式和演进方式选择字段类型；
3. 正确使用整数、浮点数、DECIMAL、字符串、日期与时间类型；
4. 理解 ARRAY、MAP、STRUCT、JSON、VARIANT 的建模边界；
5. 在精确集合计算与近似去重之间选择 BITMAP 或 HLL；
6. 建立标量、聚合、窗口、表函数和高阶函数的整体地图；
7. 使用窗口函数完成排名、累计、环比等分析；
8. 使用 LATERAL VIEW 将集合列展开为多行；
9. 识别隐式类型转换、时间语义、NULL 和 JSON Null 带来的正确性风险；
10. 判断一段业务逻辑应使用内置函数、Java UDF，还是处于实验阶段的 Python UDF。

一、理解 Doris SQL，先分清三种“兼容”

Apache Doris FE 默认通过 9030 端口提供 MySQL 网络协议服务。MySQL Client、JDBC、ODBC、DataGrip 以及大量 BI 工具都可以直接连接。这个能力显著降低了接入门槛，也很容易让团队产生一个误解：既然客户端能够连接，原有 MySQL SQL 就可以原样迁移。

真实迁移需要分成三层验证。

1.1 第一层：网络协议兼容

协议兼容解决的是“如何建立连接并交换结果”。客户端完成握手、认证、提交 SQL，FE 返回字段元数据和结果集。团队可以继续使用成熟的 MySQL 驱动、连接池、BI 工具和开发工具。

这一层带来的价值很直接：

• 应用无需安装 Doris 专用驱动；
• 数据分析师可以继续使用熟悉的 SQL 客户端；
• BI 平台通常只需新增一个 MySQL 类型的数据源；
• JDBC 连接池、监控代理和数据库网关能够继续复用。

协议层不会替业务确认 SQL 语义。客户端显示“连接成功”，只说明网络、账户和协议握手正常。

1.2 第二层：语法与函数兼容

Doris 支持标准 SQL，并覆盖大量 MySQL 常用语法与函数。常见的 SELECT、JOIN、GROUP BY、HAVING、子查询、CTE、窗口函数、INSERT、UPDATE、DELETE 都有对应能力。

迁移时仍需逐项检查：

• MySQL 私有语法是否有 Doris 等价写法；
• 函数名称、参数顺序和返回类型是否一致；
• 保留字是否与字段名冲突；
• LIMIT、日期格式化、字符串处理是否存在细微差异；
• 存储过程、触发器、外键等 OLTP 能力是否被原系统依赖；
• 原 SQL 是否隐含依赖 MySQL 的类型转换规则。

建议把业务 SQL 分成固定报表、即席查询、同步任务、数据校验和管理语句五类，批量扫描后再选择改写策略。不要只抽取十几条“跑得通”的 SQL 作为兼容结论。

1.3 第三层：执行语义兼容

执行语义关注结果正确性和性能行为。以下 SQL 在不同数据库中可能都能执行，结果或资源开销却可能不同：

SELECT true + true;

SELECT CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT) = CAST(0.6 AS FLOAT);

SELECT CAST('null' AS JSON) IS NULL;

SELECT amount / quantity FROM order_item;

Doris 的 BOOLEAN 是独立类型，内部以 0/1 表示；浮点数遵循近似数规则；JSON Null 与 SQL NULL 含义不同；DECIMAL 运算会进行精度推导。再加上分布式执行中的并行聚合顺序、数据模型、分区分桶和统计信息，最终表现会与单机 OLTP 数据库产生差异。

一套可靠的迁移流程应包含：

1. 连接验证；
2. 语法和函数扫描；
3. 小样本结果对账；
4. 全量边界值对账；
5. EXPLAIN 计划检查；
6. 冷热缓存性能测试；
7. 并发与尾延迟测试；
8. 差异清单和回滚门禁。

1.4 SQL 的书写顺序与逻辑处理顺序

SQL 文本通常按照以下顺序书写：

SELECT ...
FROM ...
JOIN ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;

理解查询时，可以使用另一条逻辑顺序：

1. FROM 与 JOIN 确定数据来源；
2. WHERE 过滤明细行；
3. GROUP BY 建立分组；
4. 聚合函数计算每组结果；
5. HAVING 过滤聚合结果；
6. 窗口函数基于当前结果集计算；
7. SELECT 形成输出表达式；
8. ORDER BY 排序；
9. LIMIT 截断返回行。

这条顺序能够解释很多常见报错。WHERE 无法引用同层聚合结果，因为聚合尚未发生；窗口函数别名通常要放入子查询后再过滤；HAVING 适合过滤 SUM(amount) > 1000 这类组级条件。

SELECT city, SUM(amount) AS total_amount
FROM orders
WHERE create_time >= '2026-08-01'
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY total_amount DESC
LIMIT 20;

优化器会重写物理执行计划，例如谓词下推、分区裁剪、局部聚合和 TopN 下推。逻辑顺序用于理解语义，物理计划用于理解性能。两套视角结合起来，才能同时回答“结果为什么这样”和“查询为什么这么快或这么慢”。

1.5 迁移 SQL 时建立差异台账

建议为每条核心 SQL 记录以下字段：

差异台账会在后续版本升级时继续发挥作用。函数签名、隐式转换、时间类型和优化器规则都可能演进，已经验证过的核心 SQL 应进入自动回归，而非依赖人工记忆。

二、数据类型是一份长期数据契约

字段类型会同时影响四件事：

• 正确性：能否精确表达金额、时间、状态和标识；
• 性能：参与扫描、比较、聚合和 Join 时需要多少 CPU 与内存；
• 存储：每行占用空间、压缩效率和索引体积；
• 演进：后续是否容易扩展、修改或与其他系统交换。

很多早期项目习惯把所有字段都定义成 STRING，希望先把数据存下来再处理。短期建表速度很快，长期会出现日期无法稳定裁剪、金额需要反复转换、脏数据进入主链路、索引无法命中、统计信息失真等问题。

2.1 推荐的选择顺序

设计字段时，按以下顺序提问：

1. 这个字段表达什么业务语义？
2. 是否要求精确值？
3. 合法范围有多大？
4. 是否参加 Key、排序、分区或分桶？
5. 常见过滤方式是等值、范围、全文还是集合运算？
6. Schema 是否稳定？
7. 数据是否需要跨时区交换？
8. 后续是否需要参与聚合、Join 或索引？

类型越贴近真实语义，后续 SQL 越简单，数据质量也越容易治理。

2.2 一张实用速查表

2.3 MySQL 迁移中经常遇到的类型差异

MySQL 与 Doris 的类型名称有大量重合，映射时仍需逐项确认：

• Doris 整数类型只支持有符号范围，MySQL UNSIGNED 要检查上界；
• Doris 没有 MEDIUMINT 和 YEAR，通常映射到 INT、DATE 或独立年份整数；
• MySQL TIMESTAMP 带有自动初始化、自动更新时间和时区转换习惯，迁移到 Doris 时要拆解这些行为；
• Doris 的 TIME 主要用于计算，不能作为 OLAP 表列保存；
• CHAR(N)、VARCHAR(N) 的长度按字节理解；
• Doris 的 STRING 是分析型扩展，MySQL 没有同名类型；
• MySQL 的 B+Tree、唯一约束和自增主键概念，需要重新映射到 Doris 数据模型、排序键和分桶设计；
• Doris UPDATE 要求 WHERE 条件，高频逐行更新需要重新评估链路。

迁移工具自动生成的 DDL 可以作为起点，正式建表仍要经过人工评审。源库类型只说明数据当前如何保存，目标表还要考虑未来查询、更新、分区、分桶和保留周期。

2.4 SQL Mode、事务和结果口径

Doris 支持部分 SQL Mode，例如 PIPES_AS_CONCAT、NO_BACKSLASH_ESCAPES 和 ONLY_FULL_GROUP_BY。同一段 SQL 在不同 Mode 下可能产生不同解析结果。团队应在连接初始化时固定 Mode，并把设置纳入测试环境和生产环境的配置基线。

事务能力也要结合分析数据库的使用方式理解。查询和单条 DDL 通常以隐式事务执行，多语句事务的能力与 OLTP 数据库存在边界。批量导入、Flink Checkpoint、Stream Load Label 和两阶段提交属于数据接入一致性问题，不能直接套用 MySQL 应用事务的设计。

结果对账建议覆盖四类数据：

1. 全量行数和主键数量；
2. 金额、计数和去重指标；
3. 时间边界、跨天和跨时区样本；
4. NULL、空字符串、0、负数和极值。

只对几条正常数据执行 SELECT *，很难发现真实迁移风险。边界样本和异常样本应进入长期回归测试。

2.5 六类常见建模失误

失误一：金额使用 DOUBLE。 初期数据量小时看不出问题，累计求和、税费拆分和多次换算后会出现小数误差。核心金额字段应从源头使用 DECIMAL。

失误二：时间全部使用字符串。 字符串可以显示时间，却会增加格式不一致、时区不明和转换失败的风险，也会给分区裁剪与日期函数增加额外成本。

失误三：状态字段没有字典。 1、2、3 在不同系统里含义各异，后续接入者只能猜测。类型设计要配套枚举字典、合法范围和未知值策略。

失误四：所有扩展字段都放进 JSON。 高频过滤字段埋在文档内部，会反复执行路径提取和类型转换。稳定热点字段应提升为顶层列。

失误五：为节省几个字节选取过小整数。 一旦业务增长超出范围，历史数据修改和上下游同步会变得复杂。需要基于三到五年的增长预测选择范围。

失误六：把字段类型当成单表内部决定。 同一个 user_id 在 MySQL、Kafka Schema、Flink、Doris 和 API 中应保持一致。跨系统类型不一致会产生截断、符号位、时区和精度问题。

字段评审应邀请业务、数据开发和应用开发共同参与。业务确认含义，数据开发确认分析与演进，应用开发确认上下游类型映射。

三、数值类型：精确值和近似值必须分开

3.1 整数类型按范围选择

Doris 提供 TINYINT、SMALLINT、INT、BIGINT 和 LARGEINT。它们分别对应 8、16、32、64 和 128 位整数。

选择原则很简单：覆盖业务合法范围，同时留出合理增长空间。

CREATE TABLE order_status_example (
    order_id BIGINT,
    status TINYINT,
    retry_count SMALLINT,
    inventory_delta INT,
    trace_number LARGEINT
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

状态码用 TINYINT 往往足够；订单 ID 通常使用 BIGINT；累计量级可能超过 32 位时使用 BIGINT。不要为了“保险”把所有整数定义成 LARGEINT，更大的类型会增加存储、内存和计算成本。

3.2 FLOAT 与 DOUBLE 适合近似数

FLOAT 和 DOUBLE 遵循 IEEE 754 浮点规则。它们能表达很大的数值范围，代价是部分十进制小数无法被二进制精确保存。

SELECT
    CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT),
    CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT) = CAST(0.6 AS FLOAT);

第二列表达式可能返回 0。分布式聚合还会受到计算顺序影响，极端数据下多次执行可能出现微小差异。

浮点类型适合：

• 传感器采样；
• 机器学习概率与相似度；
• 科学计算；
• 对微小误差容忍度较高的指标。

需要谨慎的场景：

• 金额和税务；
• 精确结算；
• 直接等值比较；
• 作为 Join Key；
• 结果需要审计复现的聚合。

3.3 DECIMAL 用于固定小数与精确计算

DECIMAL(P,S) 中，P 表示总有效位数，S 表示小数位数。

amount       DECIMAL(18, 2),
tax_rate     DECIMAL(9, 6),
exchange_rate DECIMAL(20, 10)

Doris 4.x 默认 enable_decimal256=false，此时最大精度为 38 位。开启后最高可到 76 位，计算精度提高，同时带来额外性能成本。普通金额通常使用 DECIMAL(18,2) 或结合业务上限确定；金融风险、超大整数乘除和科学高精度场景再评估 Decimal256。

DECIMAL 的乘除法会推导新的精度与小数位。当中间结果超出上限，系统可能缩减 scale 或报告溢出。生产上线前应针对最大值、最小值、乘法、除法、累计求和和负数进行边界测试。

SELECT
    CAST('999999999999.99' AS DECIMAL(18,2))
  * CAST('1000000.000000' AS DECIMAL(18,6));

表结构评审时，金额字段需要记录三项信息：业务最大值、小数位含义、舍入规则。仅写“金额用 DECIMAL”仍然不够。

3.4 BOOLEAN 是独立类型

Doris 的 BOOLEAN 与 MySQL 中常见的 TINYINT(1) 习惯存在差异。Doris 将 BOOLEAN 作为独立类型，合法值是 TRUE 和 FALSE，内部以 0 和 1 存储。

SELECT TRUE, FALSE, NOT TRUE, TRUE AND FALSE;

迁移时要检查 ORM 和 JDBC 是否把布尔列识别为整数。业务状态超过两种时使用枚举整数或字符串，不要把 BOOLEAN 继续扩展成 0、1、2、3。

四、字符串与二进制：长度、Key 能力和实际内容共同决定

4.1 CHAR 适合真正固定的短编码

CHAR(M) 是固定长度字符串，M 范围为 1–255 字节。适合国家码、固定设备编码、固定哈希片段等长度稳定的字段。

CHAR(64) 存放大量长度差异明显的内容，会造成空间浪费。业务名称、订单号、URL 通常更适合 VARCHAR。

4.2 VARCHAR 是大多数业务文本的首选

VARCHAR(M) 为变长字符串，当前 4.x 文档给出的 M 范围是 1–65533 字节。Doris 使用 UTF-8，英文字符通常占 1 字节，中文字符通常占 3 字节，所以 M 是字节上限，不能直接当作中文字符数。

适合 VARCHAR 的字段包括：

• 订单号；
• 用户名；
• 城市、地区和渠道；
• URL；
• 有明确最大长度的业务编码。

Key、分区、分桶和高频过滤字段应尽量使用长度受控的类型。过大的 VARCHAR 上限会增加内存估算和异常数据风险。

4.3 STRING 适合长文本值列

STRING 默认支持 1 MB，可通过 BE 参数调整，理论上最高接近 2 GB。它只能作为 Value 列，不能作为 Key、分区列或分桶列。

适用内容：

• 日志原文；
• 文档正文；
• 长消息；
• API 原始响应；
• 用于全文检索的文本字段。

超大文件、图片、压缩包等二进制对象更适合放入对象存储，Doris 保存 URI、元数据和可分析字段。把大对象直接塞进分析表会影响扫描、Compaction、网络传输和备份。

4.4 VARBINARY 的当前边界

4.x 文档已经定义 VARBINARY，用于字节级存储和比较，适合哈希、加密数据和压缩字节。当前文档同时说明：该类型主要用于外部 Catalog 的二进制字段映射和计算，Doris 内部表暂不支持把它作为可持久化列类型。

遇到二进制字段时，应先明确需求：

• 只需保存摘要：使用 VARCHAR 存十六进制或 Base64；
• 需要保存原始文件：对象存储 + URI；
• 从外部系统联邦查询：评估 VARBINARY 映射能力；
• 需要参与文本分析：在上游解码成结构化字段。

五、时间类型：先定义业务语义，再讨论格式

时间问题很少源于格式本身，更多来自语义不清。2026-08-16 09:00:00 到底是宁波门店的营业时间，还是全球统一事件发生时刻？两者需要不同建模方式。

5.1 DATE：只表达日历日期

DATE 适合结算日、业务日、生日、分区日期等仅包含年月日的字段。它不保存时区，修改会话 time_zone 不会改变已存值。

日期加减和截断应使用时间函数：

SELECT
    DATE_ADD(order_date, INTERVAL 7 DAY),
    DATE_TRUNC(order_date, 'month');

不要把日期强制转成整数后做算术，这会隐藏闰年、月长和边界问题。

5.2 DATETIME(p)：无时区的本地业务时间

DATETIME(p) 支持 0–6 位小数秒精度。它适合门店开门时间、批次生成时间、某地区业务系统的本地时间等场景。

DATETIME 存储的是已经确定的日历值。会话时区后续发生变化，已存时间不会跟着变化。跨地区系统若把各地本地时间直接写入同一列，查询者很难知道每一行属于哪个时区。

5.3 TIMESTAMPTZ(p)：跨时区绝对时刻

Doris 4.x 当前文档提供 TIMESTAMPTZ(p)，用于带时区意识的事件时刻。写入时统一转换为 UTC，查询时根据会话 time_zone 展示。

SET time_zone = 'Asia/Shanghai';

CREATE TABLE global_event (
    event_id BIGINT,
    occurred_at TIMESTAMPTZ(3),
    event_name VARCHAR(64)
)
DUPLICATE KEY(event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

适用场景：

• 跨区域日志；
• 全球用户行为；
• 国际支付与交易；
• 多时区设备遥测；
• 需要统一时间线的 Trace。

时间治理需要明确四项规范：

1. 源数据是否带时区；
2. 写入会话使用什么 time_zone；
3. 存储列选择 DATETIME 还是 TIMESTAMPTZ；
4. 展示层按哪个时区转换。

夏令时地区还要准备春季跳时和秋季重复时间的测试数据。

六、复杂类型：减少无意义拆表，同时保留可分析结构

传统关系模型常把每个集合拆成子表。分析系统中，部分对象天然适合保存在一行，例如商品标签、行为步骤、设备属性和联系人信息。Doris 的复杂类型为这类数据提供了更直接的表达。

6.1 ARRAY：同类型、有顺序的集合

ARRAY<T> 表示同类型元素组成的有序集合，支持多层嵌套，当前文档给出的最大嵌套深度为 9。

CREATE TABLE user_profile_type_demo (
    user_id BIGINT,
    tags ARRAY<STRING>,
    scores ARRAY<DOUBLE>,
    recent_product_ids ARRAY<BIGINT>
)
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

常见函数：

SELECT
    user_id,
    array_size(tags),
    array_contains(tags, 'vip'),
    array_distinct(tags),
    array_map(x -> upper(x), tags)
FROM user_profile_type_demo;

ARRAY 适合单行内部的小型集合。集合无限增长、需要独立生命周期、需要大量 Join 或需要按元素频繁更新时，拆成明细表更容易治理。

当前文档还规定，ARRAY 通常不能作为 Key、分区或分桶列，也不能作为 Join Key。建表前要先确认查询模式。

6.2 MAP：动态键值集合

MAP<K,V> 适合键集合具有一定变化、值类型相对统一的场景。

attributes MAP<STRING, STRING>

典型内容：

{
  "channel": "app",
  "city": "Ningbo",
  "device": "iOS"
}

查询示例：

SELECT
    user_id,
    attributes['channel'] AS channel,
    element_at(attributes, 'city') AS city
FROM user_profile_type_demo;

MAP 的键和值都可以为 NULL，相同键也可能出现，数据源治理要提前规定冲突处理。MAP 不支持作为 Key、分区、分桶或 Join Key，也不支持创建普通索引。热点字段若经常参与过滤和 Join，应提升为独立列。

6.3 STRUCT：固定字段的嵌套对象

STRUCT 适合字段名称和类型相对稳定的嵌套对象。

contact STRUCT<
    email: STRING,
    phone: STRING,
    verified: BOOLEAN
>

访问方式：

SELECT
    user_id,
    contact.email,
    contact.verified
FROM user_profile_type_demo;

STRUCT 可以嵌套 ARRAY、MAP 和其他 STRUCT。嵌套层级过深会增加数据理解、导入和查询难度。建议把高频过滤、分区、分桶和 Join 字段留在顶层。

6.4 JSON：保存完整 JSONB 对象

Doris 的 JSON 类型使用 JSONB 二进制格式，支持标准 JSON 的布尔、Null、数值、字符串、数组和对象。它比普通 STRING 更适合 JSON 解析与字段提取。

CREATE TABLE api_response (
    id BIGINT,
    body JSON
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

SELECT
    id,
    json_extract_string(body, '$.status') AS status,
    json_extract_int(body, '$.duration_ms') AS duration_ms
FROM api_response;

需要特别区分：

SELECT CAST('null' AS JSON) IS NULL;

这里的 JSON null 是一个已知 JSON 值，SQL 层面与 NULL 含义不同，结果会是 0。数据质量检查需要同时考虑 SQL NULL、JSON Null、空字符串、空对象和字段缺失。

6.5 VARIANT：字段持续演进时保留灵活性

VARIANT 用于半结构化 JSON。写入时 Doris 根据路径推断类型，并把高频路径自动抽取为列式子列，使热点字段可以继续享受裁剪、向量化和索引能力。

CREATE TABLE event_log (
    event_time DATETIME(3),
    event_id BIGINT,
    payload VARIANT
)
DUPLICATE KEY(event_time, event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

查询子路径时，建议显式转换为业务类型：

SELECT
    CAST(payload['user_id'] AS BIGINT) AS user_id,
    CAST(payload['duration_ms'] AS INT) AS duration_ms
FROM event_log
WHERE CAST(payload['event_type'] AS STRING) = 'click';

VARIANT 的使用原则：

• Schema 经常变化，查询集中在少量热点路径；
• 核心路径通过 Schema Template 固定类型；
• 非热点长尾字段保留动态性；
• 过滤、聚合、排序和 Join 前先转换子路径；
• 超宽 JSON 关注子列数量、Compaction 和 Storage Format V3；
• 频繁 SELECT payload 返回整份文档时评估 DOC Mode；
• 顶层业务 Key、分区和分桶字段继续使用静态列。

稳定字段全部塞进 VARIANT 会削弱 Schema 约束。真正高频的用户 ID、事件时间、租户 ID、状态和金额应保持静态类型。

6.6 复杂类型与拆表的决策方法

复杂类型能够减少表数量和 Join 数量，也会把更多语义放入单行。选择时可以从四个维度判断。

生命周期。 元素与主记录一起创建、更新和删除时，放在 ARRAY、MAP 或 STRUCT 中较自然；元素拥有独立生命周期时，明细表更合适。

集合规模。 每行只有几个到几十个元素时，复杂类型易于使用；单行集合可能增长到数万甚至更多时，导入、更新、展开和内存使用都要重新评估。

查询方式。 查询经常读取整份局部对象，复杂类型能减少 Join；查询经常按元素做全局过滤、排序、去重和关联，明细表更利于索引与并行计算。

更新频率。 主记录与集合一起批量重写时，复杂类型可保持结构完整；单个元素需要高频独立更新时，拆表会减少写放大。

例如，订单的优惠券列表通常数量有限，适合 ARRAY；订单商品行有价格、数量、退款和发货等独立状态，通常应建订单明细表；用户的少量联系人适合 STRUCT 或 ARRAY；数千个动态设备属性可评估 MAP 或 VARIANT，并把高频属性提升为静态列。

6.7 复杂类型的索引与查询注意事项

ARRAY 当前支持在部分元素类型上建立倒排索引，可加速 array_contains 和 arrays_overlap 等条件。MAP 和 STRUCT 的索引能力更受限制，热点查询字段更适合独立列。VARIANT 可以对路径使用 BloomFilter 或倒排索引，路径类型稳定是索引生效的前提。

查询复杂类型时，建议显式投影所需字段，避免宽表上的 SELECT *。对超宽 VARIANT，整列返回会触发文档重建；热点路径查询应直接写 payload['path'] 并转换到具体类型。Schema 漂移、同一路径类型冲突和子列数量增长需要纳入监控。

七、BITMAP 与 HLL：两种去重工具解决不同问题

用户数、设备数、曝光人数和人群交并补是分析系统的高频需求。直接 COUNT(DISTINCT id) 在超大数据量下可能消耗大量内存和网络。Doris 提供 BITMAP 与 HLL 两类聚合状态。

7.1 BITMAP：精确集合计算

BITMAP 可以表达整数集合，支持 AND、OR、XOR、NOT 等运算，适合：

• 精确 UV；
• 用户画像圈选；
• 留存分析；
• 人群交集、并集和差集；
• 需要继续使用成员集合的场景。

SELECT bitmap_union_count(to_bitmap(user_id))
FROM user_event;

BITMAP 列在 Aggregate 表中通常使用 BITMAP_UNION。实时场景需要关注 ID 映射。原始 ID 无法直接表示为非负整数时，常见方案是构建全局字典，或者根据精度要求选择 bitmap_hash64。

BITMAP 结果精确，代价是状态体积和写入成本通常高于 HLL。

7.2 HLL：大规模近似去重

HLL 使用 HyperLogLog 算法估算基数。Doris 文档给出的典型误差约 1%，极端情况下可能达到 2%。它不保存完整成员集合，状态大小更稳定。

SELECT hll_union_agg(hll_hash(user_id))
FROM user_event;

适合：

• 网站 UV 趋势；
• 广告曝光去重；
• 设备规模估算；
• 大数据量下的近似基数；
• 对少量误差有容忍度的经营指标。

财务审计、结算人数、权益发放等 100% 精确场景不适合 HLL。

7.3 选择方法

八、函数体系：从一行变换到多行分析

Doris 内置函数数量很多，逐个背诵收益有限。更有效的方法是先建立分类地图，再根据输入输出关系选择函数。

8.1 标量函数：一行输入，一行输出

常见类别：

• 字符串：lower、upper、substring、regexp_replace；
• 数学：abs、round、ceil、floor；
• 日期：date_add、date_sub、date_trunc、timestampdiff；
• 条件：if、case when、coalesce、nullif；
• JSON/VARIANT：路径提取、类型检查和转换；
• ARRAY/MAP：元素访问、过滤、排序、聚合。

示例：

SELECT
    order_id,
    upper(city) AS city_upper,
    round(amount, 2) AS amount_display,
    date_trunc(create_time, 'day') AS order_day,
    coalesce(coupon_code, 'NO_COUPON') AS coupon_code
FROM orders;

8.2 聚合函数：多行输入，每组一行

SELECT
    city,
    count(*) AS order_count,
    sum(amount) AS total_amount,
    avg(amount) AS avg_amount,
    max(create_time) AS latest_order_time
FROM orders
GROUP BY city;

聚合函数会改变行数。复杂查询中需要特别留意分组粒度。GROUP BY city, user_id 和 GROUP BY city 表达的是两套业务口径。

8.3 高阶函数：Lambda 处理 ARRAY

高阶函数允许把 Lambda 逻辑应用到数组元素。

SELECT
    array_map(x -> upper(x), tags),
    array_filter(x -> x > 60, scores),
    array_exists(x -> x = 'vip', tags)
FROM user_profile_type_demo;

这类函数适合单行内部的集合计算。数组很大、元素需要与外表 Join 或需要频繁更新时，明细表结构通常更容易扩展。

8.4 函数的确定性会影响优化空间

函数可以按照结果稳定性分为确定性函数和非确定性函数。upper('doris') 对相同输入始终返回相同结果，优化器可以进行常量折叠；now()、random() 等函数与执行时间或随机状态有关，缓存、物化视图改写和表达式重用需要更加谨慎。

UDF 也需要声明或约定稳定性。当前 Python UDF 支持 immutable、stable 和 volatile 三种 volatility。确定性声明错误会造成缓存或改写风险，过于保守的声明又会失去优化机会。团队的函数规范应记录输入类型、返回类型、NULL 策略、异常策略、确定性、性能预算和版本负责人。

8.5 函数调用也有性能成本

同样的业务逻辑可以写成多种形式。高频查询中，应减少重复解析、重复 CAST 和重复路径提取。复杂表达式可以先在 CTE 中计算一次：

WITH normalized AS (
    SELECT
        event_id,
        CAST(payload['duration_ms'] AS INT) AS duration_ms,
        CAST(payload['service'] AS STRING) AS service
    FROM application_event
)
SELECT service, AVG(duration_ms)
FROM normalized
GROUP BY service;

这种写法同时提升可读性和可测试性。优化器是否消除重复表达式要以 EXPLAIN 和 Profile 为准，关键报表不要依靠猜测。

九、窗口函数：保留原始行，补充上下文计算

普通聚合会把多行压缩成一行。窗口函数在指定窗口内计算，同时保留每一条原始记录。

基本结构：

function(...) OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN ... AND ...
)

9.1 排名

SELECT
    department,
    employee,
    salary,
    row_number() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS row_num,
    rank() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank,
    dense_rank() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dense_salary_rank
FROM employee_salary;

三者差异：

• row_number 每行编号唯一；
• rank 并列后会跳号；
• dense_rank 并列后连续编号。

9.2 累计与移动窗口

SELECT
    dt,
    amount,
    sum(amount) OVER (
        ORDER BY dt
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount,
    avg(amount) OVER (
        ORDER BY dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_7_rows_avg
FROM daily_sales;

ROWS 表示按物理行范围定义窗口。使用日期范围进行业务天数窗口时，需要认真确认缺失日期、重复日期和 RANGE 语义。

9.3 LAG 与 LEAD

SELECT
    dt,
    revenue,
    lag(revenue, 1, 0) OVER (ORDER BY dt) AS previous_revenue,
    revenue - lag(revenue, 1, 0) OVER (ORDER BY dt) AS revenue_delta
FROM daily_revenue;

适合环比、同比前置计算、状态迁移和事件序列分析。

窗口函数在 SQL 处理流程的后段执行。WHERE 不能直接引用同层窗口函数别名，常见写法是使用子查询或 CTE：

WITH ranked AS (
    SELECT
        user_id,
        amount,
        row_number() OVER (
            PARTITION BY user_id
            ORDER BY create_time DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

十、LATERAL VIEW：把集合字段展开成标准行

LATERAL VIEW 配合 EXPLODE 等生成器函数，将一个集合字段扩展成多行，并保留原行中的其他列。

SELECT
    user_id,
    tag
FROM user_profile_type_demo
LATERAL VIEW explode(tags) tag_table AS tag;

假设一行数据为：

user_id = 1001
tags = ['vip', 'ai', 'doris']

展开后得到三行：

1001, vip
1001, ai
1001, doris

常见用途：

• 展开用户标签；
• 展开商品属性；
• 展开 MAP 的键值对；
• 将分隔字符串拆成多行；
• 展开 JSON 数组；
• 展开后继续聚合、过滤和 Join。

SELECT
    tag,
    count(*) AS user_count
FROM user_profile_type_demo
LATERAL VIEW explode(tags) tag_table AS tag
GROUP BY tag
ORDER BY user_count DESC;

explode 遇到 NULL 或空集合时通常不产生行。需要保留原记录时使用对应的 explode_outer 系列函数，并在实验中验证空集合、SQL NULL 和包含 NULL 元素的数组。

十一、类型转换与 NULL：正确性问题最集中的区域

11.1 显式 CAST 优先用于跨边界转换

SELECT
    CAST(raw_amount AS DECIMAL(18,2)),
    CAST(event_time_text AS DATETIME(3)),
    CAST(payload['user_id'] AS BIGINT)
FROM staging_table;

显式转换让 SQL 的意图更清楚，也更便于检查错误行。隐式转换适合类型天然兼容、风险可控的表达式。跨系统迁移、核心指标和索引过滤条件建议使用显式转换。

11.2 字符串转数值要处理脏数据

源系统中可能出现空字符串、N/A、千分位、货币符号和全角字符。直接 CAST 容易导致导入过滤或查询错误。生产链路应在数据接入层建立规则：

• 空字符串统一转 SQL NULL；
• 去除千分位与货币符号；
• 记录无法转换的原值和错误原因；
• 设置质量阈值；
• 对账成功后再进入正式表。

11.3 SQL NULL 与 JSON Null

以下状态需要分别处理：

COUNT(*)、COUNT(col)、COALESCE、IS NULL 和 JSON 提取函数对这些状态的处理不同。指标口径文档应明确“缺失”“未知”“空值”和“无记录”各自如何计算。

11.4 隐式转换可能让索引失效

VARIANT 子路径实际存储为 STRING，却用整数常量比较：

SELECT *
FROM event_log
WHERE payload['user_id'] = 1001;

这类类型不匹配可能导致结果异常或索引无法使用。更稳定的写法：

SELECT *
FROM event_log
WHERE CAST(payload['user_id'] AS BIGINT) = 1001;

核心路径可以在 VARIANT Schema Template 中固定为 BIGINT，减少批次间类型漂移。

十二、UDF：功能扩展伴随运行时与治理成本

内置函数覆盖需求时，应优先使用内置函数。Doris 内置函数由 C++ 实现，通常可以走向量化执行、常量折叠、类型推导、缓存和优化器改写路径。

12.1 Java UDF 的适用场景

Java UDF、UDAF、UDWF 和 UDTF 适合稳定、长期复用、内置函数难以表达的逻辑，例如：

• 企业专有编码解析；
• 特殊脱敏规则；
• 行业算法；
• 自定义聚合状态；
• 特殊的一行转多行逻辑。

使用前需要解决：

• JAR 在所有 BE 上可访问；
• 依赖冲突和类加载；
• 输入输出类型映射；
• NULL 和异常处理；
• 函数确定性；
• 性能压测；
• 版本升级与回滚；
• 运行日志和故障隔离。

12.2 Python UDF 是 4.1.3 的实验能力

截至本文核验日期，Python UDF/UDAF/UDTF 在 Apache Doris 4.1.3 中标记为实验功能。它支持标量、聚合和表函数，也提供基于 Pandas 的向量化模式。

官方建议在性能敏感场景优先使用内置 C++ 函数。Python 路径适合内置函数无法满足、数据规模适中、团队愿意管理 Python 运行时的任务。

生产评估还要覆盖：

• 所有 BE 上的完整 Python 版本；
• pandas、pyarrow 等依赖；
• Conda 或 venv 环境管理；
• Python UDF Server 日志；
• 函数 volatility；
• 向量化和逐行模式性能；
• 超时、内存和异常传播。

这项能力在本日课程中作为扩展阅读，基础练习不依赖它。

十三、实战：为订单、用户画像和日志设计字段

完整 SQL 已放在本文配套目录：

sql/day06-schema-and-functions.sql

13.1 订单明细表

CREATE DATABASE IF NOT EXISTS doris_day06;
USE doris_day06;

CREATE TABLE order_detail (
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    order_status TINYINT NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    tax_amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) NOT NULL,
    city VARCHAR(64),
    coupon_codes ARRAY<STRING>,
    created_at DATETIME(3) NOT NULL,
    paid_at TIMESTAMPTZ(3),
    remark STRING
)
DUPLICATE KEY(order_id, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

设计说明：

• 金额使用 DECIMAL(18,2)；
• 币种使用固定三位 CHAR(3)；
• 状态使用 TINYINT；
• 本地创建时间使用 DATETIME(3)；
• 支付发生时刻用于跨区域对账，使用 TIMESTAMPTZ(3)；
• 优惠券集合使用 ARRAY<STRING>；
• 长备注作为 STRING Value 列。

13.2 用户画像表

CREATE TABLE user_profile (
    user_id BIGINT NOT NULL,
    register_date DATE,
    tags ARRAY<STRING>,
    attributes MAP<STRING, STRING>,
    contact STRUCT<email:STRING, phone:STRING, verified:BOOLEAN>,
    profile_ext VARIANT,
    updated_at DATETIME(3)
)
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

高频字段如 user_id、注册日期和更新时间保持静态列；标签、可选属性和联系人结构使用复杂类型；长期变化的扩展信息进入 VARIANT。

13.3 日志事件表

CREATE TABLE application_event (
    event_time TIMESTAMPTZ(3) NOT NULL,
    event_id BIGINT NOT NULL,
    service_name VARCHAR(128) NOT NULL,
    level VARCHAR(16),
    message STRING,
    payload VARIANT
)
DUPLICATE KEY(event_time, event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

日志发生时刻选择 TIMESTAMPTZ(3)，便于多个地域统一分析；服务名和级别保持静态列，方便过滤；原始消息使用 STRING；动态上下文放入 VARIANT。

13.4 函数练习

-- 1. 城市销售汇总
SELECT
    city,
    count(*) AS order_count,
    sum(amount) AS total_amount
FROM order_detail
GROUP BY city;

-- 2. 每个用户最近一笔订单
WITH ranked AS (
    SELECT
        *,
        row_number() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC
        ) AS rn
    FROM order_detail
)
SELECT * FROM ranked WHERE rn = 1;

-- 3. 标签展开统计
SELECT
    tag,
    count(*) AS user_count
FROM user_profile
LATERAL VIEW explode(tags) tag_table AS tag
GROUP BY tag;

-- 4. VARIANT 热点路径
SELECT
    service_name,
    CAST(payload['duration_ms'] AS INT) AS duration_ms
FROM application_event
WHERE CAST(payload['trace_id'] AS STRING) = 'trace-1001';

13.5 验收清单

完成实验后提交：

• 三张表的 SHOW CREATE TABLE；
• 每个字段的类型选择说明；
• 至少一条窗口函数 SQL；
• 至少一条 LATERAL VIEW SQL；
• 一个浮点误差示例；
• 一个 SQL NULL 与 JSON Null 对比；
• 一个 VARIANT 显式 CAST 示例；
• 一份《Doris 数据类型与函数使用规范》。

十四、团队级《数据类型与函数使用规范》模板

14.1 类型规范

1. 金额、税率和结算值统一使用 DECIMAL(P,S)；
2. ID 类型根据上限选择，默认业务主 ID 使用 BIGINT；
3. 浮点列不得直接参与等值 Join；
4. 本地业务时间使用 DATETIME(p)，全球事件时间优先评估 TIMESTAMPTZ(p)；
5. 高频过滤和 Join 字段使用静态类型；
6. STRING 只用于 Value 列；
7. ARRAY、MAP、STRUCT 的嵌套深度保持可读；
8. VARIANT 只承接动态部分，核心路径通过静态列或 Schema Template 固定；
9. 精确去重使用 BITMAP，近似规模估算使用 HLL；
10. Schema Change 前检查 Key、分区、分桶、索引和历史数据转换成本。

14.2 函数规范

1. 内置函数优先；
2. 核心指标明确 NULL 处理；
3. 时间函数显式指定会话时区；
4. 窗口函数明确 PARTITION BY、ORDER BY 和 Frame；
5. LATERAL VIEW 明确空集合与 NULL 行保留策略；
6. VARIANT 与 JSON 子路径在比较和聚合前显式 CAST；
7. UDF 必须有单元测试、性能测试、版本和回滚方案；
8. 非确定性函数不得进入依赖稳定缓存的关键报表；
9. 复杂表达式拆成 CTE，保留中间口径；
10. 发布前使用边界值和脏数据回归。

14.3 建表评审的十条红线

以下问题出现任意一条，都应暂停发布并补充验证材料：

1. 金额字段使用 FLOAT 或 DOUBLE，却没有误差说明和对账样本；
2. 时间字段缺少时区定义，字段名只有 time、date 等模糊词；
3. MySQL UNSIGNED BIGINT 直接映射到 Doris BIGINT，没有检查最大值；
4. 所有字段统一使用 STRING，核心指标依赖运行时转换；
5. VARCHAR 长度由经验填写，没有真实字节分布和最长样本；
6. ARRAY 或 MAP 元素数量可能无限增长，仍放入单行；
7. VARIANT 承载核心金额、状态和主键路径，却没有固定类型方案；
8. Bitmap 使用哈希生成 ID，同时业务要求严格精确；
9. 窗口函数缺少稳定排序列，重复值之间的结果顺序无法复现；
10. UDF 没有异常、NULL、并发、资源和回滚测试。

红线检查的目标是把隐患留在设计阶段。数据库上线后的类型修改可能涉及全量转换、数据重写和长期兼容，早期多花一小时评审，往往可以节省数天迁移和回滚时间。

14.4 建议维护一套长期类型回归数据

团队可以建立一张小型 type_regression_cases 表，每次 Doris 升级、驱动升级和 SQL 改写后自动运行。样本至少覆盖：

• 整数最小值、最大值和越界值；
• DECIMAL 进位、乘法、除法和舍入；
• 0.1、0.2 等典型浮点值；
• 月末、年末、闰日、夏令时切换和跨时区时间；
• 空字符串、全空格、中文、Emoji 和最大长度文本；
• SQL NULL、JSON Null、字段缺失、空数组和空 Map；
• Variant 同一路径的整数、字符串和对象冲突；
• Bitmap/HLL 的已知基数样本；
• 窗口函数中的重复排序值；
• LATERAL VIEW 的空集合、单元素和超长集合。

回归结果要保留 Doris 版本、会话变量、客户端版本、SQL 文本和期望输出。这样可以把“升级后感觉有点不一样”转化为可比较、可定位的证据。

十五、知识巩固

题目 1

MySQL Client 能够连接 Doris，是否可以直接认定全部 MySQL SQL 无需修改？

题目 2

金融金额字段应该优先选择 DOUBLE 还是 DECIMAL？为什么？

题目 3

DATETIME 与 TIMESTAMPTZ 的核心语义差异是什么？

题目 4

STRING 为什么不适合作为 Key、分区和分桶列？

题目 5

用户标签适合使用 ARRAY<STRING> 的前提有哪些？

题目 6

动态 JSON 中的 user_id 经常用于过滤，如何提升类型稳定性和索引可用性？

题目 7

BITMAP 与 HLL 的主要差异是什么？

题目 8

GROUP BY 与窗口函数对结果行数的影响有什么区别？

题目 9

LATERAL VIEW explode(tags) 遇到空数组时会发生什么？需要保留原行时如何处理？

题目 10

Python UDF 在当前版本中处于什么成熟度？生产使用前需要验证哪些内容？

答案要点

1. 不能。还要验证语法、函数、类型转换、结果语义、执行计划和性能；
2. DECIMAL，金额需要固定小数和可审计精度；
3. DATETIME 保存无时区日历值，TIMESTAMPTZ 统一转换为 UTC 并按会话时区展示；
4. 当前类型能力限制，同时长文本会放大索引、比较和内存成本；
5. 元素同类型、集合规模可控、主要在单行内计算、无需频繁独立更新；
6. 提升为静态 BIGINT 列，或通过 VARIANT Schema Template 固定路径类型，查询时显式 CAST；
7. BITMAP 结果精确且支持集合运算，HLL 只估算基数并存在小幅误差；
8. GROUP BY 压缩行数，窗口函数保留原始行并添加计算列；
9. 普通 explode 通常不产生行；需要保留时使用对应的 explode_outer；
10. 4.1.3 中为实验功能；需要验证运行时、依赖、性能、NULL、异常、日志、发布与回滚。

十六、本日小结

今天完成了 Doris 数据工程的第一块基础：用类型表达业务，用函数表达分析。

请记住六条主线：

1. MySQL 协议兼容降低连接成本，SQL 迁移仍需语义回归；
2. 金额和审计值使用 DECIMAL，近似连续量使用 FLOAT/DOUBLE；
3. 时间字段先定义“日历时间”或“绝对时刻”；
4. ARRAY、MAP、STRUCT 用于稳定的局部结构，VARIANT 承接持续变化的 JSON；
5. BITMAP 服务精确集合，HLL 服务近似基数；
6. 内置函数优先，UDF 进入依赖、性能和版本治理流程。

Day 7 将进入 Doris 最关键的建模选择：Duplicate Key、Aggregate Key 与 Unique Key。相同字段结构放入不同数据模型，会产生完全不同的写入、更新、聚合和查询行为。

官方资料索引

本文以 Apache Doris 4.x 官方文档与用户提供的培训材料为基础，核验日期为 2026-08-16：

• MySQL Protocol：https://doris.apache.org/docs/4.x/connection-integration/mysql-proto/
• DECIMAL：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/numeric/DECIMAL/
• FLOAT / DOUBLE：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/numeric/FLOATING-POINT/
• CHAR / VARCHAR / STRING：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/string-type/CHAR/
• TIMESTAMPTZ：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/date-time/TIMESTAMPTZ/
• ARRAY：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/semi-structured/ARRAY/
• MAP：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/semi-structured/MAP/
• STRUCT：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT/
• JSON：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/semi-structured/JSON/
• VARIANT：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT/
• BITMAP：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/aggregate/BITMAP/
• HLL：https://doris.apache.org/docs/4.x/sql-manual/basic-element/sql-data-types/aggregate/HLL/
• Window Functions：https://doris.apache.org/docs/4.x/sql-manual/sql-functions/window-functions/overview/
• Lateral View：https://doris.apache.org/docs/4.x/query-data/lateral-view/
• Python UDF：https://doris.apache.org/docs/4.x/query-data/udf/python-user-defined-function/
• Download / Version：https://doris.apache.org/download/