SQL计算同一bugId多条记录日期差报错，求LAG()解决方案

需要计算同一bugId对应的多条记录中两个日期的天数差，但执行SQL时出现错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

尝试过LAG()函数但未解决，原SQL代码如下：

SELECT DATEDIFF(
DAY,
(SELECT DATEFROMPARTS(dat.year, dat.month, dat.day)
FROM FactReport
JOIN DimDate AS dat ON dat.id = factReport.updatedDateId
WHERE bugId IN (
SELECT bugid FROM FactReport
GROUP BY bugid HAVING count(*) > 1)),

LAG(
(SELECT DATEFROMPARTS(dat.year, dat.month, dat.day)
FROM FactReport
JOIN DimDate AS dat ON dat.id = factReport.updatedDateId
WHERE bugId IN (
SELECT bugid FROM FactReport
GROUP BY bugid HAVING count(*) > 1)
)) 
OVER (ORDER BY FactReport.bugid))

as TimeToUpdate
FROM FactReport

原代码的核心问题是嵌套子查询返回了多行结果，且LAG()函数的使用逻辑错误——不能在LAG()中嵌套完整子查询，应先关联出日期字段，再按bugId分组排序后取上一条记录的日期。

方法一：使用CTE简化逻辑 #

WITH BugDates AS (
    SELECT 
        fr.bugId,
        DATEFROMPARTS(dat.year, dat.month, dat.day) AS updateDate
    FROM FactReport fr
    JOIN DimDate dat ON dat.id = fr.updatedDateId
    WHERE fr.bugId IN (
        SELECT bugid 
        FROM FactReport 
        GROUP BY bugid 
        HAVING COUNT(*) > 1
    )
)
SELECT 
    bugId,
    updateDate,
    LAG(updateDate) OVER (PARTITION BY bugId ORDER BY updateDate) AS prevUpdateDate,
    DATEDIFF(DAY, LAG(updateDate) OVER (PARTITION BY bugId ORDER BY updateDate), updateDate) AS TimeToUpdate
FROM BugDates
ORDER BY bugId, updateDate;

代码说明

1. 用CTEBugDates提前关联出每个bugId对应的日期，同时过滤掉仅单条记录的bugId；
2. 通过PARTITION BY bugId确保仅在同一bugId的记录组内取上一条日期，ORDER BY updateDate保证按时间顺序取数；
3. 同时返回bugId、当前日期、上一条日期，方便验证计算结果。

方法二：直接简化查询 #

如果不需要中间验证字段，可直接写成：

SELECT 
    fr.bugId,
    DATEDIFF(DAY, 
             LAG(DATEFROMPARTS(dat.year, dat.month, dat.day)) OVER (PARTITION BY fr.bugId ORDER BY DATEFROMPARTS(dat.year, dat.month, dat.day)),
             DATEFROMPARTS(dat.year, dat.month, dat.day)) AS TimeToUpdate
FROM FactReport fr
JOIN DimDate dat ON dat.id = fr.updatedDateId
WHERE fr.bugId IN (
    SELECT bugid 
    FROM FactReport 
    GROUP BY bugid 
    HAVING COUNT(*) > 1
)
ORDER BY fr.bugId, DATEFROMPARTS(dat.year, dat.month, dat.day);

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