first - sql group by last



Wählen Sie die erste Zeile in jeder Gruppe GROUP BY? (7)

Wie der Titel andeutet, möchte ich die erste Zeile jedes Satzes von Zeilen auswählen, die mit GROUP BY gruppiert sind.

Genauer gesagt, wenn ich eine purchases , die wie folgt aussieht:

SELECT * FROM purchases;

Meine Ausgabe:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

Ich möchte nach der id des größten Kaufs ( total ) fragen, der von jedem customer . Etwas wie das:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

Erwartete Ausgabe:

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

Answer #1

Benchmark

Testen der interessantesten Kandidaten mit Postgres 9.4 und 9.5 mit einer halbwegs realistischen Tabelle von 200.000 Zeilen in purchases und 10.000 verschiedenen customer_id IDs ( durchschnittlich 20 Zeilen pro Kunde ).

Für Postgres 9.5 habe ich einen zweiten Test mit effektiv 86446 verschiedenen Kunden durchgeführt. Siehe unten ( durchschnittlich 2,3 Zeilen pro Kunde ).

Konfiguration

Haupttisch

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

Ich benutze eine serial (PK Beschränkung hinzugefügt unten) und eine ganze Zahl customer_id da dies eine typische Einstellung ist. Außerdem wurde some_column hinzugefügt, um typischerweise mehr Spalten zu some_column .

Dummy-Daten, PK, Index - eine typische Tabelle hat auch einige tote Tupel:

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customer - für übergeordnete Abfrage

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

In meinem zweiten Test für 9.5 habe ich das gleiche Setup verwendet, aber mit random() * 100000 , um customer_id zu generieren, um nur wenige Zeilen pro customer_id .

Objektgrößen für purchases

Mit dieser Abfrage generiert.

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

Abfragen

1. row_number() in CTE, ( siehe andere Antwort )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. row_number() in der Unterabfrage (meine Optimierung)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON ( siehe andere Antwort )

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. rCTE mit LATERAL Unterabfrage ( siehe hier )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. LATERAL mit LATERAL ( siehe hier )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

6. array_agg() mit ORDER BY ( siehe andere Antwort )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

Ergebnisse

Ausführungszeit für obige Abfragen mit EXPLAIN ANALYZE (und alle Optionen aus ), Best of 5 Runs .

Alle Abfragen verwendeten einen Index-Only-Scan auf purchases2_3c_idx (neben anderen Schritten). Einige von ihnen nur für die kleinere Größe des Index, andere effektiver.

A. Postgres 9.4 mit 200k Zeilen und ~ 20 pro customer_id ID

1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms

B. Das gleiche gilt für Postgres 9.5

1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  

C. Wie B., aber mit ~ 2.3 Zeilen pro customer_id

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

Original (veraltet) Benchmark von 2011

Ich habe drei Tests mit PostgreSQL 9.1 auf einer realen Tabelle von 65579 Zeilen und einspaltigen btree-Indizes für jede der drei beteiligten Spalten durchgeführt und die beste Ausführungszeit von 5 Läufen genommen.
Vergleichen der ersten Abfrage von @OMGPonies ( A ) mit der obigen DISTINCT ON Lösung ( B ):

  1. Wählen Sie die gesamte Tabelle, ergibt in diesem Fall 5958 Zeilen.

    A: 567.218 ms
    B: 386.673 ms
    
  2. Verwenden Sie die Bedingung WHERE customer BETWEEN x AND y was zu 1000 Zeilen führt.

    A: 249.136 ms
    B:  55.111 ms
    
  3. Wählen Sie einen einzelnen Kunden mit WHERE customer = x .

    A:   0.143 ms
    B:   0.072 ms
    

Derselbe Test wurde mit dem in der anderen Antwort beschriebenen Index wiederholt

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

Answer #2

Auf Oracle 9.2+ (nicht 8i + wie ursprünglich angegeben), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

Unterstützt von jeder Datenbank:

Aber Sie müssen Logik hinzufügen, um die Verbindungen zu lösen:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

Answer #3

Die Lösung ist nicht sehr effizient, wie Erwin aufgrund der Anwesenheit von SubQs zeigt

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

Answer #4

Die von OMG Ponies akzeptierte "Supported by any database" -Lösung hat eine gute Geschwindigkeit aus meinem Test.

Hier biete ich die gleiche, aber vollständigere und sauberere Datenbanklösung an. Ties werden berücksichtigt (nehmen Sie an, dass Sie nur eine Zeile für jeden Kunden erhalten möchten, sogar mehrere Datensätze für die maximale Gesamtsumme pro Kunde), und andere Einkaufsfelder (zB purchase_payment_id) werden für die echten übereinstimmenden Zeilen in der Kauftabelle ausgewählt.

Unterstützt von jeder Datenbank:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

Diese Abfrage ist relativ schnell, insbesondere wenn auf der Kauftabelle ein zusammengesetzter Index wie (Kunde, gesamt) vorhanden ist.

Anmerkung:

  1. t1, t2 sind Unterabfragenalias, die je nach Datenbank entfernt werden können.

  2. Caveat : Die using (...) -Klausel wird derzeit in MS-SQL und Oracle db seit dieser Änderung am Jan. 2017 nicht unterstützt. Sie müssen sie selbst erweitern, zB on t2.id = purchase.id usw. Die USING-Syntax funktioniert in SQLite, MySQL und PostgreSQL.


