Detaillierte Erläuterung der SQL-Fensterfunktionen: Verwendung von Ranking-Fensterfunktionen

Dieser Artikel vermittelt Ihnen relevantes Wissen über SQL-Server, der hauptsächlich SQL Server-Primärschlüsseleinschränkungen (PRIMÄRSCHLÜSSEL) vorstellt. Der Primärschlüssel ist eine Spalte oder eine Gruppe von Spalten, die jede Zeile in der Tabelle eindeutig identifiziert Werfen wir einen Blick auf die Details unten. Ich hoffe, es wird für alle hilfreich sein.

Empfohlene Studie: „SQL-Tutorial“

Grundlagen zu Fensterfunktionen finden Sie im Artikel SQL-Fensterfunktionen

Wertfensterfunktionen können verwendet werden, um Datenzeilen an angegebenen Positionen innerhalb des Fensters zurückzugeben. Gängige Wertefensterfunktionen sind wie folgt:

LAG-Funktion kann die N-te Datenzeile vor der aktuellen Zeile im Fenster zurückgeben. Die LEAD-Funktion kann die N-te Datenzeile nach der aktuellen Zeile im Fenster zurückgeben. Die Funktion FIRST_VALUE kann die erste Datenzeile im Fenster zurückgeben. Die Funktion LAST_VALUE kann die letzte Datenzeile im Fenster zurückgeben. Die NTH_VALUE-Funktion kann die N-te Datenzeile im Fenster zurückgeben.

Unter diesen unterstützen die LAG-Funktion und die LEAD-Funktion keine dynamischen Fenstergrößen. Sie verwenden die gesamte Partition als Analysefenster.

Fallanalyse

Beispieltabelle für den Fall

Die folgende Abfrage verwendet eine Tabelle. In der Tabelle „sales_monthly“ werden Produktverkaufsinformationen gespeichert, „product“ stellt den Produktnamen dar, „ym“ stellt das Jahr und den Monat dar und „amount“ stellt den Verkaufsbetrag dar ( Yuan).

Das Folgende sind einige Daten in der Tabelle:

Das Initialisierungsskript dieser Tabelle finden Sie am Ende des Artikels.

1. Periodenvergleichsanalyse

Das Wachstum der aktuellen Periode im Vergleich zu den Daten der vorherigen Periode bezieht sich beispielsweise auf die Steigerung der Produktverkäufe im Juni 2019 im Vergleich zu den Verkäufen im Mai 2019.

Die folgende Aussage zählt die monatliche Wachstumsrate verschiedener Produkte:

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

Unter ihnen bedeutet LAG (Betrag, 1), das Verkaufsvolumen der vorherigen Periode zu erhalten, die Option PARTITION NACH bedeutet, nach Produkt aufzuteilen , und die Option ORDER BY bedeutet, nach Produkt zu partitionieren und nach Monat zu sortieren.

Der Umsatz des aktuellen Monats abzüglich des Umsatzes der vorherigen Periode und dann dividiert durch den Umsatz der vorherigen Periode ergibt die monatliche Wachstumsrate.

Die Abfrage gibt die folgenden Ergebnisse zurück:

Januar 2018 ist die erste Periode, daher ist die monatliche Wachstumsrate leer.

Die monatliche Wachstumsrate von „Orange“ betrug im Februar 2018 etwa 0,2856 % ((10183-10154)/10154×100) und so weiter.

2. Jahresvergleichsanalyse

Das Wachstum im Jahresvergleich bezieht sich auf das Wachstum der Daten des aktuellen Zeitraums im Vergleich zum Vorjahr oder zum gleichen Zeitraum in der Geschichte. Zum Beispiel die Verkäufe eines Produkts im Juni 2019 im Vergleich zu den Verkäufen im Juni 2018 gestiegen. Teil.

Die folgende Aussage zählt jeden Monat die jährliche Wachstumsrate verschiedener Produkte:

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

Dabei stellt LAG (Betrag, 12) das Verkaufsvolumen der 12. Periode vor dem aktuellen Monat dar, also das Verkaufsvolumen des gleichen Monats im letzten Jahr.

PARTITION BY-Option bedeutet Partitionierung nach Produkt, ORDER BY-Option bedeutet Sortierung nach Monat.

Der Umsatzbetrag des aktuellen Monats abzüglich des Umsatzes des gleichen Zeitraums des Vorjahres und dann dividiert durch den Umsatz des gleichen Zeitraums des Vorjahres ergibt die Wachstumsrate im Jahresvergleich.

Die von dieser Abfrage zurückgegebenen Ergebnisse lauten wie folgt:

Für die 12 Datenperioden im Jahr 2018 gibt es keine entsprechende jährliche Wachstumsrate. Die jährliche Wachstumsrate von „Orange“ in Im Januar 2019 lag der Wert bei etwa 9,3067 % ((11099-10154)/10154×100) und so weiter.

Tipps: Die LEAD-Funktion ähnelt der LAG-Funktion, ihr Rückgabeergebnis ist jedoch die N-te Datenzeile nach der aktuellen Zeile.

3. Zusammengesetzte Wachstumsrate

Die zusammengesetzte Wachstumsrate ist das Ergebnis der Division der Daten der N-ten Periode durch die Basisdaten der ersten Periode, der Potenzierung mit N-1 und der Subtraktion von 1.

Angenommen, die Produktverkäufe im Jahr 2018 betragen 10.000, die Produktverkäufe im Jahr 2019 betragen 12.500 und die Produktverkäufe im Jahr 2020 betragen 15.000. Dann wird die durchschnittliche Wachstumsrate dieser beiden Jahre wie folgt berechnet:

Die auf jährlicher Basis berechnete durchschnittliche Wachstumsrate wird Durchschnittliche jährliche durchschnittliche Wachstumsrate genannt, und die auf monatlicher Basis berechnete durchschnittliche Wachstumsrate wird aufgerufen Durchschnittliche monatliche Gesamtwachstumsrate.

Die folgende Abfrage zählt die durchschnittliche monatliche Umsatzwachstumsrate verschiedener Produkte seit Januar 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

First definiert einen allgemeinen Tabellenausdruck, in dem FIRST_VALUE(amount) den Umsatz der ersten Periode (201801) zurückgibt. Nun, die ROW_NUMBER Die Funktion gibt die Nummer jeder Periode zurück.

Die POWER-Funktion in der Hauptabfrage wird verwendet, um die Quadratwurzeloperation durchzuführen, die NULLIF-Funktion wird verwendet, um Division-durch-Null-Fehler in der ersten Datenperiode zu behandeln, und die Konstante 1,0 wird verwendet, um Präzisionsverluste zu vermeiden, die durch verursacht werden ganzzahlige Division.

Die von dieser Abfrage zurückgegebenen Ergebnisse lauten wie folgt:

Januar 2018 ist der erste Zeitraum, daher ist die durchschnittliche Wachstumsrate der monatlichen Durchschnittsverkäufe des Produkts leer.

“桔子”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教程》

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der SQL-Fensterfunktionen: Verwendung von Ranking-Fensterfunktionen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!