Vous appréciez mon travail ? Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

1 café Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés Génial, ça couvre mes frais de serveur mensuels.
10 cafés Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.
BazinGa's - Tips & tuto IT

PostgreSQL – Vade-mecum SQL – 3/8 – Gérer la donnée

Les requêtes

INSERT – Ajouter de la donnée

Insérer des données simples

INSERT INTO nom_schema.ma_table (
	colonne_1,
	colonne_3,
	colonne_4,
	colonne_7
)                         -- On indique les colonnes dans lesquels on va ajouter de la donnée
VALUES (
	'valeur1',
	'valeurA',
	'valeurX',
	'autre valeur'
)                         -- On renseigne les valeurs (on peut utiliser des fonctions) dans l'ordre des colonnes
;

Insérer plusieurs données en même temps

INSERT INTO mon_schema.ma_table (
	colonne_1,
	colonne_3,
	colonne_4,
	colonne_7
)
VALUES
	('valeur1', 'valeurA', 'valeurX', 'autre valeur'),
	('valeur2', 'valeurB', 'valeurY', 'autre valeur'),
	('valeur3', 'valeurC', 'valeurZ', 'autre valeur')
                                                               -- Chaque jeu de données est séparé par une virgule
;

Insérer des données depuis une autre table

INSERT INTO mon_schema.ma_table (
	colonne_1,
	colonne_3,
	colonne_4,
	colonne_7,
	colonne_8
)
SELECT                              -- SELECT sur la table d'origine
	colonne_3,
	colonne_2,
	colonne_6,
	colonne_6,                  -- On importe 2x la même colonne dans deux colonnes différentes
	mafonction(colonne_6)       -- On peut utiliser des fonctions pour transformer la donnée source
FROM
	mon_schema.ma_table_initiale
;

SELECT – affichez des données

La base

Faire un simple SELECT : afficher les données

SELECT					-- SELECT = sélection des données à afficher
	'mon texte',			-- Les textes sont entre guillemets simples
	'23-10-2019',			-- Les dates sont entre guillemets simples
	22,				-- Les chiffres sont écrit sans guillemets
	12.386,				-- Le séparateur des décimales est le point
	TRUE				-- Les valeurs booléennes sont écrits sans guillemets
	null				-- Les valeurs nulles sont écrit sans guillemets

Afficher les données d’une table

SELECT
				-- On choisit les colonnes dont on veut les données ("*" pour sélectionner toutes les colonnes)
	colonne_1,		-- Il suffit de nommer la colonne que l'on souhaite utiliser
	"Colonne_2",		-- Si une colonne (ou tout autre objet) contient des majuscules il faut utiliser des guillemets doubles
	"Colonne 3"		-- Si une colonne (ou tout autre objet) contient des caractères spéciaux il faut utiliser des guillemets doubles
FROM				-- FROM = désignation de la table dont on veut afficher les données
	mon_schema.ma_table
;

Nommer les objets

SELECT
	ma_table.colonne_1,		-- On spécifie à partir de quelle table on veut la colonne
					-- En réalité, PostgreSQL le fait implicitement mais cela peut être utile avec des jointures
	t1.colonne_2,			-- On peut spécifier le nouveau nom de la table (pour rendre les requêtes plus lisible)
	colonne_3 as nouveau_nom_3,	-- Le "as" permet de redéfinir le nom de la colonne      
	colonne_4 as "Nouveau nom 4"	-- On utilise des guillemets car on a des majuscules et des espaces (des caractères spéciaux)
FROM
	mon_schema.ma_table as t1	-- Le "as" permet également de définir un nouveau nom pour une table
;

Afficher les données d’une table virtuelle :

SELECT
	id,
	nom,
	mois,
	salaire
FROM
	(VALUES 						-- On utilise VALUES puis on définit les valeurs
		(1,'arthur',1000),
		(2,'alfred',1400),
		(3,'juliette',1100),
		(4,'albert',1230)
	) AS t1 (id,nom,salaire) 		-- On spécifie un nom de table puis le nom de chaque colonne dans l'ordre des valeurs

WHERE

Afficher des données filtrées

SELECT
	colonne_1,
	colonne_2,
	colonne_3
FROM
	mon_schema.ma_table
WHERE					-- On applique un filtre
	colonne_2 = 'ma valeur'
;

Afficher des données avec plusieurs filtres

