这一周主要学习了 Hive 的一些基础知识，学习了多个 Hive 窗口函数，虽然感觉这些窗口函数没有实际的应用意义，但还是都了解了一下。

sum（）over（）

：可以实现在窗口中实现逐行累加

其他 avg、count、min、max 的用法一样

#要先有一个统计出每个月总额的表，这里就是 t_access_amount 表，如下图一

# partition by uid：根据uid 分组，order by month ：根据月份排序，

rows between unbounded preceding and current_row：选择无边界的前面的行和当前行之间的行，最后是求 sum 即和。得到下图二

# 是在窗口求和，而窗口的定义时按照 uid 分区 order by 排序得到的。得到一个字段

select uid，month，amount，

sum（amount）over（partition by uid order by month rows between unbounded preceding and current_row ） as accumulate from t_access_amount；

preceding：前面的，后来的，往序号变大的方向

following：往后

2 preceding ：表示前2行

3 following ：表示后3行

unbounded preceding：表示从第1行开始，从前面的起点

unbounded following：表示最后一行，从后面的终点

注意：使用 rows between 时，按order by 顺序编号（没有指定order by 会默认排序）需要左边是小编号右边是大编号

rows between unbounded following and current row 是错的，应该写作

rows between current row and unbounded following 是当前行到终点行

rows between current row and 1 preceding 也是错的，应该写作

rows between 1 preceding andcurrent row 表示前1行和当前行

select

   cookieid,

   createtime,

   pv,

# 得到显示的排序编号

row_number() over(partition by cookieid order by createtime) as rn,

# 从前面的起点到当前位置，这里是从分区最后一行到当前行的和

   sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,

# 和上面一样（加 order by 和不加效果不一样）

   sum(pv) over (partition by cookieid order by createtime) as pv2,

#省略了 rows betweent 窗口函数，表示分区的所有数据

   sum(pv) over (partition by cookieid) as pv3,

# 前面3行+当前后

   sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,

# 前面3行+当前行+后面1行

   sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,

# 当前行+后面所有行

   sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6

from cookie1;

注意：上面显示的排序结果不太对，所以看起来好像结果是相反的一样，实际上单独拿出来运行是对的，可以看以相同方式排序的 rn 编号。

2、row_numver（）over（）函数、NTILE、RANK、DENSE_RANK

注意：这上面这些都不支持使用 rows between 语句，row_number() 展示出来的排序会和查询字段的最后一个 over(order by)里面的顺序一样

：分组 TOPN，即可以分组后排序，便于找到最好的几条数据

eg：有如下数据，要查出每种性别中年龄最大的2条数据

1,18，a，male

2,19，b，male

3,22，c，female

4，16，d，female

5，30，e，male

6，26，f，female

分析：如果使用按性别分组，是可以分出两条数据，但是分组的缺点是只能查出聚合函数（聚合函数只能产生一组中的一个值）和分组依据。而这里是要求多条数据（2个）

# 正确操作，这里先 partition by sex字段分组，然后根据每一组的 age 字段降序排序。得到的序号 1,2,3 等取名为 rn ，然后通过 where 判断前两个就是结果

# rn 字段是一个分组标记序号，如下图是中间（select ...）括号的结果

select * from

（select id，age，sex，row_number( ) over（partition by sex order by age desc）as rn from t_user）tmp

where rn<3；

NTILE(n)

：用于将分组数据按照顺序切分成 n 片（不一定是平均），然后每一份都编号为1-n，这样就可以拿到想要那一份数据。如果切片不均匀，默认增加第一个切片的分布，例如，14 条记录切3片就切不好，就会切成 6、4、4，其中6那份编号为1。

注意：可以不指定 order by

select

  cookieid,

  createtime,

  pv,

  ntile(2) over (partition by cookieid order by createtime) as rn2, --分组内将数据分成2片

  ntile(3) over (partition by cookieid order by createtime) as rn3, --分组内将数据分成3片

  ntile(4) over (order by createtime) as rn4 --将所有数据分成4片

from cookie2

order by cookieid,createtime;

应用：

统计各个 cookieid，pv 数最多的前1/3天的数据

select * from(

select *,ntile(3) over(partition by cookieid order by pv desc) as sn3 from cookie2) a where a.sn3=1;

RANK()

：生成数据项再分组中的排名，排名相等会在名次中留下空位

dense_rank()

：生成数据项再分组中的排名，排名相等不会再名次中留下空位

注意：上面两者都需要指定 order by，不然排名都是1

select

  cookieid,

  createtime,

  pv,

  rank() over (partition by cookieid order by pv desc) as r1,

  dense_rank() over (partition by cookieid order by pv desc) as r2,

  row_number() over (partition by cookieid order by pv desc) as rn

from cookie.cookie2

where cookieid='cookie1';

根据上图，可知区别：

rank()：按顺序编号，相同分组排序有相同的排名，但是会占位，后面的排名隔一位，就是成绩一样名次相同，但是后面的名次得低两位。

dense_rank()：按顺序编号，相同分组排序有相同的排名，后面排名顺序不边就是有并列第几名的情况。

row_number() ：按顺序编号，不会有相同的编号，即使分组排序是相同的。

