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

Question

Связанный с - PostgreSQL DISTINCT ON с другим ORDER BY

У меня есть таблица покупок (product_id, купил_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, и этот результат должен быть отсортирован в порядке убывания по полю купленного_атеста:

| 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      |

Таким образом, строки одинаковы, но порядок неправильный. Есть ли способ это исправить?

Answer 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

Answer 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

Answer 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.

Answer 4

Попробуй это !

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