行转列和列转行这两个词在报表相关的需求里出现的频率高得离谱。前几天一个做广告投放分析的朋友来问我Doris 里能不能像 Excel 拖透视表那样把user_id 渠道 金额这种一行一条的明细直接压成app、mini、pc三个横着放的列他翻了半天文档没找到PIVOT关键字怀疑自己看漏了章节。其实他没错Doris 到现在也没有 SQL Server、Snowflake 那种原生PIVOT语法行转列在 Doris 里只能靠 SQL 组合技巧拼出来。这篇文章把我自己在项目里实际用过的三种行转列写法完整拆开CASE WHEN GROUP BY、多表自连接、GROUP_CONCAT/ARRAY_AGG拼接再补上列转行的两条路子UNION ALL手写展开和LATERAL VIEW EXPLODE_SPLIT集合展开。写宽表报表的数据同学、用 SpringBoot 连 Doris 做动态查询的后端同学都能直接抄走。1. Doris 里做行转列真正难的是列在出生前就已经定死1.1 透视表在 Excel 里是拖动在 SQL 里是硬编码很多人第一次接触行转列是从 Excel 的透视表开始的把字段拖到列区域唰一下渠道就变成列头了。这套体验会给人一个错觉以为 SQL 也能这么干。实际上两者的执行模型完全不同Excel 是把整个数据集拉到内存里渲染的时候动态决定画几列而 SQL 是编译型的一条 SQL 的执行计划在提交的那一刻就要确定结果集有多少列、每列什么类型、什么名字。数据行里有多少个不同的渠道对编译期的执行计划来说是未知的。这就解释了一个现象所有在 Doris 里实现行转列的办法本质上都是同一件事——用已知的常量值渠道名、月份、状态码去写死一批列然后让每一行数据通过条件判断流到对应那一列里。你写的不是数据驱动列而是列驱动数据。理解了这一点后面三种写法的差异就都能看懂了它们只是在怎么把行灌进列这件事上选了不同的路径。1.2 静态列写死在 SQL 里动态列只能往外挪写死的列有一个绕不过去的痛点渠道加了一个月份多了一个SQL 就得改。而且这种改动往往发生在半夜发版的报表任务里。真实项目里我见过三种应对方式各有各的代价。第一种是应用层拼 SQL。先从维表或者SELECT DISTINCT里把渠道列表捞出来再在 Python、Java 里拼出一串SUM(CASE WHEN channelapp ...)最后提交给 Doris。灵活度最高代价是 SQL 文本每次都不一样Doris 前端的执行计划缓存和 SQL Cache 基本全废而且拼接过程必须做白名单校验否则就是 SQL 注入的温床。第二种是半结构化字段兜底。把不固定的那部分塞进 JSON 字符串或者 Doris 2.x 提供的VARIANT类型字段SQL 里始终只操作固定列动态展开交给应用层做。这种写法在读写频次不高、字段极其不稳定的场景很省心但过滤和聚合的性能肯定不如显式列。第三种是拼接后解析也就是本文第三种行转列方案用GROUP_CONCAT或者ARRAY_AGG把多个值压成一个字符串或数组一列装下所有渠道回到应用层再拆开。它算是动态列和纯 SQL 列之间的折中方案后面会细讲。1.3 三种行转列方案的横向对比先把结论摆在前面后面每一节再展开细节。我实测用的环境是单机 DorisFE 走 9030 端口、BE 走 9060 端口版本在 2.1.x 这个区间。顺便说一句如果你本地是 Windows 机器别折腾原生安装了FE 和 BE 依赖的运行时环境在 Windows 上基本跑不起来用 WSL2 或者 Docker 起单机版最省事装好之后第一件事就是去官网把函数手册翻一遍因为不同小版本对聚合函数和表函数的支持是有差异的。对比维度CASE WHEN GROUP BY多表自连接GROUP_CONCAT / ARRAY_AGG数据扫描次数1 次每个目标列一次1 到 2 次新增一列的改动量加一行 CASE 表达式加一个 JOIN 子句聚合部分不用改空值处理靠ELSE分支兜必须套COALESCE需要按位补占位符数据膨胀风险无高容易乘出笛卡尔积无长度 / 大小限制受 SQL 文本长度限制受 JOIN 表数量限制受字符串长度上限限制适合的数据规模大小且稀疏小基数、列数不固定输出结果可读性最好列名语义清晰好差需要应用层解析选型上我的一般原则是列数固定且不多于几十列、数据量大无脑选第一种只有当每个渠道的数据都极其稀疏、而且列数很少的时候才考虑第二种第三种只在列数完全不可控的场景当逃生通道用。1.4 先造一张测试表后面所有 SQL 都基于它为了后面不反复贴建表语句先把测试表和数据准备好。这里刻意设计成用户和渠道的组合方便观察每种写法的输出差异。CREATE TABLE dwd_order_detail ( dt DATE, user_id BIGINT, channel VARCHAR(32), order_amount DECIMAL(18, 2) ) DUPLICATE KEY(dt, user_id, channel) DISTRIBUTED BY HASH(user_id) BUCKETS 8 PROPERTIES(replication_num 1); INSERT INTO dwd_order_detail VALUES (2024-01-01, 1001, app, 120.00), (2024-01-01, 1001, mini, 80.00), (2024-01-01, 1002, app, 200.00), (2024-01-01, 1001, app, 40.00), (2024-01-02, 1001, app, 50.00), (2024-01-02, 1002, pc, 30.00), (2024-01-02, 1003, mini, 10.00);注意我在数据里埋了两个坑用户 1001 在 1 月 1 日有两条app记录用户 1003 只有mini没有app。这两个坑分别用于验证聚合函数选错导致数据丢失和空值处理也是线上最常出问题的两个点。2. 方式一CASE WHEN 加 GROUP BY通用性最强的那把锤子2.1 最小可用版本长什么样第一种写法的思路直白到没有任何技巧对每一行数据依次判断它属于哪个渠道属于哪个就把金额放进哪个列不属于这个列就放 0然后用GROUP BY把同一个用户的多行合并成一行。SELECT user_id, SUM(CASE WHEN channel app THEN order_amount ELSE 0 END) AS amt_app, SUM(CASE WHEN channel mini THEN order_amount ELSE 0 END) AS amt_mini, SUM(CASE WHEN channel pc THEN order_amount ELSE 0 END) AS amt_pc FROM dwd_order_detail WHERE dt 2024-01-01 GROUP BY user_id;跑出来用户 1001 的结果是amt_app 160、amt_mini 80、amt_pc 0。160 是两条 app 记录相加得来的这正是我们想要的。整条 SQL 只扫描了一遍明细表没有 JOIN、没有子查询执行计划干净得像一张白纸十万级、百万级的数据量下基本感觉不到开销。这也是为什么在绝大多数报表项目里第一种写法是默认选项。2.2 聚合函数别乱挑MAX、SUM、COUNT 的语义差别很大我见过不少人写行转列的时候随手用MAX(CASE WHEN ...)理由是反正一个用户一个渠道就一条MAX 和 SUM 结果一样。这句话的前半句就是错的。上面那张表里用户 1001 在app渠道有两条记录如果用MAX结果会变成 120 而不是 160——第二条记录直接被吃掉了而且不报错、不告警报表数字静悄悄就少了。这种 bug 极难发现等业务方发现对不上账的时候可能已经过去了一个月。到底该用哪个聚合函数取决于你希望目标格子里装什么语义的值业务含义写法说明累加金额SUM(CASE WHEN ... THEN amt ELSE 0 END)多条记录相加最常用取唯一值MAX(CASE WHEN ... THEN amt END)前提是该组合确实唯一计数行数COUNT(CASE WHEN ... THEN 1 END)注意COUNT(expr)会忽略 NULL计数去重COUNT(DISTINCT CASE WHEN ... THEN uid END)数据量大时开销明显拼接去重GROUP_CONCAT(DISTINCT CASE WHEN ... THEN tag END)结果长度有上限COUNT(CASE WHEN ... THEN 1 END)这个写法值得单独说一句。很多人写的是COUNT(CASE WHEN ... THEN 1 ELSE 0 END)结果每个用户每个渠道都得到非零值因为ELSE 0让不匹配的行也变成了非空表达式COUNT会把它算进去。正确做法是省略 ELSE 分支让不匹配的行返回 NULLCOUNT自动跳过。这个细节我至少纠正过三次。2.3 空值与零值是两回事报表上必须区分清楚另外一个高频争议点是某个用户压根没用过 pc 渠道amt_pc应该显示 0 还是显示空从 SQL 角度SUM(CASE WHEN channelpc THEN order_amount ELSE 0 END)会给你 0因为它对不匹配的行也贡献了一个 0而SUM(CASE WHEN channelpc THEN order_amount END)在没有匹配行的时候返回 NULL因为SUM对全 NULL 输入返回 NULL。这两种输出在报表上的含义完全不同0 表示这个渠道有记录但金额为零NULL 表示这个渠道没有任何记录。如果下游是 FlinkSQL 或者 BI 工具做进一步计算NULL 参与运算会继续传播 NULL而 0 会拉低平均值类的指标。我的习惯是在明细转宽表的环节统一输出 0然后在 BI 层用指标定义去区分无数据和零值但一定要在数据字典里写清楚否则半年后没人记得这个约定。如果确实想保留空值语义用NULLIF转换一下更稳妥SELECT user_id, NULLIF(SUM(CASE WHEN channel app THEN order_amount ELSE 0 END), 0) AS amt_app FROM dwd_order_detail GROUP BY user_id;2.4 列一多SQL 长度和列数上限就会来敲门第一种写法唯一的硬伤是列数一多就失控。假设渠道有 60 个加上几个维度交叉SQL 会变成几百行、上万字符。这里有两个实际问题要提前评估。一是单表列数上限。Doris 对单表的列数是有上限的具体数值各版本不同建宽表之前务必去官网确认目标版本的限制别等宽表建到一半才发现加不上列。真撞上上限了只能拆表比如按业务域拆成多张宽表或者改用 JSON / VARIANT 承载动态部分。二是SQL 文本长度和执行计划编译时间。几百个 CASE WHEN 表达式会让优化器在表达式下推和常量折叠阶段多花不少时间。实测在几百列的规模下编译耗时能到秒级如果这条 SQL 挂在实时接口上那就成了尾延迟的主要来源。规避方式是把它做成离线调度任务的物化宽表让接口直接查宽表而不是每次请求都去实时透视。提示多维度交叉场景下列名建议用统一的命名约定例如amt_{channel}、cnt_{channel}_{status}。这看起来是小事但当宽表有 200 列的时候一个可预测的命名规则能让下游写 SQL 的人少踩很多坑。3. 方式二自连接看着优雅实际上最容易翻车3.1 先看一个看起来对的写法自连接的思路是把每个渠道的数据各自筛成一个子查询然后按user_id把它们拼起来每个子查询对应输出一列。写出来长这样SELECT a.user_id, a.order_amount AS amt_app, b.order_amount AS amt_mini, c.order_amount AS amt_pc FROM (SELECT user_id, order_amount FROM dwd_order_detail WHERE channel app) a LEFT JOIN (SELECT user_id, order_amount FROM dwd_order_detail WHERE channel mini) b ON a.user_id b.user_id LEFT JOIN (SELECT user_id, order_amount FROM dwd_order_detail WHERE channel pc) c ON a.user_id c.user_id;这段 SQL 能跑通结果看起来也像那么回事但它有两个致命问题而且在测试数据量小的时候完全看不出来。3.2 数据膨胀的账是乘法算出来的第一个问题是笛卡尔积式的行数膨胀。子查询 a 里用户 1001 有两条 app 记录子查询 b 里用户 1001 有一条 mini 记录两表 JOIN 之后会产生 2 × 1 2 行再接上 c 的 1 行就是 2 行。如果 1001 在 pc 也有 3 条记录最终就是 2 × 1 × 3 6 行。每个 JOIN 都会在上一个结果集的基础上再乘一次渠道越多、单渠道记录数越多膨胀越夸张。我见过一个做埋点分析的场景八个渠道一连接中间结果从 200 万行涨到 4000 多万行直接把 BE 的内存打满。第二个问题是驱动边的选择。上面用 a 作为主表那些没有 app 记录、只有 mini 记录的用户比如测试数据里的 1003会在结果里彻底消失。有人会改成FULL OUTER JOIN来补救但 Doris 各版本对全外连接的优化程度不一样而且user_id列需要写COALESCE(a.user_id, b.user_id, c.user_id)JOIN 一多这个表达式会变成一大坨可读性直接崩掉。3.3 修正版用独立驱动表加子查询内聚合如果确实要用自连接把这两件事做掉就能基本安全用一个独立的用户清单当驱动表保证没有任何用户被丢掉每个子查询内部先GROUP BY聚合把行数压到一个用户一行从根上消除膨胀。WITH user_base AS ( SELECT DISTINCT user_id FROM dwd_order_detail WHERE dt BETWEEN 2024-01-01 AND 2024-01-02 ) SELECT u.user_id, COALESCE(a.amt, 0) AS amt_app, COALESCE(b.amt, 0) AS amt_mini, COALESCE(c.amt, 0) AS amt_pc FROM user_base u LEFT JOIN ( SELECT user_id, SUM(order_amount) AS amt FROM dwd_order_detail WHERE channel app GROUP BY user_id ) a ON u.user_id a.user_id LEFT JOIN ( SELECT user_id, SUM(order_amount) AS amt FROM dwd_order_detail WHERE channel mini GROUP BY user_id ) b ON u.user_id b.user_id LEFT JOIN ( SELECT user_id, SUM(order_amount) AS amt FROM dwd_order_detail WHERE channel pc GROUP BY user_id ) c ON u.user_id c.user_id;改完之后每个子查询的输出都是用户粒度唯一行LEFT JOIN 不会膨胀用户 1003 也能正常出现只是amt_app是 0。代价是明细表被扫描了四次每个子查询一次加上user_base一次在列存引擎里这个代价其实比想象中小因为每次扫描只读取channel和order_amount两列而列存对投影裁剪非常友好。3.4 自连接真正合适的场景是什么说了这么多缺点自连接也不是完全没用。它比较合适的场景是渠道数极少、且渠道之间数据高度稀疏的情况。比如只有首次下单渠道和复购渠道两个维度需要并列展示而且两个维度各自只覆盖 20% 的用户那么两次扫描加一次 JOIN 的开销和单次扫描加一大堆 CASE WHEN 差不多甚至 SQL 读起来更清晰。还有一个隐性优势每个子查询可以独立走不同的过滤条件。用 CASE WHEN 的话所有渠道共享同一个WHERE你没法给 app 渠道单独加一个dt 2024-01-01、给 pc 渠道加一个status 1。如果业务上确实需要每个渠道用不同口径自连接反而是更自然的表达。这种需求听着奇怪但在渠道口径不一致的历史遗留项目里非常常见。提示判断自连接是否会膨胀有个简单的自检方法——在子查询里执行SELECT user_id, COUNT(1) FROM ... GROUP BY user_id HAVING COUNT(1) 1只要返回了结果说明该子查询没做聚合JOIN 之后必然膨胀。4. 方式三GROUP_CONCAT 和 ARRAY_AGG列数不可控时的逃生通道4.1 两步走先聚成 KV 串再按位置取值第三种写法的出发点是既然列数不固定那就别把值分散到多个列里统一装进一列。具体做法分两步先在子查询里把数据聚合到用户 渠道粒度再用GROUP_CONCAT把多个渠道的值拼成一个带分隔符的字符串。SELECT user_id, GROUP_CONCAT(CONCAT(channel, :, CAST(amt AS STRING)), ,) AS kv FROM ( SELECT user_id, channel, SUM(order_amount) AS amt FROM dwd_order_detail WHERE dt 2024-01-01 GROUP BY user_id, channel ) t GROUP BY user_id;结果会是app:160,mini:80这样的一个字符串。到了这一步SQL 的活就干完了剩下的拆分交给应用层。如果想完全在 SQL 里做可以再套一层SPLIT_BY_STRING把字符串切成数组然后用下标取值SELECT user_id, ELEMENT_AT(SPLIT_BY_STRING(kv, ,), 1) AS kv1, ELEMENT_AT(SPLIT_BY_STRING(kv, ,), 2) AS kv2 FROM ( SELECT user_id, GROUP_CONCAT(CONCAT(channel, :, CAST(amt AS STRING)), ,) AS kv FROM ( SELECT user_id, channel, SUM(order_amount) AS amt FROM dwd_order_detail WHERE dt 2024-01-01 GROUP BY user_id, channel ) t GROUP BY user_id ) t2;这里的下标是从 1 开始的和 Hive 数组从 0 开始的习惯完全不同从 Hive 迁过来的同学十有八九会在这里栽一次。取不到的位置返回 NULL不会报错所以数组越界在 Doris 里是静默失败调试的时候要格外留意。4.2 顺序不确定的问题用定长填充加排序解决第三种写法最大的坑是GROUP_CONCAT的拼接顺序不保证。你这次跑出来是app:160,mini:80下次数据分布变了可能就变成mini:80,app:160那么下标 1 取到的到底是 app 还是 mini 就完全不可控了。这不是 bug是分布式聚合的固有特性——多个 BE 节点各自聚合一部分数据最后在 FE 侧合并合并顺序本来就不保证。绕开的办法是给拼接内容加上可排序的定长前缀然后配合ARRAY_AGG和ARRAY_SORTSELECT user_id, ARRAY_SORT( ARRAY_AGG(CONCAT(LPAD(channel, 8, _), :, LPAD(CAST(amt AS STRING), 12, 0))) ) AS kv_arr FROM ( SELECT user_id, channel, SUM(order_amount) AS amt FROM dwd_order_detail GROUP BY user_id, channel ) t GROUP BY user_id;思路是把渠道名用LPAD补齐成固定宽度金额也用LPAD补齐成固定宽度这样按字典序排序的结果就等于按渠道名排序的结果位置稳定了取第几个下标就是确定的。这个技巧在需要横向对比 12 个月、多个指标的场景特别有用因为它把动态列变成了固定位置的数组元素。取出来之后在应用层把前导的_和0去掉即可。Doris 里聚合函数的名字在不同版本略有差异ARRAY_AGG和COLLECT_LIST都出现过功能基本一致用之前先在官网函数手册里确认一下你那个版本给的是哪个名字。4.3 三个必须提前确认的雷第一种是长度上限。GROUP_CONCAT的聚合结果是有长度约束的超长会被截断而且是静默截断。这个长度和字段长度、会话设置都可能有关系不同版本默认值还不一样。我的做法是在写这种 SQL 之前先用SELECT MAX(LENGTH(...))估算一下实际最大长度留三到五倍余量超了就改用ARRAY_AGG全程不落字符串或者直接回到CASE WHEN方案。第二种是分隔符污染。如果拼接的原始内容里本身就可能出现逗号或冒号切分结果就全乱了。比如标签名里带逗号、商品名里带冒号这种情况要么在子查询里先做字符替换要么换一个几乎不可能出现的分隔符组合比如\u0001这种控制字符。第三种是NULL 缺位。GROUP_CONCAT会跳过 NULL 值这会导致某个用户没有某个渠道和某个用户该渠道值为空在结果里长得一模一样位置全部错位。处理办法是在子查询里先把缺位补上用渠道维表和用户清单做一次交叉连接生成所有组合再左连接明细这样每个用户都能拿到完整的键位。4.4 用 BITMAP 做去重版的行转列如果行转列的目标不是金额合计而是每个渠道有多少个不同的用户那么用BITMAP会比其他方案高效得多而且天然去重。做法是先建一张聚合模型的表把用户 ID 转成 BITMAP 存进去CREATE TABLE dws_channel_user_bitmap ( dt DATE, channel VARCHAR(32), user_bm BITMAP BITMAP_UNION ) AGGREGATE KEY(dt, channel) DISTRIBUTED BY HASH(channel) BUCKETS 4 PROPERTIES(replication_num 1); INSERT INTO dws_channel_user_bitmap SELECT dt, channel, BITMAP_HASH(user_id) FROM dwd_order_detail;聚合键模型会在导入阶段自动对user_bm做 BITMAP 合并所以直接插明细行就行。查询的时候SELECT dt, BITMAP_UNION_COUNT(IF(channel app, user_bm, NULL)) AS uv_app, BITMAP_UNION_COUNT(IF(channel mini, user_bm, NULL)) AS uv_mini, BITMAP_UNION_COUNT(IF(channel pc, user_bm, NULL)) AS uv_pc FROM dws_channel_user_bitmap GROUP BY dt;BITMAP_UNION_COUNT是精确去重计数比COUNT(DISTINCT)的内存占用和计算开销都低得多在几千万用户量级下优势非常明显。这是我把 BITMAP 单独拎出来讲的原因——如果你的行转列需求正好落在去重计数上那它应该是第一选择而不是第三种写法的变体。5. 列转行从 UNION ALL 手写展开到 EXPLODE_SPLIT 集合展开5.1 UNION ALL最笨但最可控的方式列转行是把宽表的 N 个列折回成长表的多行最常见的实现就是写 N 个SELECT用UNION ALL串起来SELECT user_id, amt_app AS metric, amt_app AS metric_value FROM dws_user_amount_wide UNION ALL SELECT user_id, amt_mini AS metric, amt_mini AS metric_value FROM dws_user_amount_wide UNION ALL SELECT user_id, amt_pc AS metric, amt_pc AS metric_value FROM dws_user_amount_wide;这种写法的好处是完全可控、可读性好、每一段还能单独加过滤条件比如只展开有值的指标加个WHERE amt_pc 0。坏处同样明显UNION ALL段数等于列数宽表有 100 列就得写 100 段 SQL而且每段都是一次独立扫描执行起来是 100 次扫表加一次合并。Doris 对同表多次扫描有一定优化但段数一多编译阶段就不是省油的灯了。实践中我的判断标准是列数在 10 以内用 UNION ALL超过 10 就考虑下面两种。另外UNION ALL不会去重这是我们要的千万别顺手写成UNION那会引入一次全量排序去重在大数据量下代价极高。5.2 LATERAL VIEW EXPLODE_SPLIT把拼接和展开合成一步第二种方式利用了 Doris 的表函数能力。先把宽表里的多个列通过CONCAT_WS拼成一个字符串注意要把指标名和指标值一起拼进去再用EXPLODE_SPLIT按分隔符炸成多行SELECT user_id, SPLIT_PART(kv, :, 1) AS metric, SPLIT_PART(kv, :, 2) AS metric_value FROM ( SELECT user_id, CONCAT_WS(,, IF(amt_app IS NULL, NULL, CONCAT(amt_app:, CAST(amt_app AS STRING))), IF(amt_mini IS NULL, NULL, CONCAT(amt_mini:, CAST(amt_mini AS STRING))), IF(amt_pc IS NULL, NULL, CONCAT(amt_pc:, CAST(amt_pc AS STRING))) ) AS kv_str FROM dws_user_amount_wide ) t LATERAL VIEW EXPLODE_SPLIT(kv_str, ,) tmp AS kv;这段 SQL 有几个细节值得掰开说。CONCAT_WS的第一个参数是分隔符后面所有参数用该分隔符连接而且它会自动跳过 NULL 参数——这一点正好帮我们实现了有值的指标才展开成行不用额外过滤。里面的IF(... IS NULL, NULL, ...)是显式保留空位如果某个指标确实要过滤掉直接把它从CONCAT_WS的参数里去掉就行。LATERAL VIEW的语义可以类比成对每一行原始数据把展开出来的多行横向铺出去输出行数等于展开后元素个数。相比UNION ALL它只扫一次表SQL 也短得多宽表列数变化的时候只需要改CONCAT_WS的参数列表展开逻辑完全不用动。需要留意的是SPLIT_PART的分隔符不能出现在指标值里。如果金额或者字符串型指标本身可能带冒号就得换分隔符或者先做转义。这个坑在处理用户自定义标签、备注字段的时候经常遇到。5.3 用 EXPLODE_NUMBERS 加数组下标做定长展开如果宽表的指标列数量固定还有一种更程序化的写法把指标名和指标值各构造一个数组用EXPLODE_NUMBERS生成下标序列再按下标取值。SELECT user_id, metric_names[n] AS metric, metric_values[n] AS metric_value FROM ( SELECT user_id, [amt_app, amt_mini, amt_pc] AS metric_names, [amt_app, amt_mini, amt_pc] AS metric_values FROM dws_user_amount_wide ) t LATERAL VIEW EXPLODE_NUMBERS(3) tmp AS n;好处是指标名和指标值两个数组严格对齐不用担心分隔符污染的问题类型也保持原样金额还是 DECIMAL不用来回转字符串。缺点是数组字面量的语法在不同小版本上支持情况有差异如果报语法错误可以退回到CONCAT_WS EXPLODE_SPLIT的写法。另外EXPLODE_NUMBERS的参数必须和数组长度严格一致多一个下标取到 NULL少一个就会丢指标建议把它写成一个由脚本生成的常量而不是手写魔数。提示EXPLODE_NUMBERS(n)生成的是 1 到 n 的序列正好匹配 Doris 数组从 1 开始的下标规则。如果你的 SQL 里出现metric_values[n - 1]这种写法八成是从别的引擎搬过来的习惯先确认下标基准再改。5.4 Doris 和 Hive 的写法对照下标基准是最容易踩的坑很多团队是从 Hive 迁过来的列转行的语法差异主要体现在函数名和下标基准上。整理成一张对照表迁移的时候照着改就行。操作Hive 写法Doris 写法关键差异字符串炸开成多行lateral view explode(split(s, ,)) t as xLATERAL VIEW EXPLODE_SPLIT(s, ,) t AS x函数名不同Doris 不需要先在split里再套一层数组按下标取值arr[0]arr[1]或ELEMENT_AT(arr, 1)Hive 从 0 开始Doris 从 1 开始数组构造collect_list(col)ARRAY_AGG(col)或COLLECT_LIST(col)视版本而定语义基本一致字符串拼接concat_ws(,, ...)CONCAT_WS(,, ...)行为一致都会跳过 NULL按位置切分字符串split(s, :)[0]SPLIT_PART(s, :, 1)Doris 有专门的SPLIT_PART性价比更高生成数字序列posexplode带下标EXPLODE_NUMBERS(n)手动配下标实现思路不同下标基准这个坑我踩过不止一次。写 SQL 的时候看着都对跑出来所有指标整体错位一格最后一个指标永远是 NULL排查半天才发现是下标从 1 开始。后来我养成一个习惯在 SQL 的注释里显式写一行注意数组下标从 1 开始提示自己也提示后来改这段代码的人。6. 当动态列真的绕不过去工程化该怎么收口6.1 应用层拼 SQL白名单是唯一的护城河动态列场景下从SELECT DISTINCT channel FROM dim_channel拿到渠道列表再拼 SQL是最常见的做法。但拼接过程必须做两件事标识符白名单校验和结果列数上限保护。前者防的是 SQL 注入后者防的是有人往维表里插了一万个渠道直接把 FE 编译压垮。我的实现习惯是用正则卡死import re SAFE_IDENT re.compile(r^[A-Za-z0-9_]{1,32}$) MAX_COLUMNS 120 def build_pivot_sql(channels, table, dt): safe [c for c in channels if SAFE_IDENT.match(c)] if len(safe) ! len(channels): raise ValueError(渠道名包含非法字符已拒绝拼 SQL) if len(safe) MAX_COLUMNS: raise ValueError(f渠道数 {len(safe)} 超过上限 {MAX_COLUMNS}) cols ,\n .join( fSUM(CASE WHEN channel {c} THEN order_amount ELSE 0 END) AS amt_{c} for c in safe ) return fSELECT user_id,\n {cols}\nFROM {table}\nWHERE dt {dt}\nGROUP BY user_id注意渠道名走的是字符串字面量不是标识符所以只要正则限制了字符集就没法构造出闭合引号的攻击串。但列别名amt_{c}是标识符如果c里出现了反引号或者空格别名就会出错所以正则里干脆把这两类字符都排除掉。这个细节在网络上的示例代码里经常被忽略。6.2 SpringBoot 连 Doris 时的连接参数与超时控制拼出来的 SQL 有个特点文本每次都不一样长度也随渠道数变化执行时间波动大。这类查询挂在接口上最怕的就是慢查询把连接池拖死。我的配置习惯是三层超时叠加连接超时、socket 读超时、SQL 执行超时。spring.datasource.urljdbc:mysql://fe-host:9030/analytics?\ connectTimeout3000\ socketTimeout600000\ useSSLfalse\ useServerPrepStmtsfalse\ sessionVariablesquery_timeout600 spring.datasource.hikari.maximum-pool-size8 spring.datasource.hikari.connection-timeout5000 spring.datasource.hikari.validation-timeout3000几个参数的作用值得说明一下。connectTimeout控制建连阶段Doris 的 FE 在高负载下建连可能变慢给 3 秒比较稳妥。socketTimeout是 socket 空闲超时宽表大结果集的查询可能跑好几分钟所以给到 600 秒但别给成 0无限等待否则一个卡住的查询会永久占用连接。sessionVariablesquery_timeout600是 Doris 侧的查询超时让 FE 主动把跑太久的查询杀掉比在客户端干等要好。useServerPrepStmtsfalse是因为动态拼 SQL 场景下本来就没有预编译复用的价值关掉能省掉一次额外的 prepare 往返。还有一点连接池大小和拼 SQL 的频率要匹配。如果每次请求都去实时透视8 个连接很容易被打满。我的建议是加一层本地缓存把渠道列表这种维表数据缓存几分钟同时把透视结果落成物化宽表接口只查宽表动态 SQL 只在离线调度里跑。6.3 FlinkSQL 写入宽表时UNIQUE KEY 模型的部分列更新如果宽表的每一列是由不同的实时流算出来的app 流写amt_appmini 流写amt_mini那么写入的时候千万不能用默认的整行覆盖否则后写的流会把前面的列冲成 NULL。正确做法是用 UNIQUE KEY 模型的表并开启部分列更新。CREATE TABLE dws_user_amount_wide ( user_id BIGINT, amt_app DECIMAL(18, 2), amt_mini DECIMAL(18, 2), amt_pc DECIMAL(18, 2), update_time DATETIME ) UNIQUE KEY(user_id) DISTRIBUTED BY HASH(user_id) BUCKETS 8 PROPERTIES( replication_num 1, enable_unique_key_merge_on_write true );Flink 侧在 sink 的 properties 里加上sink.properties.partial_columnstrue并且INSERT语句里只写本次要更新的列。这样每次写入只更新指定的列其他列保持原值。这个配置有前提表的写入模式必须是 MoWmerge on write老的非 MoW 模型在部分列更新上有各种限制。另外要留意部分列更新在写入吞吐上比整行写入要高一些开销因为它需要先做一次点查合并。如果宽表列数不多、数据量不大其实更简单的做法是每条流都写全量列用COALESCE把不关心的列填成 NULL让整行覆盖去处理。两种方案的选择主要看列数规模和写入 QPS。6.4 VARIANT 和 JSON 作为动态列的兜底最后一招是彻底放弃把动态键变成列把不固定的部分塞进半结构化字段。Doris 2.x 提供的VARIANT类型可以自动推断 JSON 内部结构写入时不用预先定义字段查询时可以用路径表达式取值SELECT user_id, variant_col[channel][app] AS app_info FROM dws_user_profile_variant WHERE dt 2024-01-01;这条路子的取舍很清楚schema 灵活性拉满代价是查询性能和可控性下降。半结构化字段的过滤没法走 zone map 和前缀索引聚合时要做路径提取。所以我的定位是把它当兜底而不是主方案——只有当动态键的数量级完全不可预测比如用户自定义标签动辄上千个 key或者这些数据本来就不参与高频聚合的时候才用它。扫描路径上还有一个隐藏成本VARIANT的查询往往需要对每行做一次 JSON 解析即使只需要其中一个 key。如果某个 key 的查询频率很高把它提取成显式的物理列收益会比微调 SQL 大得多。这一点在很多用 JSON 存一切的项目里被反复验证过。最后分享一个我自己的排查习惯行转列 SQL 写完之后第一件事是跑EXPLAIN看执行计划里有没有意外的 Exchange 节点宽表的透视结果如果需要在节点间重分布数据量大的时候网络就成了瓶颈第二件事是开SET enable_profile true跑一次真数据看各算子的实际耗时占比。很多看起来SQL 写得不对的性能问题最后一查都是分布键选得不好导致的重分布而不是行转列本身的开销。