SELECT
	colonne_1,
	colonne_2,
	colonne_3
FROM
	mon_schema.ma_table
WHERE				    -- On applique un filtre
	colonne_2 > 10
AND				    -- On applique un deuxième filtre : AND = ET = la condition doit également être satisfaite
	colonne_2 < 20
AND				    -- On applique un troisième filtre
	(			    -- On ouvre une parenthèse : on compare ce qui est dedans
		colonne_1 = 'valeur 1'
	OR			    -- On compare la condition précédent avec la suivante
		colonne_1 = 'valeur 2'
	)
;

Filtrer les valeurs nulles

SELECT
	colonne_1,
	colonne_2,
	colonne_3
FROM
	mon_schema.ma_table
WHERE
	colonne_2 IS NOT NULL       -- NULL représente l’absence de valeur, les opérateurs classiques ne fonctionnent donc pas
;

LIMIT

Limiter le nombre de résultats

SELECT
	colonne_1,
	colonne_2,
	colonne_3
FROM
	mon_schema.ma_table
LIMIT 							-- On limite à 3 résultats
	3
;

Limiter le nombre de résultats de façon focalisé

SELECT
	colonne_1,
	colonne_2,
	colonne_3
FROM
	mon_schema.ma_table
LIMIT 							-- On limite à 3 résultats
	3
OFFSET 							-- Mais on passe 22 lignes avant de renvoyer les données
	22
;

GROUP BY

Regrouper les données

SELECT 
	colonne_1,			-- Le regroupement s'effectue sur cette colonne, nous aurons donc des valeurs unique ici
	count(id),			-- Il peut y avoir plusieurs valeur pour la colonne "id" puisqu'il s'agit d'un regroupement. Il faut donc obligatoirement un opérateur de regroupement
	sum(surface),			-- Ici aussi on utilise un opérateur de regroupement
	min(surface),
	max(surface)
FROM
	mon_schema.ma_table
GROUP BY				-- On regroupe selon un colonne (on peut utiliser plusieurs colonnes séparées par des virgules)
	colonne_1
;

Filtrer les données puis les regrouper

SELECT 
	colonne_1,
	count(id) as "Nombre d'objet",
	sum(surface),
	min(surface),
	max(surface)
FROM
	mon_schema.ma_table
WHERE
	surface > 150
GROUP BY
	colonne_de_regroupement
;

Regrouper les données puis les filtrer

SELECT 
	colonne_1,
	count(id) as "Nombre d'objet",
	sum(surface),
	min(surface),
	max(surface)
FROM
	mon_schema.ma_table
GROUP BY
	colonne_de_regroupement
HAVING						-- On filtre les données groupées
	sum(surface) > 150
;

ORDER BY

Trier les données

SELECT
	colonne_1
FROM
	mon_schema.ma_table
ORDER BY 					-- On tri les données
	colonne_de_tri ASC 			-- On choisi la colonne et l'ordre de tri : ascendant (DESC pour descendant)
;

UPDATE – Modifier la donnée

Modifier la valeur d’une colonne entière

UPDATE 
	mon_schema.ma_table
SET
	ma_colonne = 'valeur'
;

Modifier la valeur d’un champ pour une sélection de ligne

UPDATE 
	mon_schema.ma_table
SET
	ma_colonne = 'valeur'
WHERE
	ma_condition
;

-- Il est possible de se baser sur une autre table :
UPDATE
	mon_schema.ma_table t1
SET 
	mon_champ = 'ma_valeur'
WHERE 
	t1.champ_id IN (
	SELECT
		t2.champ_id
	FROM
		mon_schema.ma_table_2 t2
	WHERE
		t2.mon_champ_y IS NOT NULL
	)
;

Modifier la valeur d’un champ depuis une autre table

UPDATE
	mon_schema.ma_table as t1 
SET
	ma_colonne = t2.une_colonne
FROM
	mon_schema.ma_table_2 as t2
WHERE
	t1.id = t2.id
;

-- Variante avec concaténation des valeurs s'il existe plusieurs correspondances
UPDATE
	mon_schema.ma_table as t1 
SET
	ma_colonne = t2.une_colonne_concatenee
FROM
	(
		SELECT
			string_agg(une_colonne, ' - ') as une_colonne_concatenee
		FROM
			mon_schema.ma_table_2
		GROUP BY
			id
	) as t2
