Frage Wählen Sie die erste Zeile in jeder Gruppe GROUP BY?


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

Genauer gesagt, wenn ich eine habe purchases Tabelle, die so aussieht:

SELECT * FROM purchases;

Meine Ausgabe:

ID | Kunde | gesamt
--- + ---------- + ------
 1 | Joe | 5
 2 | Sally | 3
 3 | Joe | 2
 4 | Sally | 1

Ich würde gerne nach dem fragen id der größte Kauf (total) von jedem gemacht customer. Etwas wie das:

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

Erwartete Ausgabe:

FIRST (ID) | Kunde | FIRST (gesamt)
---------- + ---------- + -------------
        1 | Joe | 5
        2 | Sally | 3

896
2017-09-27 01:23


Ursprung


Antworten:


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

800
2017-09-27 01:27



Im PostgreSQL Das ist typisch einfacher und schneller (mehr Leistungsoptimierung 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;

Ob total kann NULL sein (wird nicht so oder so weh tun, aber Sie wollen vorhandene Indizes übereinstimmen):

...
ORDER  BY customer, total DESC NULL LETZT, id;

Hauptpunkte

  • DISTINCT ON ist eine PostgreSQL-Erweiterung des Standards (wo nur DISTINCT im Großen und Ganzen SELECT Liste ist definiert).

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

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

    Kühne Betonung meiner.

  • DISTINCT ON kann mit kombiniert werden ORDER BY. Führende Ausdrücke müssen mit führenden übereinstimmen DISTINCT ON Ausdrücke in der gleichen Reihenfolge. Du kannst hinzufügen zusätzlich Ausdrücke zu ORDER BY um aus jeder Gruppe von Peers eine bestimmte Zeile auszuwählen. Ich fügte hinzu id als letzter Punkt, um die Krawatten zu brechen:

    "Wählen Sie die Reihe mit dem kleinsten id von jeder Gruppe, die sich die höchste teilt total. "

    Ob total kann NULL sein, du höchstwahrscheinlich möchte die Zeile mit dem größten Nicht-Null-Wert. Hinzufügen NULLS LAST wie gezeigt. Einzelheiten:

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

    • Sie muss nicht einen der Ausdrücke in einschließen DISTINCT ON oder ORDER BY.

    • Sie kann Fügen Sie einen anderen Ausdruck in die SELECT Liste. Dies ist hilfreich, um komplexere Abfragen durch Unterabfragen und Aggregat- / Fensterfunktionen zu ersetzen.

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

Index

Das perfekt Index für die obige Abfrage wäre a mehrspaltiger Index Überspannt alle drei Spalten in übereinstimmender Reihenfolge und mit übereinstimmender Sortierreihenfolge:

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 haben DESC NULLS LAST Verwenden Sie in der Abfrage dasselbe im Index, damit Postgres die Sortierreihenfolge erkennt.

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 oben genannten Index hängt weitgehend davon ab Datenverteilung.

Der Index wird verwendet, da er vorsortierte Daten liefert, und in Postgre 9.2 oder höher kann die Abfrage auch von einem Index nur scannen 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, der inzwischen veraltet ist. Ich ersetzte es durch ein detaillierte Benchmark in dieser separaten Antwort.


809
2017-10-03 02:21



Benchmark

Testen Sie die interessantesten Kandidaten mit Postgres 9.4 und 9.5 mit einer halbwegs realistischen Tabelle von 200.000 Zeilen im purchases und 10k deutlich customer_id (Durchschn. 20 Reihen pro Kunde).

Für Postgres 9.5 habe ich einen zweiten Test mit effektiv 86446 verschiedenen Kunden durchgeführt. Siehe unten (Durchschn. 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 ein serial (PK-Einschränkung wurde unten hinzugefügt) und eine Ganzzahl customer_id da ist das ein typischeres Setup. Auch hinzugefügt some_column um typischerweise mehr Spalten nachzuholen.

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 Tabelle - für überlegene 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 zweiter Test für 9.5 habe ich das gleiche Setup verwendet, aber mit random() * 100000 generieren customer_id um nur ein paar Zeilen zu bekommen customer_id.

Objektgrößen für die Tabelle purchases

Generiert mit diese Abfrage.

               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 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. customer Tisch 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), Bestes von 5 Läufen.

Alle Abfragen verwendet ein Nur Index scannen 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

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 durchgeführt 9.1 auf einer realen Tabelle von 65579 Zeilen und einspaltigen btree Indizes für jede der drei beteiligten Spalten und nahm das Beste Ausführungszeit von 5 Läufen.
Vergleichen @OMGPonies ' erste Abfrage (A) zum über 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. Benutze Bedingung WHERE customer BETWEEN x AND y ergibt 1000 Zeilen.

    A: 249.136 ms
    B:  55.111 ms
    
  3. Wählen Sie einen einzelnen Kunden mit aus 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);


82
2018-01-11 06:05



Dies ist üblich  Problem, das bereits gut getestet und hoch ist optimierte Lösungen. Persönlich bevorzuge ich die links beitreten Lösung von Bill Karwin (das Original Post mit vielen anderen Lösungen).

Beachten Sie, dass eine Reihe von Lösungen für dieses allgemeine Problem überraschend in einer der offiziellen Quellen gefunden werden kann, MySQL Handbuch! Sehen Beispiele für allgemeine Abfragen :: Die Zeilen, die das gruppenweise Maximum einer bestimmten Spalte enthalten.


37
2018-06-27 08:38



In Postgres können Sie verwenden array_agg so was:

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

Dies wird dir das geben id vom größten Kauf jedes Kunden.

Einige Dinge zu beachten:

  • array_agg ist eine Aggregatfunktion, mit der es arbeitet GROUP BY.
  • array_agg lässt Sie einen Ordnungsbereich auf nur sich selbst festlegen, sodass die Struktur der gesamten Abfrage nicht eingeschränkt wird. Es gibt auch eine Syntax für das Sortieren von NULL-Werten, wenn Sie etwas anderes als den Standard tun müssen.
  • Sobald wir das Array erstellt haben, nehmen wir das erste Element. (Postgres-Arrays sind 1-indexiert, nicht 0-indexiert).
  • Du könntest benutzen array_agg in ähnlicher Weise für Ihre dritte Ausgabespalte, aber max(total) ist einfacher.
  • nicht wie DISTINCT ONverwenden array_agg kannst du deine behalten GROUP BY, falls Sie das aus anderen Gründen wollen.

20
2017-08-27 18:14



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;

11
2018-06-17 18:02



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 funktionieren fast 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.


6
2018-03-10 15:19



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

5
2018-04-08 16:13