SQL 进阶： 窗口函数、行转列

1. SQL 语句的执行顺序

from ——> join——> on——> where ——> group by——>  avg, sum.... ——>  having  ——> select  ——> distinct  ——> order by ——> offset

 

2. 窗口函数

2.1 窗口函数说明

窗口函数，也可以被称为OLAP函数或分析函数，对数据库数据进行实时分析处理。窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。窗口函数和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

1 - Rank Function  (平常用的最多的一类）　　参考：这里

• Rank() Over()
• Row_Number() Over()
• Dense_Rank() Over()
• NTILE(N) Over()

2 - Aggregate Function

• Sum() Over()
• Count() Over()
• AVG() Over()
• MIN() Over()
• MAX() Over()

3 - Offset Function

• Lead()
• LAG()
• First_Value()
• Last_Value()
• Nth_Value()

4 - Distribution Function.

• PERCENT_RANK()
• CUME_DIST()
• PERCENT_COUNT()
• PERCENT_DISC()

窗口函数的作用：

1. topN问题：排序开窗函数+子查询

2. 组内比较问题：聚合开窗函数+子查询，也可以用关联子查询做

3. 移动平均问题：聚合开窗函数

窗口函数语法：

<窗口函数> over (partition by <用于分组的列名>
                     order by <用于排序的列名>)

窗口函数分为排序开窗函数【rank(),dense_rank(),row_number()】和聚合开窗函数【sum(),avg(),count(),max(),min()】。

窗口函数只写在select中，因为它是对where和group by子句处理后的结果进行操作。

 

2.2 排序函数

• rank() ：允许并列，占用下一个名次 ---- 1、1、3
• dense_rank()：允许并列，不占用下一个名次 ----- 1、1、2
• row_number()：连续编号，不考虑排名 ----1、2、3

• nfile()：（ 此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NFILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均，多出来的部分则依次加给第一组、第二组···直到分配完。）

  用途：将分区中的有序数据分为n个桶，记录桶号。

例子࿱