Explication détaillée des fonctions de fenêtre SQL : utilisation des fonctions de fenêtre de classement

Cet article vous apporte des connaissances pertinentes sur SQL server, qui présente principalement les contraintes de clé primaire de SQL Server (PRIMARY KEY). La clé primaire est une colonne ou un groupe de colonnes qui identifie de manière unique chaque ligne du tableau. le thème. Jetons un coup d’œil aux détails ci-dessous, j’espère que cela sera utile à tout le monde.

Étude recommandée : "Tutoriel SQL"

Pour les bases des fonctions de fenêtre, veuillez consulter l'article Fonctions de fenêtre SQL

Les fonctions de fenêtre de valeur peuvent être utilisées pour renvoyer des lignes de données à des positions spécifiées dans la fenêtre. Les fonctions courantes de la fenêtre de valeurs sont les suivantes :

La fonction LAG peut renvoyer la Nième ligne de données avant la ligne actuelle dans la fenêtre. La fonction LEAD peut renvoyer la Nième ligne de données après la ligne actuelle dans la fenêtre. La fonction FIRST_VALUE peut renvoyer la première ligne de données dans la fenêtre. La fonction LAST_VALUE peut renvoyer la dernière ligne de données de la fenêtre. La fonction NTH_VALUE peut renvoyer la Nième ligne de données dans la fenêtre.

Parmi elles, la fonction LAG et la fonction LEAD ne prennent pas en charge les tailles de fenêtre dynamiques. Elles utilisent la partition entière comme fenêtre d'analyse.

Analyse de cas

Exemple de table utilisée dans le cas

La requête suivante utilisera une table. La table sales_monthly stocke les informations sur les ventes du produit, product représente le nom du produit, ym représente l'année et le mois, et montant représente le montant des ventes ( yuans) .

Voici quelques données du tableau :

Le script d'initialisation de ce tableau peut être obtenu en bas de l'article.

1. Analyse périodique

La croissance mensuelle fait référence à la croissance des données de la période en cours par rapport aux données de la période précédente. Par exemple, l'augmentation des ventes du produit en juin 2019 par rapport aux ventes. en mai 2019.

L'instruction suivante compte le taux de croissance mensuel de divers produits :

SELECT s.product AS "产品", s.ym AS "年月", s.amount AS "销售额",
 ( 
    (s.amount - LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym))/
    LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS "环比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

Parmi eux, LAG (montant, 1) signifie obtenir le volume des ventes de la période précédente, l'option PARTITION BY signifie partitionner par produit , et l'option ORDER BY signifie partitionner par produit, trier par mois.

Le montant des ventes du mois en cours moins le montant des ventes de la période précédente, puis divisé par le montant des ventes de la période précédente, correspond au taux de croissance d'un mois à l'autre.

La requête renvoie les résultats suivants :

Janvier 2018 est la première période, son taux de croissance mensuel est donc vide.

Le taux de croissance mensuelle d'« Orange » en février 2018 était d'environ 0,2856 % ((10183-10154)/10154×100), et ainsi de suite.

2. Analyse d'une année sur l'autre

La croissance d'une année sur l'autre fait référence à la croissance des données de la période actuelle par rapport à l'année précédente ou à la même période de l'histoire. Par exemple, les ventes d'un produit en juin 2019. augmenté par rapport aux ventes de juin 2018. partie.

Le relevé suivant compte le taux de croissance d'une année sur l'autre de divers produits chaque mois :

SELECT s.product AS "产品", s.ym AS "年月", s.amount AS "销售额",
 ( 
    (s.amount - LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym))/
    LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS "同比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

Parmi eux, LAG (montant, 12) représente le volume des ventes de la 12ème période avant le mois en cours, qui est le volume des ventes du même mois l'année dernière.

L'option PARTITION BY signifie un partitionnement par produit, l'option ORDER BY signifie un tri par mois.

Le montant des ventes du mois en cours moins les ventes de la même période de l'année dernière, puis divisé par les ventes de la même période de l'année dernière, correspond au taux de croissance d'une année sur l'autre.

Les résultats renvoyés par cette requête sont les suivants :

Il n'y a pas de taux de croissance annuel correspondant pour les 12 périodes de données en 2018. Le taux de croissance annuel de "Orange" en Janvier 2019 était d'environ 9,3067 % ((11099-10154)/ 10154×100), et ainsi de suite.

Conseils : La fonction LEAD est similaire à la fonction LAG, mais son résultat renvoyé est la Nième ligne de données après la ligne actuelle.

3. Taux de croissance composé

Le taux de croissance composé est le résultat de la division des données de la Nième période par les données de base de la première période, puis de leur augmentation à la puissance N-1 et de la soustraction de 1.

Supposons que les ventes de produits en 2018 soient de 10 000, les ventes de produits en 2019 soient de 12 500 et les ventes de produits en 2020 soient de 15 000. Ensuite, le taux de croissance composé de ces deux années est calculé comme suit :

Le taux de croissance composé calculé sur une base annuelle est appelé Taux de croissance composé annuel moyen, et le taux de croissance composé calculé sur une base mensuelle est appelé Taux de croissance composé mensuel moyen.

La requête suivante compte le taux de croissance composé des ventes mensuelles moyennes de différents produits depuis janvier 2018 :

WITH s (product,ym,amount,first_amount,num) AS (
  SELECT m.product, m.ym, m.amount,
  FIRST_VALUE(m.amount) OVER (PARTITION BY m.product ORDER BY m.ym),
  ROW_NUMBER() OVER (PARTITION BY m.product ORDER BY m.ym)
  FROM sales_monthly m
)
 
SELECT product AS "产品", ym AS "年月",amount AS "销售额",
       (POWER( amount/first_amount, 1.0/NULLIF(num-1,0)) -1)*100 AS "月均复合增长率(%)"
FROM s
ORDER BY product, ym

Définit d'abord une expression de tableau générale, dans laquelle FIRST_VALUE(amount) renvoie les ventes de la première période (201801). Eh bien, le ROW_NUMBER La fonction renvoie le numéro de chaque période.

La fonction POWER dans la requête principale est utilisée pour effectuer l'opération racine carrée, la fonction NULLIF est utilisée pour gérer l'erreur de division par zéro de la première période de données et la constante 1.0 est utilisée pour éviter la perte de précision causée par la division entière.

Les résultats renvoyés par cette requête sont les suivants :

Janvier 2018 est la première période, donc le taux de croissance composé des ventes mensuelles moyennes de son produit est vide.

“桔子”2018年2月的月均销售额复合增长率等于它的环比增长率，2018年3月的月均销售额复合增长率等于0.4471%，依此类推。 

4.不同产品最高和最低销售额

以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份：

  SELECT product AS "产品", ym AS "年月",amount AS "销售额",
  
         FIRST_VALUE(m.ym) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "最高销售额月份",
         
         LAST_VALUE(m.ym) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "最低销售额月份",
         
         NTH_VALUE(m.ym,3) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "第三高销售额月份"
 
  FROM sales_monthly m
  ORDER BY product, ym;

三个窗口函数的OVER子句相同，PARTITION BY选项表示按照产品进行分区，ORDER BY选项表示按照销售额从高到低排序。

以上三个函数的默认窗口都是从分区的第一行到当前行，因此我们将窗口扩展到了整个分区。

该查询返回的结果如下： 

“桔子”的最高销售额出现在2019年6月，最低销售额出现在2018年1月，第三高销售额出现在2019年4月。

示例表和脚本

-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);

推荐学习：《SQL教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!