PostgreSQL DISTINCT ON z innym ORDER BY

Chcę uruchomić to zapytanie:

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

Ale pojawia się ten błąd:

PG :: Błąd: BŁĄD: WYBIERZ ODRÓŻNIENIE wyrażeń musi pasować do wyrażeń ORDER BY

Dodanie address_idjako pierwszego ORDER BYwyrażenia wycisza błąd, ale tak naprawdę nie chcę dodawać sortowania address_id. Czy można to zrobić bez zamówienia przez address_id?

Dokumentacja mówi:

DISTINCT ON (wyrażenie [, ...]) utrzymuje tylko pierwszy wiersz każdego zestawu wierszy, w którym dane wyrażenia są równe. [...] Należy pamiętać, że „pierwszy wiersz” każdego zestawu jest nieprzewidywalny, chyba że zostanie zastosowane polecenie ORDER BY, aby zapewnić, że żądany wiersz pojawi się jako pierwszy. [...] Wyrażenie DISTINCT ON musi pasować do wyrażenia ORDER BY najbardziej na lewo.

Oficjalna dokumentacja

Musisz dodać address_iddo zamówienia.

Alternatywnie, jeśli szukasz pełnego wiersza zawierającego najnowszy zakupiony produkt dla każdego z address_idtych wyników i posortowanego według purchased_attego, próbujesz rozwiązać największy problem N na grupę, który można rozwiązać za pomocą następujących metod:

Ogólne rozwiązanie, które powinno działać w większości DBMS:

SELECT t1.* FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    WHERE product_id = 1
    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

Bardziej zorientowane na PostgreSQL rozwiązanie oparte na odpowiedzi @ hkf:

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

Problem wyjaśniony, rozszerzony i rozwiązany tutaj: wybieranie wierszy uporządkowanych według jednej kolumny i odrębnych w innej

Możesz zamówić według adresu_id w podzapytaniu, a następnie uporządkować według tego, co chcesz w zapytaniu zewnętrznym.

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

Podzapytanie może go rozwiązać:

SELECT *
FROM  (
    SELECT DISTINCT ON (address_id) *
    FROM   purchases
    WHERE  product_id = 1
    ) p
ORDER  BY purchased_at DESC;

Wyrażenia wiodące ORDER BYmuszą zgadzać się z kolumnami w DISTINCT ON, więc nie można uporządkować według różnych kolumn w tym samym SELECT.

Użyj dodatkowego ORDER BYw podzapytaniu tylko wtedy, gdy chcesz wybrać konkretny wiersz z każdego zestawu:

SELECT *
FROM  (
    SELECT DISTINCT ON (address_id) *
    FROM   purchases
    WHERE  product_id = 1
    ORDER  BY address_id, purchased_at DESC  -- get "latest" row per address_id
    ) p
ORDER  BY purchased_at DESC;

Jeśli purchased_atmożesz NULL, zastanów się DESC NULLS LAST. Pamiętaj jednak, aby dopasować swój indeks, jeśli zamierzasz go używać. Widzieć:

• Sortuj PostgreSQL według daty i godziny, najpierw zerowy?
• Dlaczego ORDER BY NULLS LAST wpływa na plan zapytań na klucz podstawowy?

Powiązane, z dodatkowymi wyjaśnieniami:

• Wybierz pierwszy wiersz w każdej grupie GROUP BY?
• Sortuj PostgreSQL według daty i godziny, najpierw zerowy?

Funkcja okna może rozwiązać to w jednym przebiegu:

SELECT DISTINCT ON (address_id) 
   LAST_VALUE(purchases.address_id) OVER wnd AS address_id
FROM "purchases"
WHERE "purchases"."product_id" = 1
WINDOW wnd AS (
   PARTITION BY address_id ORDER BY purchases.purchased_at DESC
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Dla każdego używającego Flask-SQLAlchemy, to zadziałało dla mnie

from app import db
from app.models import Purchases
from sqlalchemy.orm import aliased
from sqlalchemy import desc

stmt = Purchases.query.distinct(Purchases.address_id).subquery('purchases')
alias = aliased(Purchases, stmt)
distinct = db.session.query(alias)
distinct.order_by(desc(alias.purchased_at))

Możesz to również zrobić za pomocą klauzuli group by

   SELECT purchases.address_id, purchases.* FROM "purchases"
    WHERE "purchases"."product_id" = 1 GROUP BY address_id,
purchases.purchased_at ORDER purchases.purchased_at DESC