天景科技苑

这个屌丝很懒，什么也没留下！

热门标签

sql的窗口函数_sql窗口函数

作者：天景科技苑 | 2024-07-28 13:00:34

踩

sql窗口函数

1.窗口函数基本用法

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。用于计算基于组的聚合值、排名、取值。它和聚合函数的不同之处是：每个组返回多行，而聚合函数对于每个组返回一行。

1.1基本语法

<窗口函数> OVER([PARTITION BY <列清单>] [ORDER BY <排序列清单>] [ROWS BETWEEN 开始位置 AND 结束位置]) OVER()函数有PARTITION BY、ORDER BY、ROWS三个参数。这三个参数都是可选参数。如果三个参数都不使用，窗口大小是针对查询产生的所有数据。

PARTITION BY划分的范围被称为窗口（可以理解为分组）。如果缺省，则默认为所以数据为一组。
ORDER BY决定窗口数据的顺序，针对不同的分析函数有不同的作用。不指定rows时，聚合函数计算的范围是从有序组（有排序的优先级如时间等，如果排序和分组的字段相同，则范围还是分组大小）的第一行到当前行（）。如果缺省，则范围是全组。取值函数、排名函数则是正常用来排序，如果缺省，则是按默认顺序。
ROWS是用来指定窗口内数据的范围（限定分组后数据的范围，也称为window子句)。可以使用到聚合函数、取值函数，但是不能对排名函数使用。

在加入窗口函数的基础上SQL的执行顺序也会发生变化，具体的执行顺序如下（window就是窗口函数):

1.2 OVER（）

使用窗口函数，一般要用over开窗


/*over()没有参数，则默认为全部结果集*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over() sal_sum,
    CONCAT(ROUND(sal/SUM(sal) over()*100,2),'%') sal_percent
FROM
    employee

执行结果：

窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果集；

1.3 PARTITION BY

在over窗口中进行分区，对某列或多列进行分区统计，窗口的大小就是分区的大小


/*增加了PARTITION BY参数为deptno，统计金额从全部工资，变成了部门工资*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno) deptno_sal_sum,
    CONCAT(ROUND(sal/SUM(sal) over(PARTITION BY deptno)*100,2),'%') sal_percent
FROM
    employee

执行结果：

1.4 ORDER BY

对聚合函数来说，over() 没有order by 子句是结果是整个组的累加,有则是逐行累加（范围内的第一行到当前行）。对取值函数、排序函数来书over() 没有order by 子句则默认顺序，有则按排序字段排序。


/*聚合函数*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno ORDER BY sal) order_sal_sum,
    CONCAT(ROUND(sal/SUM(sal) over(PARTITION BY deptno ORDER BY sal)*100,2),'%') sal_percent
FROM
    employee

执行结果:


/*取值函数*/
/*无order by子句*/
SELECT
    deptno, 
    ename, 
    sal,
    lag(sal,1,0) over(PARTITION BY deptno ) order_sal_sum,
    CONCAT(ROUND(sal/lag(sal,1,0) over(PARTITION BY deptno )*100,2),'%') sal_percent
FROM
    employee
/*有order by子句*/
SELECT
    deptno, 
    ename, 
    sal,
    lag(sal,1,0) over(PARTITION BY deptno ORDER BY sal) order_sal_sum,
    CONCAT(ROUND(sal/lag(sal,1,0) over(PARTITION BY deptno ORDER BY sal)*100,2),'%') sal_percent
FROM
    employee

执行结果（无order by子句）：

执行结果（有order by子句）：

1.5 ROWS（window子句）

是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。 rows参数 我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行），常用该窗口来计算累加。

PRECEDING :往前 FOLLOWING：往后 CURRENT ROW ：当前 UNBOUNDED 起点（一般结合PRECEDING和FOLLOWING使用） UNBOUNDED PRECEDING 表示窗口最前面的行（起点） UNBOUNDED FOLLOWING表示窗口最后面的行（终点）

常用的窗口范围：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 窗口起点到当前行 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口当前行和当前行的上一行和下一行 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 窗口当前行到重点 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围内的所有数据

窗口数据图：

示例：


/*窗口起点到当前行*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sal_sum
FROM
    employee
/*等价默认的*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno ORDER BY sal) sal_sum
FROM
    employee

执行结果：


/*当前行和前一行、下一行*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND  1 FOLLOWING) sal_sum
FROM
    employee

执行结果：


/*当前行到终点*/
SELECT
    deptno, 
    ename, 
    sal,
    SUM(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN CURRENT ROW AND  UNBOUNDED FOLLOWING) sal_sum
FROM
    employee

执行结果:

2.序列函数

序列函数用于确定结果集中每一行的排名或分布情况，以便分析数据中每一行的相对位置。序列函数都不支持window子句。

2.1 row_number()、rank()、dense_rank()

row_number()。排名顺序增加不会重复；如1、2、3、4、… …
RANK()。排名相等会在名次中留下空位；如1、2、2、4、5、… …
DENSE_RANK()。排名相等会在名次中不会留下空位；如1、2、2、3、4、… …


 /*ROW_NUMBER()、RANK()、DENSE_RANK()的对比*/
    SELECT
        deptno, 
        ename, 
        sal,
        ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal ) `ROW_NUMBER`,
        RANK() over(PARTITION BY deptno ORDER BY sal) `RANK`,
        DENSE_RANK()   over(PARTITION BY deptno ORDER BY sal) `DENSE_RANK`
    FROM
    employee

执行结果：

2.3 ntile()

用来取一个分组内的一部分。把组内的数据平均成几堆，且将堆号分给每行。如果不能不均分，则各个堆最多相差一行数据。


