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博客

         
         专业网站制作公司  涿州网站制作多少钱  江门企业网站建设公司  国外平面设计网站大全  做一套网页设计多少钱  设计风格比较好的网站