千万级数据并发如何处理?进入学习
推荐学习:《SQL教程》
(资料图片)
关于窗口函数的基础,请看文章SQL窗口函数
取值窗口函数可以用于返回窗口内指定位置的数据行。常见的取值窗口函数如下:
LAG函数可以返回窗口内当前行之前的第N行数据。LEAD函数可以返回窗口内当前行之后的第N行数据。FIRST_VALUE函数可以返回窗口内第一行数据。LAST_VALUE函数可以返回窗口内最后一行数据。NTH_VALUE函数可以返回窗口内第N行数据。
其中,LAG函数和LEAD函数不支持动态的窗口大小,它们以整个分区作为分析的窗口。
案例分析
案例使用的示例表
下面的查询中会用到一张表,sales_monthly表中存储了商品销量信息,product表示产品名称,ym表示年月,amount表示销售金额(元)。
以下是该表中的部分数据:
这个表的初始化脚本可以在文章底部获取。
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 sORDER BY s.product,s.ym
其中,LAG(amount,1)表示获取上一期的销售额,PARTITION BY选项表示按照产品分区,ORDER BY选项表示按照月份进行排序。
当前月份的销售额amount减去上一期的销售额,再除以上一期的销售额,就是环比增长率。
该查询返回的结果如下:
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 sORDER BY s.product,s.ym
其中,LAG(amount,12)表示当前月份之前第12期的销售额,也就是去年同月份的销售额。
PARTITION BY选项表示按照产品分区,ORDER BY选项表示按照月份进行排序。
当前月份的销售额amount减去去年同期的销售额,再除以去年同期的销售额,就是同比增长率。
该查询返回的结果如下:
2018年的12期数据都没有对应的同比增长率,“桔子”2019年1月的同比增长率约为9.3067%((11099-10154)/10154×100),依此类推。
提示:LEAD函数与LAG函数的使用方法类似,不过它的返回结果是当前行之后的第N行数据。
3.复合增长率
复合增长率是第N期的数据除以第一期的基准数据,然后开N-1次方再减去1得到的结果。
假如2018年的产品销售额为10000,2019年的产品销售额为12500,2020年的产品销售额为15000。那么这两年的复合增长率的计算方式如下:
以年度为单位计算的复合增长率被称为年均复合增长率,以月度为单位计算的复合增长率被称为月均复合增长率。
以下查询统计了自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 sORDER BY product, ym
首先定义了一个通用表表达式,其中FIRST_VALUE(amount)返回了第一期(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中文网其它相关文章!