SQL Server Dense rank for counting distinct items using case statement

jaxagkaj 于 2023-05-16 发布在其他

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

I've looked at similar questions but so far none of the responses have helped. I have a table that looks similar to the one given below:

comp_id      Item_id      Priority
   123456      123          1
   123456      321          1
   123456      333          2
   123456      334          3
   123456      334          3
   123456      337          4
   123457      339          1

I want to run a dense_rank function that will allow me to count distinct item_id, that have priority of (1 or 2). For the same comp_id, I would also like to give the same rank (distinct count) to the items that have have been assigned other priorities (e.g. 3 or 4).

Below is what I currently have:

(CASE WHEN Priority in (1,2) THEN DENSE_RANK() OVER (PARTITION BY comp_id, CASE WHEN Priority in (1,2) THEN 1 ELSE 0 END  ORDER BY item_id ASC )  END
 + CASE WHEN Priority in (1,2) THEN DENSE_RANK() OVER (PARTITION BY comp_id, CASE WHEN Priority in (1,2) THEN 1 ELSE 0 END ORDER BY item_id DESC) END
  - 1
  - CASE COUNT(item_id) OVER (PARTITION BY comp_id)
    WHEN COUNT(*) OVER (PARTITION BY comp_id)
    THEN 0
    ELSE 1
    END) as distinct_count

My current output:

comp_id      Item_id      Priority     Dense_Rank (distinct_count)
   123456      123          1             3
   123456      321          1             3
   123456      333          2             3
   123456      334          3             NULL
   123456      334          3             NULL
   123456      337          4             NULL
   123457      339          1             1

My desired output is:

comp_id      Item_id      Priority     Desired_Dense_Rank (distinct_count)
   123456      123          1             3
   123456      321          1             3
   123456      333          2             3
   123456      334          3             3
   123456      334          3             3
   123456      337          4             3
   123457      339          1             1

Any pointers would be appreciated. I'm using MS SQL server 2012

sql-server

来源：https://stackoverflow.com/questions/76202115/dense-rank-for-counting-distinct-items-using-case-statement

2条答案

按热度按时间

guykilcj1#

You can do this with two levels of window functions: one to calculate the DENSE_RANK , and another to get the MAX of that rank. The second one will only partition by comp_id

SELECT *,
  distinctCount = MAX(dr) OVER (PARTITION BY comp_id)
FROM (
    SELECT *,
      dr = CASE WHEN Priority IN (1, 2) THEN 
             DENSE_RANK() OVER (PARTITION BY comp_id,
               IIF(Priority IN (1, 2), 1, 0) ORDER BY item_id)
           END
    FROM YourTable t
) t;

db<>fiddle

赞(0）回复(0）举报 2023-05-16

k3bvogb12#

I wrote the code based on priority, but I didn't understand why distinct count for other cases(3,4,...), why three is obtained, if it should be two (you have two unique Item_id 334,337 for comp_id=123456)

;with _lis as (

   select case when Priority in(1,2) then 'A' else 'B' end Pr,*  
  from tt
)
select *
, DENSE_RANK() OVER (PARTITION BY comp_id,Pr ORDER BY item_id ASC) +
DENSE_RANK() OVER (PARTITION BY comp_id,Pr ORDER BY item_id DESC) - 1 
 from _lis
 order by comp_id,item_id,Priority

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server Dense rank for counting distinct items using case statement

2条答案

相关问题

热门标签

最新问答