SQL技巧高级系列①——窗口分析函数的使用

公众号: 数据小斑马,关注即可获得价值1000元的数据分析学习资料

SQL系列目录（文末有大奖赠送）：
SQL技巧初级系列①—建表/更新表/删除表
SQL技巧初级系列②—聚合和排序（group by,having,order by)
SQL技巧初级系列③——数据拼接（集合运算union和列连接join)
SQL技巧中级系列①——字符串函数的使用
SQL技巧中级系列②——日期函数的使用
SQL技巧高级系列②——聚合函数和CASE WHEN的使用
Hive Sql 性能优化——看这一篇就够啦！

平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了~
hive、oracle提供开窗函数，mysql不提供

group by 与over(partition by …)主要区别为，带上group by的hive sql语句只能显示与分组聚合相关的字段，而带上over(partition by …)的hive sql语句能显示所有字段.。

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化

首先介绍下Over子句中rows between,也叫做window子句(窗口子句）：
sum(gmv) over(order by gmv rows between unbounded preceding and current row)
如果不指定rows between，默认为从起点到当前行；
如果不指定order by，则将分组内所有值累加；
preceding:往前
following：往后
current rows：当前行
unbounded：起点
unbounded preceding：表示从前面的起点
unbounded following：表示到后面的终点
rows between unbounded preceding and current row 是最常用的定位框架，可以省略

1、sum(columns) over (partition by col1 order by col2 rows between n/unbounded preceding and m following/current row )
order by子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

2、ntile(2) over(partition by group order by gmv desc）
ntile(n),用于将分组数据按照顺序切分成N片，返回当前切片值。ntile不支持rows between，如果切片不均匀，默认增加第一个切片的分布。 NTILE这个很强大，以前要获取一定比例的数据是非常困难的，NTILE就是把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。

3、排序函数
rank(),生成数据项在分组中的排名，排名相等会在名次中留下空位。
row_number(),从1开始，按照顺序，生成分组内记录的序列
dense_rank(),生成数据项在分组中的排名，排名相等会在名词中不会留下空位。

4、 first_value(city) over(partition by group order by gmv desc/Asc) : 按分组排序后，取范围内第一个值
last_value,按分组排序后，取范围内，最后一个值，如果是用默认定义框架，则会出错

5、 lead(column,1,null) over(partition by group order by gmv desc)
lag(city,1,null) over(partition by group order by gmv desc)
如果要取排名前/后两位的城市，调整lag和lead第二个参数即可。第三个参数为默认值，可以省略，省略的话，默认取不到时是 NULL，如果不省略的话，取不到时则取指定默认值。

本人互联网数据分析师，目前已出Excel，SQL，Pandas，Matplotlib，Seaborn，机器学习，统计学，个性推荐，关联算法，工作总结系列。

微信搜索并关注 " 数据小斑马" 公众号，回复“sql”就可以免费领取下方sql必知必会、sql基础教程等9本sql入门到精通9本书籍