sql - 如何将相同的多个窗口函数组合为一个，并在查询中需要时通过别名调用？

Question

如何解决在单个 SQL 查询中针对不同聚合多次使用相同窗口函数的问题？有什么方法可以给它取别名并在查询中根据需要多次调用它。

我尝试使用 'Window' 子句，但 SQL Server 当前不支持 'Window' 子句。

select empid, qty, 
       sum(qty) over (partition by empid order by month rows between unbounded preceding and current row) as running_sum, 
       avg(qty) over (partition by empid order by month rows between unbounded preceding and current row) as running_avg, 
       min(qty) over (partition by empid order by month rows between unbounded preceding and current row) as running_min, 
       max(qty) over (partition by empid order by month rows between unbounded preceding and current row) as running_max
from employee

有没有办法消除代码中的冗余？

标签： sqlsql-serverfunctionwindow

Answer 1

不在 SQL Server 中，ANSI SQL 支持WINDOWS用于定义可重复使用的窗口的子句。但是，SQL Server 不支持它。

我认为您可以稍微简化您的逻辑：

select empid, qty, 
       sum(qty) over (partition by empid order by month) as running_sum, 
       avg(qty) over (partition by empid order by month) as running_avg, 
       min(qty) over (partition by empid order by month) as running_min, 
       max(qty) over (partition by empid order by month) as running_max
from employee;