multiple - sql select first occurrence




Sélectionnez la première rangée dans chaque groupe GROUP BY? (7)

Comme le titre l'indique, je voudrais sélectionner la première rangée de chaque série de rangées groupées avec un GROUP BY .

Plus précisément, si j'ai une table d' purchases qui ressemble à ceci:

SELECT * FROM purchases;

Ma sortie:

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

Je voudrais demander l' id du plus gros achat ( total ) effectué par chaque customer . Quelque chose comme ça:

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

Production attendue:

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

Référence

Test des candidats les plus intéressants avec Postgres 9.4 et 9.5 avec un tableau réaliste à mi-chemin de 200k lignes dans les purchases et 10k client_id distinct ( moyenne de 20 lignes par client ).

Pour Postgres 9.5 j'ai effectué un 2ème test avec 86446 clients distincts. Voir ci-dessous ( moyenne de 2,3 lignes par client ).

Installer

Table principale

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

J'utilise une serial (contrainte PK ajoutée ci-dessous) et un nombre entier customer_id car c'est une configuration plus typique. En outre ajouté some_column pour compenser pour généralement plus de colonnes.

Données factices, PK, index - une table typique a également quelques tuples mortes:

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;

table customer - pour une requête supérieure

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;

Dans mon deuxième test pour 9.5 j'ai utilisé la même configuration, mais avec random() * 100000 pour générer customer_id pour obtenir seulement quelques lignes par customer_id .

Tailles d'objet pour les purchases table

Généré avec cette requête .

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

Requêtes

1. row_number() en CTE, ( voir autre réponse )

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() dans la sous-requête (mon optimisation)

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 ( voir autre réponse )

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

4. rCTE avec sous-requête LATERAL ( voir ici )

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. table de customer avec LATERAL ( voir ici )

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() avec ORDER BY ( voir autre réponse )

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

Résultats

Temps d'exécution pour les requêtes ci-dessus avec EXPLAIN ANALYZE (et toutes les options désactivées ), le meilleur de 5 passages .

Toutes les requêtes ont utilisé un balayage d'index seulement sur les purchases2_3c_idx (parmi d'autres étapes). Certains d'entre eux seulement pour la plus petite taille de l'indice, d'autres plus efficacement.

A. Postgres 9.4 avec 200k lignes et ~ 20 par 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. La même chose avec 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. Même que B., mais avec ~ 2,3 lignes par 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

Valeur de référence originale (dépassée) de 2011

J'ai effectué trois tests avec PostgreSQL 9.1 sur une table réelle de 65579 lignes et des index btree à une colonne sur chacune des trois colonnes impliquées et j'ai pris le meilleur temps d'exécution de 5 cycles.
Comparaison de la première requête de @OMGPonies ( A ) à la solution DISTINCT ON ci - dessus ( B ):

  1. Sélectionnez la table entière, résultats dans 5958 lignes dans ce cas.

    A: 567.218 ms
    B: 386.673 ms
    
  2. Utilisez la condition WHERE customer BETWEEN x AND y résultant en 1000 lignes.

    A: 249.136 ms
    B:  55.111 ms
    
  3. Sélectionnez un seul client avec WHERE customer = x .

    A:   0.143 ms
    B:   0.072 ms
    

Même test répété avec l'indice décrit dans l'autre réponse

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

Sur Oracle 9.2+ (non 8i + comme indiqué à l'origine), 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

Pris en charge par n'importe quelle base de données:

Mais vous devez ajouter une logique pour rompre les liens:

  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

Dans PostgreSQL, c'est généralement plus simple et plus rapide (plus d'optimisation des performances ci-dessous):

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

Ou plus court (si pas aussi clair) avec des nombres ordinaux de colonnes de sortie:

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

Si total peut être NULL (ne blessera pas de toute façon, mais vous voudrez faire correspondre les index existants):

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

Points majeurs

  • DISTINCT ON est une extension PostgreSQL du standard (où seul DISTINCT sur toute la liste SELECT est défini).

  • Liste n'importe quel nombre d'expressions dans la clause DISTINCT ON , la valeur de ligne combinée définit des doublons. Le manuel:

    De toute évidence, deux lignes sont considérées comme distinctes si elles diffèrent dans au moins une valeur de colonne. Les valeurs nulles sont considérées comme égales dans cette comparaison.

    Emphase audacieuse mienne

  • DISTINCT ON peut être combiné avec ORDER BY . Les expressions principales doivent correspondre aux principales expressions DISTINCT ON dans le même ordre. Vous pouvez ajouter des expressions supplémentaires à ORDER BY pour sélectionner une ligne particulière de chaque groupe de pairs. J'ai ajouté l' id comme dernier élément pour rompre les liens:

    "Choisissez la rangée avec le plus petit id de chaque groupe partageant le total le plus élevé."

    Si total peut être NULL, vous voulez probablement la ligne avec la plus grande valeur non nulle. Ajouter NULLS LAST comme démontré. Détails:

  • La liste SELECT n'est pas contrainte par les expressions dans DISTINCT ON ou ORDER BY de quelque manière que ce soit. (Pas nécessaire dans le cas simple ci-dessus):

    • Vous ne devez inclure aucune des expressions dans DISTINCT ON ou ORDER BY .

    • Vous pouvez inclure toute autre expression dans la liste SELECT . Cela permet de remplacer des requêtes beaucoup plus complexes par des sous-requêtes et des fonctions d'agrégat / de fenêtre.

  • J'ai testé avec les versions 8.3 - 10. Mais la fonctionnalité a été là au moins depuis la version 7.1, donc fondamentalement toujours.