WHERE
	t1.id = t2.id
;

Il est possible de mettre à jour plusieurs colonne d’un seul coup

UPDATE 
	mon_schema.ma_table
SET
	ma_colonne_1 = 'valeur'
	ma_colonne_2 = 'valeur'
;

-- Ou encore à partir d'une autre table
UPDATE
	mon_schema.ma_table as t1 
SET
	ma_colonne_1 = t2.une_colonne,
	ma_colonne_2 = t2.une_autre_colonne,
FROM
	mon_schema.ma_table_2 as t2
WHERE
	t1.id = t2.id
;

Par défaut, PostgreSQL retourne le nombre de lignes mises à jour. Vous pouvez spécifier les colonnes à retourner :

UPDATE 
	mon_schema.ma_table
SET
	ma_colonne = 'valeur'
WHERE
	ma_condition
RETURNING
	ma_colonne,
	une_autre_colonne
;

DELETE – Supprimer la donnée

Supprimer une ligne

DELETE FROM
	mon_schema.ma_table
WHERE 							-- On applique une condition pour ne supprimer qu'une (ou plusieurs) ligne(s)
	ma_condition
;

Supprimer une ligne en renvoyant une valeur

DELETE FROM
	mon_schema.ma_table
WHERE
	ma_condition
RETURNING
	count() 					-- On renvoi le nombre d'élément supprimé
;

Suppression de toutes le données d’une table

DELETE FROM
	mon_schema.ma_table
;

-- OU 
TRUNCATE mon_schema.ma_table 	-- TRUNCATE est une fonction spécifique à PostgreSQL, plus rapide que le DELETE
;

Quelques requêtes complexes

Les jointures

Le but des jointures est de lier une table avec une ou plusieurs autre(s). Attention, les jointures peuvent rapidement être gourmandes en ressource (surtout les jointures géométriques).

Comme dans les requêtes précédentes, vous pouvez choisir les colonnes à afficher ou utiliser le sélecteur ‘*’ pour tout récupérer.

Voici les tables qui vont servir d’exemple

-- Table enfants
  id | nom  
-----+---------
  0  | juliette
  1  | arthur
  2  | alfred

-- Table jouets
  id | objet 
-----+-------
  1  | ski
  2  | ballon
  3  | arc
CROSS JOIN

On récupère toutes les combinaisons possibles

SELECT
	* 
FROM
	mon_schema.enfants 
CROSS JOIN
	mon_schema.jouets
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants,
	mon_schema.jouets
;

--> Résultat
  id | nom      |  id | objet 
-----+----------+-----+-------
  0  | juliette |  1  | ski
  1  | arthur   |  1  | ski
  2  | alfred   |  1  | ski
  0  | juliette |  2  | ballon
  1  | arthur   |  2  | ballon
  2  | alfred   |  2  | ballon
  0  | juliette |  3  | arc
  1  | arthur   |  3  | arc
  2  | alfred   |  3  | arc
INNER JOIN

On ne récupère que les correspondances

SELECT
	* 
FROM
	mon_schema.enfants as t1
INNER JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	*
FROM
	mon_schema.enfants as t1,
	mon_schema.jouets as t2
WHERE
	t1.id=t2.id
;

--> Résultat
  id | nom     |  id | objet 
-----+---------+-----+-------
  1  | arthur  |  1  | ski
  2  | alfred  |  2  | ballon
LEFT OUTER JOIN

On récupère tous les enregistrements de la table de gauche ainsi que ceux de la table de droite qui correspondent.

SELECT
	* 
FROM
	mon_schema.enfants as t1
LEFT OUTER JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants as t1
LEFT JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants
LEFT JOIN
	mon_schema.jouets
USING
	(id)
;

--> Résultat
  id | nom       |  id | objet 
-----+-----------+-----+-------
  0  | juliette  |     | 		-- Aucune valeur ne correspond dans la table "jouets"
  1  | arthur    |  1  | ski
  2  | alfred    |  2  | ballon
RIGHT OUTER JOIN

On récupère tous les enregistrements de la table de droite ainsi que ceux de la table de gauche qui correspondent.

SELECT
	* 
FROM
	mon_schema.enfants as t1
RIGHT OUTER JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants as t1
RIGHT JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants
RIGHT JOIN
	mon_schema.jouets
USING
	(id)
;

