DISTINCT ON query w / ORDER BY максимальное значение столбца

Мне было поручено преобразовать приложение Rails из MySQL в Postgres как можно скорее и столкнулось с небольшой проблемой.
Запрос активной записи:

current_user.profile_visits.limit(6).order("created_at DESC").where("created_at > ? AND visitor_id <> ?", 2.months.ago, current_user.id).distinct

Производит SQL:

 SELECT visitor_id, MAX(created_at) as created_at, distinct on (visitor_id) * FROM "profile_visits" WHERE "profile_visits"."social_user_id" = 21 AND (created_at > '2015-02-01 17:17:01.826897' AND visitor_id <> 21) ORDER BY created_at DESC, id DESC LIMIT 6 

Я довольно уверен в работе с MySQL, но я честно новичок в Postgres. Я думаю, что этот запрос терпит неудачу по нескольким причинам.

  1. Я считаю, что каждый должен быть первым.
  2. Я не знаю, как упорядочить по результатам максимальной функции
  3. Могу ли я использовать функцию max так?

Цель высокого уровня этого запроса – вернуть 6 последних просмотров профиля пользователя. Любые указатели на то, как исправить этот запрос ActiveRecord (или это результат SQL), будут очень признательны.

Цель высокого уровня этого запроса – вернуть 6 последних просмотров профиля пользователя.

Это было бы просто. Для этого вам не нужны max() и DISTINCT :

 SELECT * FROM profile_visits WHERE social_user_id = 21 AND created_at > (now() - interval '2 months') AND visitor_id <> 21 -- ?? ORDER BY created_at DESC NULLS LAST, id DESC NULLS LAST LIMIT 6; 

Я подозреваю, что ваш вопрос неполный. Если ты хочешь:
6 последних посетителей со своим последним посещением страницы
то вам нужен подзапрос. Вы не можете получить этот порядок сортировки на одном уровне запросов, ни с DISTINCT ON , ни с помощью оконных функций:

 SELECT * FROM ( SELECT DISTINCT ON (visitor_id) * FROM profile_visits WHERE social_user_id = 21 AND created_at > (now() - interval '2 months') AND visitor_id <> 21 -- ?? ORDER BY visitor_id, created_at DESC NULLS LAST, id DESC NULLS LAST ) sub ORDER BY created_at DESC NULLS LAST, id DESC NULLS LAST LIMIT 6; 

Subquery sub получает последнее посещение для пользователя (но не старше двух месяцев, а не для определенного посетителя 21 ORDER BY должен иметь те же самые ведущие столбцы, что и DISTINCT ON .

Тогда вам нужен внешний запрос, чтобы получить 6 последних посетителей.
Рассмотрим последовательность событий:

  • Лучший способ получить счетчик результатов до применения LIMIT

Почему NULLS LAST ? Разумеется, вы не указали определение таблицы.

  • Сортировка PostgreSQL по дате времени asc, null сначала?