Блог

Я написал запрос для получения таких данных (прикрепленное изображение), user_id и topic_id группируются и вычисляется среднее значение оценки. Я хочу ранжировать оценку для каждого пользователя, чтобы каждый пользователь имел ранг 1 для своего самого высокого topic_id. Как мы можем написать запрос для ранжирования их результатов, потому что мне нужно выбрать верхнюю строку для каждого пользователя.

Таблица выглядит следующим образом —

 USER_ID        TOPIC_ID    SCORE  
------------- ------------- ------ 
 b33e3100a7be  829e4b89c318  85     
 b33e3100a7be  b19b6f2b2975  82     
 b33e3100a7be  e305c970701c  81.6   
 b33e3100a7be  6c6fac161e65  81.6   
 7379ce6bc5a9  6c6fac161e65  54.6   
 7379ce6bc5a9  e305c970701c  54.6   
 7379ce6bc5a9  b19b6f2b2975  51.6 
 

и хочу, чтобы результат выглядел примерно так.

  USER_ID        TOPIC_ID    SCORE  RANK
------------- ------------- ------ -----
 b33e3100a7be  829e4b89c318  85     1
 b33e3100a7be  b19b6f2b2975  82     2
 b33e3100a7be  e305c970701c  81.6   3
 b33e3100a7be  6c6fac161e65  81.6   3
 7379ce6bc5a9  6c6fac161e65  54.6   1
 7379ce6bc5a9  e305c970701c  54.6   1
 7379ce6bc5a9  b19b6f2b2975  54.6   2
 

введите описание изображения здесь

Вы можете использовать оконные функции, чтобы получить все лучшие оценки:

 select t.*
from (select t.*,
             rank() over (partition by user_id order by score desc) as seqnum
      from t
     ) t
where seqnum = 1;
 

В этом контексте rank() возвращаются все самые высокие темы, если есть ничья. row_number() возвращает один произвольно.

Вы также можете использовать коррелированный подзапрос:

 select t.*
from t
where t.score = (select max(t2.score)
                 from t t2
                 where t2.user_id = t.user_id
                );
 

Обратите внимание, что оба этих метода могут быть включены в запрос агрегирования. Нет необходимости сохранять промежуточные результаты, чтобы получить то, что вы хотите.

Вы можете получить строку с наивысшим баллом за каждое использование с помощью distinct on :

 select distinct on (user_id) t.*
from mytable t
order by user_id, score desc
 

Вероятно, мы могли бы дополнительно оптимизировать код, если бы вы показывали данные, с которых вы начинаете, и запрос, который вы использовали для создания промежуточного результирующего набора, который вы показываете. Возможно, вы захотите задать новый вопрос для этого, предоставив надлежащие образцы данных, желаемые результаты и существующий код.

Если вы хотите разрешить привязки, то вы можете использовать rank() :

 select *
from (
    select t.*,
        rank() over(partition by user_id order by score desc) rn
    from mytable t
) t
where rn = 1
 

Или, в Postgres 13:

 select t.*
from mytable t
order by rank() over(partition by user_id order by score desc)
fetch first 1 row with ties