--> Résultat
  id | nom    |  id | objet 
-----+--------+-----+-------
  1  | arthur |  1  | ski
  2  | alfred |  2  | ballon
     |        |  3  | arc
FULL OUTER JOIN

On récupère tous les enregistrements en conservant la correspondance.

SELECT
	* 
FROM
	mon_schema.enfants as t1
FULL OUTER JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants as t1
FULL JOIN
	mon_schema.jouets as t2
ON
	t1.id=t2.id
;

-- OU

SELECT
	* 
FROM
	mon_schema.enfants
FULL JOIN
	mon_schema.jouets
USING
	(id)
;

--> Résultat
  id | nom      |  id | objet 
-----+----------+-----+-------
  0  | juliette |     | 
  1  | arthur   |  1  | ski
  2  | alfred   |  2  | ballon
     |          |  3  | arc
NATURAL JOIN

Il s’agit d’une jointure implicite sur les champs possédants le même nom dans chaque table. Plusieurs variantes existent :

  • NATURAL JOIN = INNER JOIN
  • NATURAL FULL JOIN = FULL JOIN
  • NATURAL LEFT JOIN = LEFT OUTER JOIN
SELECT
	* 
FROM
	mon_schema.enfants as t1
NATURAL JOIN
	mon_schema.jouets as t2
;

--> Résultat
  id | nom     |  id | objet 
-----+---------+-----+-------
  1  | arthur  |  1  | ski
  2  | alfred  |  2  | ballon
SELF JOIN

Jointure sur soi-même : on ressort les correspondances avec soi-même

SELECT
	* 
FROM
	mon_schema.enfants as t1
INNER JOIN
	mon_schema.enfants as t2
ON
	t1.id=t2.id
;

--> Résultat
  id | nom       |  id | objet 
-----+-----------+-----+-------
  0  | juliette  |  0  | juliette
  1  | arthur    |  1  | arthur
  2  | alfred    |  2  | alfred
Union

Il s’agit d’une jointure particulière : le but n’est pas de joindre des colonnes mais plutôt des lignes.

Le principe est simple, on colle deux requêtes l’une à la suite de l’autre avec le mot clé UNION, leurs résultat se mettent à la suite les uns des autres.

Attention, les colonnes doivent avoir le même nom et le même type.

SELECT
	1 as nb,
	'mon texte' as txt
UNION
SELECT
	12 as nb,
	'autre texte' as txt
;

Cette syntaxe supprime automatiquement les doublons, essayez ceci :

SELECT
	12 as nb,
	'autre texte' as txt
UNION
SELECT
	12 as nb,
	'autre texte' as txt
;

En ajoutant ALL, vous récupérer ces valeurs en doublon :

SELECT
	12 as nb,
	'autre texte' as txt
UNION ALL
SELECT
	12 as nb,
	'autre texte' as txt
;

Gérer les doublons

Petit cas particulier : si vous avez besoin de retrouver des doublons

SELECT 
	count(mon_champ_id) as "Nombre",
	string_agg(mon_champ_id::text, ',') as "Identifiants"
FROM
	mon_schema.ma_table
GROUP BY
	mon_champ_a_comparer_1,
	mon_champ_a_comparer_2,
	mon_champ_a_comparer_3
;


-- Variante n'affichant que les doublons (et plus)
SELECT 
	count(mon_champ_id) as "Nombre",
	string_agg(mon_champ_id::text, ',') as "Identifiants"
FROM
	mon_schema.ma_table
GROUP BY
	mon_champ_a_comparer_1,
	mon_champ_a_comparer_2,
	mon_champ_a_comparer_3
HAVING count(mon_champ_id) > 1
;


-- Variante affichant les objets, le nombre de doublon ainsi que les ids doublons
SELECT
	t1.mon_champ_id,
	t2."Nombre",
	t2."Identifiants"
FROM
	mon_schema.ma_table as t1,
	(SELECT 
		count(mon_champ_id) as "Nombre",
		string_agg(mon_champ_id::text, ',') as "Identifiants"
	FROM
		mon_schema.ma_table
	GROUP BY
		mon_champ_a_comparer_1,
		mon_champ_a_comparer_2,
		mon_champ_a_comparer_3
	HAVING count(mon_champ_id) > 1) as t2
WHERE
	t1.id::text = ANY (string_to_array(t2."Identifiants",','))
;

