SQLウィンドウ関数の詳しい説明: ランキングウィンドウ関数の使用

この記事では、SQL サーバー に関する関連知識を提供します。主に SQL Server の主キー制約 (PRIMARY KEY) について紹介します。主キーは、テーブル内の各行を一意に識別する列またはグループです。コラムでは、このトピックについて詳しく展開しています。一緒に見てみましょう。皆様のお役に立てれば幸いです。

#推奨学習: 「

SQL チュートリアル 」

ウィンドウ関数の基本については、「SQL ウィンドウ関数」の記事を参照してください

値ウィンドウ関数を使用すると、ウィンドウ内の指定された位置にあるデータ行を返すことができます。一般的な値ウィンドウ関数は次のとおりです。

LAG 関数は、ウィンドウ内の現在の行の前の N 行目のデータを返すことができます。 LEAD 関数は、ウィンドウ内の現在の行の後の N 行目のデータを返すことができます。 FIRST_VALUE 関数は、ウィンドウ内のデータの最初の行を返すことができます。 LAST_VALUE 関数は、ウィンドウ内のデータの最後の行を返すことができます。 NTH_VALUE 関数は、ウィンドウ内のデータの N 行目を返すことができます。

このうち、LAG 関数と LEAD 関数は動的ウィンドウ サイズをサポートしておらず、パーティション全体を分析ウィンドウとして使用します。

ケース分析

このケースで使用されるテーブルの例

次のクエリではテーブルが使用されます。sales_monthly テーブルには製品の販売情報が格納され、product は製品名を表します。ymは年月を表し、金額は売上高（元）を表します。

以下はテーブル内のデータの一部です:

このテーブルの初期化スクリプトは記事の最後から入手できます。

1. 前月比分析

期間前月比の伸びとは、前期間のデータと比較した今期のデータの伸びを指します。たとえば、製品の売上高などです。 2019年6月の売上高と2019年5月の売上高の増加分を比較。

次のステートメントは、さまざまな製品の前月比成長率をカウントします。

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

このうち、LAG (amount, 1) は、前期間の売上高を取得することを意味し、 PARTITION BY オプションは、「製品のパーティション化の場合、ORDER BY オプションは月ごとの並べ替えを示します」に従っていることを意味します。

当月の売上高から前期の売上高を差し引き、前期の売上高で割ったものが前月比伸び率となります。

このクエリによって返される結果は次のとおりです:

#2018 年 1 月は最初の期間であるため、前月比の増加率は空です。

2018 年 2 月の「オレンジ」の前月比伸び率は約 0.2856% ((10183-10154)/10154×100) でした。

2. 前年比分析

前年比増加とは、前年または過去の同じ期間と比較した、今期のデータの増加を指します。たとえば、次のようになります。 2019 年 6 月の製品の売上を 2018 年 6 月の売上と比較しました。月間売上と比較した増加率。

次のステートメントは、さまざまな製品の前年比成長率を毎月カウントします:

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

このうち、LAG (amount, 12) は、前第 12 期の売上高を表します。当月、つまり昨年の同じ月の売上高。

PARTITION BY オプションは製品ごとのパーティション分割を示し、ORDER BY オプションは月ごとの並べ替えを示します。

当月の売上高から前年同期の売上を引いたものを前年同期の売上で割ったものが前年同期比成長率となります。

このクエリによって返される結果は次のとおりです。

#12 期間のデータには、対応する前年比増加率がありません。 2018. 2019年1月の「オレンジ」 前年比伸び率は約9.3067％（（11099-10154）/10154×100）など。

ヒント: LEAD 関数は LAG 関数に似ていますが、戻り結果は現在の行の後の N 行目のデータになります。

3. 複合成長率

複合成長率は、N 期のデータを 1 期のベンチマーク データで除算し、N 乗した結果です。 1 から 1 を引きます。

2018 年の製品販売数が 10,000、2019 年の製品販売数が 12,500、2020 年の製品販売数が 15,000 であるとします。次に、これら 2 年間の複合成長率は次のように計算されます。

年ベースで計算された複合成長率は、

平均年間複合成長率##と呼ばれます。 #、月ベースで計算された複合成長率は、平均月次複合成長率と呼ばれます。 次のクエリは、2018 年 1 月以降のさまざまな製品の月平均売上の複合成長率をカウントします。

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_VALUE (金額) が売上高の場合に返されます。最初の期間 (201801) の場合、ROW_NUMBER 関数は各期間の番号を返します。

メイン クエリの POWER 関数は平方根演算の実行に使用され、NULLIF 関数はデータの最初の期間のゼロ除算エラーの処理に使用され、定数 1.0 は精度を避けるために使用されます。整数の除算によって生じる損失。

このクエリによって返される結果は次のとおりです:

2018 年 1 月が最初の期間であるため、その製品の月次平均売上の複合成長率は次のようになります。空の。

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

以上がSQLウィンドウ関数の詳しい説明: ランキングウィンドウ関数の使用の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。