cume_dist

：小于等于当前值的行数/分组内总行数，注意这个要指定排序方式，不然全都是1

select

*,

# 对 pv 降序排序后，计算小于等于当前行 pv 值得行数占总行数得比分

cume_dist() over(order by pv) as rn1,

# 计算cookieid 分组内，小于等于当前行 pv 值的行数占总行数的比分

cume_dist() over(partition by cookieid order by pv) as rn2

from cookie3;

percent_rank

：分组内当前行的（rank 值-1）/（分组内总行数-1）

select

* ,

# 求得 rank 值

rank() over(order by pv) as r1,

# 求得总共有多少行，这里用 sum(1) 效果一样

count(1) over() as c1 ,

# 得到的结果其实就是 rank -1/count(1) -1

percent_rank() over(order by pv) as p1

from cookie3;

总结：排序、切片、编号、的都需要使用 order by，不然会全都是1，但是除了 row_number() 因为这个函数编号不重复且顺延，所以还是会有编号，但是不确定编号逻辑。

lag

：用于获得窗口内往上第n行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

# 就是用往上多少行的数据来替换当前行，可以为0，但是不能为负数

select

*,

row_number() over(partition by cookieid order by createtime) as r1,

lag(createtime,1) over(partition by cookieid order by createtime) as la1,

lag(createtime,2,'this is tidai') over(partition by cookieid order by createtime) as la2 from cookie4;

lead

：与lag 相反，用于获取窗口内往下第n 行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

select

   *,

   row_number() over(partition by cookieid order by createtime) as r1,

   lead(createtime,1) over(partition by cookieid order by createtime) as la1,

   lead(createtime,2,'this is tidai') over(partition by cookieid order by createtime) as la2 from cookie4;

last_value

：取分组内排序后，截止到当前行，最后一个值

first_value

：取分组内排序后，第一个值

select

*,

# 展示排序好像和最后一个 over 里面的 order by 有关

row_number() over(partition by cookieid order by createtime) as r1,

# 获取最后一个值，但是其实都还是自己，因为只到当前行

last_value(url) over(partition by cookieid order by createtime) as l1,

# 转换一下 order by 为desc，这样第一个就是之前的最后一个，可以避免 rn 为1的写法，但是如果有多个字段去重那还是取 rn=1 的方便些

first_value(url) over(partition by cookieid order by createtime desc ) as f1 from cookie4;

注意：使用窗口分析函数时，要特别注意 order by 的使用，如果使用的不恰当会导致统计的不是我们想要的。row_number() over() 的展示排序好像时根据最后一个字段的over（order by）来展示的。

grouping sets 、grouping__id、cube、rollup

这几个分析函数通常用于 olap 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如分时、分、天、月的 UV 数

grouping__id

：表示结果属于哪一个分组集合，注意中间是两个下划线

select

month ,

day,

count(distinct cookieid) as uv,

GROUPING__ID

from cookie5 group by month ,day

grouping sets(month,day)

order by GROUPING__ID;

第一列时按照 month 进行分组的，

第二列时按照 day 进行分组的

第三列时按照对应month、day 分组统计出来的结果

第四列 grouping__id 表示这一组结果属于哪个分组集合

注意：grouping sets 里面就是说明以什么分组，上面的group by 是指定可以进行组合的分组字段sets 里面的只能使用这里指定的字段，

如，这里 grouping sets(month , day) 表示分别根据 month、day 字段分组， grouping sets(month,day,(month,day)) 则表示分别根据 month、day、month和day 分组。

这里group by 和 grouping sets 可以搭配使用，不是这里的专属，并且这里也可以不用 groupind sets

select

  month ,

  day,

  count(distinct cookieid) as uv,

  GROUPING__ID

from cookie5 group by month ,day

grouping sets(month,day,(month,day))

order by GROUPING__ID;

# 上面的语句执行结果等价于下面的

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__IDFROM cookie5GROUP BY month

UNION ALL

SELECTNULL,day,COUNT(DISTINCT cookieid) AS uv,2 ASGROUPING__ID FROM cookie5GROUP BY day

UNION ALL

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 ASGROUPING__ID FROM cookie5GROUP BY month,day

cube

：根据group by 的维度的所有组合进行聚合，就是省略 grouping sets ，直接将group by 后面的字段以各种可能的形式分组，然后union all 得到结果。

select

month,

day,

count(distinct cookieid) as uv,

grouping__id

from cookie5

group by month,day with cube

order by grouping__id;

等价于下面的语句

SELECTNULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM cookie5 -- 不分组

UNION ALL

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5GROUP BY month --只对 month 分组

UNION ALL

SELECTNULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5GROUP BY day --只对 day 分组

UNION ALL

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5GROUP BY month,day -- 对 month、day 分组

rollup

：是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合

# 是cube 的子集，以 month 为主，保留 month、month+day、不指定分组三种情况

select

month,

day,

count(distinct cookieid) as uv ,

grouping__id

from cookie5

group by month,day with rollup

order by grouping__id;

上面可以实现一个叫上钻的效果：

月天的uv==》月的uv==》总的uv

Hive 学习总结