sql over()函数使用

起因

最近我在推送数据到中间库时，由于源表比较大，所以采用了基于主键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) StepID
	FROM Steps
	WHERE StepID > @last_id
	ORDER 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 Steps
    WHERE StepID > @last_id
    ORDER BY StepID
) AS subquery;

如果over()参数不为空，则会呈现如下差异：

-- 按 processID 分组的最大值（每行显示所属process的最大stepID）
MAX(stepID) OVER(PARTITION BY processID)

总结

over()函数的特点：

1. 保持数据粒度 - 不改变原始行数
2. 提供上下文 - 每行数据都了解整体情况
3. 提升性能 - 比子查询更高效
4. 增强分析能力 - 支持复杂的数据分析场景