Doris 查询指南

1. SELECT 语句全貌

Doris 的 SELECT 兼容 MySQL 语法，完整结构如下（各子句须严格按此顺序书写）：

SELECT
    [hint_statement, ...]
    [ALL | DISTINCT | DISTINCTROW | ALL EXCEPT (col1, col2, ...)]
    select_expr [, select_expr ...]
    [FROM table_references
        [PARTITION partition_list]
        [TABLET tabletid_list]
        [TABLESAMPLE sample_value [ROWS | PERCENT] [REPEATABLE pos_seek]]]
    [WHERE where_condition]
    [GROUP BY [GROUPING SETS | ROLLUP | CUBE] {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name']

关键规则：

WHERE 先于 GROUP BY 过滤行；HAVING 过滤聚合结果（可使用聚合函数，WHERE 不能）；
GROUP BY 后跟 WITH ROLLUP 可多次汇总结果；GROUPING SETS / ROLLUP / CUBE 是 GROUP BY 的多维扩展；
列可在 GROUP BY / ORDER BY / HAVING 中通过列名、别名或位置序号（从 1 开始）引用；
未指定 LIMIT 时，ORDER BY 默认只返回前 65535 条排序结果（限制内存）；
使用 LIMIT m, n 前必须配合 ORDER BY，否则结果不确定；
ALL EXCEPT (col)：返回除指定列外的所有列（1.2+ 支持）；
TABLESAMPLE：按行数或百分比伪随机抽样，REPEATABLE seed 可复现抽样，仅限 OLAP 表。

2. 数据类型速查

2.1 数值类型

类型	存储（字节）	范围/说明
BOOLEAN	1	0=false，1=true
TINYINT	1	有符号 [-128, 127]
SMALLINT	2	[-32768, 32767]
INT	4	[-2147483648, 2147483647]
BIGINT	8	[-2^63, 2^63-1]
LARGEINT	16	[-2^127+1, 2^127-1]
FLOAT	4	不精确浮点
DOUBLE	8	不精确浮点
DECIMAL(P,S)	4/8/16/32	高精度定点；P≤9 占 4B，≤18 占 8B，≤38 占 16B，≤76 占 32B（开启 enable_decimal256）

注意：金额等精确场景用 DECIMAL，不用 FLOAT/DOUBLE（浮点不精确，且不支持倒排索引）。

2.2 日期时间类型

类型	存储（字节）	说明
DATE	4	'0000-01-01' ~ '9999-12-31'，格式 yyyy-MM-dd
DATETIME[(P)]	8	时间精度 P∈[0,6]（微秒），格式 yyyy-MM-dd HH:mm:ss.SSSSSS
TIMESTAMP_NS	8	固定纳秒精度，范围 1677-2262 年

2.3 字符串类型

类型	说明
CHAR(M)	定长，M∈[1,255] 字节
VARCHAR(M)	变长，M∈[1,65533] 字节，UTF-8 编码（中文占 3 字节）
STRING	变长，默认上限 1MB（可调至 2GB）；只能用在 Value 列，不能作 Key 列、分区分桶列

2.4 半结构化类型

类型	说明
ARRAY<T>	数组，不能作 Key 列；仅 Duplicate / Unique 模型
MAP<K,V>	键值映射，不能作 Key 列；仅 Duplicate / Unique 模型
STRUCT	结构体，Field 名和数量固定且总为 Nullable；仅 Duplicate 模型
JSON	二进制 JSON，通过 JSON 函数访问内部字段
VARIANT	动态类型，专为半结构化 JSON 设计，自动拆子列存储提升分析性能；只能用在 Value 列

2.5 聚合类型（配合对应聚合函数使用）

类型	用途
HLL	模糊去重，大基数下优于 COUNT DISTINCT，误差约 1%~2%；配 HLL_UNION，用 hll_cardinality 等查询
BITMAP	精确去重；配 BITMAP_UNION，用 bitmap_union_count 等查询
QUANTILE_STATE	分位数近似值；配 QUANTILE_UNION，用 QUANTILE_PERCENT 查询
AGG_STATE	聚合函数状态，配合 state/merge/union 函数组合器

2.6 IP 与 UUID

IPv4（4 字节）/ IPv6（16 字节）：二进制存储，配合 ipv4_* / ipv6_* 函数；
UUID（16 字节）：原生 128 位标识符，小写标准文本显示。

可用 SHOW DATA TYPES; 查看完整列表。

3. 基础查询

-- 1. 条件过滤 + IN
SELECT Name FROM student WHERE age IN (18, 20, 25);

-- 2. 排除指定列
SELECT * EXCEPT(age) FROM student;

-- 3. 分组聚合
SELECT type, AVG(price) FROM tb_book GROUP BY type;

-- 4. 去重
SELECT DISTINCT type FROM tb_book;

-- 5. 排序 + 限量
SELECT * FROM tb_book ORDER BY id DESC LIMIT 3;

-- 6. 模糊查询（% 匹配多字符，_ 匹配单字符）
SELECT * FROM tb_book WHERE name LIKE '_h%';

-- 7. 函数与表达式
SELECT SUM(price) AS total, type FROM tb_book GROUP BY type;
SELECT *, price * 0.8 AS "折扣价" FROM tb_book;

-- 8. 抽样（伪随机选 Tablet，REPEATABLE 可复现）
SELECT * FROM t1 TABLESAMPLE(1000 ROWS) REPEATABLE 2 LIMIT 1000;

4. 连接查询（JOIN）

4.1 JOIN 类型

JOIN 类型	语义
INNER JOIN	两表都匹配的行
LEFT [OUTER] JOIN	内连接结果 + 左表无匹配的行（右列为 NULL）
RIGHT [OUTER] JOIN	内连接结果 + 右表无匹配的行（左列为 NULL）
FULL [OUTER] JOIN	左右两表全部行，无匹配补 NULL
CROSS JOIN	无连接条件的笛卡尔积
LEFT SEMI JOIN	返回左表中在右表有匹配的行（只输出左表列）
RIGHT SEMI JOIN	返回右表中在左表有匹配的行
LEFT ANTI JOIN	返回左表中在右表无匹配的行
RIGHT ANTI JOIN	返回右表中在左表无匹配的行
NULL AWARE LEFT ANTI JOIN	同 LEFT ANTI JOIN，但忽略左表连接列为 NULL 的行

-- INNER JOIN
SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

-- LEFT JOIN（找左表独有行）
SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;

-- LEFT SEMI JOIN（等价于 EXISTS）
SELECT t1.* FROM t1 LEFT SEMI JOIN t2 ON t1.c1 = t2.c1;

-- LEFT ANTI JOIN（等价于 NOT EXISTS）
SELECT t1.* FROM t1 LEFT ANTI JOIN t2 ON t1.c1 = t2.c1;

注意：INNER JOIN 支持非等值连接，但性能上强烈建议用等值连接；其他 JOIN 只支持等值连接。

4.2 实现方式：Hash Join 与 Nested Loop Join

Hash Join：基于等值连接列在右表建哈希表，左表数据流式匹配。等值条件专用，性能高；
Nested Loop Join：两层循环逐行比较，适合非等值（>、<）与笛卡尔积，性能较差。

4.3 分布式 Shuffle 策略（影响大表 JOIN 性能的关键）

策略	原理	网络开销	适用
Broadcast Join	右表全量分发到所有节点，左表不动	N × T(右表)	小表 JOIN 大表（小表做右表）
Partition Shuffle Join	两表按连接列哈希分区后分发到对应节点	T(左) + T(右)	无分桶可用的通用等值 JOIN
Bucket Shuffle Join	JOIN 条件命中左表分桶列时，左表不动，只把右表按左表分桶信息分发	T(右)	连接列为左表分桶列
Colocate Join	两表按相同分桶键 + 相同桶数预先分布，本地直接 JOIN，无 Shuffle	0（无网络传输）	建表时用 `colocate_with` 归组、分桶键一致的等值 JOIN

最佳实践：

连接列尽量用等值条件；
小表放右表（Broadcast 只广播右表）；
高频 JOIN 的两张表：建表时用相同分桶键 + 相同桶数 + colocate_with 归组，查询走 Colocate Join 零 Shuffle；
大表间 JOIN，连接列优先选左表分桶列，触发 Bucket Shuffle 减少网络传输。

5. 子查询

5.1 分类

标量子查询：始终返回单值（一行一列）；无数据时返回 NULL，可用于任何单值表达式位置；
非标量子查询：返回结果集（多行多列）；无数据时返回空集，可用于集合（IN/EXISTS 等）位置；
非相关子查询：不引用外层列，可独立计算；
相关子查询：引用外层查询的列（常在外层 WHERE 中作为过滤条件）。

-- 标量子查询（t2 为空表时返回 NULL）
SELECT * FROM t1 WHERE t1.c1 > (SELECT SUM(t2.c1) FROM t2);

-- 非标量子查询（t2 为空表时返回空集）
SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2);

-- 相关子查询（内层引用外层 t1.c2）
SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2 WHERE t2.c2 = t1.c2);

