sql - 如何在 SQL Server 中使用带有框架的窗口函数执行 COUNT(DISTINCT)

Question

捎带这个可爱的问题: Partition Function COUNT() OVER possible using DISTINCT

我希望计算不同值的移动计数。大致如下:

Count(distinct machine_id) over(按模型顺序按前6行和当前行之间的_timestamp行分区)

显然，SQL Server 不支持该语法。不幸的是，我不太了解(没有内化会更准确)dense_rank 绕行是如何工作的:

dense_rank() over (partition by model order by machine_id) 
+ dense_rank() over (partition by model order by machine_id) 
- 1

因此我无法调整它以满足我对移动窗口的需求。如果我按 machine_id 订购，是否也可以按 _timestamp 订购并使用 rows between？

Answer 1

dense_rank() 给出当前记录的密集排名。当您首先使用 ASC 排序顺序运行它时，您会从第一个元素中获得当前记录的密集排名(唯一值排名)。当您以 DESC 顺序运行时，您会从最后一条记录中获得当前记录的密集排名。然后你删除 1 因为当前记录的密集排名被计算了两次。这给出了整个分区中的总唯一值(并为每一行重复)。

由于dense_rank不支持frames，所以不能直接使用这个方案。您需要通过其他方式生成 frame。一种方法是使用正确的 unique id 比较JOIN 同一个表。然后，您可以在组合版本上使用 dense_rank。

请查看以下解决方案建议。假设您的表中有一个唯一的记录键 (record_id)。如果您没有唯一键，请在第一个 CTE 之前添加另一个 CTE 并为每条记录生成唯一键(使用 new_id() 函数或使用 concat()< 组合多个列 之间带有分隔符以说明 NULLs)

; WITH cte AS (
SELECT 
  record_id
  , record_id_6_record_earlier = LEAD(machine_id, 6, NULL) OVER (PARTITION BY model ORDER BY _timestamp)
  , .... other columns
FROM mainTable
)
, cte2 AS (
SELECT 
  c.*
  , DistinctCntWithin6PriorRec = dense_rank() OVER (PARTITION BY c.model, c.record_id ORDER BY t._timestamp)
    + dense_rank() OVER (PARTITION BY c.model, c.record_id ORDER BY t._timestamp DESC)
    - 1
  , RN = ROW_NUMBER() OVER (PARTITION BY c.record_id ORDER BY t._timestamp )
FROM cte c
     LEFT JOIN mainTable t ON t.record_id BETWEEN c.record_id_6_record_earlier  and c.record_id
)
SELECT *
FROM cte2
WHERE RN = 1

此解决方案有 2 个限制:

1. 如果帧的记录少于 6 条，则 LAG() 函数将为 NULL，因此此解决方案将不起作用。这可以通过不同的方式处理:我能想到的一种快速方法是生成 6 个 LEAD 列(1 条记录之前，2 条记录之前等)，然后将 BETWEEN 子句更改为类似这样BETWEEN COALESCE(c.record_id_6_record_earlier, c.record_id_5_record_earlier, ...., c.record_id_1_record_earlier, c.record_id) 和 c.record_id

2. COUNT() 不计算 NULL。但是 DENSE_RANK 可以。如果它适用于您的数据，您也需要考虑这一点