SQL时间戳滚动窗口COUNT DISTINCT报错求助：OVER子句不支持DISTINCT

大多数SQL数据库的窗口函数不支持在聚合函数里直接用DISTINCT，所以得换个思路实现你要的「滑动2小时窗口内的去重客户数」需求，以下是两种可行方案：

方案一：标记首次出现后累加计数 #

先给每个客户在时间序列里的首次出现做标记，再在滑动窗口内累加这些标记，得到去重后的客户数，性能相对更优，适合大数据量场景：

WITH ranked_orders AS (
    SELECT 
        OrderTimestamp,
        CustomerName,
        -- 标记当前客户在自身时间线中是否是首次出现
        CASE 
            WHEN ROW_NUMBER() OVER (
                PARTITION BY CustomerName 
                ORDER BY OrderTimestamp
            ) = 1 
            THEN 1 
            ELSE 0 
        END AS is_first_occurrence
    FROM Orders
    WHERE CustomerName IS NOT NULL 
      AND CustomerName != ''
)
SELECT 
    OrderTimestamp,
    CustomerName,
    -- 在2小时滑动窗口内累加首次出现的客户数量
    SUM(is_first_occurrence) OVER (
        ORDER BY OrderTimestamp 
        RANGE BETWEEN INTERVAL 2 HOUR PRECEDING AND CURRENT ROW
    ) AS count_per_time
FROM ranked_orders;

方案二：自连接统计窗口内去重数 #

如果你的数据库支持LATERAL JOIN（比如PostgreSQL）或CROSS APPLY（比如SQL Server），可以用这种更直观的方式，每条记录单独查询其2小时窗口内的去重客户数：

PostgreSQL 版本 #

SELECT 
    o.OrderTimestamp,
    o.CustomerName,
    (SELECT COUNT(DISTINCT CustomerName)
     FROM Orders o2
     WHERE o2.OrderTimestamp >= o.OrderTimestamp - INTERVAL '2 hour'
       AND o2.OrderTimestamp <= o.OrderTimestamp
       AND o2.CustomerName IS NOT NULL 
       AND o2.CustomerName != '') AS count_per_time
FROM Orders o
WHERE o.CustomerName IS NOT NULL 
  AND o.CustomerName != '';

SQL Server 版本 #

SELECT 
    o.OrderTimestamp,
    o.CustomerName,
    o2.count_per_time
FROM Orders o
CROSS APPLY (
    SELECT COUNT(DISTINCT CustomerName) AS count_per_time
    FROM Orders o2
    WHERE o2.OrderTimestamp >= DATEADD(HOUR, -2, o.OrderTimestamp)
      AND o2.OrderTimestamp <= o.OrderTimestamp
      AND o2.CustomerName IS NOT NULL 
      AND o2.CustomerName != ''
) o2
WHERE o.CustomerName IS NOT NULL 
  AND o.CustomerName != '';

注意：自连接方案逻辑简单但性能相对弱一些，数据量较大时建议优先用方案一。

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