SQL窗口函数实践笔记（一）——SPARKSQL中COUNT（*）和COUNT(DISTINCT *)并不是窗口函数

​在 SQL 中，处理“去重计数”（Count Distinct）时，窗口函数的情况其实稍微有一点点复杂。因为在标准的 SQL 规范中，COUNT(DISTINCT ...) 是不能直接作为窗口函数（配合 OVER 子句）使用的。

​1. 为什么不能直接写 COUNT(DISTINCT) OVER？
​大多数主流数据库（如 MySQL, PostgreSQL, Hive, Spark SQL）不支持直接在窗口函数中使用 DISTINCT。
​错误写法： COUNT(DISTINCT user_id) OVER(PARTITION BY city)
​原因：窗口函数的设计初衷是逐行计算，而 DISTINCT 在滑动窗口中的计算成本极高。

​2. 替代方案：如何实现去重计数
​方案 A：使用 DENSE_RANK() (最巧妙的技巧)
​如果你想统计某个分类下的去重总数，可以将该分类内的值排序，利用 DENSE_RANK() 正向和反向排序的特性来计算。但这个逻辑较复杂，通常不推荐。
​方案 B：先去重，再聚合（最推荐，性能最好）
​这是处理大数据量时的标准做法。先用 GROUP BY 压掉重复项，再套用窗口函数。

​场景：计算每个部门的员工数（去重），且保留明细。

SELECT 
    user_id,
    department,
    -- 在已经去重的基础上，统计部门人数

SELECT 
    user_id,
    city,
    -- 将该分区下的 ID 收集到集合中（自动去重），然后计算集合大小
    SIZE(COLLECT_SET(user_id) OVER(PARTITION BY city)) as city_unique_users
FROM user_log;

    COUNT(user_id) OVER(PARTITION BY department) as dept_user_count
FROM (
    -- 第一层：先按用户和部门去重
    SELECT user_id, department 
    FROM orders 
    GROUP BY user_id, department
) t;
​方案 C：使用 SIZE(COLLECT_SET(...)) (适用于 Hive/Spark)
​如果你使用的是大数据环境，可以使用集合函数来变相实现：

SELECT 
    user_id,
    city,
    -- 将该分区下的 ID 收集到集合中（自动去重），然后计算集合大小
    SIZE(COLLECT_SET(user_id) OVER(PARTITION BY city)) as city_unique_users
FROM user_log;