Hologres:SELECT

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
from_item [ NATURAL ] join_type from_item
          [ ON join_condition | USING ( join_column [, ...] ) ]

Clause WITH

La clause WITH définit des expressions de table communes (CTE), c'est-à-dire des sous-requêtes nommées référencées dans la requête principale.

WITH query_name [ ( column_name [, ...] ) ] AS ( select )

Séparez plusieurs CTE par des virgules. Une CTE ultérieure peut référencer une CTE antérieure. Dans les requêtes suivantes, utilisez query_name directement comme une vue.

Sans liste de column_name, les noms de colonnes correspondent par défaut à ceux renvoyés par la sous-requête. Avec une liste de column_name, le nombre doit correspondre aux colonnes renvoyées par l'instruction SELECT.

Liste SELECT

La liste SELECT (entre les mots-clés SELECT et FROM) spécifie les expressions qui forment les lignes de sortie.

Ajoutez AS output_name après une expression pour nommer la colonne de sortie. Le mot-clé AS est facultatif, sauf si le nom entre en conflit avec un mot-clé PostgreSQL ; dans ce cas, utilisez AS ou placez le nom entre guillemets doubles. Sans nom explicite, PostgreSQL utilise le nom de la colonne pour les références simples ou génère un nom tel que ?column? pour les expressions complexes.

Référencez une colonne de sortie par son nom dans les clauses ORDER BY et GROUP BY. Dans les clauses WHERE et HAVING, utilisez l'expression complète à la place, car les noms de colonnes de sortie n'y sont pas reconnus.

Utilisez un astérisque (*) comme raccourci pour toutes les colonnes des lignes sélectionnées, ou table_name.* pour toutes les colonnes d'une table spécifique. Lors de l'utilisation de * ou table_name.*, AS ne peut pas renommer les colonnes de sortie.

Clause FROM

La clause FROM spécifie une ou plusieurs tables sources. Plusieurs tables sources produisent un produit cartésien (jointure croisée) de toutes les lignes. Généralement, une clause WHERE avec des conditions de jointure restreint le résultat à un petit sous-ensemble du produit cartésien.

La clause FROM prend en charge les éléments suivants :

Clause WHERE

La clause WHERE facultative filtre les lignes du résultat.

WHERE condition

Clause GROUP BY

La clause GROUP BY facultative condense les lignes partageant les mêmes valeurs d'expression de regroupement en une seule ligne de résumé.

GROUP BY grouping_element [, ...]

L'expression grouping_element peut être un nom de colonne d'entrée, le nom ou le numéro ordinal d'une colonne de sortie de la liste SELECT, ou une expression formée à partir de valeurs de colonnes d'entrée. Si un nom est ambigu, GROUP BY l'interprète comme un nom de colonne d'entrée plutôt que comme un nom de colonne de sortie.

Si un élément de regroupement contient GROUPING SETS, ROLLUP ou CUBE, la clause GROUP BY définit plusieurs ensembles de regroupement indépendants. Cela équivaut à un UNION ALL de sous-requêtes avec des clauses GROUP BY individuelles pour chaque ensemble de regroupement.

Les fonctions d'agrégation calculent sur toutes les lignes de chaque groupe, produisant une seule valeur par groupe. Sans clause GROUP BY, la requête traite toutes les lignes sélectionnées comme un seul groupe.

Ajoutez une clause FILTER à un appel de fonction d'agrégation pour inclure uniquement les lignes correspondant à la condition de filtre dans l'entrée de cet agrégat.

Lorsqu'une clause GROUP BY ou une fonction d'agrégation est présente, les expressions de la liste SELECT ne peuvent pas référencer des colonnes non groupées, sauf au sein d'une fonction d'agrégation ou lorsque la colonne non groupée dépend fonctionnellement des colonnes groupées. Une dépendance fonctionnelle existe lorsque les colonnes groupées incluent la clé primaire (ou un sous-ensemble de celle-ci) de la table contenant la colonne non groupée.

Toutes les fonctions d'agrégation sont calculées avant toute expression scalaire dans la clause HAVING ou la liste SELECT. Une expression CASE ne peut pas ignorer le calcul d'une fonction d'agrégation.

Clause HAVING

La clause HAVING filtre les groupes produits par GROUP BY. La syntaxe correspond à celle de WHERE, mais HAVING opère sur des lignes groupées plutôt que sur des lignes individuelles.

HAVING condition [, ...]

Utilisez HAVING pour filtrer sur les résultats d'agrégation. Par exemple, HAVING sum(length) < interval '5 hours' supprime les groupes où la longueur totale est supérieure ou égale à 5 heures.

CUBE, ROLLUP et GROUPING SETS

Ces sous-clauses étendent GROUP BY pour définir plusieurs ensembles de regroupement dans une seule requête.

CUBE ( { expression | ( expression [, ...] ) } [, ...] )

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )

GROUPING SETS ( grouping_element [, ...] )

Clause DISTINCT

