Выбор строк, упорядоченных некоторым столбцом и отличающихся друг от друга

Связано с - PostgreSQL DISTINCT ON с разными ORDER BY

У меня есть покупки в таблице (product_id, purchase_at, address_id)

Пример данных:

| id | product_id |   purchased_at    | address_id |
| 1  |     2      | 20 Mar 2012 21:01 |     1      |
| 2  |     2      | 20 Mar 2012 21:33 |     1      |
| 3  |     2      | 20 Mar 2012 21:39 |     2      |
| 4  |     2      | 20 Mar 2012 21:48 |     2      |

Результатом, который я ожидаю, является последний приобретенный продукт (полная строка) для каждого address_id, и этот результат должен быть отсортирован по порядку потомков по полю purchase_at:

| id | product_id |   purchased_at    | address_id |
| 4  |     2      | 20 Mar 2012 21:48 |     2      |
| 2  |     2      | 20 Mar 2012 21:33 |     1      |

Использование запроса:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM "purchases"
WHERE "purchases"."product_id" = 2
ORDER BY purchases.address_id ASC, purchases.purchased_at DESC

Я получаю:

| id | product_id |   purchased_at    | address_id |
| 2  |     2      | 20 Mar 2012 21:33 |     1      |
| 4  |     2      | 20 Mar 2012 21:48 |     2      |

Итак, строки такие же, но порядок неправильный. Любой способ исправить это?

Ответ 1

Довольно ясный вопрос:)

SELECT t1.* FROM purchases t1
LEFT JOIN purchases t2
ON t1.address_id = t2.address_id AND t1.purchased_at < t2.purchased_at
WHERE t2.purchased_at IS NULL
ORDER BY t1.purchased_at DESC

И, скорее всего, более быстрый подход:

SELECT t1.* FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC

Ответ 2

Ваш ORDER BY используется DISTINCT ON для выбора той строки для каждого отдельного address_id. Если вы хотите заказать результирующие записи, сделайте DISTINCT ON подзаголоком и закажите его результаты:

SELECT * FROM
(
  SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
  FROM "purchases"
  WHERE "purchases"."product_id" = 2
  ORDER BY purchases.address_id ASC, purchases.purchased_at DESC
) distinct_addrs
order by distinct_addrs.purchased_at DESC

Ответ 3

Этот запрос сложнее перефразировать правильно, чем выглядит.

принятый в настоящее время ответ на соединение не позволяет корректно обрабатывать случай, когда две строки-кандидаты имеют одинаковое значение purchased_at: он вернет обе строки.

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

SELECT * FROM purchases AS given
WHERE product_id = 2
AND NOT EXISTS (
    SELECT NULL FROM purchases AS other
    WHERE given.address_id = other.address_id
    AND (given.purchased_at < other.purchased_at OR given.id < other.id)
)
ORDER BY purchased_at DESC

Обратите внимание на то, как он имеет резерв сравнения значений id, чтобы устранить несоответствие случаю, в котором соответствуют значения purchased_at. Это гарантирует, что условие может быть истинным только для одной строки среди тех, которые имеют одинаковое значение address_id.

Исходный запрос с помощью DISTINCT ON обрабатывает этот случай автоматически!

Также обратите внимание на то, что вы вынуждены кодировать тот факт, что вы хотите "последнее для каждого address_id" дважды, как в условии given.purchased_at < other.purchased_at, так и в предложении ORDER BY purchased_at DESC, и вы должны убедиться, что они совпадение. Мне пришлось потратить несколько лишних минут, чтобы убедить себя, что этот запрос действительно положительный.

Его гораздо проще написать этот запрос правильно и понятно, используя DISTINCT ON вместе с внешним подзапросом, как это было предложено dbenhur.