SQL窗口函数之排名窗口函数的使用
作者:梁萌 发布时间:2024-01-26 18:49:31
关于窗口函数的基础,请看文章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 s
ORDER 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 s
ORDER 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 s
ORDER 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);
来源:https://blog.csdn.net/liangmengbk/article/details/124257706
猜你喜欢
- 一、操作流程首先复制代码会吧?1.有张照片这是网上随便找的一张照片,自行保存测试2.看看照片运行代码,其中show_img函数是展示照片3.
- 刚刚看了bootstrap的导航栏,发现有点弄混了,现在来整理一下;导航栏是一个很好的功能,是 Bootstrap 网站的一个突出特点。导航
- OpenCV介绍OpenCV是一个基于BSD许可(开源)发行的跨平台计算机视觉库,可以运行在Linux、Windows、Android和Ma
- 关于string的split 和 join 方法对导入os模块进行os.path.splie()/os.path.join() 貌似是处理机
- 本文实例讲述了python通过ssh-powershell监控windows的方法。分享给大家供大家参考。具体分析如下:对于服务器的监控来说
- 开发的很多场景中都会用到手机号的校验和验证码的校验,具体实现如下<div> <input type="text&
- Pycharm代码运行调试,具体内容如下1、准备工作(1)Python版本为2.7或者更高版本(2)已经创建了一个Python工程并且添加了
- Web Accessibility Initiative Accessible Rich Internet Applications认识AR
- 本文实例讲述了PHP缓存集成库phpFastCache用法。分享给大家供大家参考。具体分析如下:phpFastCache是一个开源的PHP缓
- 本文实例讲述了PHP队列用法。分享给大家供大家参考。具体分析如下:什么是队列,是先进先出的线性表,在具体应用中通常用链表或者数组来实现,队列
- 目录前期准备界面编写截图功能实现OCR实现内容显示总结前期准备在这个阶段主要准备整个小程序的结构,既然要实现ocr,那么输入就是一张图片,而
- 一直以来,jQuery是我最喜欢的框架之一。1月14号 1.4 Released版本发布后,就迫不及待去了解了一下1.4版本对比以往1.3.
- ImageField的使用笔记今天完善作业写的订单系统,主要是给每一个菜品增加图片,看起来美观一些,但是没想到这个小小的需求花了我一天时间,
- 前言前面几个章节我们学习了对于普通文件的操作,比如说文件的创建、复制粘贴、裁剪粘贴、文件名的重命名、删除等等。另外还学习了一些基本练习,如何
- 用QQ聊过天的朋友都对它的自动隐藏窗口功能爱不释手,它可以使窗口显得清爽整洁而且富有动感,笔者的几个朋
- Pygame 中提供了一个draw模块用来绘制一些简单的图形状,比如矩形、多边形、圆形、直线、弧线等。pygame.draw模块的常用方法如
- 一.Orcal临时表分类1.会话级临时表1).保存一个会话Session的数据。2).当会话退出时,临时表数据自动清空。表结构与元数据还存储
- excel转置分为两种情况,一个是较为简单的只需要行转列,列转行最简单的转置,利用pandas里面的转置**.T**函数代码如下:impor
- 前言今天我们简单说下Python函数和控制语句,大纲如下:函数“脏活累活交给函数来做”,首先,看看P
- 一、scrapy1.1 概述Scrapy,Python开发的一个快速、高层次的屏幕抓取和web抓取框架,用于抓取web站点并从页面中提取结构