SQL分析函数场景实例

分析函数

1. 用来排序的函数
   它和聚合函数的不同之处是对于每个组返回多行
   row_number() over() rank() over() dense_rank() over() first_value() over()

   语法:

   row_number() over([partition by xxx] [order by xxx]) 返回的是行信息，没有排名
   rank ( ) over([partition by xxx] [order by xxx]) 返回的相关等级不会跳跃
   dense_rank ( ) over([partition by xxx] [order by xxx]) 返回的返回的相关等级会跳跃

场景: 查询每个班的第一名的成绩

SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

Lag Lead

语法:

lag(exp_str,offset,defval) over(partion by ..order by …)
lead(exp_str,offset,defval) over(partion by ..order by …)
其中exp_str是字段名

Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）,默认值是1

Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

2. 聚合函数

   聚合函数对于每个组只返回一行
   count、max、min、sum、avg、Variance、Stddev
   
   Count 用来求有效数据的数量
   Max 用来求给定数据中最大的那一个数据
   Min    用来求给定数据中最小的那一个数据
   Avg    用来求给定数据的平均值
   Sum 用来求给定数据的总和
   Variance 用来求给定数据的标准差
   Stddev 用来求给定数据的方差
   median 主要用于统计整表或者分组情况下的中位数（限定参数为数值型或日期/时间型），忽略NULL值
   
   对于聚合函数，如果给定的值中存在空值的话，oracle将会直接忽略
   select count(*) from xxx
   
   对于聚合函数中可以使用distinct关键字来压缩重复值
   比如我们想统计总共有多少个部门的话
   我们如果写
   Select count(deptno) from emp;
   将会得到错误的结果。因为实际上有很多重复的值也被计算在内。为了找到正确的答案，你应该这样写。
   Select count(distinct deptno) from emp;
   

3. collect函数

collect_list collect_set

它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。

语法:

collect_list(column_name)
collect_set(column_name)

有的时候我们想根据A进行分组然后随便取出每个分组中的一个B，代入到这个实验中就是按照用户进行分组，然后随便拿出一个他看过的视频名称即可

collect_list(column_name)[0] 和取数组一样简单

###