SQL Server Resolve error related to using DISTINCT with the OVER clause?

bmp9r5qi 于 12个月前发布在其他

关注(0)|答案(2)|浏览(117)

I encountered a problem with SQL Server which displayed the following error message:
Msg 10759, Level 15, State 1, Line 5
Use of DISTINCT is not allowed with the OVER clause.

This occurred while executing a specific query:

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
),
...

The issue stemmed from the use of COUNT(DISTINCT l.session_id) in combination with the OVER clause, which SQL Server does not support.

In search of a solution, I consulted various online resources, including Stackoverflow. The recommended fix was to include DENSE_RANK() in the query.

However, when using DENSE_RANK() , I get a new error instructing to add an ORDER BY clause, despite already having one in the query:
Msg 4112, Level 15, State 1, Line 5
The function 'DENSE_RANK' must have an OVER clause with ORDER BY.

Here's the adjusted query:

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

Additionally, the SQL Server environment details are as follows:

SQL Server Management Studio        15.0.18424.0
Operating System                    10.0.17763

sql-server

来源：https://stackoverflow.com/questions/77611621/resolve-error-related-to-using-distinct-with-the-over-clause

2条答案

按热度按时间

djp7away1#

COUNT(DISTINCT <expression>) OVER (...)

is not yet implemented. You coul use a*

ROW_NUMBER()  OVER (... ORDER BY <expression> DESC) AS N,

then and use

COUNT(N) OVER(PARTITION BY ....)  WHERE N = 1

赞(0）回复(0）举报 12个月前

wi3ka0sx2#

You can emulate it by only counting the first occurances of each session row:

create table #logins (session_id int, start_at datetime, user_id nvarchar(30))

insert into #logins
values  (1, '20231206 10:30', 'test')
,   (1, '20231206 11:30', 'test')
,   (1, '20231206 12:30', 'test')
,   (2, '20231206 13:30', 'test')
,   (1, '20231206 12:30', 'test2')
,   (2, '20231206 13:30', 'test3')

;with cte as (
        select  *
        ,   COUNT(CASE WHEN uniqueSess = 1 THEN 1 END) OVER(PARTITION BY session_date, user_id) AS cnt
        from (
            select  CAST(l.start_at AS DATE) AS session_date
            ,   UPPER(l.user_id) AS user_id
            ,   session_id
            ,   ROW_NUMBER() OVER(PARTITION BY CAST(l.start_at AS DATE), user_id, session_id order by @@spid) as uniqueSess
            from #logins l
        ) x
    )
select *
from cte

Since you cannot wrap multiple window functions inside each other, you have to create a subquery.

ROW_NUMBER() OVER(PARTITION BY CAST(l.start_at AS DATE), user_id, session_id order by @@spid) creates a counter of 1,2,3 for every unique session row. Since you don't care about order, i use @@spid as order by.

COUNT(CASE WHEN uniqueSess = 1 THEN 1 END) OVER(PARTITION BY session_date, user_id) AS cnt this conditionally aggreggates so only first row counts towards the unique session count.

Output:
| session_date | user_id | session_id | uniqueSess | cnt |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 2023-12-06 | TEST | 1 | 1 | 2 |
| 2023-12-06 | TEST | 1 | 2 | 2 |
| 2023-12-06 | TEST | 1 | 3 | 2 |
| 2023-12-06 | TEST | 2 | 1 | 2 |
| 2023-12-06 | TEST2 | 1 | 1 | 1 |
| 2023-12-06 | TEST3 | 2 | 1 | 1 |

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Resolve error related to using DISTINCT with the OVER clause?

2条答案

相关问题

热门标签

最新问答