Doris窗口函数详解：从语法到性能优化实战

1. 窗口函数能做什么先搞清楚它解决什么问题如果你做过一段时间的SQL开发大概率遇到过这类需求算累计值、算环比同比、给每个分组排个名次、取每组前N条记录。用传统的方式写要么嵌套一堆子查询要么join自己SQL写得又长又绕性能还特别差。窗口函数就是专门解决这类问题的——它能在不改变行数的情况下对每一行同时保留原始明细和聚合计算结果。我第一次用DorisApache Doris的窗口函数时第一感受是“终于能少写一半SQL了”。Doris作为一款MPP架构的OLAP数据库窗口函数是它的核心能力之一。无论是做数据报表、用户行为分析还是实时大屏的数据加工窗口函数都用得上。这篇文章从语法、常用函数、实战场景到性能优化一次性讲透。Doris是分析型数据库OLAP它的窗口函数和MySQL、PostgreSQL的窗口函数在语法上高度兼容但实现细节和适用场景有自己的特点。文章适合三类人看正在做数据仓库建模和ETL开发的工程师、需要写复杂分析SQL的数据分析师以及那些从MySQL或Oracle迁移到Doris、想用更优雅的方式解决问题的同学。看完之后你至少能掌握窗口函数的语法结构、常用的分析函数写法以及怎么在真实业务中选对窗口函数。2. 窗口函数的基础语法与执行逻辑2.1 窗口函数的结构三部分缺一不可窗口函数的SQL写法有固定套路语法结构长这样SELECT col1, col2, SUM(col3) OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN ... AND ...) AS running_total FROM table_name;拆开来看OVER子句是窗口函数的核心它由三个部分组成PARTITION BY定义窗口的分区可以理解成“分组”。同一组内的数据才会参与计算类似GROUP BY的作用但区别在于它不会把多行合并成一行。ORDER BY定义窗口内的排序规则。排序影响窗口函数的计算顺序比如累计求和、排名都依赖排序。ROWS BETWEEN ... AND ...定义窗口的帧范围指定当前行参与计算的上下边界。如果不写默认是从分区第一行到当前行配合ORDER BY时这在累计计算中非常常见。很多刚接触窗口函数的人搞不清PARTITION BY和GROUP BY的区别。简单记一句话GROUP BY把多行合并成一行输出行数减少PARTITION BY保持行数不变每一行都保留自己的原始值只是在当前行旁边多了一列计算结果。这个差异在处理“明细汇总”同时展示的场景时特别有用。2.2 执行逻辑Doris是怎么算窗口的理解了语法再看Doris内部的执行逻辑。窗口函数在查询计划中处于什么位置答案是在JOIN、WHERE、GROUP BY和HAVING之后在ORDER BY和LIMIT之前执行。举个例子这条SQL的完整执行顺序是SELECT dept_id, emp_name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employee WHERE dept_id IN (A, B);WHERE先把不满足条件的部门过滤掉Doris再对剩下的数据按照dept_id分区在每个分区内按薪水降序排列然后计算排名最后才输出结果。如果先分区再过滤计算结果就会出错所以Doris刻意把窗口函数的执行放在过滤之后这个设计是合理的。这里有个容易踩的坑WHERE子句里不能直接引用窗口函数的结果列。比如你想筛选出每个部门薪水排名前3的员工不能写成WHERE RANK() OVER (...) 3而应该把窗口函数包一层子查询在外面用WHERE rk 3过滤。这个点经常有人问记住就能少踩一次坑。2.3 窗口帧搞清楚计算范围窗口帧是窗口函数里最容易出错的点。ROWS BETWEEN有几种常见写法写法计算范围ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区第一行到当前行常用于累计值ROWS BETWEEN N PRECEDING AND CURRENT ROW从前N行到当前行常用于滑动平均ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING从当前行到分区最后一行ROWS BETWEEN N PRECEDING AND N FOLLOWING从前N行到后N行常用于数据平滑当OVER子句中同时出现PARTITION BY和ORDER BY但没写ROWS BETWEEN时默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即在分区内从起点到当前行。这个默认行为对SUM这种函数意味着“累计求和”对RANK这种排名函数则没有影响因为排名函数本身就是逐行比较的。RANGE和ROWS还有一个区别RANGE按照“值”来圈定窗口边界ROWS按照“行的物理位置”来圈定。假设ORDER BY排序的列有重复值RANGE会把相同值的行全部包含进窗口ROWS则严格按照行号来。实际业务中大部分场景用ROWS就够除非你明确需要按值圈定范围。3. Doris常用窗口函数逐一拆解3.1 聚合窗口函数SUM、AVG、COUNT、MAX、MIN聚合函数配合窗口使用是窗口函数最基础、也最常用的形式。它们的作用和普通聚合一样区别在于计算范围被限制在窗口帧内。Doris支持在窗口上下文中使用SUM、AVG、COUNT、MAX、MIN等常见的聚合函数。场景很直观计算每个用户的累计消费金额。表user_orders里有user_id、order_date、amount三个字段。SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_amount FROM user_orders;这条SQL按user_id分组组内按订单日期排序SUM以当前行及其之前的所有订单金额之和作为累计金额。每一行都带着原始订单明细同时多出一列累计值。同样的思路AVG可以做滑动平均。比如计算最近7天的平均销售额用于平滑数据波动SELECT stat_date, sales_amount, AVG(sales_amount) OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM daily_sales;MAX和MIN也有用武之地比如找出每只股票的历史最高价与当前价的差距。这些聚合窗口函数写法基本都是模板化的理解了窗口帧的含义套用即可。3.2 排名窗口函数ROW_NUMBER、RANK、DENSE_RANK排名函数是窗口函数中使用频率最高的一类需求也很典型取每个分类下销量最高的商品、每个部门的工资排名、订单按时间排序后的编号。三个函数的区别必须记住ROW_NUMBER()按排序顺序对每一行分配一个唯一的序号从1开始。即使排序值相同它们的序号也不同。RANK()排序值相同的行会得到相同的排名但下一个排名会跳过。比如两个并列第1下一个是第3。DENSE_RANK()排序值相同的行会得到相同的排名且下一个排名是连续的数字。两个并列第1下一个是第2。以销售排名为例SELECT product_id, category_id, sales_amount, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS row_num, RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rk, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS dense_rk FROM product_sales;三者输出差异用一个简单例子就能看清。假设某分类下有三条记录销量分别是100、100、80那么ROW_NUMBER的结果是1、2、3RANK的结果是1、1、3DENSE_RANK的结果是1、1、2。实际业务中怎么选如果只是给记录编号用ROW_NUMBER如果需要严格的名次概念且允许并列用RANK如果后续要对排名做加减且希望名次连续用DENSE_RANK。比如“排行榜”场景通常用RANK因为跳过的名次是正常的而“等级划分”场景比如按名次分成青铜、白银、黄金时用DENSE_RANK更合适。3.3 取值窗口函数LAG、LEAD、FIRST_VALUE、LAST_VALUE这组函数用来在窗口内访问“其他行”的数据特别适合做对比分析。LAG(expr, offset, default)返回当前行向上偏移offset行的值。LEAD(expr, offset, default)返回当前行向下偏移offset行的值。FIRST_VALUE(expr)返回窗口内第一行的值。LAST_VALUE(expr)返回窗口内最后一行的值。LAG和LEAD最常见的场景是算环比、同比。比如计算每天的销售额相比前一天的变化SELECT stat_date, sales_amount, LAG(sales_amount, 1) OVER (ORDER BY stat_date) AS prev_day_amount, sales_amount - LAG(sales_amount, 1) OVER (ORDER BY stat_date) AS day_over_day_change FROM daily_sales;LAG(sales_amount, 1)取出前一天的销售额当前行减去前一天的差值就是日环比变化。如果要算7天前的数据把offset改成7就行。注意当偏移超出窗口范围时返回NULL可以用第三个参数设置默认值比如LAG(sales_amount, 1, 0)避免后续计算出现NULL污染。FIRST_VALUE和LAST_VALUE的场景则偏向“首次、末次”分析比如用户首次下单时间、最后一次登录时间。但用LAST_VALUE时有个细节容易翻车如果不指定窗口帧默认窗口是从起点到当前行LAST_VALUE返回的是“当前行”的值而不是整个分区的最后一行。想要获取分区最后一行必须指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这个点不留意查出来的数据会“看似正确实则错误”。3.4 分布与分析窗口函数PERCENT_RANK、CUME_DIST、NTILE这几个函数虽然用得不如前面几类频繁但在某些场景里价值很高。NTILE(n)把分区内的行分成n个桶返回行所属的桶编号1到n。常用于“四分位数”“Top N%”这类场景。PERCENT_RANK()计算当前行的百分比排名公式是(rank - 1) / (rows - 1)。CUME_DIST()累计分布值公式是当前行值之前的行数 / 分区总行数。NTILE的一个经典用法是给数据打分。比如给用户按照消费金额分成5档SELECT user_id, total_amount, NTILE(5) OVER (ORDER BY total_amount DESC) AS level FROM user_total_amount;这条SQL把所有用户按消费金额倒序排列后分成5组level1是消费最高的20%level5是最低的20%。拿去给用户分等级、做差异化运营非常方便。4. 实战案例用窗口函数解决3类典型业务问题4.1 案例一取每个部门工资最高的员工这个需求是窗口函数入门必做题。表结构employee(id, name, dept_id, salary)。SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 1;子查询里先按部门分组、再按工资倒序排列用ROW_NUMBER给每个部门内的员工编号工资最高的编号为1。外层查询过滤出rn1的行就拿到了每个部门的最高工资员工。这里的ROW_NUMBER换成RANK或DENSE_RANK行为会不一样如果部门里有两个工资一样的员工RANK会把两个人都返回都是第1名ROW_NUMBER只返回其中一个。到底用哪个取决于业务上是否需要展示并列第一。这是面试里经常被追问的细节很多候选人都栽在这里。4.2 案例二计算用户相邻两次下单的时间间隔电商场景很常见的需求分析用户的购买频率、复购周期。表orders(order_id, user_id, order_time)。SELECT user_id, order_time, LAG(order_time) OVER (PARTITION BY user_id ORDER BY order_time) AS prev_order_time, DATEDIFF(order_time, LAG(order_time) OVER (PARTITION BY user_id ORDER BY order_time)) AS interval_days FROM orders;LAG拿到每个用户上一次下单时间再算差值就是相邻两次下单的间隔天数。这个数据可以继续用于计算用户的平均复购周期、流失预警等。实际经验提醒一句如果订单表数据量很大这种写法会扫描全表建议先按user_id过滤到目标用户池再套窗口函数能省下不少资源。我就是在一张几亿行的订单表上直接跑这类SQL结果把BE节点内存打满后来又老老实实加过滤条件的。4.3 案例三会话(session)划分与用户行为序列分析用户行为日志分析里有个经典难题如何把用户的连续操作划分成一个个独立的会话session。定义通常是如果一个用户相邻两次操作的时间间隔超过30分钟就视为一个新会话。用窗口函数可以优雅地解决。假设表user_events(user_id, event_time, event_name)SELECT user_id, event_time, event_name, SUM(flag) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id FROM ( SELECT user_id, event_time, event_name, IF(TIMESTAMPDIFF(MINUTE, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) 30, 1, 0) AS flag FROM user_events ) t;内层查询用LAG获取上一次操作时间如果间隔超过30分钟标记为1否则为0。外层用SUM窗口函数累加这个标记累加结果相同的行属于同一个会话。这个套路非常实用比用游标或递归查询高效得多。用这个方法生成的会话ID可以继续配合窗口函数做更多分析比如计算每个会话内的操作路径、每个会话的时长、转化漏斗等。这是我在做用户行为分析时最常用的一个技巧。5. 窗口函数与GROUP BY的取舍什么时候用哪个窗口函数和GROUP BY都能做分组聚合但两者返回的行数不同决定它们截然不同的适用场景。GROUP BY把一组数据压成一行丢失明细窗口函数保持明细不变在每行旁边附加汇总结果。什么场景选GROUP BY当只需要聚合结果、不关心明细时比如算每个部门的总工资、每个月的订单量。这种场景用GROUP BY更直观性能也更好。什么场景选窗口函数当需要同时展示明细和聚合结果时比如“每个部门员工工资 部门平均工资”的报表或者需要在聚合基础上做进一步计算时比如“使用每个部门平均工资和员工工资的差值排序”。这些用GROUP BY很难写但窗口函数天然支持。有一种写法需要注意窗口函数里套聚合函数是合法的例如SELECT dept_id, emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary, salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg FROM employee;这条SQL直接在窗口函数里用了AVG(salary)相当于先按部门聚合再把这个值放到每一行上。这么写既拿到了明细又拿到了部门均值一条SQL解决了两个问题。性能层面Doris对窗口函数的处理是在Exchange节点之后进行的数据会先在BE节点之间做shuffle把同一个分区的数据发送到同一个节点。数据量大的时候shuffle压力不小。所以在写窗口函数时PARTITION BY的字段选择非常关键尽量选基数不高、分布均匀的字段避免数据倾斜。6. Doris窗口函数的性能优化指南6.1 分区字段选择决定查询性能窗口函数执行时Doris需要把所有具有相同PARTITION BY值的行发送到同一台BE节点上。如果分区键的某个值在所有数据中占比过高那台节点的内存和CPU可能成为瓶颈其他节点却闲着这就是数据倾斜。比如PARTITION BY user_id时有的大客户贡献了上千万条订单记录这一整个分区都堆在一个节点上计算必然慢。解决办法通常有两种:一是提前做数据过滤只取需要分析的时间窗口减少每个分区的数据量二是如果业务允许对分区键做分层处理比如在user_id前加一个region_id前缀分散到不同的分区维度上。我在生产环境里见过一条窗口函数SQL把整个集群跑挂原因就是PARTITION BY的字段基数太低数据分布严重不均。后来把分区字段改成组合字段把倾斜的分区拆细了一层查询就顺畅了。6.2 尽量缩小窗口帧范围窗口帧范围越大Doris需要缓存和处理的数据就越多。默认情况下窗口从分区起点到当前行如果业务其实只需要“近7天的数据”务必在SQL里明确写ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。这样Doris可以更精准地控制内存占用减少不必要的计算量。这里有一点需要注意的Doris在内存中为窗口操作维护缓冲区窗口大小直接决定缓冲区的数据量。一个分区几百行和一个分区几百万行的内存开销完全不是一个量级。所以在PARTITION BY之后尽量让每个分区的行数保持在可控范围。如果某个分区的数据量不可控建议在数据模型设计时就按时间或按模块拆表避免单分区过大。6.3 在子查询中尽早过滤数据窗口函数在WHERE之后执行所以在WHERE里过滤掉的数据根本不会进入窗口计算。充分利用这个特性在最内层就把数据范围缩小。举个反例我在业务里见过这样的SQLSELECT user_id, SUM(amount) OVER (PARTITION BY user_id) AS total FROM orders;orders表全量数据被拉出来算了一遍窗口其实只需要最近30天的数据。改成SELECT user_id, SUM(amount) OVER (PARTITION BY user_id) AS total FROM ( SELECT user_id, amount FROM orders WHERE order_date DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) t;内层已经过滤掉大部分数据窗口计算的输入量大幅减少查询时间从几秒降到几百毫秒。这个习惯养成了Doris集群的稳定性会好很多。7. 常见报错与排查实录7.1 报错 “Unknown column xxx in window function”这个报错通常是在窗口函数的ORDER BY或PARTITION BY里引用了查询输出里不存在的列。排查思路很简单先把SELECT列表里的列拿到核对窗口子句里的列名是否在源表里真实存在、有没有拼写错误。Doris对列名校验比较严格大小写也敏感如果建表时用了小写SQL里写了大写就会报这个错。7.2 报错 “Window function xxx requires an ORDER BY clause”某些窗口函数比如RANK、ROW_NUMBER、LAG、LEAD要求OVER里有ORDER BY如果漏写了Doris直接报错。处理方式是在OVER里补充排序条件。但需要注意聚合类窗口函数如SUM不强制要求ORDER BY这时如果不写ORDER BY整个分区作为一组参与计算每行得到的结果是一样的。7.3 报错 “Cant connect to MySQL server on 127.0.0.1:9030”这类报错不是窗口函数本身的语法问题而是Doris连不上。从热词里也看到这条很有代表性。排查步骤固定先检查FE进程是否存活然后看9030端口是否被占用最后确认客户端连接的是不是配置的FE地址。我遇到过几次都是因为FE配置里query_port改过端口客户端还按默认9030连自然连不上。还有一次是BE节点OOM导致整个集群不稳定客户端也会报类似错误。7.4 窗口函数结果和预期不一致结果不对最常见的三个原因一是窗口帧没指定使用了默认的RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW导致窗口范围和预期不符二是LAST_VALUE没指定UNBOUNDED FOLLOWING返回了当前行值而不是分区末尾的值三是RANK和ROW_NUMBER选错导致并列值的处理方式不同。结果不对时先检查这三处大多数问题都能定位。8. 窗口函数在真实业务中的扩展思路关于Doris和ClickHouse的选型问题最近讨论很多。Doris在窗口函数方面的支持确实比ClickHouse更完整窗口语义更接近MySQL和PostgreSQL学习成本低。而ClickHouse的窗口函数是近几个版本才逐渐补齐的老版本更多靠数组函数和neighbor函数模拟。如果你的团队之前是MySQL背景选择Doris做OLAP窗口函数这条路上会顺畅很多。窗口函数可以和Doris的其他特性组合使用。比如和Explain结合分析SQL执行计划确认窗口函数是在哪个阶段执行的EXPLAIN SELECT ...看看执行计划里ANALYTIC节点位置是否合理和物化视图结合把高频的窗口计算结果固化下来查询时直接读结果。还有一个常用套路是窗口函数INSERT INTO SELECT语句把加工结果定期写入新的汇总表构建一套简单的分层数据。用Doris替换Elasticsearch做日志分析也是最近的热门方向。ES擅长全文检索但做聚合分析时要小心内存开销。Doris的窗口函数加上Bitmap和分桶能力在结构化日志分析场景下表现不错。把日志数据导入Doris之后用窗口函数做用户路径分析、事件序列分析比ES的聚合语法直观不少。最后说一个我自己摸索出来的习惯写复杂的窗口函数SQL时先在命令行里用小数据集验证逻辑确认输出符合预期后再放开到全量数据。Doris的LIMIT可以配合子查询控制数据量比如SELECT ... FROM (SELECT ...) t LIMIT 100。在生产环境直接跑一条有问题的窗口函数SQL轻则白跑几分钟重则把BE内存打爆。这个坏习惯我踩过坑分享出来希望你能绕开。