-- EXISTS / NOT EXISTS
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.c2 = t1.c2);

5.2 Doris 支持范围与限制

子查询类型	支持情况
所有非相关子查询	完整支持
WHERE / HAVING 中的相关标量子查询	支持，但相关条件必须为等值，且子查询输出必须是无 GROUP BY 的单个聚合函数结果
WHERE / HAVING 中的相关 IN / NOT IN / EXISTS / NOT EXISTS	支持；IN 输出必须单列、不能有 LIMIT、不能有聚合函数或 GROUP BY；EXISTS 不能同时带 OFFSET 和 LIMIT
SELECT 列表中的相关标量子查询	支持
嵌套子查询	仅支持与直接父查询相关，不支持跨级引用更外层查询的列

常见不支持写法示例：

-- 相关标量子查询：非等值关联，不支持
SELECT * FROM t1 WHERE t1.c1 = (SELECT MAX(t2.c1) FROM t2 WHERE t1.c2 > t2.c2);

-- 相关 IN：子查询带 GROUP BY，不支持
SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2 WHERE t1.c2 = t2.c2 GROUP BY t2.c1);

5.3 Mark Join

当 WHERE 中 (NOT) IN / (NOT) EXISTS 子查询与普通条件以 OR 组合时，普通 LEFT SEMI JOIN 改写会丢行，Doris 用 Mark Join 解决：输出左表全部行 + 一个 mark_join_flag 标记列（TRUE/FALSE/NULL），再把过滤条件改写为 mark_join_flag OR 其他条件。

