开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： 网站流量日志分析--统计分析--分组 topN--row_number over 函数使用】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12231
网站流量日志分析--统计分析--分组 topN--row_number over 函数使用
1、要想解决分组 top n 问题，就会提到面向分析的数据库或者数据仓库中一个常见的函数叫做分组函数，在数据中可以在每个分组内按照指定的字段排序之后，依次打上步骤号标号，比如新增的字段 step，每个分组内可以指定按照所谓的字段排序依次打上标号，比如111排第一名，13排第二，下面这个六排第三，不管是否重复，只要排序好之后，就按照顺序依次打上标号。
在1点中重新打标号，89排名第一，
如果可以提供这样的能力，再取每个分组内最多的前三个前几个，加个条件即可，比如step小于等于3，解决这类问题的核心在不同的分组内找出组内的共性，不管是111还是89一定是组内的第一名，要找出第一名，跟数值无关，因此能够满足于在分组内进行操作，并且打标号的一系列问题，就是分组函数。
2、需求描述:统计每小时各来访 host 的产生的 pvs 数最多的前 N 个(topN)。
row number() 函数
语法: row number() over (partition by XXX order by xxx) rank, rank 为分组的别名，相当于新增一个字段为 rank。在 hive 中带 over 关键字的语句叫分组函数。
3、分组函数的关键是 sql 中带有 over 语句。
over (partition by XXX order by xxx)xxx都是具体的字段。
partition by 用于指定根据什幺字段迸行分組
order by 用于指定分組内根据什幺字段迸行排序(asc | desc )
排序时还可以控制排序的顺序
4、分组完之后还要结合具体的函数来表示，聚合函数是输入多行输出一行的，row number() 分组函数搭配over语句使用之后它可以在分好的每个组内根据排序的字段依次打上步骤号或者标号，返回构成一个新的字段，这个字段可以自己重新命名。
row_ number() over (partition by xxx order by xxx) 标号字段
row_ number()用于在分好的组内根据排序的字段依次不重复的打上步骤号返回构成一个新的字段，新目字段，就是标号字段。
后续可以根据返回的步骤号进行过滤查询从而满足分组统计功能。
符合需求，非常好用的row_ number()函数。
5、同样都是一，为什么排第六名，排第七名第八名，在生活中如果两个同学都考一百分，那么谁是第一名还是并列第一名，在 row_ number() 中它是完全没有考虑数据的重复性，它不管怎么排序，只会依次打标号。
6、比如有一个全班的学生表，根据性别进行分组，一个班的同学就会男的一组，女的一组，在每个分组内再根据年龄进行排序，男的排序，女的排序，控制正序道序，依次给每个成绩打上步骤号就可以过滤。
（1）partition by 用于分组，比方依照 sex 字段分组
（2）order by 用于分组内排序，比方依照 sex 分组后，组内按照 age 排序。
（3）排好序之后，为每个分组内每一 条分组记录从1开始返回-一个数字
（4）取组内某个数据，可以使用 where 表名. rank>x 之类的语法去取。
以下语句对每个小时内的来访 host 次数倒序排序标号:
select ref host, ref host cnts, concat (month. dav, hour),
Row_number ()
Over(partition byconcat (month, day, hour)orderby
ref host cnts desc) as od from dw pVS refererhost everyhour;
分组函数应用到数据中，重点要确定根据谁分组，根据谁排序，要统计每个小时内最多的前n个，所以分组应该跟 hour 相关，零点一组，一点一组，partition by 确定的是hour字段，每个小时内分组内根据来访的pv倒序排序，partition by 时间， order by 倒序，讲义中使用了 concat 拼接函数，把三个字段进行拼接排序，month, day, hour。本意上要根据小时 hour 进行排序，但是数据中根据 month, day, hour 进行排序，如果一天当中数据干净，month, day 没有任何意义，不管是零点钟还是一点钟还是两点钟都来自同一月同一天，分区函数和分区统计一样，只要保证分区中数据是干净的，都是十一月同一天，后面字段才会起决定作用，所以真正起决定作用是 hour，分组完根据来访的pvs倒序进行排序，返回构成一个新的字段 od。
7、复制在 hive 中执行，sql 执行比较慢，服务器把 sql 编译成 map 和样本执行，为了它在小数据的情况下本地执行快，建议打开智能本地模式，命令在参考资料中，打开参考资料，点击数据入库。
点击 ods-data-import.sql，里面有设置 hive 执行的模式自动本地，当满足条件的时候可以切换本地set hive . exec . mode . local . auto=true;
复制参数在 hive 终端进行执行，查看刚才的结果，进行设置，方便后面的计算。可以看到数据比较明显，数据中有一些人为的变成了2号，第一名89，再想取 top3加 where od 小于等于3，od出来的数只有321三个数，想统计来坊最少的前三个很简单，把正序反过来，pvs 倒序排序，按照正序排序前几个又变成相反的过程，所以理解它的本质和精髓非常的重要，这就是分组函数。
8、打开参考资料，在数据分析中有分组 topn 统计脚本。创建临时表，把结果保存出来，临时表叫 pvs_refhost_topn_everyhour 每个小时 topn，当中哪个小时的第几名，哪里来的，pvs 多少。
drop table dw_ pvs_ refhost_ topn_ everyhour ;
Create table dw_ pvs_ refhost_ topn_ everyhour (
hour string,
toporder string,
ref_ host string ,
ref_ host_ cnts string
) partitioned by (datestr string);
insert into table dw_ pvs_ refhost_ topn_ everyhour partition (datestr= '20181101')
select t.hour,t.od,t.ref_ host,t.ref_ host_ cnts from
(select ref_ host,ref_ host_ cnts , concat (month , day ,hour) as hour ,
row_ number() over (partition by concat (month , day, hour) order by ref_ host_ cnts desc) as od
from dw_ pvs_ refererhost_ everyhour) t where od<=3 ;
使用 insert 加 select 语句把数据插入到表中，做了一个嵌套查询，查询 pvs 表，根据的时间小时进行分组，根据来访的host做倒序排序，返回构成一个新的字段 od，基于表做过滤，od 小于等于3，最多前三个，把查询出来的结果，插入到创建的临时表中，核心的关键是 row_number() 分组函数，复制临时表，在 hive 终端执行，用 insert 语句把查询结果保存在临时表中，方便后面的使用，因为开启本地模式，所以速度会比较快。输入select*from dw_ pvs_refhost_topn_
everyhour);不加 limit 直接执行，可以看到每个小时的 top1-top3 来访的 host，pvs，非常方便，满足了业务的需求。
9、针对分组函数，有的资料也把它称为窗口函数，窗口函数的窗口就是熟悉的windows，举例，画一个表，操作表，要么查询一行当中选取某些资料，要么是查询多行做聚合，传统的操作方式是操作一行或操作多行，现在不一样，在操作表的同时会把数据按照某种规律进行分组，举例，对数据进行分组，第一组，第二组，第三组，第四组那，分组的规律可以自己控制，分完组之后在不同的组内进行操作，刚才的那些函数是作用于分组内，并排在一起的框框像一个并排的窗户，因此有的资料上把分组函数称之为窗口函数。
10、所谓的窗口函数是形象的描述分组函数的形式，作用于一段范围内的分析函数，先分组再分析。