Pour supprimer les doublons

DELETE FROM 
	mon_schema.ma_table
WHERE 
	mon_champ_id NOT IN (
		SELECT 
			max(mon_champ_id) 
		FROM 
			mon_schema.ma_table
		GROUP BY
			mon_champ_a_verifie_1,
			mon_champ_a_verifie_2,
			mon_champ_a_verifie_3
	)
;

Concepts clé

Les structures conditionnelles

PostgreSQL vous permet d’utiliser ce qu’on appelle les structures conditionnelles. Voici comment cela se construit :

CASE
	WHEN mon_champ = 'valeur 1'		-- Condition n°1
	THEN 'Je renvoie ceci'			-- Valeur renvoyée si la condition n°1 est respectée
	WHEN mon_champ = 'valeur 2'		-- Condition n°2
	THEN 'Je renvoie cela'			-- Valeur renvoyée si la condition n°2 est respectée
						-- Si aucune condition n'est remplie, alors la valeur renvoyée est 'null'
END

Il est également possible de spécifier la valeur à retourner lorsqu’aucune condition n’est remplie.

CASE
	WHEN mon_champ = 'valeur 1'
	THEN 'Je renvoie ceci'
	WHEN mon_champ = 'valeur 2'
	THEN 'Je renvoie cela'
	ELSE 'Je renvoie rien'			-- Si aucune condition n'est remplie, alors on indique la valeur à retourner
END

Voici une autre syntaxe

CASE mon_champ					-- On déclare le champ sur lequel tester les conditions
	WHEN 'valeur 1'				-- On indique la valeur permettant de remplir la condition (uniquement du égal)
	THEN 'Je renvoie ceci'
	WHEN 'valeur 2'
	THEN 'Je renvoie cela'
	ELSE 'Je renvoie rien'
END

Voici un cas plus complexe utilisant plusieurs colonnes

CASE
	WHEN
		mon_champ = 'valeur 1'
		OR mon_champ = 'valeur 2'	-- Il est possible d'utiliser des opérateurs logiques pour combiner les conditions
	THEN 'Je renvoie ceci'
	WHEN
		mon_champ_2 = 'valeur A'	-- Il est possible de se baser sur un deuxième champ
	THEN 'Je renvoie cela'
	ELSE 'Je renvoie rien'
END

Où utiliser ces structures ?
Dès que vous devez utiliser une colonne (dans un SELECT, dans une clause WHERE…) vous pouvez utiliser les structures conditionnelles.

Voici un exemple :

SELECT
	ma_colonne,
	CASE ma_colonne_test
		WHEN TRUE THEN 'Vrai'
		WHEN FALSE THEN 'Faux'
		ELSE 'Inconnu'
	END,
	CASE
		WHEN ma_colonne_test_2 < 0 THEN 'Négatif'
		WHEN ma_colonne_test_2 > 0 THEN 'Positif'
		ELSE 'Nul'
	END
FROM
	ma_table
;

Les CTE (Common Table Expression)

Il existe une syntaxe particulière permettant de stocker temporairement le résultat d’une requête pour l’utiliser juste après dans une seconde requête. Il s’agit des Common Table Expression.

CTE classique

Le but est de définir une requête puis d’y faire référence dans une seconde :

WITH requete_1 AS (				-- La première requête est nommée "requete_1" puis définie (il s'agit d'un simple SELECT)
	SELECT
		colonne_1 as colonne_a,
		colonne_2 as colonne_b
	FROM
		mon_schema.ma_table_1
)
SELECT						-- La deuxième requête est lancée
	t1.colonne_a
FROM
	requete_1 as t1				-- La première requête est réutilisée simplement en l'appelant dans la clause FROM
WHERE
	t1.colonne_b > 12
;

Cette syntaxe permet de simplifier certaines requêtes, voici un exemple sur une requête que nous avions précédemment pour détecter les doublons. Cette requête affiche les objets, le nombre de doublon ainsi que les ids doublons.

Requête initiale :

SELECT
	t1.mon_champ_id,
	t2."Nombre",
	t2."Identifiants"
FROM
	mon_schema.ma_table as t1,
	(SELECT 
		count(mon_champ_id) as "Nombre",
		string_agg(mon_champ_id::text, ',') as "Identifiants"
	FROM
		mon_schema.ma_table
	GROUP BY
		mon_champ_a_comparer_1,
		mon_champ_a_comparer_2,
		mon_champ_a_comparer_3
	HAVING count(mon_champ_id) > 1) as t2
