用WINDOW子句引用替换窗口函数OVER()结果不一致问题

问题重现 #

核心问题：相同的窗口逻辑，用内联OVER(...)子句时结果符合预期，但改用WINDOW子句定义并引用后，sum(count(s.price))的计算值从分组后的窗口求和（1、3、4）变成全量求和（4、4、4），最终导致avg7DayPrice计算错误。

原查询（内联OVER，结果正确） #

-- Inline window over() clauses
-- Results are as expected
with calendar as (
    select min(date) as date, max(date) as endDate
    from sales
    union all
    select dateadd(day, 1, date), endDate
    from calendar
    where date < enddate
),
products as (
    select distinct productid
    from sales
)
select
    p.productid,
    c.date as salesDate, 
    avg(price) as avg1DayPrice,
    sum(sum(s.price)) over (partition by p.productid
                            order by c.date
                            rows between 6 preceding and current row)
        / nullif(
             sum(count(s.price)) over(partition by p.productid
                                      order by c.date
                                      rows between 6 preceding and current row),
             0)
        as avg7DayPrice
from calendar c
cross join products p
left join sales s
    on s.date = c.date
    and s.productid = p.productid
group by p.productid, c.date

修改后查询（WINDOW子句，结果异常） #

-- Reference to common defined window clause
-- Expect same results, but that is not what I get.
with calendar as (
    select min(date) as date, max(date) as endDate
    from sales
    union all
    select dateadd(day, 1, date), endDate
    from calendar
    where date < enddate
),
products as (
    select distinct productid
    from sales
)
select
    p.productid,
    c.date as salesDate, 
    avg(price) as avg1DayPrice,
    sum(sum(s.price)) over last7days
        / nullif(
             sum(count(s.price)) over last7days,
             0)
        as avg7DayPrice
from calendar c
cross join products p
left join sales s
    on s.date = c.date
    and s.productid = p.productid
group by p.productid, c.date
window last7days as (partition by p.productid
                     order by c.date
                     rows between 6 preceding and current row)

测试数据 #

结果对比 #

原查询正确结果

修改后查询异常结果

原因分析 #

差异的核心是SQL引擎对WINDOW子句和内联OVER子句中列的解析时机与作用域不同：

1. 内联OVER子句：在SELECT列表中定义，此时p.productid和c.date已经是GROUP BY后的聚合结果列。窗口函数在分组后的3行数据上执行，sum(count(s.price))是对分组后的每日销售数量（1、2、1）做窗口求和，得到1、3、4，符合预期。
2. WINDOW子句：定义在GROUP BY之后，但引擎会将窗口中引用的p.productid、c.date解析为原始表的列（分组前的行数据），而非GROUP BY后的聚合列。这导致窗口函数在分组前的4行销售数据（而非分组后的3行）上执行：
   • count(s.price)在分组前的每行都是1（每行对应一条有效销售记录）
   • sum(count(s.price)) over last7days计算的是窗口内所有行的总数（4），因此每个分组后的行都得到4，最终导致avg7DayPrice计算错误。

这种差异是SQL引擎的实现细节导致的——部分引擎在处理WINDOW子句时，优先关联原始表的列，而非GROUP BY后的别名或聚合列。

解决方法 #

修改WINDOW子句，使用SELECT列表中定义的聚合列别名（或GROUP BY后的列名，去掉原始表前缀），明确告诉引擎引用分组后的结果列：

with calendar as (
    select min(date) as date, max(date) as endDate
    from sales
    union all
    select dateadd(day, 1, date), endDate
    from calendar
    where date < enddate
),
products as (
    select distinct productid
    from sales
)
select
    p.productid as productid,
    c.date as salesDate, 
    avg(price) as avg1DayPrice,
    sum(sum(s.price)) over last7days
        / nullif(
             sum(count(s.price)) over last7days,
             0)
        as avg7DayPrice
from calendar c
cross join products p
left join sales s
    on s.date = c.date
    and s.productid = p.productid
group by p.productid, c.date
window last7days as (partition by productid  -- 使用分组后的列名，去掉原始表前缀
                     order by salesDate      -- 使用SELECT中定义的别名
                     rows between 6 preceding and current row)

修改后，引擎会正确将窗口应用在分组后的3行数据上，结果与原查询一致。

内容的提问来源于stack exchange，提问作者T N