Блог

У меня есть запрос, который выбирает пользователей с указанием районов, которые они посетили, и количества посещений.

 select users.id, places.district, count(users.id) as counts from users
  left join visits on users.id = visits.user_id
  inner join places on visits.place_id = places.id
group by users.id, places.district
  

Мне нужно выбрать только тех пользователей, которые чаще всего посещали указанный район. Например, у меня есть пользователь с идентификатором 1, который посетил район A один раз и район B три раза. Если я укажу район B в качестве параметра, пользователь 1 будет выбран. Если я хочу выбрать пользователей из района A, пользователь 1 не будет выбран.

Я думаю, что это ранжирование, а затем фильтрация:

 select *
from (
    select u.id, p.district, count(*) as cnt_visits,
        rank() over(partition by u.id order by count(*) desc)
    from users u
    inner join visits v on u.id = v.user_id
    inner join places p on p.id = v.place_id
    group by u.id, p.district
) t
where rn = 1 and district = ?
  

Обратите внимание, что на самом деле вам не нужна таблица users для получения этого результата. Мы могли бы упростить запрос следующим образом:

 select *
from (
    select v.user_id, p.district, count(*) as cnt_visits,
        rank() over(partition by u.id order by count(*) desc)
    from visits v
    inner join places p on p.id = v.place_id
    group by v.user_id, p.district
) t
where rn = 1 and district = ?
  

Этот запрос обрабатывает верхние связи: если у пользователя было одинаковое максимальное количество посещений в двух разных районах, учитываются оба. Если вам не нужна эта функция, мы можем упростить подзапрос с помощью distinct on :

 select *
from (
    select distinct on (v.user_id) v.user_id, p.district, count(*) as cnt_visits
    from visits v
    inner join places p on p.id = v.place_id
    group by v.user_id, p.district
    order by v.user_id, cnt_visits desc
) t
where district = ?