/*NTILE(N)*/
	SELECT
		deptno, 
		ename, 
		sal,
		NTILE(3) over(PARTITION BY deptno ORDER BY sal ) `nitle`,
		ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal ) `ROW_NUMBER`,
		RANK() over(PARTITION BY deptno ORDER BY sal) `RANK`,
		DENSE_RANK()   over(PARTITION BY deptno ORDER BY sal) `DENSE_RANK`
	FROM
	employee

执行结果：

2.4 cume_dist()

cume_dist是指cume_dist小于等于当前值的行数/分组内总行数（如果order by排序为desc倒叙则理解为大于等于），比如，统计小于等于当前薪水的人数，所占总人数的比例。与order by 参数有关，不能缺省，缺省值都为1.


/*CUME_DIST() 小于(大于和升降序有关)等于当前行的行数/分组总行数*/
 
	/*CUME_DIST() 小于(大于和升降序有关)等于当前行的行数/分组总行数*/
	SELECT
		deptno, 
		ename, 
		sal,
		-- 升序
		CUME_DIST() over(PARTITION BY deptno  ORDER BY sal ASC ) `CUME_DIST_ASC`,
  	-- 降序
		CUME_DIST() over(PARTITION BY deptno  ORDER BY sal DESC ) `CUME_DIST_DESC`
	FROM
	employee
	ORDER BY 
	deptno ASC

执行结果：

2.5 percent_rank()

用于计算给定行在排序后结果集中的百分位排名（百分位数），以表示行在整个排序结果中的相对位置。常用来计算每个学生的分数在班级中的百分位排名。与order by 参数有关，不能缺省，缺省值都为0. 百分比：比此数据小的数据个数除以与此数据进行比较的数据个数总数。

姓名	分数	排名	百分比排名
小张	97	1	100%
小明	85	4	0%
小红	91	3	33.3%
小小	93	2	66.6%

以小张为例,来说明其百分比排名是如何计算出来的.表中比小张分数少的共有3人,与小张进行排名比较的共有3人(小张自己不计算在内),所以其百分比排名则是3/3*100%=100%,其意义则是小张比100%的人分数高.


/*	PERCENT_RANK()计算比当前行rank排名低的个数占其他人总个数的百分比*/
	SELECT
		deptno, 
		ename, 
		sal,
		RANK() over(PARTITION BY deptno ORDER BY sal) `RANK`,
		COUNT(*) over(PARTITION BY deptno) count,
		(RANK() over(PARTITION BY deptno ORDER BY sal)-1)/(COUNT(*) over(PARTITION BY deptno)-1) `PERCENT_RANK_0`,
		PERCENT_RANK() over(PARTITION BY deptno ORDER BY sal ASC )`PERCENT_RANK`
	FROM
	employee
	ORDER BY 
	deptno ASC

执行结果：

3.聚合函数

3.1 sum()、 avg()、min()、 max()

sum()函数窗口范围内数据的累加。
avg()函数窗口范围内数据的平均值。
min()函数窗口范围内数据的最小值。
max()函数窗口范围内数据的最大值。


/*取当前行及上下一行的和、平均值、最大值、最小值、总行数*/
	SELECT
		deptno, 
		ename, 
		sal,
		SUM(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) `SUM`,
		-- ROUND(X,D) 取小数
		ROUND(AVG(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ),1) 	 `AVG`,
		MIN(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) `MIN`,
		MAX(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) `MAX`,
		COUNT(*)  over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) `COUNT`
	FROM
	employee
	ORDER BY deptno,sal

执行结果：

4.取值函数

这类窗口函数用于在结果集中的每一行上执行某种操作，通常涉及到比较当前行与其他行的值。取值函数LAG()、LEAD()都不支持window子句,FIRST_VALUE()、LAST_VALUE（）支持window子句。

4.1 lag()、lead()、first_value、last_value

LAG(列,第N行,默认值)：上取第N行。只有列参数，缺省其他参数，默认上取一行。
LEAD(列,第N行,默认值)：下取第N行。只有列参数，缺省其他参数，默认下取取一行。
FIRST_VALUE(列)：取分组范围内的第一行。
LAST_VALUE(列)：取分组范围内的最后一行。


/*LAG(列,第N行,默认值)取上N行，LEAD(列,第N行,默认值)取下N行，FIRST_VALUE(列)取分组的第一行,LAST_VALUE(列) 取分组的最后一行,*/
	SELECT
		deptno, 
		ename, 
		sal,
		LAG(sal,2,NULL) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN current row AND UNBOUNDED FOLLOWING ) `LAG`,
		LEAD(sal,2,NULL) OVER(PARTITION BY deptno ORDER BY sal ) `LEAD`,
-- 		ROWS缺省,order by参数缺省时，rows默认范围时 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		FIRST_VALUE(sal) OVER(PARTITION BY deptno) `FIRST_VALUE`,
		LAST_VALUE(sal) OVER(PARTITION BY deptno ) `LAST_VALUE`,
-- 		ROWS缺省,且有order by参数时，rows默认范围时 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
		FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal ) `ORDER_FIRST_VALUE`,
		LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal ) `ORDER_LAST_VALUE`,
		FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) `ORDER_ROWS_FIRST_VALUE`,
		LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) `ORDER_ROWS_LAST_VALUE`
	FROM
	employee

执行结果：

参考教程：

窗口函数的基本使用_窗口函数用法_独影月下酌酒的博客-CSDN博客 Hive 之窗口函数_窗口函数over里面没有参数_梵圣的博客-CSDN博客

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/天景科技苑/article/detail/894267