Science Des Données Et Bases De Données

Une introduction aux fonctions de la fenêtre SQL

La fonctionnalité très puissante que vous aimez détester (mais que vous devez savoir)

Les fonctions de fenêtre SQL fournissent des fonctionnalités extrêmement puissantes et utiles. Mais pour beaucoup, comme ils sont si étrangers au SQL standard, ils sont difficiles à apprendre et à comprendre, ont une syntaxe étrange - et sont très souvent évités.

Les fonctions de fenêtre peuvent être simplement expliquées comme des fonctions de calcul similaires à l'agrégation, mais où l'agrégation normale via GROUP BY La clause combine puis masque les lignes individuelles en cours d'agrégation, les fonctions de fenêtre ont accès à des lignes individuelles et peuvent ajouter certains des attributs de ces lignes dans le jeu de résultats.

Diagramme comparant les fonctions d

Dans ce didacticiel sur les fonctions de fenêtre SQL, je vais vous familiariser avec les fonctions de fenêtre, vous expliquer les avantages et le moment où vous les utiliserez, et vous donner de vrais exemples pour vous aider avec les concepts.

Une fenêtre sur vos données

L'une des fonctionnalités les plus utilisées et les plus importantes de SQL est la possibilité d'agréger ou de regrouper des lignes de données de manière particulière. Dans certains cas, cependant, le regroupement peut devenir extrêmement complexe, en fonction de ce qui est requis.

Avez-vous déjà voulu parcourir les résultats de votre requête pour obtenir un classement, une liste des x premiers ou similaire? Avez-vous eu des projets d'analyse dans lesquels vous vouliez préparer vos données parfaitement pour un outil de visualisation, mais avez trouvé cela presque impossible ou si complexe que cela n'en valait pas la peine?

comment savoir quel type de llc j'ai

Les fonctions de fenêtre peuvent faciliter les choses. Après avoir obtenu le résultat de votre requête, c'est-à-dire après le WHERE clause et toute agrégation standard, les fonctions de fenêtre agiront sur les lignes restantes (le fenêtre de données) et obtenez ce que vous voulez.

Certaines des fonctions de fenêtre que nous allons examiner incluent:

OVER
COUNT()
SUM()
ROW_NUMBER()
RANK()
DENSE_RANK()
LEAD()
LAG()

Trop facile

Le OVER clause est ce qui spécifie une fonction de fenêtre et doit toujours être incluse dans l'instruction. La valeur par défaut dans un OVER clause est l'ensemble de lignes complet. À titre d'exemple, examinons un tableau d'employés dans une base de données d'entreprise et montrons le nombre total d'employés sur chaque ligne, ainsi que les informations de chaque employé, y compris quand ils ont commencé avec l'entreprise.

SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Nombre d'employés	Prénom	nom de famille	le rendez vous a commencé
3	John	Forgeron	01/01/2019 00: 00: 00.000
3	Sortie	Jones	15/02/2019 00: 00: 00.000
3	Sam	Gordon	18/02/2019 00: 00: 00.000

Ce qui précède, comme beaucoup de fonctions de fenêtre, peut également être écrit d’une manière non fenêtrée plus familière - ce qui, dans cet exemple simple, n’est pas si mal:

SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Mais maintenant, disons que nous souhaitons afficher le nombre d'employés qui ont commencé le même mois que l'employé de la ligne. Nous devrons réduire ou limiter le décompte à ce mois-là pour chaque ligne. Comment cela se fait-il? Nous utilisons la fenêtre PARTITION clause, comme ceci:

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

NumPerMonth	Le mois	Prénom	Nom de famille
un	Janvier 2019	John	Forgeron
2	Février 2019	Sortie	Jones
2	Février 2019	Sam	Gordon

Les partitions vous permettent de filtrer la fenêtre en sections selon une ou plusieurs valeurs. Chaque section est souvent appelée la fenêtre Cadre .

Pour aller plus loin, disons que nous voulions non seulement savoir combien d'employés ont commencé le même mois, mais nous voulons montrer dans quel ordre ils ont commencé ce mois-là. Pour cela, nous pouvons utiliser le familier ORDER BY clause. Cependant, dans une fonction de fenêtre, ORDER BY agit un peu différemment de ce qu'il fait à la fin d'une requête.

comment fixer le prix d'une introduction en bourse

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

NumThisMonth	Le mois	Prénom	nom de famille
un	Janvier 2019	John	Forgeron
un	Février 2019	Sortie	Jones
2	Février 2019	Sam	Gordon

Dans ce cas, ORDER BY modifie la fenêtre pour qu'elle passe du début de la partition (dans ce cas, le mois et l'année de début de l'employé) à la ligne courante. Ainsi, le décompte redémarre à chaque partition.

Classez-le

Les fonctions de fenêtre peuvent être très utiles à des fins de classement. Auparavant, nous avons vu que l'utilisation de COUNT la fonction d'agrégation nous a permis de voir dans quel ordre les employés ont rejoint l'entreprise. Nous aurions également pu utiliser des fonctions de classement de fenêtre, telles que ROW_NUMBER(), RANK() et DENSE_RANK().

Les différences sont visibles après l'ajout d'un nouvel employé le mois suivant et la suppression de la partition:

SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Classement de départ	EmployéRank	DenseRank	Le mois	Prénom	nom de famille	le rendez vous a commencé
un	un	un	Janvier 2019	John	Forgeron	01/01/2019
2	2	2	Février 2019	Sortie	Jones	15/02/2019
3	2	2	Février 2019	Sam	Gordon	18/02/2019
4	4	3	Mars 2019	Julie	Sanchez	19/03/2019

Vous pouvez voir les différences. ROW_NUMBER() donne un décompte séquentiel dans une partition donnée (mais en l'absence de partition, il parcourt toutes les lignes). RANK() donne le rang de chaque ligne en fonction de ORDER BY clause. Il montre les égalités, puis ignore le classement suivant. DENSE_RANK affiche également des égalités, mais continue avec la valeur consécutive suivante comme s'il n'y avait pas d'égalité.

Les autres fonctions de classement comprennent:

CUME_DIST - Calcule le rang relatif de la ligne actuelle dans une partition
NTILE - Divise les lignes pour chaque partition de fenêtre aussi également que possible
PERCENT_RANK - Rang en pourcentage de la ligne actuelle

Notez également dans cet exemple que vous pouvez avoir plusieurs fonctions de fenêtre dans une seule requête - et la partition et l'ordre peuvent être différents dans chacune!

Laquelle des règles suivantes est correcte pour l'analyse de la budgétisation des immobilisations ?

Lignes et plages et cadres, oh mon

Pour définir ou limiter davantage le cadre de votre fenêtre dans le OVER() clause, vous pouvez utiliser ROWS et RANGE. Avec le ROWS , vous pouvez spécifier les lignes incluses dans votre partition comme celles qui précèdent ou après la ligne actuelle.

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Dans cet exemple, le cadre de la fenêtre passe de la première ligne à la ligne actuelle moins 1 et la taille de la fenêtre continue d'augmenter pour chaque ligne.

La gamme fonctionne un peu différemment et nous pouvons obtenir un résultat différent.

que pouvez-vous faire avec ruby sur rails

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

La plage comprendra les lignes du cadre de la fenêtre qui ont le même ORDER BY valeurs comme ligne actuelle. Ainsi, il est possible que vous puissiez obtenir des doublons avec RANGE si le ORDER BY n'est pas unique.

Certains décrivent ROWS comme opérateur physique tandis que RANGE est un opérateur logique. Dans tous les cas, les valeurs par défaut pour ROWS et RANGE sont toujours UNBOUNDED PRECEDING AND CURRENT ROW.

Quoi d'autre?

La plupart des fonctions d'agrégation standard fonctionnent avec les fonctions de fenêtre. Nous avons vu COUNT dans les exemples déjà. D'autres incluent SUM, AVG, MIN, MAX, etc.

Avec les fonctions de fenêtre, vous pouvez également accéder aux enregistrements précédents et aux enregistrements suivants en utilisant LAG et LEAD, et FIRST_VALUE et LAST_VALUE. Par exemple, imaginons que vous souhaitiez afficher sur chaque ligne un chiffre de vente pour le mois en cours et la différence entre le chiffre de vente du mois dernier. Vous pourriez faire quelque chose comme ceci:

SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Fondamentalement, les fonctions de fenêtre SQL sont très puissantes

Bien qu'il s'agisse d'une introduction rapide aux fonctions des fenêtres SQL, j'espère que cela suscitera votre intérêt de voir tout ce qu'elles peuvent faire. Nous avons appris que les fonctions de fenêtre effectuent des calculs similaires à ceux des fonctions d'agrégation, mais avec l'avantage supplémentaire d'avoir accès aux données dans les lignes individuelles, ce qui les rend assez puissantes. Ils contiennent toujours le OVER et peut contenir PARTITION BY, ORDER BY, et une multitude d'agrégations (SUM, COUNT, etc.) et d'autres fonctions positionnelles (LEAD, LAG). Nous avons également appris sur les cadres de fenêtre et comment ils encapsulent des sections de données.

Notez que différentes versions de SQL peuvent implémenter les fonctions de fenêtre différemment, et certaines peuvent ne pas avoir implémenté toutes les fonctions ou clauses de fenêtre. Assurez-vous de consulter la documentation de la plate-forme que vous utilisez.

Si, en tant que Développeur SQL , vous souhaitez optimiser la vérification des performances de votre base de données SQL Réglage des performances de la base de données SQL pour les développeurs .

Bon fenêtrage!

Pour plus d'informations sur des implémentations spécifiques, consultez:

PostgreSQL Fonctions de la fenêtre documentation pour une implémentation PostgreSQL.
SELECT - Clause OVER (Transact-SQL) docs par Microsoft.
Fonctions de fenêtre dans SQL Server pour un excellent aperçu des implémentations de SQL Server et de ses partie 2 .

Comprendre les bases

Que fait une fonction de fenêtre dans SQL?

Une fonction de fenêtre effectue des calculs sur un ensemble de lignes et utilise les informations dans les lignes individuelles si nécessaire.

En quoi une fonction d'agrégation de fenêtre est-elle différente d'une fonction d'agrégation «grouper par»?

Avec «group by», vous ne pouvez agréger que les colonnes ne figurant pas dans la clause «group by». Les fonctions de fenêtre vous permettent de rassembler à la fois des valeurs agrégées et non agrégées.