ROW_NUMBER() in Postgres sql-CodePudding

Im trying to add ROW_NUMBER() in a select query on top of a calculated field and get unique rownum for unique calculated field. But Im getting sequuential rownum for all the values in calculated field.

How can I achieve it in Postgres SQL?

Thanks in advance.

select m.subscriber, count(distinct s.session_id)  as num_session 
,ROW_NUMBER() OVER (partition by count(distinct s.session_id) ORDER BY count(distinct s.session_id) DESC) AS rownum
from dbms.music_stream m, dbms.session s, dbms.users u
where  m.session_id = s.session_id
and m.subscriber = u.user_id
and lower(u.subscription_type) = 'premium'
and lower(s.platform) = 'app'
group by m.subscriber

Im applying row_number on count(distinct s.session_id) and I expect the distinct value of count(distinct s.session_id) have same rownum but I get it in an order as below.

 subscriber | num_session | rownum 
------------ ------------- --------
        118 |           2 |      1
         73 |           2 |      2
        139 |           2 |      3
         59 |           2 |      4
         81 |           1 |      1
        103 |           1 |      2

But this is what I expect :

 subscriber | num_session | rownum 
------------ ------------- --------
        118 |           2 |      1
         73 |           2 |      1
        139 |           2 |      1
         59 |           2 |      1
         81 |           1 |      2
        103 |           1 |      2

CodePudding user response：

You want DENSE_RANK(), not ROW_NUMBER(), and also do not include any partition:

SELECT
    m.subscriber,
    COUNT(DISTINCT s.session_id) AS num_session,
    DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT s.session_id) DESC) AS rownum
FROM dbms.music_stream m
INNER JOIN dbms.session s ON m.session_id = s.session_id
INNER JOIN dbms.users u ON m.subscriber = u.user_id
WHERE LOWER(u.subscription_type) = 'premium' AND
      LOWER(s.platform) = 'app'
GROUP BY m.subscriber;

Note also that I replaced your old school implicit joins with modern explicit inner joins. Putting commas in the FROM clause is usually a bad idea.

CodePudding user response：

select m.subscriber as user_id, u.first_name, u.last_name, u.gender, count(distinct s.session_id)  as num_session 
,DENSE_RANK() OVER (ORDER BY count(distinct s.session_id) DESC) as rank
from dbms.music_stream m, dbms.session s, dbms.users u
where  m.session_id = s.session_id
and m.subscriber = u.user_id
and lower(u.subscription_type) = 'premium'
and lower(s.platform) = 'app'
group by m.subscriber, u.first_name, u.last_name, u.gender