来自一个大数据分析师的对sql窗口函数的认识。

DreamLife
277 阅读5分钟

1. 窗口函数有什么用？

1.1 简介

窗口函数叫法有很多，包括分析函数、开窗函数、分析窗口函数等。

故名思意，窗口函数用于为行定义一个窗口，它对于一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列

普通的聚合函数只能用来计算一行内的结果或把所有行聚合成一行结果，而窗口函数支持为每一行生成一个结果

1.2 需求常见

数据准备

create table tb_sale
 (
 sale_month integer not null,
 sale_amount integer not null
);

insert into tb_sale
values
    (1, 182),
    (2, 169),
    (3, 325),
    (4, 287),
    (5, 266),
    (6, 355),
    (7, 349),
    (8, 320),
    (9, 297),
    (10, 288),
    (11, 423),
    (12, 311);
  1. 查询1月到12月的累计销售额

-- 使用窗口函数，我们可以这样来做，以此大大降低了数据分析师的工作
select sale_month
	, sum(sale_amount) over(order by sale_month asc rows between unbounded preceding and current row) 
from tb_sale

针对于每一行的值进行累加该行之前的值，得到当前行的累计销售额

  1. 计算逐月环比（如：(二月 - 一月) / 一月）

    select sale_month
	, (prev_amonut - sale_amount ) / sale_amount as prev_amonut
 from (select sale_month
			, sale_amount
			, lag(sale_amount,1) over (order by sale_month asc) as prev_amonut
		 from tb_sale) as temp

    通过窗口函数取出下一行值作为另一列新的值。

2. 语法

2.1 窗口函数的基本语法结构

函数名([expr]) over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)

2.2 说明

窗口函数都是和分析函数一起使用：窗口函数+over

窗口函数包括：

  • 排名函数：
    • rank()：窗口分区内值的排名，相同值拥有相同的排名，排名不是连续的，例如有两个相同值的排名为1，则下一个值得排名为3
    • dense_rank()：窗口分区内值得排名，相同值拥有相同的排名，排名是连续的，例如有两个相同值得排名为1，则下一个值得排名为2
    • row_number()：窗口发了去内值得排名，相当于行号
    • percent_rank()：计算窗口分区内各行的百分比排名
  • 偏移函数：
    • lag(x,offset,default value) ：返回窗口分区内位于当前行上方第offset行的值，如果不存在行，则返回default value
    • lead(x,offset,default_value) ：返回窗口分区内位于当前行下方第offset行的值，如果不存在行，则返回default_value
    • nth_value(x,offset):返回窗口分区中第offset行的值
    • first_value() / last_value()：返回窗口分区当中第一行/最后一行的函数
  • 聚合函数：sum()、avg()、max()等

over关键字，用来指定函数执行的窗口范围，其中包含三个分析字句

  • 分区（partition by）子句：用于划分窗口分区，如果没有指定partition by子句，则整个查询与分析结果集作为一个窗口分区
  • 排序（order by）子句：用于对窗口分区内的行进行排序，可以和partition子句配合使用，也可以单独使用。如果没有partition 子句，数据范围则是整个表的数据行
  • 窗口（rows）子句：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口函数使用。rows元素在窗口分区内对行进一步限制。rows元素不适用于排名函数。

2.3 窗口范围

  • 取当前行和前面两行

    rows between 2 preceding and current row

  • 包括本行和之前所有行

    rows between unbounded preceding and current row

  • 包括本行和之后所有行

    rows between current row and unbounded following

  • 包括本行和前面三行

    rows between 3 preceding and current row

  • 从前面三行和下面一行，总共5行

    rows between 3 preceding and 1 following

  • 当 order by后面缺少窗口从句条件，窗口规范默认是

    rows between unbounded preceding and current row

  • 当 order by 和窗口从句都缺失，窗口规范默认是

    rows between unbounded preceding and unbounded following

说明

名词含义
preceding往前
following往后
current row当前行
unbounded起点（一般结合preceding、following使用）
unbounded preceding表示该窗口最前面的行（起点）
unbounded following表示该窗口最后面的行（终点）

3.rows between 和 range between 之间的区别

  1. ROWS：是物理行来进行窗口级别里再次进行范围选择的
  2. RANGE：是按逻辑行来进行窗口级别里再次进行范围选择的。RANGE时，相同行会被合并成同一条数据是否时相同行，是根据order by 排序时的结果决定的

4. 案例

数据准备


-- 插入4个部门
insert into `tb_dept` values 
    (10, '会计部', '北京'),
    (20, '研发部', '成都'),
    (30, '销售部', '重庆'),
    (40, '运维部', '深圳');

-- 创建员工表
create table `tb_emp`
(
`eno` int not null comment '员工编号',
`ename` varchar(20) not null comment '员工姓名',
`job` varchar(20) not null comment '员工职位',
`mgr` int comment '主管编号',
`sal` int not null comment '员工月薪',
`comm` int comment '每月补贴',
`dno` int not null comment '所在部门编号',
primary key (`eno`),
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
);

  1. 业务场景1：查询月薪排名4~6名的员工排名、姓名和月薪

    select * from(
select ename,sal,(dense_rank() over(order by sal desc)) as `order` from tb_emp) as a where `order` between 4 and 6;

    在此：为了解释rank() / dense_rank() / row_number() 函数之间的区别,我做了下列函数的变化

    select * from(
	select ename
		, sal
		, (dense_rank() over(order by sal desc)) as rk1
		, (rank() over(order by sal desc)) as rk2
		, (ROW_NUMBER() over(order by sal desc)) as rk3
	 from tb_emp) as a

    结果

数据图2.png

对上述例子，如果不用窗口函数，我们会怎么去思考呢，我是这样思考的，可以使用变量来累加。以此给每一行打一个标签

如：

-- 不用窗口函数 如何做:相当于row_number()效果
select ename
				, sal 
				, rk
	from (select ename
				, sal 
				, @x := @x + 1 as rk
			 from tb_emp
			order by sal desc) as t1,(select @x := 0) as t2
	where rk between 4 and 6


-- 做一个rank()的效果
select rk
	, ename
	, sal
	from (select ename
	, sal
	, (select count(*)
				from tb_emp as t2 
					where t1.sal < t2.sal) as rk 
from 
	tb_emp as t1) as temp 
where rk between 4 and 6;

  1. 查询每个部门月薪排前2名的员工姓名、月薪和部门编号

    使用窗口函数：

    select ename
	, sal 
	, dno
from (
	select ename
		, sal 
		, dno 
		, rank() over(partition by dno order by sal desc) as ranking
	from 
		tb_emp
) as temp where ranking <=2

    不使用窗口函数：采用计数法，通过子查询的方式，查出每个部门前2名的值，再排序输出

    select ename
	, sal
	, dno
 from tb_emp as t1 where 
	(select count(*) from tb_emp as t2 
		where t1.dno = t2.dno and t1.sal <= t2.sal) <=2
	order by 
		t1.dno asc, t1.sal desc
avatar
文章
阅读
粉丝