Penjelasan terperinci tentang fungsi tetingkap SQL: penggunaan fungsi tetingkap ranking

Artikel ini membawa anda pengetahuan yang berkaitan tentang pelayan SQL, yang terutamanya memperkenalkan kekangan kunci utama SQL Server (KUNCI UTAMA Kekunci utama ialah lajur atau kumpulan yang mengenal pasti secara unik setiap baris dalam jadual). Lajur, artikel itu mengembangkan topik secara terperinci Mari kita lihat bersama-sama. Saya harap ia akan membantu semua orang.

Kajian yang disyorkan: "Tutorial SQL"

Untuk asas fungsi tetingkap, sila lihat artikel Fungsi Tetingkap SQL

Fungsi tetingkap nilai boleh digunakan untuk mengembalikan baris data pada kedudukan yang ditentukan dalam tetingkap. Fungsi tetingkap nilai biasa adalah seperti berikut:

Fungsi LAG boleh mengembalikan baris ke-N data sebelum baris semasa dalam tetingkap. Fungsi LEAD boleh mengembalikan baris ke-N data selepas baris semasa dalam tetingkap. Fungsi FIRST_VALUE boleh mengembalikan baris pertama data dalam tetingkap. Fungsi LAST_VALUE boleh mengembalikan baris terakhir data dalam tetingkap. Fungsi NTH_VALUE boleh mengembalikan baris data ke-N dalam tetingkap.

Antaranya, fungsi LAG dan fungsi LEAD tidak menyokong saiz tetingkap dinamik Mereka menggunakan keseluruhan partition sebagai tetingkap analisis.

Analisis kes

Contoh jadual yang digunakan dalam kes

Pertanyaan berikut akan menggunakan jadual jualan_bulanan menyimpan maklumat jualan produk dan produk mewakili nama produk mewakili tahun dan bulan, dan jumlah mewakili jumlah jualan (yuan).

Berikut ialah beberapa data dalam jadual:

Skrip permulaan untuk jadual ini boleh didapati di bahagian bawah artikel.

1. Analisis tempoh ke bulan

Pertumbuhan tempoh ke bulan merujuk kepada pertumbuhan data tempoh semasa berbanding dengan data tempoh sebelumnya, contohnya, jualan produk pada Jun 2019 dan jualan pada Mei 2019 Berbanding bahagian yang meningkat.

Penyata berikut mengira kadar pertumbuhan bulan ke bulan pelbagai produk:

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

Antaranya, LAG (jumlah, 1) bermaksud mendapatkan volum jualan bagi tempoh sebelumnya , dan pilihan PARTITION BY Menunjukkan pembahagian mengikut produk, dan pilihan ORDER BY menunjukkan pengisihan mengikut bulan.

Amaun jualan bulan semasa tolak jumlah jualan tempoh sebelumnya, dibahagikan dengan jumlah jualan tempoh sebelumnya, ialah kadar pertumbuhan bulan ke bulan.

Pertanyaan mengembalikan hasil berikut:

Januari 2018 ialah tempoh pertama, jadi kadar pertumbuhan bulan ke bulannya kosong.

Kadar pertumbuhan bulan ke bulan "Oren" pada Februari 2018 adalah lebih kurang 0.2856% ((10183-10154)/10154×100), dan seterusnya.

2. Analisis tahun ke tahun

Pertumbuhan tahun ke tahun merujuk kepada pertumbuhan data tempoh semasa berbanding dengan tahun sebelumnya atau tempoh yang sama dalam sejarah. jualan produk pada Jun 2019 dibandingkan dengan pada Jun 2018. Peningkatan berbanding jualan bulanan.

Penyata berikut mengira kadar pertumbuhan tahun ke tahun pelbagai produk setiap bulan:

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

Antaranya, LAG (jumlah, 12) mewakili volum jualan bagi tempoh ke-12 sebelum bulan semasa, juga Itulah jualan pada bulan yang sama tahun lepas.

Pilihan PARTITION BY menunjukkan pembahagian mengikut produk, dan pilihan ORDER BY menunjukkan pengisihan mengikut bulan.

Amaun jualan bulan semasa tolak jualan tempoh yang sama tahun lepas, dibahagikan dengan jualan tempoh yang sama tahun lepas, ialah kadar pertumbuhan tahun ke tahun.

Keputusan yang dikembalikan oleh pertanyaan ini adalah seperti berikut:

12 tempoh data pada 2018 tidak mempunyai kadar pertumbuhan tahun ke tahun yang sepadan. "Oren" pada Januari 2019 Kadar pertumbuhan tahun ke tahun adalah lebih kurang 9.3067% ((11099-10154)/10154×100), dan seterusnya.

Petua: Fungsi LEAD adalah serupa dengan fungsi LAG, tetapi hasil pulangannya ialah baris ke-N data selepas baris semasa.

3. Kadar pertumbuhan kompaun

Kadar pertumbuhan kompaun adalah hasil daripada membahagikan data tempoh N dengan data penanda aras tempoh pertama, kemudian menaikkannya kepada kuasa N- 1 dan tolak 1.

Andaikan jualan produk pada 2018 ialah 10,000, jualan produk pada 2019 ialah 12,500, dan jualan produk pada 2020 ialah 15,000. Kemudian kadar pertumbuhan kompaun dua tahun ini dikira seperti berikut:

Kadar pertumbuhan kompaun yang dikira pada asas tahunan dipanggil kadar pertumbuhan kompaun purata tahunan, kadar pertumbuhan kompaun yang dikira secara bulanan dipanggil kadar pertumbuhan kompaun purata bulanan.

Pertanyaan berikut mengira purata kadar pertumbuhan kompaun jualan bulanan bagi produk berbeza sejak Januari 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

Pertama, ungkapan jadual umum ditakrifkan, dengan FIRST_VALUE (jumlah) Jumlah jualan daripada tempoh pertama (201801) dikembalikan dan fungsi ROW_NUMBER mengembalikan nombor setiap tempoh.

Fungsi POWER dalam pertanyaan utama digunakan untuk melaksanakan operasi punca kuasa dua, fungsi NULLIF digunakan untuk mengendalikan ralat pembahagian demi sifar dalam tempoh pertama data, dan pemalar 1.0 digunakan untuk mengelakkan kehilangan ketepatan yang disebabkan oleh pembahagian integer.

Pertanyaan mengembalikan keputusan berikut:

Januari 2018 ialah tempoh pertama, jadi kadar pertumbuhan purata jualan bulanan kompaun produknya adalah kosong.

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

Atas ialah kandungan terperinci Penjelasan terperinci tentang fungsi tetingkap SQL: penggunaan fungsi tetingkap ranking. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!