Clauses DISTINCT ON multiples dans PostgreSQL

Question

Est-il possible de sélectionner des lignes qui sont DISTINCT ON des ensembles de colonnes séparés et indépendants ?

Supposons que je veuille toutes les lignes qui correspondent aux conditions suivantes :

• distinct sur (name, birth)
• distinct sur (name, height)

De sorte que, dans le tableau suivant, les lignes marquées d'une croix rouge ne seraient pas distinctes (avec une indication de la clause d'échec) :

name      birth    height
--------------------------
William    1976      1.82
James      1981      1.68
Mike       1976      1.68
Tom        1967      1.79
William    1976      1.74   ❌ (name, birth)
William    1981      1.82   ❌ (name, height)
Tom        1978      1.92
Mike       1963      1.68   ❌ (name, height)
Tom        1971      1.86
James      1981      1.77   ❌ (name, birth)
Tom        1971      1.89   ❌ (name, birth)

Dans l'exemple ci-dessus, si le DISTINCT ON clause venait juste d'être DISTINCT ON (name, birth, height) alors toutes les rangées auraient été considérées comme distinctes.

J'ai essayé et ça n'a pas marché :

• SELECT DISTINCT ON (name, birth) (name, height) ...
• SELECT DISTINCT ON (name, birth), (name, height) ...
• SELECT DISTINCT ON ((name, birth), (name, height)) ...
• SELECT DISTINCT ON (name, birth) AND (name, height) ...
• SELECT DISTINCT ON (name, birth) AND ON (name, height) ...
• SELECT DISTINCT ON (name, birth) DISTINCT ON (name, height) ...
• SELECT DISTINCT ON (name, birth), DISTINCT ON (name, height) ...

Answer 1

Comme je l'ai dit la solution à votre problème n'est pas bien définie. Le nombre de lignes de résultats peut être différent pour chaque appel. Si vous êtes satisfait des résultats arbitraires, La solution d'@klin est suffisant.

Dans le cas contraire, vous devez définir plus précisément vos conditions. Comme :

• distinct sur (name, birth) - choisir d'abord la plus petite hauteur, puis la plus petite ID en cas d'égalité.
• distinct sur (name, height) - choisir d'abord la naissance la plus ancienne, puis la plus petite ID en cas d'égalité.

Votre table doit avoir une clé primaire (ou un peu de manière d'identifier les rangées de manière unique) :

CREATE TEMP TABLE tbl (tbl_id serial PRIMARY KEY, name text, birth int, height numeric);
    INSERT INTO tbl (name, birth, height)
       VALUES
         ('William', 1976, 1.82)
       , ('James',   1981, 1.68)
       , ('Mike',    1976, 1.68)
       , ('Tom',     1967, 1.79)
       , ('William', 1976, 1.74)
       , ('William', 1981, 1.82)
       , ('Tom',     1978, 1.92)
       , ('Mike',    1963, 1.68)
       , ('Tom',     1971, 1.86)
       , ('James',   1981, 1.77)
       , ('Tom',     1971, 1.89);

Une requête :

SELECT DISTINCT ON (name, height) *
FROM  (
   SELECT DISTINCT ON (name, birth) *
   FROM   tbl
   ORDER  BY name, birth, height, tbl_id  -- pick smallest height, ID as tiebreaker
   ) s
ORDER  BY name, height, birth, tbl_id;    -- pick earliest birth, ID as tiebreaker

 tbl_id |  name   | birth | height
--------+---------+-------+--------
      2 | James   |  1981 |   1.68
      8 | Mike    |  1963 |   1.68
      4 | Tom     |  1967 |   1.79
      9 | Tom     |  1971 |   1.86
      7 | Tom     |  1978 |   1.92
      5 | William |  1976 |   1.74
      6 | William |  1981 |   1.82
(7 rows)    -- !!!

La racine du problème est qu'une requête avec DISTINCT ON sans déterminisme ORDER BY peut retourner n'importe quelle ligne arbitraire de chaque ensemble de doublons. Appliqué une fois, vous obtenez toujours un nombre déterministe de lignes (avec des choix arbitraires). Appliquée à plusieurs reprises, le nombre de lignes est également arbitraire. Voir aussi :

• Sélectionner la première ligne dans chaque groupe GROUP BY ?

Answer 2

Utilisez un tableau dérivé :

with my_table(name, birth, height) as (
values
('William',    1976,      1.82),
('James',      1981,      1.68),
('Mike',       1976,      1.68),
('Tom',        1967,      1.79),
('William',    1976,      1.74),  -- ? (name, birth)
('William',    1981,      1.82),  -- ? (name, height)
('Tom',        1978,      1.92),
('Mike',       1963,      1.68),  -- ? (name, height)
('Tom',        1971,      1.86),
('James',      1981,      1.77),  -- ? (name, birth)
('Tom',        1971,      1.89)   -- ? (name, birth)
)
select distinct on (name, height) *
from (
    select distinct on (name, birth) *
    from my_table
    ) s

  name   | birth | height 
---------+-------+--------
 James   |  1981 |   1.68
 Mike    |  1963 |   1.68
 Tom     |  1967 |   1.79
 Tom     |  1971 |   1.89
 Tom     |  1978 |   1.92
 William |  1976 |   1.82
(6 rows)