高效查找单果种当日销量≥前两日总和2倍的SQL方案问询

现有一张每日水果销量表，Fruit列包含1000+种不同品类。需要筛选出满足以下条件的记录：针对每种水果，当日销量≥前两日销量总和的2倍。例如：苹果第1、2日各售出1个（总和2），第3日售出4个则符合条件，售出3个不符合。

当前采用的游标方案繁琐低效，且结果准确性存疑。需注意：数据需按DateSold降序排列，存在日期缺失、ID无序的情况。

declare @testTable table(Id int not null identity(1,1),Fruit nvarchar(10),Sold int, DateSold date)

DECLARE @StartDate datetime = '2024-01-22';
DECLARE @EndDate   datetime = Dateadd(day,100,@StartDate);

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
     insert into @testTable(Fruit,Sold,DateSold)
SELECT 'Apple',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
  union 
  SELECT 'Orange',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Pears',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Plums',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
OPTION (MAXRECURSION 0)

declare @appleRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Apple' ORDER BY NEWID());
declare @ornageRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Orange' ORDER BY NEWID());
declare @pearRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Pears' ORDER BY NEWID());
declare @plumRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Plums' ORDER BY NEWID());

update @testTable
set Sold = Sold + 250
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);

select * from @testTable;

-- 以下是预期筛选出的目标记录
select * from @testTable
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);

无需使用游标，用窗口函数LAG()即可高效实现需求，性能远优于游标，适合大品类数据量场景：

WITH FruitSalesWithPrev AS (
    SELECT
        Id,
        Fruit,
        Sold,
        DateSold,
        -- 获取该水果前1日的销量（按日期升序）
        LAG(Sold, 1) OVER (PARTITION BY Fruit ORDER BY DateSold) AS PrevDay1_Sold,
        -- 获取该水果前2日的销量（按日期升序）
        LAG(Sold, 2) OVER (PARTITION BY Fruit ORDER BY DateSold) AS PrevDay2_Sold
    FROM YourSalesTable -- 替换为实际表名
)
SELECT
    Id,
    Fruit,
    Sold,
    DateSold
FROM FruitSalesWithPrev
WHERE
    -- 可选：仅保留有完整前两日数据的记录，若允许前两日数据不全可去掉这两个条件
    PrevDay1_Sold IS NOT NULL
    AND PrevDay2_Sold IS NOT NULL
    AND Sold >= 2 * (PrevDay1_Sold + PrevDay2_Sold)
ORDER BY DateSold DESC;

方案说明： #

1. 分组排序：通过PARTITION BY Fruit按水果分组，ORDER BY DateSold按日期升序排列，确保LAG()能正确获取当前日期的前两日销量
2. 处理日期缺失：LAG()会自动跳过缺失日期，仅取该水果实际存在的上一条/上两条记录。若业务要求严格按自然日的前两日（即使该日无销量，视为0），需先关联日期维度表补全每日数据再进行计算
3. 性能优势：窗口函数是集合型操作，相比逐行处理的游标，在1000+品类的大数据量下执行效率提升显著

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