Guide du débutant pour comprendre les fonctions de fenêtre SQL et leurs capacités

Tout au long du processus d'embauche, j'ai eu le plaisir de rencontrer de nombreux candidats talentueux pour mon équipe. Comme notre travail implique de manipuler des ensembles de données complexes, il était important pour moi de jauger la capacité de chaque candidat à trouver des solutions intelligentes. J'ai posé des questions sur leur expérience avec les fonctions de fenêtre dans SQL pour évaluer leur compétence. Alors que la plupart d'entre eux connaissaient ces fonctions, peu étaient capables de les utiliser efficacement.

Même si les fonctions de fenêtre existent depuis près de 20 ans, de nombreux développeurs SQL ont encore du mal à les comprendre. Il n'est pas rare, même pour les développeurs expérimentés, de simplement copier et coller le code de StackOverflow sans vraiment comprendre ce qu'il fait. Cet article est là pour vous aider ! J'expliquerai les fonctions de la fenêtre d'une manière facile à comprendre et je fournirai des exemples pour vous montrer comment elles fonctionnent dans le monde réel.

Avez-vous entendu parler des fonctions de fenêtre ? Ce sont des outils d'analyse impressionnants qui peuvent résoudre de nombreux problèmes. Par exemple, supposons que vous deviez calculer un ensemble de lignes partageant un attribut commun, comme un ID client. C'est là que les fonctions de fenêtre sont utiles ! Ils fonctionnent comme des fonctions d'agrégat mais vous permettent de conserver l'unicité de chaque ligne au lieu de les regrouper. De plus, les résultats des fonctions de fenêtre apparaissent comme un champ supplémentaire dans la sélection de sortie. Ceci est très utile lorsque vous créez des rapports analytiques, calculez des moyennes mobiles et des totaux cumulés, ou déterminez différents modèles d'attribution.

Bienvenue dans le monde des fonctions SQL et des fenêtres ! Si vous débutez, vous êtes au bon endroit. Cet article est adapté aux débutants, avec des explications claires et sans terminologie compliquée ni concepts avancés. Vous pourrez suivre facilement, même si vous êtes complètement nouveau sur le sujet.

Aperçu du contenu

• Types de fonctions utilisées avec les fonctions de fenêtre
  • Fonctions d'agrégation
  • Fonctions de classement
  • Fonctions de valeur
• Fonctions de fenêtre d'agrégation
• Points clés à retenir

Types de fonctions utilisées avec les fonctions de fenêtre

