主要是工作中写Hive QL积累的一些经验= 。 =

窗口函数

利用Frame子句进行滚动计和

如果说需要针对每个商品统计滚动统计过去一周的总销量，可以在frame子句中指定分区范围：

1
2
3
partition by [partition clause]
order by [order clause]
rows between [frame_start] and [frame_end]

聚合函数

字符串拼接函数

MySQL里的聚合字符串拼接函数是group_concat，但是hive里面好像不能用，得用另一种方法来代替这个函数：

1
2
3
group_concat([distinct] 连接字段 [order by 排序字段 asc/desc ] [separator 分隔符])
=
concat_ws(',', collect_set(cast(连接字段 as string)))
  1. 因为concat_ws必须是字符串类型，所以需要用cast转换一下。
  2. collect相关函数是将分组后的某列转成数组返回，collect_set作用是把group by后的非分组字段去重‘收集’起来，而collect_list是不去重的。

    求分位数函数

    1
    2
    percentile_approx(col,p)
    col为待求列，p为分位数，p=2表示求二分位数

    多维分析子句

    grouping sets
    个人认为是group by的优化版本，grouping sets里面只能是group by的字段，适合多维统计，实际上就是在原来group by的结构中的增加了”全部”字段
    1
    2
    group by 一般是到最细粒度，grouping sets允许group by字段的任意组合，如果group by字段未出现，则统一为'null'值
    grouping sets(...)
    with cube
    1
    2
    3
    group by a,b,c with cube
    =
    group by a,b,c grouping sets ((a,b,c),(a,b),(b,c),(a,c),(c),())
    with rollup
    1
    2
    3
    group by a,b,c with rollup
    =
    group by a,b,c grouping sets((a,b,c),(a,b),(a),())

    时间函数

    next_day

    1
    2
    3
    4
    next_day(date,weekday)
    weekday可以是'MONDAY','TUESDAY'...
    比如周内数据汇总到周一那天，则可以用：
    group by next_day(date_sub(dt,7),'MON')

    日期加减法

    1
    在hive中是用date_sub/date_add(dt,8)表示