5.4 使用注意

标量子查询返回多于一行时报运行时错误（相关：correlate scalar subquery must return only 1 row；非相关：Expected EQ 1）。

6. 聚合与多维分析

6.1 GROUP BY 与 HAVING

-- 分组聚合
SELECT type, AVG(price), SUM(price) FROM tb_book GROUP BY type;

-- HAVING 过滤聚合结果（可用聚合函数，WHERE 不行）
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;

-- GROUP BY 默认自动升序排序；不需要排序时加 ORDER BY NULL 省开销
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

6.2 多维聚合：ROLLUP / CUBE / GROUPING SETS

一次查询输出多层汇总（语义等价于多条 GROUP BY 用 UNION ALL 拼接，但只扫一次基表）：

语法	适用场景	输出汇总组合
ROLLUP	时间、地理、类别等自然层次维度逐级汇总	沿列顺序逐层汇总，从最细粒度到全局总计
CUBE	多个相互独立维度全面交叉分析	所有维度子集的全部组合（2^n 个层级）
GROUPING SETS	只关心若干特定维度组合	用户显式指定的分组集

-- ROLLUP：年→月→全局逐层汇总
SELECT YEAR(d_date), MONTH(d_date), SUM(ss_net_paid) AS total_sum
FROM store_sales, date_dim d1
WHERE d1.d_date_sk = ss_sold_date_sk
GROUP BY ROLLUP(YEAR(d_date), MONTH(d_date))
ORDER BY YEAR(d_date), MONTH(d_date);
-- 输出：按月小计、按年小计、全局总计（对应列 NULL）

