窗口函数Count Distinct替代方案优化求助

我们有一张交易明细表DataTable，还有一张TimeSortOrder30表——该表记录了一天中的每个30分钟间隔，包含Hour、Min、CustomDateSort三个字段。CustomDateSort的作用是将门店6点开业时间设为起始值，并按顺序顺延至次日，这也是代码中使用DATEADD减去6小时的原因。

客户单日多次交易的情况很常见，我们需要统计每个30分钟间隔时，当日截至该时段的唯一客户数量。但单日交易数据量可达数亿条，数据集规模极大，尝试过临时表方案和CTE方案，代码均运行数小时仍未完成。由于无法在窗口函数中直接使用DISTINCT，我尝试了以下两种实现代码：

临时表方案 #

DROP TABLE IF EXISTS #TripPrep;

SELECT
    DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)               AS IntervalStartTime
   ,CAST(Dateadd(HH,-6,StartTime) AS DATE)                                      AS Date
   ,DATEPART(HH, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Hour
   ,DATEPART(MI, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Min
   ,Cust_ID
INTO
    #TripPrep
FROM DataTable
WHERE
    StoreID       = 1
    AND StartTime >= '2024-07-09 06:00:00 -05:00';
DROP TABLE IF EXISTS #TripPrepWithSortOrder;

SELECT
    TP.IntervalStartTime
   ,TP.Date
   ,TP.I_Hour
   ,TP.I_Min
   ,TP.Cust_ID
   ,TSO.CustomDateSort
INTO
    #TripPrepWithSortOrder
FROM #TripPrep               AS TP
    JOIN dbo.TimeSortOrder30 AS TSO
        ON TP.I_Hour    = TSO.Hour
           AND TP.I_Min = TSO.Min;
DROP TABLE IF EXISTS #DistinctTrips;

SELECT
    TP1.IntervalStartTime
   ,TP1.Date
   ,TP1.I_Hour
   ,TP1.I_Min
   ,TP1.CustomDateSort
   ,COUNT(DISTINCT TP2.Cust_ID) AS Trips
INTO
    #DistinctTrips
FROM #TripPrepWithSortOrder     AS TP1
    JOIN #TripPrepWithSortOrder AS TP2
        ON TP1.Date               = TP2.Date
           AND TP1.CustomDateSort >= TP2.CustomDateSort
GROUP BY
    TP1.IntervalStartTime
   ,TP1.Date
   ,TP1.I_Hour
   ,TP1.I_Min
   ,TP1.CustomDateSort;

SELECT
    DistinctTrips.IntervalStartTime
   ,DistinctTrips.Date
   ,DistinctTrips.I_Hour
   ,DistinctTrips.I_Min
   ,DistinctTrips.CustomDateSort
   ,DistinctTrips.Trips
FROM #DistinctTrips
ORDER BY
    DistinctTrips.IntervalStartTime;

CTE方案 #

WITH TripPrep
AS
 (
 SELECT
     DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)               AS IntervalStartTime
    ,CAST(Dateadd(HH,-6,StartTime) AS DATE)                                      AS "Date"
    ,DATEPART(HH, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Hour
    ,DATEPART(MI, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Min
    ,Cust_ID
 FROM DataTable
 WHERE
     StoreID     = 1
     AND StartTime >= '2024-07-09 06:00:00 -05:00'
 )
    ,TripPrepWithSortOrder
AS
 (
 SELECT
     TP.IntervalStartTime
    ,TP.[Date]
    ,TP.I_Hour
    ,TP.I_Min
    ,TP.Cust_ID
    ,TSO.CustomDateSort

 FROM TripPrep                AS TP
     JOIN dbo.TimeSortOrder30 AS TSO
         ON TP.I_Hour    = TSO.Hour
            AND TP.I_Min = TSO.Min
 )
    ,DistinctTrips
AS
 (
 SELECT
     TP1.IntervalStartTime
    ,TP1.[Date]
    ,TP1.I_Hour
    ,TP1.I_Min
    ,TP1.CustomDateSort
    ,COUNT(DISTINCT TP2.Cust_ID) AS Trips
 FROM TripPrepWithSortOrder     AS TP1
     JOIN TripPrepWithSortOrder AS TP2
         ON TP1.[Date]         = TP2.[Date]
            AND TP1.CustomDateSort >= TP2.CustomDateSort
 GROUP BY
     TP1.IntervalStartTime
    ,TP1.[Date]
    ,TP1.I_Hour
    ,TP1.I_Min
    ,TP1.CustomDateSort
 )
SELECT
    DistinctTrips.IntervalStartTime
   ,DistinctTrips.[Date]
   ,DistinctTrips.I_Hour
   ,DistinctTrips.I_Min
   ,DistinctTrips.CustomDateSort
   ,DistinctTrips.Trips
FROM DistinctTrips
ORDER BY
    DistinctTrips.IntervalStartTime;

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