Indice

L'index parfait pour la requête ci-dessus serait un index multi-colonnes couvrant les trois colonnes dans la séquence correspondante et avec l'ordre de tri correspondant:

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

Peut être trop spécialisé pour les applications du monde réel. Mais utilisez-le si les performances de lecture sont cruciales. Si vous avez DESC NULLS LAST dans la requête, utilisez la même chose dans l'index afin que Postgres connaisse l'ordre de tri.

Efficacité / optimisation des performances

Vous devez peser les coûts et les avantages avant de créer un index personnalisé pour chaque requête. Le potentiel de l'indice ci-dessus dépend largement de la distribution des données .

L'index est utilisé car il délivre des données pré-triées. Dans Postgres 9.2 ou version ultérieure, la requête peut également bénéficier d'un index uniquement si l'index est plus petit que la table sous-jacente. L'index doit être scanné dans son intégralité, cependant.

Référence

J'ai eu un simple benchmark ici pour Postgres 9.1, qui était obsolète en 2016. J'ai donc couru un nouveau avec une meilleure configuration, reproductible pour Postgres 9.4 et 9.5 et ajouté les résultats détaillés dans une autre réponse .


Dans Postgres vous pouvez utiliser array_agg comme ceci:

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

Cela vous donnera l' id du plus gros achat de chaque client.

Quelques choses à noter:

  • array_agg est une fonction d'agrégat, donc cela fonctionne avec GROUP BY .
  • array_agg vous permet de spécifier un ordre limité à lui-même, de sorte qu'il ne contraint pas la structure de la requête entière. Il y a aussi une syntaxe pour trier les valeurs NULL, si vous avez besoin de faire quelque chose de différent de la valeur par défaut.
  • Une fois que nous construisons le tableau, nous prenons le premier élément. (Les tableaux Postgres sont indexés sur 1, pas indexés sur 0).
  • Vous pouvez utiliser array_agg de la même manière pour votre troisième colonne de sortie, mais max(total) est plus simple.
  • Contrairement à DISTINCT ON , l'utilisation de array_agg vous permet de conserver votre GROUP BY , au cas où vous le souhaitez pour d'autres raisons.

La requête:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

COMMENT ÇA MARCHE! (J'ai été là)

Nous voulons nous assurer que nous avons seulement le total le plus élevé pour chaque achat.

Des trucs théoriques (passez cette partie si vous voulez seulement comprendre la requête)

Soit Total une fonction T (client, id) où elle renvoie une valeur donnée avec le nom et l'id Pour prouver que le total donné (T (client, id)) est le plus élevé, nous devons prouver que Nous voulons prouver soit

  • ∀x T (client, id)> T (client, x) (ce total est supérieur à tous les autres totaux pour ce client)

OU

  • ¬∃x T (client, id) <T (client, x) (il n'y a pas de total plus élevé pour ce client)

La première approche aura besoin de nous pour obtenir tous les enregistrements pour ce nom que je n'aime pas vraiment.

Le second aura besoin d'une manière intelligente de dire qu'il ne peut y avoir aucun record plus haut que celui-ci.

Retour à SQL

Si nous quittons rejoint la table sur le nom et le total étant inférieur à la table jointe:

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

nous nous assurons que tous les enregistrements qui ont un autre enregistrement avec le total le plus élevé pour le même utilisateur à joindre:

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

Cela nous aidera à filtrer le total le plus élevé pour chaque achat sans regroupement nécessaire:

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

Et c'est la réponse dont nous avons besoin.


La solution "Supported by any database" d'OMG Ponies acceptée a une bonne vitesse de mon test.

Ici, je propose une approche identique, mais plus complète et plus propre solution de base de données. Les liens sont pris en compte (supposons que chaque client ne souhaite recevoir qu'une seule ligne, même plusieurs enregistrements pour un total maximum par client) et d'autres champs d'achat (par exemple purchase_payment_id) seront sélectionnés pour les lignes correspondantes dans la table d'achat.

Pris en charge par n'importe quelle base de données:

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

Cette requête est raisonnablement rapide surtout quand il y a un indice composite comme (client, total) sur la table d'achat.

Remarque:

  1. t1, t2 sont des alias de sous-requête qui peuvent être supprimés en fonction de la base de données.

  2. Avertissement : la clause using (...) n'est actuellement pas supportée dans MS-SQL et Oracle db à partir de cette édition de janvier 2017. Vous devez l'étendre vous-même par exemple on t2.id = purchase.id etc. La syntaxe USING fonctionne en SQLite, MySQL et PostgreSQL.


Solution très rapide

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

et vraiment très rapide si la table est indexée par id:

create index purchases_id on purchases (id);




greatest-n-per-group