Answer #5

Ich benutze diesen Weg (nur postgresql): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

Dann sollte dein Beispiel fast so funktionieren wie es ist:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;

CAVEAT: Es ignoriert die NULL-Zeilen

Bearbeiten 1 - Verwenden Sie stattdessen die Postgres-Erweiterung

Jetzt benutze ich diesen Weg: http://pgxn.org/dist/first_last_agg/

Zur Installation auf Ubuntu 14.04:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

Es ist eine Postgres-Erweiterung, die Ihnen erste und letzte Funktionen bietet; anscheinend schneller als der obige Weg.

Edit 2 - Bestellung und Filterung

Wenn Sie Aggregatfunktionen (wie diese) verwenden, können Sie die Ergebnisse bestellen, ohne dass die Daten bereits geordnet sein müssen:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Das äquivalente Beispiel mit der Reihenfolge wäre etwa:

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

Natürlich können Sie so bestellen und filtern, wie Sie es für richtig halten. Es ist eine sehr mächtige Syntax.


Answer #6

In PostgreSQL ist dies in der Regel einfacher und schneller (mehr Performance-Optimierung unten):

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

Oder kürzer (wenn nicht so klar) mit Ordnungszahlen von Ausgabespalten:

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Wenn die total NULL sein kann (wird auch nicht schaden, aber Sie möchten die vorhandenen Indizes abgleichen):

...
ORDER  BY customer, total DESC NULLS LAST, id;

Hauptpunkte

  • DISTINCT ON ist eine PostgreSQL-Erweiterung des Standards (wobei nur DISTINCT für die gesamte SELECT Liste definiert ist).

  • Listen Sie eine beliebige Anzahl von Ausdrücken in der DISTINCT ON Klausel auf, der kombinierte Zeilenwert definiert Duplikate. Das Handbuch:

    Offensichtlich werden zwei Zeilen als unterschiedlich angesehen, wenn sie sich in mindestens einem Spaltenwert unterscheiden. Nullwerte werden in diesem Vergleich als gleich angesehen.

    Kühne Betonung meiner.

  • DISTINCT ON kann mit ORDER BY kombiniert werden. Führende Ausdrücke müssen mit den führenden DISTINCT ON Ausdrücken in der gleichen Reihenfolge übereinstimmen. Sie können ORDER BY zusätzliche Ausdrücke hinzufügen, um aus jeder Gruppe von Peers eine bestimmte Zeile auszuwählen. Ich habe id als letztes Element hinzugefügt, um die Bindungen zu lösen:

    "Wähle die Reihe mit der kleinsten id aus jeder Gruppe, die die höchste total teilt."

    Wenn die total NULL sein kann, möchten Sie höchstwahrscheinlich die Zeile mit dem größten Nicht-Null-Wert. Add NULLS LAST wie gezeigt. Einzelheiten:

  • Die SELECT Liste ist nicht durch Ausdrücke in DISTINCT ON oder ORDER BY in irgendeiner Weise eingeschränkt. (Im obigen einfachen Fall nicht benötigt):

    • Sie müssen keinen der Ausdrücke in DISTINCT ON oder ORDER BY .

    • Sie können einen beliebigen anderen Ausdruck in die SELECT Liste aufnehmen. Dies ist hilfreich, um komplexere Abfragen durch Unterabfragen und Aggregat- / Fensterfunktionen zu ersetzen.

  • Ich habe mit den Versionen 8.3 - 10 getestet. Aber das Feature gibt es zumindest seit Version 7.1, also grundsätzlich immer.

Index

Der perfekte Index für die obige Abfrage wäre ein mehrspaltiger Index , der alle drei Spalten in übereinstimmender Reihenfolge und mit übereinstimmender Sortierreihenfolge umfasst:

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Vielleicht zu spezialisiert für reale Anwendungen. Aber verwenden Sie es, wenn Leseleistung entscheidend ist. Wenn Sie DESC NULLS LAST in der Abfrage verwenden, verwenden Sie dasselbe im Index, damit Postgres die Sortierreihenfolge DESC NULLS LAST .

Effektivität / Leistungsoptimierung

Sie müssen Kosten und Nutzen abwägen, bevor Sie für jede Anfrage einen maßgeschneiderten Index erstellen. Das Potenzial des obigen Index hängt weitgehend von der Datenverteilung ab .

Der Index wird verwendet, da er vorsortierte Daten liefert, und in Postgre 9.2 oder höher kann die Abfrage auch von einem Nur-Index-Scan profitieren, wenn der Index kleiner als die zugrunde liegende Tabelle ist. Der Index muss jedoch vollständig gescannt werden.

Benchmark

Ich hatte hier einen einfachen Benchmark für Postgres 9.1, der bis 2016 veraltet war. Also habe ich einen neuen mit einem besseren, reproduzierbaren Setup für Postgres 9.4 und 9.5 laufen lassen und die detaillierten Ergebnisse in einer anderen Antwort hinzugefügt.


Answer #7

Sehr schnelle Lösung

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

und wirklich sehr schnell, wenn die Tabelle durch die ID indiziert wird:

create index purchases_id on purchases (id);




greatest-n-per-group