窗口函数(Window Function)是 SQL2003 标准中定义的一项新特性,并在 SQL2011、SQL2016 中又加以完善,添加了若干拓展。
一.窗口函数有什么用?
在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:
排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励
面对这类需求,就需要使用sql的高级功能窗口函数了。
二.什么是窗口函数?
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
那么语法中的<窗口函数>都有哪些呢?
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
三.如何使用?
接下来,就结合实例,给大家介绍几种窗口函数的用法。
1.专用窗口函数rank
例如下图,是班级表中的内容
如果我们想在每个班级内按成绩排名,得到下面的结果。
以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内,按成绩排名了。
得到上面结果的sql语句代码如下:
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表
我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:
1)每个班级内:按班级分组
partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)
2)按成绩排名
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。
窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢?
这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。
相信通过这个例子,你已经明白了这个窗口函数的使用:
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表
窗口函数不同于我们熟悉的常规函数及聚合函数,它为每行数据进行一次计算,特点是输入多行(一个窗口)、返回一个值。
在报表等数据分析场景中,你会发现窗口函数真的很强大,灵活运用窗口函数可以解决很多复杂问题,比如去重、排名、同比及环比、连续登录等等。
既然窗口函数这么强大,更要了解和灵活运用它了,本文将对窗口函数进行一个全面的整理,讲一讲窗口函数是什么,有哪些分类,用法是什么,以及窗口函数的案例加深大家的理解。
那什么是窗口函数呢?
窗口函数出现在 SELECT 子句的表达式列表中,它最显著的特点就是 OVER 关键字。语法定义如下:
Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>]
[<window_expression>])</window_expression>
Function (arg1,…, argn) 可以是下面的函数:
Aggregate Functions: 聚合函数,比如:sum(…)、 max(…)、min(…)、avg(…)等.
Sort Functions: 数据排序函数, 比如 :rank(…)、row_number(…)等.
Analytics Functions: 统计和比较函数, 比如:lead(…)、lag(…)、 first_value(…)等.
OVER ([PARTITION BY <…>] [ORDER BY <….>] 其中包括以下可选项:
PARTITION BY 表示将数据先按 字段 进行分区
ORDER BY 表示将各个分区内的数据按 排序字段 进行排序
window_expression 用于确定窗边界:
名词 | 含义 |
---|---|
preceding | 往前 |
following | 往后 |
current row | 当前行 |
unbounded | 起点 |
unbounded preceding | 从前面的起点 |
unbounded following | 到后面的终点 |
窗口边界使用详解
- 如果不指定 PARTITION BY,则不对数据进行分区,换句话说,所有数据看作同一个分区;
- 如果不指定 ORDER BY,则不对各分区做排序,通常用于那些顺序无关的窗口函数,例如 SUM()
- 如果不指定窗口子句,则默认采用以下的窗口定义:
a、若不指定 ORDER BY,默认使用分区内所有行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
b、若指定了 ORDER BY,默认使用分区内第一行到当前值 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
窗口函数的计算过程(语法中每个部分都是可选的)
- 按窗口定义,将所有输入数据分区、再排序(如果需要的话)
- 对每一行数据,计算它的窗口范围
- 将窗口内的行集合输入窗口函数,计算结果填入当前行
数据准备
-- 创建表
CREATE TABLE IF NOT EXISTS q1_sales (
emp_name string,
emp_mgr string,
dealer_id int,
sales int,
stat_date string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED as TEXTFILE;
-- 插入测试数据
insert into table q1_sales (emp_name,emp_mgr,dealer_id,sales,stat_date)
values
('Beverly Lang','Mike Palomino',2,16233,'2020-01-01'),
('Kameko French','Mike Palomino',2,16233,'2020-01-03'),
('Ursa George','Rich Hernandez',3,15427,'2020-01-04'),
('Ferris Brown','Dan Brodi',1,19745,'2020-01-02'),
('Noel Meyer','Kari Phelps',1,19745,'2020-01-05'),
('Abel Kim','Rich Hernandez',1,12369,'2020-01-03'),
('Raphael Hull','Kari Phelps',1,8227,'2020-01-02'),
('Jack Salazar','Kari Phelps',1,9710,'2020-01-01'),
('May Stout','Rich Hernandez',3,9308,'2020-01-05'),
('Haviva Montoya','Mike Palomino',2,9308,'2020-01-03');
-- 查看测试数据信息
select * from q1_sales;
+--------------------+-------------------+---------------------+-----------------+---------------------+
| q1_sales.emp_name | q1_sales.emp_mgr | q1_sales.dealer_id | q1_sales.sales | q1_sales.stat_date |
+--------------------+-------------------+---------------------+-----------------+---------------------+
| Beverly Lang | Mike Palomino | 2 | 16233 | 2020-01-01 |
| Kameko French | Mike Palomino | 2 | 16233 | 2020-01-03 |
| Ursa George | Rich Hernandez | 3 | 15427 | 2020-01-04 |
| Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 |
| Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 |
| Abel Kim | Rich Hernandez | 1 | 12369 | 2020-01-03 |
| Raphael Hull | Kari Phelps | 1 | 8227 | 2020-01-02 |
| Jack Salazar | Kari Phelps | 1 | 9710 | 2020-01-01 |
| May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 |
| Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 |
+--------------------+-------------------+---------------------+-----------------+---------------------+
10 rows selected (0.223 seconds)
窗口聚合函数有哪些?
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
AVG() | 参数类型为DECIMAL的返回类型为DECIMAL,其他为DOUBLE | AVG 窗口函数返回输入表达式值的平均值,忽略 NULL 值。 |
COUNT() | BIGINT | COUNT 窗口函数计算输入行数。 COUNT(*) 计算目标表中的所有行,包括Null值;COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。 |
MAX() | 与传参类型一致 | MAX窗口函数返回表达式在所有输入值中的最大值,忽略 NULL 值。 |
MIN() | 与传参类型一致 | MIN窗口函数返回表达式在所有输入值中的最小值,忽略 NULL 值。 |
SUM() | 针对传参类型为DECIMAL的,返回类型一致;除此之外的浮点型为DOUBLE;传参类型为整数类型的,返回类型为BIGINT | SUM窗口函数返回所有输入值的表达式总和,忽略 NULL 值。 |
select emp_name,
emp_mgr,
dealer_id,
sales,
sum(sales) over () as sample1, -- 所有sales和
sum(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组,组内数据累加
sum(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组,时间排序,组内数据逐个相加
sum(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组,时间排序,组内由起点到当前行的聚合
sum(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组,时间排序,组内当前行和前面一行做聚合
sum(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组,时间排序,组内当前行和前一行和后一行聚合
sum(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组,时间排序,组内当前行和后面所有行
from q1_sales;
select emp_name,
emp_mgr,
dealer_id,
sales,
count(sales) over () as sample1, -- 所有条数
count(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组,组内数据数量
count(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组,时间排序,组内数据条数逐个相加
count(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组,时间排序,组内由起点到当前行的聚合
count(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组,时间排序,组内当前行和前面一行做聚合
count(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组,时间排序,组内当前行和前一行和后一行聚合
count(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组,时间排序,组内当前行和后面所有行
from q1_sales;
select emp_name,
emp_mgr,
dealer_id,
sales,
avg(sales) over () as sample1, -- 所有sales聚合
avg(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组,组内数据累加
avg(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组,时间排序,组内数据逐个相加
avg(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组,时间排序,组内由起点到当前行的聚合
avg(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组,时间排序,组内当前行和前面一行做聚合
avg(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组,时间排序,组内当前行和前一行和后一行聚合
avg(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组,时间排序,组内当前行和后面所有行
from q1_sales;
select emp_name,
emp_mgr,
dealer_id,
sales,
max(sales) over () as sample1, -- 所有sales聚合
max(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组,组内数据累加
max(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组,时间排序,组内数据逐个相加
max(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组,时间排序,组内由起点到当前行的聚合
max(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组,时间排序,组内当前行和前面一行做聚合
max(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组,时间排序,组内当前行和前一行和后一行聚合
max(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组,时间排序,组内当前行和后面所有行
from q1_sales;
select emp_name,
emp_mgr,
dealer_id,
sales,
min(sales) over () as sample1, -- 所有sales聚合
min(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组,组内数据累加
min(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组,时间排序,组内数据逐个相加
min(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组,时间排序,组内由起点到当前行的聚合
min(sales)
OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组,时间排序,组内当前行和前面一行做聚合
min(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组,时间排序,组内当前行和前一行和后一行聚合
min(sales)
over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组,时间排序,组内当前行和后面所有行
from q1_sales;
排名窗口函数
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
ROW_NUMBER() | BIGINT | 根据具体的分组和排序,为每行数据生成一个起始值等于1的唯一序列数 |
RANK() | BIGINT | 对组中的数据进行排名,如果名次相同,则排名也相同,但是下一个名次的排名序号会出现不连续。 |
DENSE_RANK() dense是稠密的意思,可以引申记忆 | BIGINT | dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。当出现名次相同时,则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。 |
PERCENT_RANK() | DOUBLE | 计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为:(当前行的rank值-1)/(分组内的总行数-1) |
CUME_DIST() | DOUBLE | 计算某个窗口或分区中某个值的累积分布。假定升序排序,则使用以下公式确定累积分布:小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值 |
NTILE() | INT | 已排序的行划分为大小尽可能相等的指定数量的排名的组,并返回给定行所在的组的排名。如果切片不均匀,默认增加第一个切片的分布,不支持ROWS BETWEEN |
select *,
ROW_NUMBER() over(partition by dealer_id order by sales desc) rk01,
RANK() over(partition by dealer_id order by sales desc) rk02,
DENSE_RANK() over(partition by dealer_id order by sales desc) rk03,
PERCENT_RANK() over(partition by dealer_id order by sales desc) rk04
from q1_sales;
select *,
CUME_DIST() over(partition by dealer_id order by sales ) rk05,
CUME_DIST() over(partition by dealer_id order by sales desc) rk06
from q1_sales;
select *,
NTILE(2) over(partition by dealer_id order by sales ) rk07,
NTILE(3) over(partition by dealer_id order by sales ) rk08,
NTILE(4) over(partition by dealer_id order by sales ) rk09
from q1_sales;
值窗口函数
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
LAG() | 与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL. | |
LEAD() | 用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL. | |
FIRST_VALUE | 取分组内排序后,截止到当前行,第一个值 | |
LAST_VALUE | 取分组内排序后,截止到当前行,最后一个值 |
注意: last_value默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
,表示当前行永远是最后一个值,需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
。
select emp_name, dealer_id, sales, first_value(sales) over (partition by dealer_id order by sales) as dealer_low from q1_sales;
|-----------------|------------|--------|-------------|
| emp_name | dealer_id | sales | dealer_low |
|-----------------|------------|--------|-------------|
| Raphael Hull | 1 | 8227 | 8227 |
| Jack Salazar | 1 | 9710 | 8227 |
| Ferris Brown | 1 | 19745 | 8227 |
| Noel Meyer | 1 | 19745 | 8227 |
| Haviva Montoya | 2 | 9308 | 9308 |
| Beverly Lang | 2 | 16233 | 9308 |
| Kameko French | 2 | 16233 | 9308 |
| May Stout | 3 | 9308 | 9308 |
| Abel Kim | 3 | 12369 | 9308 |
| Ursa George | 3 | 15427 | 9308 |
|-----------------|------------|--------|-------------|
10 rows selected (0.299 seconds)
select emp_name, dealer_id, sales, <code>year</code>, last_value(sales) over (partition by emp_name order by <code>year</code>) as last_sale from emp_sales where <code>year</code> = 2013;
|-----------------|------------|--------|-------|------------|
| emp_name | dealer_id | sales | year | last_sale |
|-----------------|------------|--------|-------|------------|
| Beverly Lang | 2 | 5324 | 2013 | 5324 |
| Ferris Brown | 1 | 22003 | 2013 | 22003 |
| Haviva Montoya | 2 | 6345 | 2013 | 13100 |
| Haviva Montoya | 2 | 13100 | 2013 | 13100 |
| Kameko French | 2 | 7540 | 2013 | 7540 |
| May Stout | 2 | 4924 | 2013 | 15000 |
| May Stout | 2 | 8000 | 2013 | 15000 |
| May Stout | 2 | 15000 | 2013 | 15000 |
| Noel Meyer | 1 | 13314 | 2013 | 13314 |
| Raphael Hull | 1 | -4000 | 2013 | 14000 |
| Raphael Hull | 1 | 14000 | 2013 | 14000 |
| Ursa George | 1 | 10865 | 2013 | 10865 |
|-----------------|------------|--------|-------|------------|
12 rows selected (0.284 seconds)
开窗案例举例
如何使用开窗函数去重
select * from (select *,row_number() over(partition by emp_mgr order by stat_date desc) rk from q1_sales) tmp where rk = 1;
+-----------------+-----------------+----------------+------------+----------------+---------+
| tmp.emp_name | tmp.emp_mgr | tmp.dealer_id | tmp.sales | tmp.stat_date | tmp.rk |
+-----------------+-----------------+----------------+------------+----------------+---------+
| Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 | 1 |
| Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 | 1 |
| Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 | 1 |
| May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 | 1 |
+-----------------+-----------------+----------------+------------+----------------+---------+
4 rows selected (25.707 seconds)
如何使用开窗函数进行排名
select *,row_number() over(partition by dealer_id order by sales desc) rk from q1_sales;
+--------------------+-------------------+---------------------+-----------------+---------------------+-----+
| q1_sales.emp_name | q1_sales.emp_mgr | q1_sales.dealer_id | q1_sales.sales | q1_sales.stat_date | rk |
+--------------------+-------------------+---------------------+-----------------+---------------------+-----+
| Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 | 1 |
| Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 | 2 |
| Abel Kim | Rich Hernandez | 1 | 12369 | 2020-01-03 | 3 |
| Jack Salazar | Kari Phelps | 1 | 9710 | 2020-01-01 | 4 |
| Raphael Hull | Kari Phelps | 1 | 8227 | 2020-01-02 | 5 |
| Kameko French | Mike Palomino | 2 | 16233 | 2020-01-03 | 1 |
| Beverly Lang | Mike Palomino | 2 | 16233 | 2020-01-01 | 2 |
| Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 | 3 |
| Ursa George | Rich Hernandez | 3 | 15427 | 2020-01-04 | 1 |
| May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 | 2 |
+--------------------+-------------------+---------------------+-----------------+---------------------+-----+
10 rows selected (23.38 seconds)
数仓增量数据合并
基于上述的排名和区中方法结合,可以实现数仓增量抽取的数据和历史数据合并去重。
环比
数据准备
select * from temp_test12;
create table if not exists temp_test12 (
month string comment '月份',
shop string comment '店铺',
money string comment '营业额'
);
insert into table temp_test12 (month,shop,money)
values
('2019-01','a',1),
('2019-04','a',4),
('2019-02','a',2),
('2019-03','a',3),
('2019-06','a',6),
('2019-05','a',5),
('2019-01','b',2),
('2019-02','b',4),
('2019-03','b',6),
('2019-04','b',8),
('2019-05','b',10),
('2019-06','b',12);
select * from temp_test12;
+--------------------+-------------------+---------------------+
| temp_test12.month | temp_test12.shop | temp_test12.money |
+--------------------+-------------------+---------------------+
| 2019-01 | a | 1 |
| 2019-04 | a | 4 |
| 2019-02 | a | 3 |
| 2019-03 | a | 4 |
| 2019-06 | a | 6 |
| 2019-05 | a | 5 |
| 2019-01 | b | 2 |
| 2019-02 | b | 4 |
| 2019-03 | b | 6 |
| 2019-04 | b | 8 |
| 2019-05 | b | 10 |
| 2019-06 | b | 12 |
+--------------------+-------------------+--------------------+
10 rows selected (23.38 seconds)
需求描述
查询店铺上个月的营业额,结果字段如下:
| 月份 | 商铺 | 本月营业额 | 上月营业额|
不使用开窗函数实现方案
实现这个需求我们需要先使用row_number()over按商铺分组,按月份排序得出这样一个结果:
SELECT month
,shop
,money
,ROW_NUMBER() OVER (
PARTITION BY shop ORDER BY month
) AS rn
FROM temp_test12;
结果:
month shop money rn
2019-01 a 1 1
2019-02 a 2 2
2019-03 a 3 3
2019-04 a 4 4
2019-05 a 5 5
2019-06 a 6 6
2019-01 b 2 1
2019-02 b 4 2
2019-03 b 6 3
2019-04 b 8 4
2019-05 b 10 5
2019-06 b 12 6
然后进行偏移自关联,将每个商铺的每个月的营业额和上个月的关联在一起:
WITH a
AS (
SELECT month
,shop
,MONEY
,ROW_NUMBER() OVER (
PARTITION BY shop ORDER BY month
) AS rn
FROM temp_test12
)
SELECT a1.month
,a1.shop
,a1.MONEY
,nvl(a2.month, '2018-12') before_month --为了便于理解,这里加入上月的月份。如果上月没有的月份取为2018-12
,nvl(a2.MONEY, 1) before_money --上月没有的营业额取为1
FROM a a1 --代表本月
LEFT JOIN a a2 --代表上月
ON a1.shop = a2.shop
AND a1.month = substr(add_months(CONCAT (
a2.month
,'-01'
), 1), 1, 7) --增加月份的函数add_months中至少要传入年月日
GROUP BY a1.month
,a1.shop
,a1.MONEY
,nvl(a2.month, '2018-12')
,nvl(a2.MONEY, 1);
结果:
a1.month a1.shop a1.money before_month before_money
2019-01 a 1 2018-12 1
2019-02 a 2 2019-01 1
2019-03 a 3 2019-02 2
2019-04 a 4 2019-03 3
2019-05 a 5 2019-04 4
2019-06 a 6 2019-05 5
2019-01 b 2 2018-12 1
2019-02 b 4 2019-01 2
2019-03 b 6 2019-02 4
2019-04 b 8 2019-03 6
2019-05 b 10 2019-04 8
2019-06 b 12 2019-05 10
lag 开窗函数实现环比
SELECT month
,shop
,MONEY
,LAG(MONEY, 1, 1) OVER ( --取分组内上一行的营业额,如果没有上一行则取1
PARTITION BY shop ORDER BY month --按商铺分组,按月份排序
) AS before_money
FROM temp_test12;
-- 结果集如下
month shop money before_money
2019-01 a 1 1
2019-02 a 2 1
2019-03 a 3 2
2019-04 a 4 3
2019-05 a 5 4
2019-06 a 6 5
2019-01 b 2 1
2019-02 b 4 2
2019-03 b 6 4
2019-04 b 8 6
2019-05 b 10 8
2019-06 b 12 10
lag 其他用法演示
SELECT month
,shop
,MONEY
,LAG(MONEY, 1, 1) OVER (
PARTITION BY shop ORDER BY month
) AS before_money
,LAG(MONEY, 1) OVER (
PARTITION BY shop ORDER BY month
) AS before_money --第三个参数不写的话,如果没有上一行值,默认取null
,LAG(MONEY) OVER (
PARTITION BY shop ORDER BY month
) AS before_money --第二个参数不写默认为1,第三个参数不写的话,如果没有上一行值,默认取null,结果与上一列相同
,LAG(MONEY, 2, 1) OVER (
PARTITION BY shop ORDER BY month
) AS before_2month_money --取两个月前的营业额
FROM temp_test12;
-- 结果集
month shop money before_money before_money before_money before_2month_money
2019-01 a 1 1 NULL NULL 1
2019-02 a 2 1 1 1 1
2019-03 a 3 2 2 2 1
2019-04 a 4 3 3 3 2
2019-05 a 5 4 4 4 3
2019-06 a 6 5 5 5 4
2019-01 b 2 1 NULL NULL 1
2019-02 b 4 2 2 2 1
2019-03 b 6 4 4 4 2
2019-04 b 8 6 6 6 4
2019-05 b 10 8 8 8 6
2019-06 b 12 10 10 10 8
-- 解释说明:
-- shop为a时,before_money指定了往上第1行的值,如果没有上一行值,默认取null,这里指定为1。
-- a的第1行,往上1行值为NULL,指定第三个参数取1,不指定取null 。
-- a的第2行,往上1行值为第1行营业额值,1。
-- a的第6行,往上1行值为为第5行营业额值,5
lead 求下月营业额
lead(col,n,default)与lag相反,统计分组内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,不填默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)。
新添一列每个商铺下个月的营业额,结果字段如下: 月份 商铺 本月营业额 下月营业额
SELECT month
,shop
,MONEY
,LEAD(MONEY, 1, 7) OVER (
PARTITION BY shop ORDER BY month
) AS after_money
,LEAD(MONEY, 1) OVER (
PARTITION BY shop ORDER BY month
) AS after_money --第三个参数不写的话,如果没有下一行值,默认取null
,LEAD(MONEY, 2, 7) OVER (
PARTITION BY shop ORDER BY month
) AS after_2month_money --取两个月后的营业额
FROM temp_test12;
结果:
month shop money after_money after_money after_2month_money
2019-01 a 1 2 2 3
2019-02 a 2 3 3 4
2019-03 a 3 4 4 5
2019-04 a 4 5 5 6
2019-05 a 5 6 6 7
2019-06 a 6 7 NULL 7
2019-01 b 2 4 4 6
2019-02 b 4 6 6 8
2019-03 b 6 8 8 10
2019-04 b 8 10 10 12
2019-05 b 10 12 12 7
2019-06 b 12 7 NULL 7
解释说明:
shop为a时,after_money指定了往下第1行的值,如果没有下一行值,默认取null,这里指定为1。
a的第1行,往下1行值为第2行营业额值,2。
a的第2行,往下1行值为第3行营业额值,4。
a的第6行,往下1行值为NULL,指定第三个参数取7,不指定取null。
first_value(col)
用于取分组内排序后,截止到当前行,第一个col的值。
ELECT month
,shop
,MONEY
,first_value(MONEY) OVER (
PARTITION BY shop ORDER BY month
) AS first_money
FROM temp_test12;
结果:
month shop money first_money
2019-01 a 1 1
2019-02 a 2 1
2019-03 a 3 1
2019-04 a 4 1
2019-05 a 5 1
2019-06 a 6 1
2019-01 b 2 2
2019-02 b 4 2
2019-03 b 6 2
2019-04 b 8 2
2019-05 b 10 2
2019-06 b 12 2
解释说明:
shop为a时,截止到每一行时,分组内的第一行值都是1。
shop为b时,截止到每一行时,分组内的第一行值都是2。
last_value(col)
用于取分组内排序后,截止到当前行,最后一个col的值。
SELECT month
,shop
,MONEY
,last_value(MONEY) OVER (
PARTITION BY shop ORDER BY month
) AS last_money
FROM temp_test12;
结果:
month shop money last_money
2019-01 a 1 1
2019-02 a 2 2
2019-03 a 3 3
2019-04 a 4 4
2019-05 a 5 5
2019-06 a 6 6
2019-01 b 2 2
2019-02 b 4 4
2019-03 b 6 6
2019-04 b 8 8
2019-05 b 10 10
2019-06 b 12 12
解释说明:
shop为a时,截止到每一行时,分组内的最后一行值都是该行本身。
shop为b时,截止到每一行时,分组内的最后一行值都是该行本身。
连续登录
数据准备
源数据,文件中是以,号隔开的
id,date
A,2018-09-04
B,2018-09-04
C,2018-09-04
A,2018-09-05
A,2018-09-05
C,2018-09-05
A,2018-09-06
B,2018-09-06
C,2018-09-06
A,2018-09-04
B,2018-09-04
C,2018-09-04
A,2018-09-05
A,2018-09-05
C,2018-09-05
A,2018-09-06
B,2018-09-06
C,2018-09-06
展现连续登陆两天的用户信息
select
*
from
(
select
id ,
date,
lead(date,1,-1) over(partition by id order by date desc ) as date1 -- 按照用户分组,登录时间降序排序,获取上一次登录日期
from tb_use a
group by id,date -- 去重当日重复登录,
) as b
where date_sub(cast(b.date as date),1)=cast(b.date1 as date); -- 判定当前登录日期的上一天是否与上一次登录日期一致,一致则判定为连续登录
结果:
b.id b.date b.date1
A 2018-09-06 2018-09-05
A 2018-09-05 2018-09-04
C 2018-09-06 2018-09-05
C 2018-09-05 2018-09-04
统计连续登陆两天的用户个数
(n天就只需要把lead(date,2,-1)中的2改成n-1并且把date_sub(cast(b.date as date),2)中的2改成n-1)
select
count(distinct b.id) as c1
from
(
select id ,date,
lead(date,1,-1) over(partition by id order by date desc ) as date1
from tb_use a
group by id,date
) as b
where date_sub(cast(b.date as date),1)=cast(b.date1 as date);
结果:
c1
2
特说说明:上文指出了连续登录2天的场景,针对其他连续登录场景,假设连续登录n天,可将lead(date,1,-1)中的1改成n-1,date_sub(cast(b.date as date),1)中的1改成n-1。
占比、同比、环比计算(lag函数,lead函数)
数据准备
-- 创建表并插入数据
CREATE TABLE <code>saleorder</code> (
<code>order_id</code> int ,
<code>order_time</code> date ,
<code>order_num</code> int
)
-- 插入测试数据
INSERT INTO <code>saleorder</code> VALUES
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);
select * from saleorder;
+---------------------+-----------------------+----------------------+
| saleorder.order_id | saleorder.order_time | saleorder.order_num |
+---------------------+-----------------------+----------------------+
| 1 | 2020-04-20 | 420 |
| 2 | 2020-04-04 | 800 |
| 3 | 2020-03-28 | 500 |
| 4 | 2020-03-13 | 100 |
| 5 | 2020-02-27 | 300 |
| 6 | 2020-01-07 | 450 |
| 7 | 2019-04-07 | 800 |
| 8 | 2019-03-15 | 1200 |
| 9 | 2019-02-17 | 200 |
| 10 | 2019-02-07 | 600 |
| 11 | 2019-01-13 | 300 |
+---------------------+-----------------------+----------------------+
11 rows selected (0.331 seconds)
使用窗口函数实现占比
SELECT
order_month,
num, -- 月销量
total, -- 年销量
round( num / total, 2 ) AS ratio -- 月销量占年销量比
FROM
(
select
substr(order_time, 1, 7) as order_month, --查询月份
sum(order_num) over (partition by substr(order_time, 1, 7)) as num, --根据月份分组,统计月销量
sum( order_num ) over ( PARTITION BY substr( order_time, 1, 4 ) ) total, --根据年分组,统计年销量
row_number() over (partition by substr(order_time, 1, 7)) as rk
from saleorder
) temp
where rk = 1;
+--------------+-------+--------+--------+
| order_month | num | total | ratio |
+--------------+-------+--------+--------+
| 2019-04 | 800 | 3100 | 0.26 |
| 2019-03 | 1200 | 3100 | 0.39 |
| 2019-02 | 800 | 3100 | 0.26 |
| 2019-01 | 300 | 3100 | 0.1 |
| 2020-04 | 1220 | 2570 | 0.47 |
| 2020-03 | 600 | 2570 | 0.23 |
| 2020-02 | 300 | 2570 | 0.12 |
| 2020-01 | 450 | 2570 | 0.18 |
+--------------+-------+--------+--------+
8 rows selected (49.433 seconds)
使用窗口函数实现环比计算
什么是环比、什么是同比?
与上年度数据对比称"同比",与上月数据对比称"环比"。
相关公式如下:
同比增长率计算公式:(当年值-上年值)/上年值x100%
环比增长率计算公式:(当月值-上月值)/上月值x100%
-- 环比增长率
select
now_month,
now_num,
last_num,
concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" )
FROM
(
-- 2、查询上月销量
select
now_month,
now_num,
lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num
from
(
-- 1、按月统计销量
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from saleorder
group by
substr(order_time, 1, 7)
) t1
) t2;
+------------+----------+-----------+----------+
| now_month | now_num | last_num | _c3 |
+------------+----------+-----------+----------+
| 2019-01 | 300 | NULL | 0.0% |
| 2019-02 | 800 | 300 | 166.67% |
| 2019-03 | 1200 | 800 | 50.0% |
| 2019-04 | 800 | 1200 | -33.33% |
| 2020-01 | 450 | 800 | -43.75% |
| 2020-02 | 300 | 450 | -33.33% |
| 2020-03 | 600 | 300 | 100.0% |
| 2020-04 | 1220 | 600 | 103.33% |
+------------+----------+-----------+----------+
8 rows selected (50.521 seconds)
-- 同比增长率计算公式
同比的话,如果每个月都齐全,都有数据lag(num,12)就ok.。我们的例子中只有19年和20年1-4月份的数据。这种特殊情况应该如何处理?
SELECT
t1.now_month,
nvl ( now_num, 0 ) AS now_num,
nvl ( last_num, 0 ) AS last_num,
nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio
FROM
(
SELECT
DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
sum( order_num ) AS now_num
FROM
saleorder
GROUP BY
DATE_FORMAT( order_time, 'yyyy-MM' )
) t1
LEFT JOIN
(
SELECT
DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
sum( order_num ) AS last_num
FROM
saleorder
GROUP BY
DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' )
) AS t2 ON t1.now_month = t2.now_month;
+---------------+----------+-----------+--------+
| t1.now_month | now_num | last_num | ratio |
+---------------+----------+-----------+--------+
| 2019-01 | 300 | 0 | 0.0 |
| 2019-02 | 800 | 0 | 0.0 |
| 2019-03 | 1200 | 0 | 0.0 |
| 2019-04 | 800 | 0 | 0.0 |
| 2020-01 | 450 | 300 | 0.5 |
| 2020-02 | 300 | 800 | -0.63 |
| 2020-03 | 600 | 1200 | -0.5 |
| 2020-04 | 1220 | 800 | 0.53 |
+---------------+----------+-----------+--------+
8 rows selected (76.929 seconds)
其他案例
-- 建表
CREATE TABLE order_info
(
name string,
orderdate string,
cost string
);
-- 数据加载
INSERT INTO table order_info (name,orderdate,cost) VALUE ('jack','2020-01-01','10'),
('tony','2020-01-02','15'),
('jack','2020-02-03','23'),
('tony','2020-01-04','29'),
('jack','2020-01-05','46'),
('jack','2020-04-06','42'),
('tony','2020-01-07','50'),
('jack','2020-01-08','55'),
('mart','2020-04-08','62'),
('mart','2020-04-09','68'),
('neil','2020-05-10','12'),
('mart','2020-04-11','75'),
('neil','2020-06-12','80'),
('mart','2020-04-13','94');
SELECT name,
orderdate,
cost, --当前window内,当前行的前一行到后一行 金额总和
sum(cast(cost AS INT)) over(PARTITION BY name
ORDER BY orderdate DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS precedingFollow, --当前window内,当前行到最后行的金额总和
sum(cast(cost AS INT)) over(PARTITION BY name
ORDER BY orderdate DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS currentFollow, --当前window内,按照时间进行排序
row_number() OVER(PARTITION BY name
ORDER BY orderdate DESC) AS rank,--用户上次购买的时间
lag(orderdate,1,'查无结果') over(PARTITION BY name
ORDER BY orderdate) AS lastTime,--用户下一次购买的时间
lead(orderdate,1,'查无结果') over(PARTITION BY name
ORDER BY orderdate)AS nextTime,--用户上次购物金额
lag(cost,1,'查无结果')over(PARTITION BY name
ORDER BY orderdate) AS lastCost,--用户下次购物金额
lead(cost,1,'查无结果') OVER (PARTITION BY name
ORDER BY orderdate) AS nextCost,--用户上一次+这次的购物金额
sum(cast(cost AS INT)) over(PARTITION BY name
ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS lastCurrentCost,--用户每月购物金额
sum(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)
ORDER BY month(orderdate)) AS monthCost,--用户当月单词消费最大值
max(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)
ORDER BY orderdate) AS monthMaxCost,--用户当月单词消费最小值
min(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)
ORDER BY orderdate) as monthMinCost
FROM TEST.COSTITEM
间隔,最近两次间隔,登录间隔,出院间隔等等
select
user_name,
age,
in_hosp,
out_hosp,
datediff(in_hosp,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)) as days
from t_hosp;
扩展
一些优化思想
有时候,一个 SELECT 语句中包含多个窗口函数,它们的窗口定义(OVER 子句)可能相同、也可能不同。显然,对于相同的窗口,完全没必要再做一次分区和排序,我们可以将它们合并成一个 Window 算子。
那如何利用一次排序计算多个窗口函数呢?某些情况下,这是可能的。下面的例子如下:
ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,
AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales ...
虽然这 2 个窗口并非完全一致,但是 AVG(sales) 不关心分区内的顺序,完全可以复用 ROW_NUMBER() 的窗口,这里提供了一种方式,尽一切可能利用能够复用的机会。
窗口函数 VS. 聚合函数
从聚合这个意义上出发,似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是,它们之间的相似点也仅限于此了!这其中的关键区别在于:
窗口函数仅仅只会将结果附加到当前的结果上,它不会对已有的行或列做任何修改。而 Group By 的做法完全不同:对于各个 Group 它仅仅会保留一行聚合结果。
有的读者可能会问,加了窗口函数之后返回结果的顺序明显发生了变化,这不算一种修改吗?因为 SQL 及关系代数都是以 multi-set 为基础定义的,结果集本身并没有顺序可言,ORDER BY 仅仅是最终呈现结果的顺序。
另一方面,从逻辑语义上说,SELECT 语句的各个部分可以看作是按以下顺序“执行”的:
注意到窗口函数的求值仅仅位于 ORDER BY 之前,而位于 SQL 的绝大部分之后。这也和窗口函数只附加、不修改的语义是呼应的,结果集在此时已经确定好了,再依次计算窗口函数。