Il existe trois principaux types de fonctions auxquelles les fonctions de fenêtre peuvent être appliquées sur un ensemble de lignes (ce que l'on appelle une fenêtre) : il s'agit des fonctions d'agrégation, de classement et de valeur. Dans l'image ci-dessous, vous pouvez voir les noms des différentes fonctions qui entrent dans chaque catégorie.

Fonctions d'agrégation

Ceux-ci effectuent des opérations mathématiques sur un groupe de données, résultant en une seule valeur cumulée. Ils sont utilisés pour calculer divers agrégats, y compris la moyenne, le nombre total de lignes, les valeurs maximales ou minimales ou la somme totale dans chaque fenêtre ou partition.

• SUM : additionne toutes les valeurs de la colonne

• COUNT : calcule le nombre de valeurs dans la colonne, à l'exclusion des valeurs NULL

• AVG : trouve la valeur moyenne dans la colonne

• MAX : identifie la valeur la plus élevée de la colonne

• MIN : identifie la valeur la plus basse de la colonne

  
  

Fonctions de classement

Ceux-ci sont utilisés pour donner à chaque ligne d'une partition un rang ou un ordre. Cela se fait en évaluant des critères spécifiques, comme l'attribution de numéros séquentiels ou en basant le classement sur des valeurs spécifiques.

• ROW_NUMBER : attribue un numéro de rang séquentiel à chaque nouvel enregistrement dans une partition
• RANK : spécifie le rang de chaque ligne dans le jeu de résultats. Dans ce cas, si le système détecte des valeurs identiques, il leur attribuera le même rang et sautera la valeur suivante.
• DENSE_RANK : attribue un rang à chaque ligne dans une partition de l'ensemble de résultats. Contrairement à la fonction RANK, la fonction renvoie des classements pour des valeurs identiques sans ignorer les valeurs suivantes.
• NTILE : permet de déterminer à quel groupe appartient la ligne courante. Le nombre de groupes est indiqué entre parenthèses.
  

Fonctions de valeur

Celles-ci facilitent la comparaison des valeurs entre différentes lignes d'un groupe et vous permettent également de comparer les valeurs avec la première ou la dernière valeur de ce groupe. Cela signifie que vous pouvez facilement parcourir différentes lignes dans une fenêtre et extraire des valeurs au début ou à la fin de la fenêtre.

• LAG ou LEAD : accédez aux données de la ligne précédente ou suivante sans avoir à effectuer une opération d'auto-jointure. Ces fonctions sont particulièrement utiles lors de la résolution de problèmes nécessitant la comparaison d'une ligne avec une autre ligne dans le même jeu de résultats ou la même partition, comme le calcul des différences dans le temps.
• FIRST_VALUE ou LAST_VALUE : récupère la première ou la dernière valeur d'une fenêtre ou d'une partition définie. Ces fonctions sont particulièrement utiles lorsque vous souhaitez calculer des différences dans une période de temps spécifique.

Pour commencer avec les fonctions de la fenêtre, créons une table de "salaire" hypothétique et remplissons-la de données.

Création de tableau :

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

Remplir le tableau :

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

Vérifions si nous avons bien rempli la table 'salaire' :

 select * from salary 

La requête suivante affichera les noms et les salaires des employés de notre table :

 select employee_name, salary from salary 

Le calcul de la somme des salaires, du salaire moyen, du maximum, du minimum et du nombre de lignes sont des cas d'utilisation courants des fonctions d'agrégation :

Lorsqu'une fonction d'agrégation est appliquée, les salaires sont agrégés et affichés sur une seule ligne.

Mais que se passe-t-il si nous voulons afficher les noms et les salaires des employés de la table 'salaire', et dans la troisième colonne, la somme de tous les salaires ? Cette valeur doit être la même pour toutes les lignes.

C'est une excellente occasion d'utiliser une fonction de fenêtre !

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

Examinons de plus près la fonction window qui calcule la somme des salaires dans chaque ligne de sum(salary) over() .

L'expression over() définit une fenêtre ou un ensemble de lignes sur lesquelles la fonction opère. Dans notre exemple, la fenêtre est la table entière, ce qui signifie que la fonction s'appliquera à toutes les lignes.

L'expression over() ne fonctionne que lorsqu'elle est associée à des fonctions qui ont été demandées avant over() .

Par exemple, sum(salary) over() , où sum() est une fonction d'agrégation. Et toute l'expression sum(salary) over() est une fonction de fenêtre agrégée.

Comme je l'ai déjà dit, toutes les fonctions auxquelles les fonctions de fenêtre sont appliquées peuvent être divisées en trois groupes : les fonctions d'agrégation, de classement et de valeur.

Les fonctions d'agrégation sum() , count() , avg() , min() , max() ainsi que l'expression over() forment un groupe de fonctions de fenêtre d'agrégation.

Dans cet article, nous nous concentrerons sur ce type spécifique de fonctions de fenêtre.

Fonctions de fenêtre d'agrégation

Retour aux exemples !

Demandons les noms des employés ; leurs salaires ; la somme de tous les salaires; salaire moyen, maximum et minimum ; le nombre d'employés.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

Maintenant que les fonctions de fenêtre sont plus claires, explorons quelques cas où elles peuvent être utiles dans votre travail.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

Nous avons calculé le pourcentage du budget salarial total pour chaque salaire dans la quatrième colonne. Le salaire de Jessa représente près de 15 % de l'ensemble du budget des salaires.

Notez que nous avons également placé la formule qui calcule les pourcentages salary/sum(salary)over() dans le tri après order by . Une fonction de fenêtre peut être trouvée non seulement dans la sortie select , mais aussi dans order by .

Autre exemple : comparons les salaires avec le salaire moyen de l'entreprise.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

Comme nous pouvons le voir, le salaire d'Andrew est inférieur de 2110 à la moyenne et celui de Jessa est de 1690 supérieur à la moyenne.

Demandons trois colonnes : nom de l'employé, service et salaire. De plus, nous les trierons par département.

 select employee_name, department, salary from salary order by department 

Nous allons maintenant demander les trois mêmes colonnes, plus une colonne avec la somme des salaires de tous les employés. Vous savez déjà que cela peut être fait avec une fonction de fenêtre.

 select employee_name, department, salary, sum(salary)over() from salary order by department 

Mais que se passe-t-il si nous voulons demander non pas la somme de tous les salaires, mais la somme des salaires de chaque département, comme indiqué dans la dernière colonne :

Nous pouvons le faire en ajoutant le paramètre partition by à l'expression over() :

 select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department

Partition by nous permet d'appliquer la fonction de fenêtre non pas à toutes les lignes (toute la fenêtre), mais aux sections de colonne.

Cela ne ressemble-t-il pas à un simple regroupement ? Pour calculer la somme des salaires de chaque département, nous ferions un regroupement par départements (sections dans le jargon des fonctions de fenêtre) et calculerions le montant :

 select department, sum(salary) from salary group by department 

Essentiellement, la différence entre le regroupement et partition by est que group by renvoie une ligne par groupe, tandis que partition by , bien que les résultats de la fonction soient identiques aux résultats d'une fonction d'agrégation avec group by , fournit toutes les lignes avec la fonction d'agrégation basée sur un groupe.

Revenons aux fonctions de la fenêtre :

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

Grâce à la fonction fenêtre, notamment la partition by paramètre, on peut calculer la part du salaire de chaque employé à partir de la somme des salaires du service. Ou, par exemple, pour comparer les salaires avec le salaire moyen du département.

Points clés à retenir

Récapituler:

• Une fonction de fenêtre effectue un calcul sur un ensemble de lignes qui sont en quelque sorte liées à la ligne actuelle,

• Les principaux types de fonctions auxquelles les fonctions de fenêtre sont appliquées sont les fonctions d'agrégation, de classement et de valeur,

• Pour utiliser une fonction de fenêtre, vous devez appliquer la clause over() qui définit une fenêtre (un ensemble de lignes) dans un ensemble de résultats de requête. La fonction window calcule alors une valeur pour chaque ligne de la fenêtre,

• Pour spécifier la colonne pour laquelle vous souhaitez effectuer une agrégation, vous devez ajouter la clause partition by à la clause over() . Partition by est quelque peu similaire au regroupement mais renvoie toutes les lignes avec la fonction d'agrégation appliquée, au lieu d'une ligne par groupe.

  
  

C'est tout pour l'instant ! Dans les prochains articles, je vais explorer des concepts SQL plus avancés avec des exemples simples adaptés aux débutants, alors restez à l'écoute !