WHERE
	t1.id::text = ANY (string_to_array(t2."Identifiants",','))
;

Requête CTE :

WITH liste_doublon AS (
	SELECT 
		count(mon_champ_id) as "Nombre",
		string_agg(mon_champ_id::text, ',') as "Identifiants"
	FROM
		mon_schema.ma_table
	GROUP BY
		mon_champ_a_comparer_1,
		mon_champ_a_comparer_2,
		mon_champ_a_comparer_3
	HAVING count(mon_champ_id) > 1
)

SELECT
	t1.mon_champ_id,
	t2."Nombre",
	t2."Identifiants"
FROM
	mon_schema.ma_table as t1,
	liste_doublon as t2
WHERE
	t1.id::text = ANY (string_to_array(t2."Identifiants",','))
;

L’utilisation de cette syntaxe permet de simplifier considérablement la lecture de la requête même si le résultat reste le même.

Notez que vous pouvez enchainer les CTE et les utiliser les unes dans les autres :

WITH 
	requete_1 AS (
		SELECT 
			colonne_1
		FROM
			mon_schema.ma_table
	),
	-- On démarre une autre requête CTE
	requete_2 AS (
		SELECT 
			t1.colonne_2,
			t2.colonne_4
		FROM
			mon_schema.ma_table_2 AS t1
		LEFT JOIN				-- On utilise la première requête
			requete_1 AS t2
			ON t1.colonne_3 = t2.colonne_1
	),

SELECT
	*
FROM
	requete_2
;

CTE Récursif

Il est également possible d’introduire de la récursivité dans vos requêtes avec cette syntaxe. Le but de la récursivité est d’utiliser une requête dans elle-même afin d’obtenir une sorte d’incrémentation.

Une requête récursive est toujours construite de la façon suivante :

  • Le mot clé RECURSIVE
  • Une requête de base
  • Une jointure avec une requête récursive qui utilise la requête CTE en cours de définition

La récursivité s’arrête lorsque la requête récursive ne renvoie plus de ligne. Attention à veiller à ne pas créer de boucle infinie.

Voici un exemple qui permet d’afficher les nombre de 1 à 100 :

WITH RECURSIVE ma_table AS (				-- Déclaration du caractère récursif avec le mot clé RECURSIVE
	SELECT 						-- Requête de base non récursive
		1 AS colonne_valeur
	UNION ALL					-- Jointure des résultats d'une seconde requête
		SELECT					-- Seconde requête, récursive : réutilisant la requête CTE
			colonne_valeur+1
		FROM
			ma_table			-- Appel de la requête WITH dans elle-même
		WHERE					-- La clause permet de stopper la récursivitée lorsque la valeur 100 est atteinte
			colonne_valeur < 100
)
SELECT
	colonne_valeur
FROM
	ma_table
;

Voici les opérations effectuées par PostgreSQL pour cette requête :

  1. Exécution de la requête non récursive :
    SELECT 1
    Valeur retournée : 1
  2. Exécution de la requête récursive :
    SELECT 1+1
    Valeur retournée : 2
  3. Exécution de la requête récursive jusqu’à ce qu’aucune ligne ne soit renvoyée (jusqu’à colonne_valeur = 99)
    SELECT 99+1
    Valeur retournée : 100
  4. Exécution de la requête principale (qui appelle la requête CTE)
    Affichage de toutes les valeurs.

Le fenêtrage

Le fenêtrage consiste en un regroupement de données au sein d’une requête sur laquelle une opération est effectuée.

Voici le cas standard :

SELECT
	champ_1,
	fonction(champ_2) OVER (PARTITION BY champ_regroupement ORDER BY champ_tri)

Avec fonction() qui peut être une fonction d’agrégation (voir par ici) ou une fonction de fenêtrage (voir par là).

Le mieux pour visualiser de quoi je parle, est d’utiliser un exemple :

-- Table d'exemple
  id |   nom   |   mois  | salaire
-----+---------+---------+----------
  1  | arthur  | janvier | 1000
  2  | alfred  | janvier | 1500
  3  | arthur  | fevrier | 1100
  4  | alfred  | fevrier | 1500
  5  | arthur  | mars    | 1075
  6  | alfred  | mars    | 1500

