Databricks中count distinct窗口函数不支持的等效实现方法

Databricks 部分低版本运行时不支持在窗口函数中直接使用COUNT(DISTINCT 字段)语法，可以通过以下两种方案实现完全等价的计算效果：

方案1：简洁写法（小数据量场景推荐） #

使用collect_set窗口函数配合size函数实现：collect_set会在指定窗口分区内收集目标字段的去重值集合，再通过size计算集合长度，得到的结果就是分区内目标字段的去重计数。
示例代码：

SELECT 
  *,
  size(collect_set(Marks) OVER(PARTITION BY Name)) AS distinct_marks_cnt
FROM data

注意：该写法在单个分区内去重值量级过高（10万+）时，会产生较高的内存占用，不适合超大规模数据集场景。

方案2：高性能通用写法（大数据量场景推荐） #

通过先去重聚合、再关联回原表明细的方式实现，性能稳定无内存溢出风险，全版本Databricks环境通用。
示例代码：

WITH dedup_detail AS (
  -- 先按分区键+目标去重字段做去重
  SELECT DISTINCT Name, Marks FROM data
),
part_cnt AS (
  -- 计算每个分区的去重值总数
  SELECT Name, count(Marks) AS distinct_marks_cnt
  FROM dedup_detail
  GROUP BY Name
)
-- 关联回原表所有明细行
SELECT d.*, pc.distinct_marks_cnt
FROM data d
LEFT JOIN part_cnt pc
ON d.Name = pc.Name

补充说明：Databricks Runtime 10.4及以上的新版本已经原生支持COUNT(DISTINCT ...) OVER(...)语法，升级环境后可以直接运行你最初编写的SQL。

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