起因
最近我在推送数据到中间库时,由于源表比较大,所以采用了基于主键ID的分批推送,于是就有了如下脚本:
-- 第一步,推送数据
insert into Interconnect..Steps
SELECT TOP (@batch_size) StepID, {其他业务字段}
FROM Steps
WHERE StepID > @last_id
ORDER BY StepID-- 第二步,查询并赋值最新的last_id
SELECT @last_id = MAX(StepID) FROM (SELECT TOP (@batch_size) StepIDFROM StepsWHERE StepID > @last_idORDER BY StepID
) t;
但这存在一个效能问题,我需要查询两次Steps表,才能完成推送与更新last_id的需求。那么有没有办法一次查询就把两件事给办了?
over()窗口函数
核心作用:在保持原始行不变的前提下,为每一行添加基于某个数据范围的聚合或分析结果。
对于上面这种情况,我可以在查询中添加一列max(StepID) over() as Last_ID
,就可以基于整个结果集计算出最大的StepID。
不过对于这个例子,因为窗口函数是基于全表数据进行聚合的,所以真正使用时还需要嵌套子表,以保证是基于当前@batch_size结果集得出的last_id
SELECT StepID, {业务字段},MAX(StepID) OVER() AS LAST_ID
FROM (SELECT TOP (@batch_size) StepID, {业务字段}FROM StepsWHERE StepID > @last_idORDER BY StepID
) AS subquery;
如果over()参数不为空,则会呈现如下差异:
-- 按 processID 分组的最大值(每行显示所属process的最大stepID)
MAX(stepID) OVER(PARTITION BY processID)
总结
over()
函数的特点:
- 保持数据粒度 - 不改变原始行数
- 提供上下文 - 每行数据都了解整体情况
- 提升性能 - 比子查询更高效
- 增强分析能力 - 支持复杂的数据分析场景