-- Un moyen de l'utiliser sans réellement créer la table 
SELECT
	id,
	nom,
	mois,
	salaire
FROM
	(VALUES
		(1,'arthur','janvier',1000),
		(2,'alfred','janvier',1500),
		(3,'arthur','fevrier',1100),
		(4,'alfred','fevrier',1500),
		(5,'arthur','mars',1075),
		(6,'alfred','mars',1500)
	) AS t1 (id,nom,mois,salaire)

A partir de cette table, nous souhaitons visualiser plusieurs éléments :

  • Le salaire moyen total.
  • Le salaire moyen mensuel.
  • Le salaire maximum et minimum par employé.
  • Le classement des salaires pour chaque mois.

OVER ()

Le salaire moyen total

Ici, on pourrait utiliser la requête suivante :

SELECT
	avg(salaire)
FROM
	table_exemple
;

Nous obtenons donc le salaire moyen total qui est de 1279.167€.

Seulement, nous n’avons plus de visibilité sur les autres valeurs. Avec le fenêtrage, vous pouvez afficher ce résultat en face de chaque ligne de notre table. Pour cela on utilise la clause OVER () :

SELECT
	id,
	nom,
	mois,
	salaire,
	avg(salaire) OVER ()
FROM
	table_exemple

-- Ce qui nous donne 
id |   nom  |   mois  | salaire | avg      
---|--------|---------|---------|----------
 1 | arthur | janvier |  1000   | 1279.1667
 2 | alfred | janvier |  1500   | 1279.1667
 3 | arthur | fevrier |  1100   | 1279.1667
 4 | alfred | fevrier |  1500   | 1279.1667
 5 | arthur | mars    |  1075   | 1279.1667
 6 | alfred | mars    |  1500   | 1279.1667

Plutôt pratique ?!

OVER (PARTITION BY xxx)

Salaire moyen mensuel

Pour connaitre le salaire moyen mensuel, il faut calculer la moyenne pour chaque mois. Pour cela on utilise la clause OVER (PARTITION BY champ_de_regroupement) :

SELECT
	id,
	nom,
	mois,
	salaire,
	avg(salaire) OVER (PARTITION BY mois)
FROM
	table_exemple

-- Ce qui nous donne 
id |   nom  |   mois  | salaire | avg      
---|--------|---------|---------|----------
 1 | arthur | fevrier |  1000   | 1300
 2 | alfred | fevrier |  1500   | 1300
 3 | arthur | janvier |  1100   | 1250
 4 | alfred | janvier |  1500   | 1250
 5 | arthur | mars    |  1075   | 1287.5
 6 | alfred | mars    |  1500   | 1287.5

Pas mal hein ?! Vous pouvez trier les mois avec la clause ORDER BY que vous connaissez bien.

Salaire maximum par employé

Facile :

SELECT
	id,
	nom,
	mois,
	salaire,
	max(salaire) OVER (PARTITION BY nom),
	min(salaire) OVER (PARTITION BY nom)
FROM
	table_exemple

-- Ce qui nous donne 
id|nom   |mois   |salaire|max |min 
--|------|-------|-------|----|----
 2|alfred|janvier|   1500|1500|1500
 4|alfred|fevrier|   1500|1500|1500
 6|alfred|mars   |   1500|1500|1500
 1|arthur|janvier|   1000|1100|1000
 3|arthur|fevrier|   1100|1100|1000
 5|arthur|mars   |   1075|1100|1000

OVER (PARTITION BY xxx ORDER BY yyy)

Classement des salaires pour chaque mois
SELECT
	id,
	nom,
	mois,
	salaire,
	row_number() OVER (PARTITION BY mois ORDER BY salaire DESC)
FROM
	table_exemple

-- Ce qui nous donne 
id|nom   |mois   |salaire|row_number
--|------|-------|-------|----------
 4|alfred|fevrier|   1500|         1
 3|arthur|fevrier|   1100|         2
 2|alfred|janvier|   1500|         1
 1|arthur|janvier|   1000|         2
 6|alfred|mars   |   1500|         1
 5|arthur|mars   |   1075|         2

Pour chaque mois, nous avons le classement des employés par salaire.


Maintenant que vous savez gérer votre données, nous allons voir quelques fonctions qui peuvent être pratique.

Sommaire général

Voici le sommaire général du cours :


Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto

50%