-- CUBE：年 × 品类 × 州 全组合交叉
SELECT YEAR(d_date), i_category, ca_state, SUM(ss_net_paid) AS total_sum
FROM store_sales, date_dim d1, item, customer_address ca
WHERE ...
GROUP BY CUBE(YEAR(d_date), i_category, ca_state)
ORDER BY YEAR(d_date), i_category, ca_state;

-- GROUPING SETS：只算需要的几种组合，避免 CUBE 全量开销
SELECT YEAR(d_date), i_category, ca_state, SUM(ss_net_paid) AS total_sum
FROM ...
GROUP BY GROUPING SETS(
    (YEAR(d_date), i_category),
    (YEAR(d_date), ca_state),
    (YEAR(d_date), ca_state, i_category)
)
ORDER BY YEAR(d_date), i_category, ca_state;

选型建议：层次维度用 ROLLUP；独立维度全组合用 CUBE；只关心少数组合、CUBE 开销大时用 GROUPING SETS。

6.3 GROUPING 与 GROUPING_ID：识别小计行、区分两种 NULL

多维聚合结果里的 NULL 有两类：表数据本身的 NULL 与 聚合生成的 NULL（小计/总计行）。用 GROUPING 系列函数区分：

GROUPING(col)：该列是小计行（聚合生成 NULL）返回 1，否则返回 0（表内真实 NULL 也返回 0）；
GROUPING_ID(col1, col2, ...)：把各列 GROUPING 值拼成二进制位向量并转成整数，一行即可识别层级（如 CUBE(a,b)：0=明细，1=仅 a 汇总，2=仅 b 汇总，3=总计）。

-- 用法 1：HAVING 中只保留指定层级（总计、按年、按州）
GROUP BY CUBE(year(d_date), i_category, ca_state)
HAVING grouping(year(d_date)) = 1 AND grouping(i_category) = 1 AND grouping(ca_state) = 1
    OR grouping(year(d_date)) = 0 AND grouping(i_category) = 1 AND grouping(ca_state) = 1
    OR grouping(year(d_date)) = 1 AND grouping(i_category) = 1 AND grouping(ca_state) = 0;

-- 用法 2：IF 把小计 NULL 替换成可读标签
SELECT
    IF(grouping(year(d_date)) = 1, '全年合计', year(d_date)) AS year,
    IF(grouping(i_category) = 1, '全品类合计', i_category) AS category,
    SUM(ss_net_paid) AS total_sum
FROM ...
GROUP BY CUBE(year(d_date), i_category);

-- 用法 3：GROUPING_ID 一行识别层级
SELECT year(d_date), i_category, SUM(ss_net_paid) AS total_sum,
       GROUPING(year(d_date)), GROUPING(i_category),
       GROUPING_ID(year(d_date), i_category)
FROM ...
GROUP BY CUBE(year(d_date), i_category);

6.4 语义等价关系

GROUP BY ROLLUP(a, b, c) ≡ GROUPING SETS ((a,b,c),(a,b),(a),())；
GROUP BY CUBE(a, b, c) ≡ GROUPING SETS ((a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),())；
GROUPING SETS ((k1,k2),(k1),(k2),()) ≡ 四条 GROUP BY 的 UNION ALL（后者多次扫表，效率低）。

7. 共用表达式（CTE / WITH）

Doris 用 WITH 子句定义公共表表达式（CTE），一个语句可含多个逗号分隔的 CTE；CTE 之间可互相引用，但不支持递归 CTE。

-- 1. 基本 CTE
WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM table2)
SELECT b, d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

-- 2. 常量/派生 CTE + UNION ALL
WITH cte AS (
    SELECT 1 AS col1, 2 AS col2
    UNION ALL
    SELECT 3, 4
)
SELECT col1, col2 FROM cte;

-- 3. CTE 在 UPDATE 中使用（详见《Doris 数据更新指南》）
WITH discount_orders AS (
    SELECT * FROM orders WHERE o_totalprice > 100
)
UPDATE lineitem SET l_discount = l_discount * 0.9
FROM discount_orders
WHERE lineitem.o_orderkey = discount_orders.o_orderkey;