SELECT DISTINCT supprime toutes les lignes en double de l'ensemble de résultats, en conservant une ligne par groupe de doublons.

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT

COUNT DISTINCT compte le nombre de valeurs distinctes dans une colonne. Une valeur apparaissant plusieurs fois n'est comptée qu'une seule fois. Les valeurs NULL sont exclues du comptage.

Comptage exact :

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Comptage approximatif :

Le COUNT DISTINCT exact peut être gourmand en ressources. Hologres fournit approx_count_distinct comme alternative approximative moins gourmande en ressources :

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;

Clause UNION

UNION calcule l'union ensembliste des lignes renvoyées par deux instructions SELECT.

select_statement UNION [ ALL | DISTINCT ] select_statement

Clause INTERSECT

INTERSECT calcule l'intersection ensembliste des lignes renvoyées par deux instructions SELECT.

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

Clause EXCEPT

EXCEPT calcule la différence ensembliste : les lignes du résultat SELECT de gauche qui ne se trouvent pas dans le résultat SELECT de droite.

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

Clause ORDER BY

La clause ORDER BY facultative trie les lignes de résultat selon une ou plusieurs expressions.

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

Les lignes sont triées d'abord par l'expression la plus à gauche, puis par les expressions suivantes pour départager les égalités. Les lignes égales sur toutes les expressions sont renvoyées dans un ordre dépendant de l'implémentation.

Chaque expression peut être :

• Le nom ou le numéro ordinal d'une colonne de sortie de la liste SELECT.

• Une expression arbitraire formée à partir de valeurs de colonnes d'entrée.

Le numéro ordinal fait référence à la position de la colonne de sortie (de gauche à droite), ce qui est utile pour les colonnes sans noms uniques. L'attribution d'un nom avec AS est généralement préférable.

Lorsqu'une expression ORDER BY est un nom simple qui correspond à la fois à un nom de colonne de sortie et à un nom de colonne d'entrée, ORDER BY utilise le nom de la colonne de sortie. Cela diffère de GROUP BY, qui utilise le nom de la colonne d'entrée dans la même situation. Cette incohérence existe pour la compatibilité avec la norme SQL.

Direction du tri :

• Ajoutez ASC (croissant, par défaut) ou DESC (décroissant) après n'importe quelle expression.

• Alternativement, utilisez une clause USING avec un opérateur de tri qui est un membre inférieur ou supérieur d'une famille d'opérateurs B-tree. ASC est généralement équivalent à USING <, et DESC à USING >.

Ordre des valeurs NULL :

• NULLS LAST : les valeurs NULL sont triées après toutes les valeurs non NULL (par défaut pour ASC).

• NULLS FIRST : les valeurs NULL sont triées avant toutes les valeurs non NULL (par défaut pour DESC).

• Par défaut, les valeurs NULL sont traitées comme supérieures aux valeurs non NULL.

• Avec une clause USING, la position NULL par défaut dépend du fait que l'opérateur soit inférieur ou supérieur.

Les options de tri s'appliquent uniquement à l'expression qui les suit. Par exemple, ORDER BY x, y DESC n'est pas identique à ORDER BY x DESC, y DESC.

Limitation : Lorsque ORDER BY est appliqué à un résultat UNION, INTERSECT ou EXCEPT, seuls les noms de colonnes de sortie ou les numéros ordinaux sont autorisés, et non les expressions.

Clause LIMIT

La clause LIMIT restreint le nombre de lignes renvoyées. FETCH FIRST est une syntaxe équivalente conforme à la norme SQL.

LIMIT { count | ALL }
OFFSET start

Syntaxe alternative conforme à la norme SQL :

FETCH FIRST count ROWS ONLY

Lorsque les deux sont spécifiés, le système ignore les lignes start avant de compter les lignes count à renvoyer.

Utilisez une clause ORDER BY avec LIMIT pour obtenir des résultats prévisibles. Sans ORDER BY, différentes exécutions peuvent renvoyer différents sous-ensembles ; il s'agit d'un comportement attendu, et non d'un bug.

Le planificateur de requêtes intègre LIMIT dans le plan, de sorte que différentes valeurs LIMIT et OFFSET peuvent produire différents ordres de lignes. Imposez un ordre déterministe avec ORDER BY pour une pagination cohérente.

Requête de base

Récupérez toutes les colonnes d'une table :

SELECT * FROM distributors ORDER BY name;

Jointure de deux tables

SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did;

Clause WITH (CTE)

WITH distributor_name(name) AS (
    SELECT name FROM distributors
)
SELECT name FROM distributor_name ORDER BY name;

GROUP BY

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind;

GROUP BY avec HAVING

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind
HAVING sum(length) < interval '5 hours';

GROUP BY CUBE

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY CUBE ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY ROLLUP

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY ROLLUP ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY GROUPING SETS

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY GROUPING SETS ((l_returnflag, l_shipmode), ())
ORDER BY l_returnflag, l_shipmode;

DISTINCT

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT (exact et approximatif)

Exact :

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Approximatif :

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;