Hive 学习总结

作者: 大石兄 | 来源:发表于2018-12-22 14:23 被阅读10次

        这一周主要学习了 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 学习总结

        本文链接:https://www.haomeiwen.com/subject/fvyikqtx.html