嵌套CTE的SQL查询返回错误结果，与非嵌套写法输出不一致求排查

我在SQL Server中编写了两个查询用于获取指定产品的上一年销售额，其中非嵌套CTE写法输出正确，嵌套CTE写法结果错误，以下是两个查询及结果差异说明：

非嵌套CTE（输出正确） #

WITH cte AS
(
    SELECT * 
    FROM 
        (SELECT
             category, product_id,
             DATEPART(YEAR, order_date) AS order_year,
             SUM(sales) AS sales
         FROM
             namastesql.dbo.orders 
         GROUP BY 
             category, product_id, DATEPART(YEAR, order_date)) a
)
SELECT
    *,
    LAG(sales) OVER (PARTITION BY category ORDER BY order_year) AS prev_year_sales
FROM
    cte  
WHERE
    product_id = 'FUR-FU-10000576'

嵌套CTE（输出错误） #

with cte as (
    select category, product_id, DATEPART(YEAR, order_date) as order_year, sum(sales) as t_sales
    from namastesql.dbo.orders 
    GROUP BY category, product_id, DATEPART(YEAR, order_date)
),
cte2 as (
    select *, lag(t_sales) over (partition by category order by order_year) as prev_year_sales
    from cte
)  
select * from cte2 where product_id = 'FUR-FU-10000576';

结果差异 #

• 正确输出：仅显示目标产品各年份的销售额，prev_year_sales为该产品自身上一年的销售额（无对应数据时为NULL）
• 错误输出：prev_year_sales显示的是同一分类下其他产品上一年的销售额，而非目标产品自身的

核心问题：窗口函数计算时机与分区范围不同 #

1. 非嵌套CTE的执行逻辑

   • 先完成全量数据聚合，得到所有产品各年份的销售额汇总
   • 先通过WHERE过滤出目标产品的所有年份记录
   • 最后针对过滤后的目标产品数据集，按category分区、order_year排序，用LAG取该产品自身上一年的销售额

2. 嵌套CTE的执行逻辑

   • 先完成全量数据聚合
   • 在cte2中，对所有产品的所有记录按category分区、order_year排序，计算每个记录的上一年销售额（这里的"上一年"是同一分类下任意产品的上一条记录，不区分产品ID）
   • 最后过滤出目标产品的记录，此时prev_year_sales是同一分类中上一年的任意产品销售额，而非目标产品自身的

嵌套CTE修复方案 #

方案1：调整窗口函数分区范围

在PARTITION BY中同时包含category和product_id，确保LAG只针对同一个产品的年份数据计算：

with cte as (
    select category, product_id, DATEPART(YEAR, order_date) as order_year, sum(sales) as t_sales
    from namastesql.dbo.orders 
    GROUP BY category, product_id, DATEPART(YEAR, order_date)
),
cte2 as (
    select *, lag(t_sales) over (partition by category, product_id order by order_year) as prev_year_sales
    from cte
)  
select * from cte2 where product_id = 'FUR-FU-10000576';

方案2：对齐非嵌套CTE的过滤时机

先过滤目标产品，再计算窗口函数：

with cte as (
    select category, product_id, DATEPART(YEAR, order_date) as order_year, sum(sales) as t_sales
    from namastesql.dbo.orders 
    GROUP BY category, product_id, DATEPART(YEAR, order_date)
),
cte2 as (
    select * from cte where product_id = 'FUR-FU-10000576'
)  
select *, lag(t_sales) over (partition by category order by order_year) as prev_year_sales
from cte2;

内容的提问来源于stack exchange，提问作者Rajesh Kumar Dash