SQL Server中OVER子句搭配DISTINCT报错及DENSE_RANK使用问题求助

问题重现 #

执行包含COUNT(DISTINCT l.session_id) OVER (PARTITION BY ...)的查询时，触发错误：

Msg 10759, Level 15, State 1, Line 5
Use of DISTINCT is not allowed with the OVER clause.

尝试用DENSE_RANK替代时，因OVER子句缺少ORDER BY触发新错误：

Msg 4112, Level 15, State 1, Line 5
The function 'DENSE_RANK' must have an OVER clause with ORDER BY.

正确解决方法 #

方法1：先分组聚合再关联（兼容所有SQL Server版本） #

先按日期和用户分组计算唯一会话数，再关联回原查询保留其他字段，是最稳妥的兼容方案：

WITH user_daily_logins AS (
    SELECT 
        CAST(l.start_at AS DATE) AS session_date,
        UPPER(l.user_id) AS user_id,
        COUNT(DISTINCT l.session_id) AS logins_cnt
    FROM dbo.user_login l
    GROUP BY CAST(l.start_at AS DATE), UPPER(l.user_id)
),
users_login AS (
    SELECT 
        CAST(l.start_at AS DATE) AS session_date,
        UPPER(l.user_id) AS user_id,
        udl.logins_cnt,
        -- 原查询中的其他字段
        ...
    FROM dbo.user_login l
    JOIN user_daily_logins udl 
        ON CAST(l.start_at AS DATE) = udl.session_date
        AND UPPER(l.user_id) = udl.user_id
),
...

方法2：DENSE_RANK正确用法（窗口函数实现） #

DENSE_RANK必须搭配ORDER BY，通过计算会话ID的升降序排名之和，间接得到唯一会话数量：

WITH users_login AS (
    SELECT 
        CAST(l.start_at AS DATE) AS session_date,
        UPPER(l.user_id) AS user_id,
        l.session_id,
        -- 按会话ID升序排名
        DENSE_RANK() OVER (PARTITION BY CAST(l.start_at AS DATE), UPPER(l.user_id) ORDER BY l.session_id) AS rank_asc,
        -- 按会话ID降序排名
        DENSE_RANK() OVER (PARTITION BY CAST(l.start_at AS DATE), UPPER(l.user_id) ORDER BY l.session_id DESC) AS rank_desc,
        -- 原查询中的其他字段
        ...
    FROM dbo.user_login l
),
user_logins_agg AS (
    SELECT 
        session_date,
        user_id,
        MAX(rank_asc + rank_desc - 1) AS logins_cnt,
        -- 原查询其他字段需用聚合函数或加入GROUP BY
        ...
    FROM users_login
    GROUP BY session_date, user_id, ...
),
...

原理：每个唯一会话ID对应的rank_asc + rank_desc -1等于分组内的唯一会话总数，取最大值即可得到最终结果。

方法3：SQL Server 2022+专属：原生支持COUNT(DISTINCT) OVER #

如果你的SQL Server版本是2022或更高，微软已原生支持该语法，直接使用原查询即可：

WITH users_login AS 
(
    SELECT 
        CAST(l.start_at AS DATE) AS session_date,
        UPPER(l.user_id) AS user_id,
        COUNT(DISTINCT l.session_id) OVER (PARTITION BY CAST(l.start_at AS DATE), UPPER(l.user_id)) AS logins_cnt,
        ...
    FROM 
        dbo.user_login l
),
...

环境适配说明 #

你的当前环境：

SQL Server Management Studio        15.0.18424.0
Operating System                    10.0.17763

注：SSMS 15对应SQL Server 2019及以下版本，推荐使用方法1或方法2。

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