Exemple de fonction fenêtre SQL avec explications

Vous êtes intéressé par le fonctionnement des fonctions de fenêtre ? Faites défiler la page vers le bas pour voir notre exemple de fonction de fenêtre SQL avec des explications définitives !

Les fonctions de fenêtre SQL sont un peu différentes ; elles calculent leur résultat sur la base d'un ensemble de lignes plutôt que sur une seule ligne. En fait, la "fenêtre" dans "fonction fenêtre" fait référence à cet ensemble de lignes.

Les fonctions de fenêtre sont similaires aux fonctions d'agrégation, mais il existe une différence importante. Lorsque nous utilisons des fonctions d'agrégation avec la clause GROUP BY nous "perdons" les lignes individuelles. Nous ne pouvons pas mélanger les attributs d'une ligne individuelle avec les résultats d'une fonction d'agrégation ; la fonction est exécutée sur les lignes en tant que groupe entier. Ce n'est pas le cas lorsque nous utilisons les fonctions de la fenêtre SQL: nous pouvons générer un ensemble de résultats avec certains attributs d'une ligne individuelle ainsi que les résultats de la fonction fenêtre. Il est bon que les nouveaux développeurs SQL gardent cela à l'esprit. Examinons donc un exemple simple de fonction fenêtre SQL en action.

Vous voulez apprendre les fonctions de fenêtre SQL ? Consultez notre Fonctions de fenêtrage cours interactif !

Exemple de fonction fenêtre SQL

Les fonctions de fenêtre peuvent être appelées dans l'instruction SELECT ou dans la clause ORDER BY ou dans la clause Toutefois, elles ne peuvent jamais être appelées dans la clause WHERE clause. Vous remarquerez que tous les exemples de cet article appellent la fonction de fenêtre dans la liste de colonnes SELECT dans la liste de colonnes.

Passons au premier exemple de fonction fenêtre SQL. Nous allons utiliser la fonction "Employee" table :

Nous allons commencer par RANKqui est l'un des exemples les plus simples de fonction fenêtre SQL. Elle retourne la position de n'importe quelle ligne à l'intérieur de la partition. Utilisons-la pour classer les salaires au sein des départements :

SELECT	
		RANK() OVER (PARTITION BY department ORDER BY salary DESC) 
			AS dept_ranking,
		department,
		employee_id, 
		full_name, 
		salary
FROM employee;

Nous pouvons voir les résultats ci-dessous :

Et si nous voulions avoir le même rapport mais avec tous les employés les mieux classés en premier, puis tous les employés de second rang, et ainsi de suite ? Nous vous donnons ce défi à relever par vous-même. Partagez vos idées dans la section des commentaires !

Poursuivons avec notre exemple de fonction fenêtre SQL, et trouvons où se situe le salaire de chaque employé par rapport au salaire le plus élevé de son département. Cela nécessite une expression mathématique, comme

employee_salary / max_salary_in_depth

La requête suivante affichera tous les employés classés selon la mesure ci-dessus ; les employés dont le salaire est le plus bas (par rapport au salaire le plus élevé de leur département) seront listés en premier :

SELECT
  employee_id, 
  full_name, 
  department,
  salary,
  salary / MAX(salary) OVER (PARTITION BY department ORDER BY salary DESC) 
    AS salary_metric
FROM employee
ORDER BY 5;

Vous souhaitez apprendre les fonctions de fenêtre SQL ? Essayez notre cours interactif Fonctions de fenêtrage cours interactif !

Un autre exemple de fonction de fenêtre SQL

Passons d'une base de données de salaires d'employés à la base de données d'horaires de trains suivante :

Supposons que nous voulions ajouter une nouvelle colonne appelée "temps jusqu'à la prochaine station". Pour obtenir cette valeur, nous soustrayons les temps de station pour les paires de stations contiguës. Nous pouvons calculer cette valeur sans utiliser une fonction de fenêtre SQL, mais cela peut être très compliqué. Il est plus simple de le faire en utilisant la fonction LEAD pour le faire. Cette fonction compare les valeurs d'une ligne avec la ligne suivante pour obtenir un résultat. Dans ce cas, elle compare les valeurs de la rubrique "temps d'une station avec la station qui la suit immédiatement.

Voici donc un autre exemple de fonction de fenêtre SQL, cette fois pour l'horaire des trains :

SELECT 
	train_id, 
	station,
	time as "station_time",
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
		AS time_to_next_station
FROM train_schedule;

Notez que nous calculons la fonction fenêtre LEAD en utilisant une expression impliquant une colonne individuelle et une fonction fenêtre; ceci n'est pas possible avec les fonctions agrégées.

Voici les résultats de cette requête :

Dans l'exemple suivant, nous allons ajouter une nouvelle colonne qui indique le temps qui s'est écoulé entre le premier arrêt du train et la station actuelle. Nous l'appellerons "temps de trajet écoulé". La fonction de fenêtre MIN obtiendra l'heure de départ du voyage et nous soustrairons l'heure de la gare actuelle. Voici l'exemple suivant de fonction fenêtre SQL

SELECT 	
	train_id, 
	station,
	time as "station_time",
	time - min(time) OVER (PARTITION BY train_id ORDER BY time) 	
								AS elapsed_travel_time,
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
								AS time_to_next_station
FROM train_schedule;

Remarquez la nouvelle colonne dans le tableau des résultats :

Découvrez d'autres exemples de fonctions de fenêtre SQL !

Lesfonctions de fenêtre ne sont pas des aspects très connus de SQL, mais leur puissance et leur flexibilité les rendent très importantes. Il existe des clauses (par exemple PARTITION BY et cadre de fenêtre) et des sujets que nous n'avons pas abordés dans cet article, mais que cela ne vous arrête pas ! Vous pouvez en savoir plus et trouver d'autres exemples de fonctions de fenêtre SQL à l'aide de notre blog et le Fonctions de fenêtrage cours dans LearnSQL.fr. Commencez dès aujourd'hui !