SQL Server CROSS APPLY场景下获取销售计数达4的目标日期

现有两张SQL Server业务表，结构与测试数据如下：

CREATE TABLE [dbo].[Sale](
    [ID] [int] NOT NULL,
    [SaleDate] [date] NOT NULL,
    [CustomerRef] [varchar](20) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Verification](
    [CustomerRef] [varchar](20) NOT NULL,
    [VerificationDate] [date] NOT NULL
) ON [PRIMARY]

INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (1, CAST(N'2022-02-01' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (2, CAST(N'2022-02-02' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (3, CAST(N'2022-02-03' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (4, CAST(N'2022-02-13' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (5, CAST(N'2022-02-14' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (6, CAST(N'2022-02-15' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (7, CAST(N'2022-02-16' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (8, CAST(N'2022-03-08' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (9, CAST(N'2022-03-08' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (10, CAST(N'2022-03-10' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (11, CAST(N'2022-03-11' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (12, CAST(N'2022-03-12' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (13, CAST(N'2022-03-13' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (14, CAST(N'2022-02-20' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (15, CAST(N'2022-03-14' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (16, CAST(N'2022-02-10' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (17, CAST(N'2022-02-11' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (18, CAST(N'2022-02-12' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (19, CAST(N'2022-03-18' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (20, CAST(N'2022-03-19' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (21, CAST(N'2022-03-20' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (22, CAST(N'2022-02-15' AS Date), N'3')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (23, CAST(N'2022-02-16' AS Date), N'3')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (24, CAST(N'2022-02-20' AS Date), N'4')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (25, CAST(N'2022-02-21' AS Date), N'4')
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'1', CAST(N'2022-02-01' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'2', CAST(N'2022-02-10' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'3', CAST(N'2022-02-15' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'4', CAST(N'2022-02-20' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'1', CAST(N'2022-03-10' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'2', CAST(N'2022-03-20' AS Date))
GO

现有查询逻辑为统计每个客户每次核验后，到下一次核验前的首笔销售日期起2周内的销售总笔数，已通过CTE+两层CROSS APPLY实现，核心代码如下：

;WITH CTE AS(
SELECT *,LEAD(VERIFICATIONDATE,1,'20990101') OVER(PARTITION BY CUSTOMERREF ORDER BY VERIFICATIONDATE) AS NEXTVERIFICATIONDATE
FROM VERIFICATION A
)
SELECT * 
FROM CTE A
--获取核验日期之后、下一次核验日期之前的首笔销售日期
CROSS APPLY 
    (SELECT MIN(SaleDate) FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF 
       AND B.SALEDATE >= A.VERIFICATIONDATE
       AND B.SALEDATE < NEXTVERIFICATIONDATE) Z
--统计首笔销售日期之后2周内、下一次核验前的销售总数
CROSS APPLY 
    (SELECT COUNT(*) COUNT 
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF
       AND B.SALEDATE >= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
       AND SALEDATE < DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
       AND SALEDATE < NEXTVERIFICATIONDATE) U

需要在上述查询结果中新增TARGETDATE列，返回统计周期内累计销售笔数达到4时对应的销售日期，不足4笔则返回空值。

• 初始写法报错原因：直接在CROSS APPLY的WHERE子句中使用ROW_NUMBER()窗口函数，违反SQL Server语法限制，窗口函数仅允许出现在SELECT或ORDER BY子句中，因此触发错误：
  Windowed functions can only appear in the SELECT or ORDER BY clauses.
• 后续编写的OUTER APPLY嵌套子查询方案逻辑正确：内层子查询先筛选符合时间范围的销售记录、按日期排序生成行号，外层筛选行号为4的记录取销售日期，不足4笔时OUTER APPLY会自动返回NULL，符合需求。但该写法存在两处可优化点：
  • 内层ROW_NUMBER()的PARTITION BY B.CUSTOMERREF是冗余逻辑，APPLY关联时已经按客户ID做了过滤，内层返回的全是当前客户的销售记录，不需要额外分区，去掉可减少计算开销。
  • 仅按SaleDate排序存在不稳定风险：如果同一客户同一天存在多笔销售，行号计算结果可能不符合实际业务顺序，需要加上主键ID作为排序次键，保证排序确定性。

修正后的TARGETDATE计算写法如下：

OUTER APPLY(
    SELECT SALEDATE AS TARGETDATE FROM(
        SELECT SALEDATE,ROW_NUMBER()OVER(ORDER BY B.SALEDATE, B.ID) RNO
         FROM SALE B 
         WHERE A.CUSTOMERREF = B.CUSTOMERREF
           AND B.SALEDATE >= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
           AND B.SALEDATE < DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
           AND B.SALEDATE < NEXTVERIFICATIONDATE
                   ) TBL
                   WHERE RNO=4
) V

性能优化建议：原有写法中统计销售总笔数、计算TARGETDATE分别做了两次Sale表查询，可以把两个逻辑合并到同一个APPLY中，只扫描一次符合条件的销售记录就能同时得到两个结果，执行效率更高，合并写法示例：

CROSS APPLY (
    SELECT 
        COUNT(*) AS COUNT,
        MIN(CASE WHEN RNO=4 THEN SALEDATE END) AS TARGETDATE
    FROM (
        SELECT 
            SALEDATE,
            ROW_NUMBER()OVER(ORDER BY SALEDATE, ID) AS RNO
        FROM SALE B
        WHERE A.CUSTOMERREF = B.CUSTOMERREF
          AND B.SALEDATE >= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
          AND B.SALEDATE < DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
          AND B.SALEDATE < A.NEXTVERIFICATIONDATE
    ) T
) U

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