使用建议：复杂查询把重复的子查询提取为 CTE，既可读又可复用；CTE 名在语句内可被后续 CTE 引用，实现链式拆分。

8. UNION 组合查询

SELECT a FROM t1 WHERE a = 10 ORDER BY a LIMIT 10
UNION
SELECT a FROM t2 WHERE a = 11 ORDER BY a LIMIT 10;

-- UNION ALL（不去重，更快、内存更省；默认 UNION = UNION DISTINCT 去重）
SELECT age FROM student_01
UNION ALL
SELECT age FROM student_02;

各 SELECT 对应位置的列类型须一致，结果列名取第一个 SELECT；
需要对合并结果再 ORDER BY / LIMIT 时，把 UNION 放进子查询再取外层：

SELECT * FROM (
    SELECT age FROM student_01 UNION ALL SELECT age FROM student_02
) AS t1 ORDER BY age LIMIT 4;

9. 查询性能最佳实践

过滤先行：WHERE 尽量命中分区列（时间裁剪）与排序键前缀（前缀索引），减少扫描范围；
等值连接优先：JOIN 用等值条件走 Hash Join；小表放右表触发 Broadcast；
利用 Colocate Join：高频 JOIN 的表建表时统一分桶键 + 桶数 + colocate_with 归组，查询零 Shuffle；
避免无谓排序：不需要排序结果时用 ORDER BY NULL 关掉 GROUP BY 自动排序；
大结果集分批：需要全量结果时配合 LIMIT 分页，避免单次拉取过大；LIMIT 前必须 ORDER BY 保证稳定；
多维聚合按需选择：只算少数组合用 GROUPING SETS，别滥用 CUBE；
抽样排查数据：TABLESAMPLE + REPEATABLE 复现抽样，快速验证；
子查询注意相关性限制：多层相关子查询写不成时，改写为 JOIN 更高效（如 EXISTS → LEFT SEMI JOIN）。

10. 参考资料

Apache Doris 官方文档 · SELECT：https://doris.apache.org/docs/4.x/sql-manual/sql-statements/data-query/SELECT/
Apache Doris 官方文档 · 数据类型：https://doris.apache.org/zh-CN/docs/dev/table-design/data-type/
Apache Doris 官方文档 · Join：https://doris.apache.org/docs/3.x/query-data/join/
Apache Doris 官方文档 · 子查询：https://doris.apache.org/docs/2.1/query-data/subquery/
Apache Doris 官方文档 · 聚合多维分析：https://doris.incubator.apache.org/zh-CN/docs/4.x/query-data/multi-dimensional-analytics/

qrcode_for_gh_c7fda9436d1b_344

posted @ 2026-10-29 11:21 东百牧码人阅读(9) 评论(0) 收藏举报

刷新页面返回顶部

喜爱糖葫芦

如果你的成功定义是超越别人，那么注定会失败，因为世上总有比你强的人，如果成功的定义是超越自己，那么真的只要努力就会成功。

Doris 查询指南

Doris 查询指南

目录

1. SELECT 语句全貌

2. 数据类型速查

2.1 数值类型

2.2 日期时间类型

2.3 字符串类型

2.4 半结构化类型

2.5 聚合类型（配合对应聚合函数使用）

2.6 IP 与 UUID

3. 基础查询

4. 连接查询（JOIN）

4.1 JOIN 类型

4.2 实现方式：Hash Join 与 Nested Loop Join

4.3 分布式 Shuffle 策略（影响大表 JOIN 性能的关键）

5. 子查询

5.1 分类

5.2 Doris 支持范围与限制

5.3 Mark Join

5.4 使用注意

6. 聚合与多维分析

6.1 GROUP BY 与 HAVING

6.2 多维聚合：ROLLUP / CUBE / GROUPING SETS

6.3 GROUPING 与 GROUPING_ID：识别小计行、区分两种 NULL

6.4 语义等价关系

7. 共用表达式（CTE / WITH）

8. UNION 组合查询

9. 查询性能最佳实践

10. 参考资料

公告