Использование предложения DISTINCT для фильтрации данных, но по-прежнему вытягивать другие поля, которые не являются DISTINCT

Я пытаюсь написать запрос в Postgresql, который вытягивает набор упорядоченных данных и фильтрует его отдельным полем. Мне также нужно вытащить несколько других полей из одной и той же строки таблицы, но они должны быть исключены из отдельной оценки. Пример:

  SELECT DISTINCT(user_id) user_id, 
         created_at 
    FROM creations 
ORDER BY created_at   
   LIMIT 20

Мне нужно user_id быть DISTINCT, но все равно, уникальна ли дата created_at. Поскольку дата created_at включена в оценку, я получаю дубликат user_id в моем результирующем наборе.

Кроме того, данные должны быть заказаны по дате, поэтому использование DISTINCT ON здесь не является опцией. Он потребовал, чтобы поле DISTINCT ON было первым полем в предложении ORDER BY и которое не доставляет результаты, которые я ищу.

Как правильно использовать предложение DISTINCT, но ограничивать его область видимости только одним полем при выборе других полей?

Ответ 1

Как вы обнаружили, стандартный SQL рассматривает DISTINCT как применяемый ко всему списку select, а не только один столбец или несколько столбцов. Причиной этого является то, что он неоднозначно, какое значение следует помещать в столбцы, которые вы исключаете из DISTINCT. По той же причине стандартный SQL не позволяет вам иметь неоднозначные столбцы в запросе с помощью GROUP BY.

Но PostgreSQL имеет нестандартное расширение для SQL, чтобы разрешить то, что вы спрашиваете: DISTINCT ON (expr).

SELECT DISTINCT ON (user_id) user_id, created_at 
FROM creations 
ORDER BY user_id, created_at   
LIMIT 20

Вы должны включить выражение (и) в качестве самой левой части вашего предложения ORDER BY.

Дополнительную информацию см. в руководстве по разделе DISTINCT.

Ответ 2

GROUP BY должен обеспечивать различные значения сгруппированных столбцов, это может дать вам то, что вам нужно.

(Заметьте, что я помещаю свои 2 цента, хотя я не знаком с PostgreSQL, а скорее MySQL и Oracle)

В MySql

SELECT user_id, created_at
FROM creations
GROUP BY user_id
ORDER BY user_id

В Oracle sqlplus

SELECT user_id, FIRST(created_at)
FROM creations
GROUP BY user_id
ORDER BY user_id

Это даст вам user_id, а затем first created_at, связанный с этим user_id. Если вы хотите другой created_at, у вас есть возможность заменить FIRST другими функциями, такими как AVG, MIN, MAX или LAST в Oracle, вы также можете попробовать добавить ORDER BY в другие столбцы ( включая те, которые не возвращены, чтобы дать вам другой created_at.

Ответ 3

Ваш вопрос непонятен - когда вы говорите, что вам нужны и другие данные из той же строки, вы не определяете, какую строку.

Вы говорите, что вам нужно заказать результаты created_at, поэтому я предполагаю, что вам нужны значения из строки с min created_at (самые ранние).

Теперь это становится одним из наиболее распространенных вопросов SQL - получение строк, содержащих некоторое суммарное значение (MIN, MAX).

Например

SELECT user_id, MIN(created_at) AS created_at
FROM creations
GROUP BY user_id
ORDER BY MIN(create_at)
LIMIT 20

Этот подход не позволит вам (легко) выбрать другие значения из одной строки.

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

SELECT c.user_id, c.created_at, c.other_columns
FROM creations c LEFT JOIN creation c_help
     ON c.user_id = c_help.user_id AND c.created_at > c_help.create_at
WHERE c_help IS NULL
ORDER BY c.created_at
LIMIT 20

Ответ 4

Если вам нужен самый последний created_at для каждого пользователя, я предлагаю вам заполнить следующим образом:

SELECT user_id, MAX(created_at)
FROM creations
WHERE ....
GROUP BY user_id
ORDER BY created_at DESC

Это приведет к возврату самого последнего created_at для каждого user_id Если вы хотите только 20 лучших, добавьте

LIMIT 20

EDIT: Это в основном то же самое, что и Unreason сказал выше... определите, из какой строки вы хотите, чтобы данные были скопированы.

Ответ 5

Использование подзапроса было предложено кем-то на канале irС#postgresql. Он работал:

SELECT user_id  
FROM (SELECT DISTINCT ON (user_id) * FROM creations) ss  
ORDER BY created_at DESC  
LIMIT 20;