Jak wybrać identyfikator z maksymalną datą grupy według kategorii w PostgreSQL?


88

Na przykład chciałbym wybrać id z maksymalną datą grupy według kategorii, wynik to: 7, 2, 6

id  category  date
1   a         2013-01-01
2   b         2013-01-03
3   c         2013-01-02
4   a         2013-01-02
5   b         2013-01-02
6   c         2013-01-03
7   a         2013-01-03
8   b         2013-01-01
9   c         2013-01-01

Czy mogę wiedzieć, jak to zrobić w PostgreSQL?


4
Zawsze dobrze jest dołączyć swoją wersję PostgreSQL.
Erwin Brandstetter

Odpowiedzi:


141

Jest to doskonały przypadek użycia dla DISTINCT ON(specyficzne rozszerzenie standardu Postgres DISTINCT):

SELECT DISTINCT ON (category)
       id  -- , category, date -- add any other column (expression) from the same row
FROM   tbl
ORDER  BY category, "date" DESC;

Ostrożnie z malejącym porządkiem sortowania. Jeśli kolumna może mieć wartość NULL, możesz dodać NULLS LAST:

DISTINCT ONjest najprostszy i szybki. Szczegółowe wyjaśnienie w tej powiązanej odpowiedzi:

W przypadku dużych stołów rozważ alternatywne podejście:

Optymalizacja wydajności dla wielu wierszy na category:


Wygląda świetnie, ale czy masz absolutną pewność, że to zadziała za każdym razem?
Atherion

@Tixel: Absolutnie. Kliknij linki, aby uzyskać więcej informacji.
Erwin Brandstetter

21

Spróbuj tego:

SELECT t1.* FROM Table1 t1
JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM Table1
   GROUP BY category
) t2
ON T1.category = t2.category
AND t1.date = t2.MAXDATE

Zobacz to SQLFiddle


1
Jest jeszcze jedna opcja wykorzystująca funkcję okna rank ().
Denis de Bernardy

@ user1735921: Otrzymasz wszystkie kolumny z Tabeli1. Możesz wybrać, co chcesz.
Himanshu Jansari

15

Innym podejściem jest użycie first_valuefunkcji okna: http://sqlfiddle.com/#!12/7a145/14

SELECT DISTINCT
  first_value("id") OVER (PARTITION BY "category" ORDER BY "date" DESC) 
FROM Table1
ORDER BY 1;

... chociaż podejrzewam, że sugestia hims056 będzie zazwyczaj działać lepiej, gdy obecne są odpowiednie indeksy.

Trzecie rozwiązanie to:

SELECT
  id
FROM (
  SELECT
    id,
    row_number() OVER (PARTITION BY "category" ORDER BY "date" DESC) AS rownum
  FROM Table1
) x
WHERE rownum = 1;

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.