Dans le article précédent , nous avons parlé des fonctions de fenêtre, qui constituent un excellent outil pour résoudre divers problèmes analytiques ; ils peuvent vous donner accès à des fonctionnalités telles que l'analyse avancée et la manipulation de données sans avoir besoin d'écrire des requêtes complexes. Avant de continuer la lecture, je vous recommande de commencer par la première partie, car elle vous aidera à comprendre l'idée de base du fonctionnement des fonctions de fenêtre dans SQL. Alors maintenant que vous connaissez les bases, explorons des concepts SQL plus avancés. Cela peut paraître un peu compliqué à première vue, mais je vais fournir des exemples simples adaptés aux débutants pour tous les cas possibles, il sera donc facile de comprendre ce que je veux dire. Aperçu du contenu Cumulative Sum Ranking Window Functions Use cases Offset window functions Key Takeaways Somme cumulée Nous avons déjà considéré des exemples où l'expression n'avait aucun paramètre ou avait une partition par paramètre. Maintenant, nous allons examiner le deuxième paramètre possible pour l'expression : over() over() trier par. Demandons le numéro d'identification de l'employé, le nom de l'employé, le service, le salaire et la somme de tous les salaires : select employee_id, employee_name, department, salary, sum(salary) over() from salary Maintenant, nous allons ajouter le paramètre à l'expression : order by over() select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary Je suppose que nous devons examiner de plus près ce qui s'est passé ici : Tout d’abord, est désormais trié par ordre décroissant. Employee_id Dans la colonne résultant de l'application de la fonction fenêtre, on trouve désormais une somme cumulée. Je pense que vous connaissez la somme cumulée. Son essence est simple : la somme cumulée ou total cumulé signifie « combien jusqu’à présent ». La définition de la somme cumulée est la somme d’une séquence donnée qui augmente ou s’agrandit avec plus d’ajouts. Voici ce que nous avons dans notre exemple : pour l'employé avec la valeur la plus élevée, le salaire est de 3700, et la somme cumulée est également de 3700. Le deuxième employé a un salaire de 1500, et la somme cumulée est de 5200. Le troisième employé a un salaire de 1500, et la somme cumulée est de 5200. , avec un salaire de 2900, a une somme cumulée de 8100, et ainsi de suite. Employee_id Le paramètre order by dans l’expression spécifie l’ordre. Dans le cas des fonctions de fenêtre d'agrégation, il détermine l'ordre du total cumulé. over() Dans l'expression , les attributs partition by et order by peuvent être spécifiés. over() select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary Dans ce cas, le total cumulé sera calculé par tranches. Attention ! Si les deux attributs sont spécifiés dans l'expression , la partition by vient toujours en premier, suivie de l' . Par exemple : . over() ordre by over(partition by Department Order by Employee_id) Après avoir discuté de la somme cumulée, nous devons dire que c'est peut-être le seul type de total cumulé fréquemment utilisé. La moyenne cumulée et le décompte cumulé sont, au contraire, rarement utilisés. Néanmoins, nous allons donner un exemple de calcul de la moyenne cumulative — il nous indique la moyenne d'une série de valeurs jusqu'à un certain point : select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary Fonctions de la fenêtre de classement Nous utilisons les fonctions de fenêtre de classement pour déterminer la position d'une valeur dans un ensemble de valeurs. L'expression dans la clause dicte la base du classement, chaque valeur étant affectée d'un rang dans sa partition désignée. Lorsque les lignes partagent des valeurs identiques pour les critères de classement, le même classement leur est attribué. ORDER BY OVER Pour voir comment fonctionnent les fonctions de la fenêtre de classement, demandons les colonnes suivantes du tableau des salaires : ID d'employé, nom de l'employé, service et salaire : select employee_id, employee_name, department, salary from salary Maintenant, nous ajoutons une colonne supplémentaire avec la fonction window : row_number() over() select employee_id, employee_name, department, salary, row_number() over() from salary La fonction de fenêtre a attribué des numéros aux lignes sans modifier leur ordre. Jusqu’à présent, cela ne nous apporte pas beaucoup de valeur, n’est-ce pas ? row_number() over() Mais que se passe-t-il si l’on veut numéroter les lignes par ordre décroissant de salaire ? Pour y parvenir, nous devons spécifier l’ordre de tri, c’est-à-dire passer le paramètre order by à l’expression over(). select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary Nous ajouterons les fonctions de classement restantes à la requête à des fins de comparaison : select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary Passons en revue chaque fonction de la fenêtre de classement : La fonction de fenêtre classe les lignes par ordre décroissant de salaire et attribue des numéros de ligne. Notez qu'Annie et Tony ont le même salaire, mais ils se voient attribuer des numéros différents. row_number() over(order by salaire desc) La fonction de fenêtre ) attribue des rangs par ordre décroissant de salaire. Il attribue le même rang pour des valeurs identiques, mais la valeur suivante obtient un nouveau numéro de ligne. Rank() over(order by salaire desc La fonction de fenêtre attribue des rangs par ordre décroissant de salaire. Il attribue le même rang pour des valeurs identiques. dense_rank() over(order by salaire desc) La fonction de fenêtre est le rang relatif (en pourcentage) de la ligne actuelle, calculé par la formule : (rang - 1) / (nombre total de lignes dans la partition - 1). percent_rank() over(order by salaire desc) La fonction de fenêtre divise le nombre de lignes en 5 parties égales et attribue un numéro à chaque partie. Le nombre de parties est spécifié dans la fonction . ntile(5) over(order by salaire desc) ntile(5) Attention ! Contrairement aux fonctions d'agrégation, par exemple sum(salary), les fonctions de classement, par exemple row_number(), ne prennent pas de colonne à l'intérieur. Cependant, dans la fonction ntile(5), le nombre de parties est spécifié. Cas d'utilisation Il est temps d'explorer des tâches pratiques à l'aide des fonctions de la fenêtre de classement. Nous afficherons le numéro d'identification de l'employé, son nom, son service et son salaire, et attribuerons des numéros de ligne par ordre décroissant de salaire. select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary Parfois, vous devrez peut-être numéroter les lignes par ordre décroissant de salaire au sein des départements (sections). Cela peut être fait en ajoutant l'attribut partition by à l'expression over() : select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary Rendons la tâche plus difficile. Nous devons retenir un seul employé par département ayant le salaire le plus élevé. Ceci peut être réalisé en utilisant une sous-requête : select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 Et encore un exemple, si nous devons afficher trois employés dans chaque ville avec le salaire le plus élevé, nous ferons ce qui suit : select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 Ces types de tâches sont très courants, en particulier lorsque vous devez afficher un nombre spécifique de lignes dans des sections (groupes) par ordre croissant ou décroissant d'un attribut. En pratique, j'utilise systématiquement la fonction de fenêtre et, bien sûr, également. row_number() over() dense_rank() over() Fonctions de fenêtre de décalage Ces fonctions vous permettent de renvoyer les données d'autres lignes en fonction de leur distance par rapport à la ligne actuelle. Pour le rendre plus visuel, passons en revue les fonctions first_value(), last_value() et nth_value(). select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department Attention ! Dans les trois fonctions de fenêtre, c'est essentiel pour spécifier la partition par paramètre. Le paramètre order by n'est pas obligatoire, mais en le spécifiant, vous pouvez modifier l'ordre des lignes au sein de la partition. Par exemple, dans la requête ci-dessous, nous avons trié par salaire au sein de la section (département), et désormais first_value est le salaire le plus élevé de la section. select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department Les fonctions et affichent la première et la dernière valeur de salaire dans la section (département). first_value(salary) over(partition by Department) last_value(salary) over(Partition by Department) À son tour, la fonction affiche la deuxième valeur du salaire au sein de la section (département). Veuillez noter que dans , un argument supplémentaire est spécifié : le numéro de ligne dans la section. Dans notre cas, le numéro de ligne est 2, la fonction affiche donc la deuxième valeur du salaire. nth_value(salary, 2) over(partition by Department) nth_value() Outre ce qui précède, il existe également des fonctions et . La fonction est utilisée pour obtenir la valeur de la ligne qui précède la ligne actuelle. La fonction lead() est utilisée pour obtenir la valeur d’une ligne qui succède à la ligne actuelle. lag() lead() lag() select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 Comme vous pouvez le voir, la fonction décale les salaires vers le bas d'une ligne, et la fonction déplace les salaires vers le haut d'une ligne. Bien que ces fonctions soient assez similaires, je trouve plus pratique d'utiliser . lag (salary) over (order by salaire) lead(salary) over (order by salaire) lag() Attention ! Pour ces fonctions, il est obligatoire de préciser le paramètre order by dans l'expression over(). Vous pouvez également spécifier le partitionnement en utilisant partition by, mais ce n'est pas obligatoire. select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department Ici, remplit la même fonction qu'auparavant, mais désormais spécifiquement au sein des sections (départements). lag() Points clés à retenir Et enfin, un bref aperçu de ce que nous avons couvert aujourd’hui : La somme cumulée représente le total cumulé d’une séquence, s’accumulant à chaque ajout ultérieur. Les fonctions de la fenêtre de classement sont utilisées pour déterminer la position d'une valeur dans un ensemble de valeurs, l'expression spécifiant la base du classement. d'ordre par Les fonctions de fenêtre de décalage incluent , et , permettant la récupération de données d'autres lignes en fonction de leur distance par rapport à la ligne actuelle. N'oubliez pas les fonctions et . La fonction peut être pratique pour obtenir la valeur de la ligne qui précède la ligne actuelle, tandis que la fonction est utilisée pour obtenir la valeur d'une ligne qui succède à la ligne actuelle. first_value() last_value() nth_value() lag() lead() lag() lead() Merci de m'avoir rejoint. J'espère que cet article vous aidera à mieux comprendre les capacités des fonctions de fenêtre dans SQL et vous rendra plus confiant et plus rapide dans les tâches de routine.
