DISTINCT

Documentation de la clause DISTINCT

Si SELECT DISTINCT est spécifié, seules les lignes uniques apparaissent dans le résultat d’une requête. Ainsi, parmi chaque ensemble de lignes parfaitement identiques dans le résultat, une seule est conservée.

Vous pouvez spécifier la liste des colonnes dont les valeurs doivent être uniques : SELECT DISTINCT ON (column1, column2,...). Si les colonnes ne sont pas spécifiées, elles sont toutes prises en compte.

Considérez la table :

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

Utiliser DISTINCT sans spécifier de colonnes :

SELECT DISTINCT * FROM t1;

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

Utilisation de DISTINCT avec des colonnes spécifiques :

SELECT DISTINCT ON (a,b) * FROM t1;

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

​

DISTINCT et ORDER BY

ClickHouse permet d’utiliser les clauses DISTINCT et ORDER BY sur des colonnes différentes dans une même requête. La clause DISTINCT est exécutée avant la clause ORDER BY.

Considérons la table :

┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘

Sélection de données :

SELECT DISTINCT a FROM t1 ORDER BY b ASC;

┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘

Sélection des données avec un ordre de tri différent :

SELECT DISTINCT a FROM t1 ORDER BY b DESC;

┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘

La ligne 2, 4 a été tronquée avant le tri.

Tenez compte de cette spécificité d’implémentation lors de l’écriture de requêtes.

​

Traitement de NULL

DISTINCT traite NULL comme si NULL était une valeur spécifique, et NULL==NULL. Autrement dit, dans les résultats de DISTINCT, les différentes combinaisons avec NULL n’apparaissent qu’une seule fois. Cela diffère du traitement de NULL dans la plupart des autres contextes.

​

Alternatives

Il est possible d’obtenir le même résultat en appliquant GROUP BY sur le même ensemble de valeurs que celui spécifié dans la clause SELECT, sans utiliser de fonctions d’agrégation. Mais il existe quelques différences par rapport à l’approche GROUP BY :

• DISTINCT peut être appliqué conjointement avec GROUP BY.
• Avant le démarrage de l’exécution externe, une requête sans ORDER BY peut s’arrêter dès qu’elle a lu suffisamment de lignes distinctes pour satisfaire LIMIT.
• Avant le démarrage de l’exécution externe et lorsque ORDER BY est omis, une plage LIMIT ... AFTER ... UNTIL sans ALL peut également arrêter la requête dès que la plage est terminée.
• Les blocs de données sont affichés au fur et à mesure de leur traitement jusqu’au démarrage de l’exécution externe.

​

DISTINCT en mémoire externe

DISTINCT peut écrire des données temporaires sur disque afin de traiter des ensembles de valeurs uniques trop volumineux pour tenir en mémoire. Cela nécessite des E/S disque supplémentaires et peut ralentir les requêtes.

Deux paramètres déterminent le moment où le déversement sur disque commence :

• max_bytes_before_external_distinct définit un seuil, en octets, de la mémoire totale de la requête. Sa valeur par défaut est 0 (désactivé).
• max_bytes_ratio_before_external_distinct définit une fraction de la mémoire disponible dans les limites fixées par le serveur ou par l’utilisateur, mesurée au début de l’exécution. Sa valeur par défaut est 0.5 et il reste sans effet si aucune de ces limites ne s’applique.

Lorsque les deux seuils s’appliquent, le plus petit est retenu. Définissez les deux paramètres à 0 pour désactiver le déversement sur disque.

max_memory_usage n’influe pas sur le ratio. Pour régler le déversement sur disque par rapport à une limite de mémoire de requête, définissez un seuil absolu inférieur à cette limite. Par exemple, cette requête utilise un seuil de déversement de 16 Mio avec une limite de mémoire de requête de 256 Mio :

SELECT DISTINCT number % 1000000 AS id
FROM numbers(2000000)
SETTINGS
    max_bytes_before_external_distinct = 16777216,
    max_bytes_ratio_before_external_distinct = 0,
    max_memory_usage = 268435456;

Ces seuils ne plafonnent pas l’utilisation de la mémoire. Laissez de la marge pour le reste du traitement de la requête et pour le déversement lui-même. Le déversement peut également démarrer plus tôt en cas de pression mémoire.

Des lignes peuvent être renvoyées avant le déversement, et un LIMIT satisfait à ce stade peut mettre fin à la requête de façon anticipée. Une fois le déversement commencé, le reste de l’entrée doit être lu avant que les résultats restants puissent être renvoyés. Si la requête comporte un ORDER BY, ces résultats sont renvoyés dans l’ordre demandé.

Lorsque DISTINCT traite une entrée triée selon un préfixe de ses clés, il ne déverse pas sur disque. Un grand groupe de lignes partageant le même préfixe peut toutefois consommer une quantité importante de mémoire.

Comme avec optimize_distinct_in_order, le déversement peut dédupliquer des valeurs à virgule flottante ayant des représentations binaires différentes mais considérées comme égales, notamment 0.0 et -0.0, ou des valeurs NaN dont les charges utiles diffèrent.