我的Hive学习之旅

Posted on

row number

作用 ： 返回这个Partition下的当前Row号，根据排序字段生成，无重复。

语法 ：row_number() over (partition by 字段a order by 计算项b desc ) rank

1
2
3
4
select seasonid, episodeid , 
row_number() over(partition by seasonid order by episodeid asc) as rank 
from tutor.dw_season_live_information a 
where dt = date_sub(current_date,1) limit 100

内置rank分析函数区别 ：

  • row_number:不管排名是否有相同的，都按照顺序1，2，3…..n
  • rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
  • dense_rank:排名相同的名次一样，且后面名次不跳跃

with cube & grouping sets & rollup

主要用于不能用简单group by语句处理需求的情况

  • grouping sets : 其后面跟括号里指定了零个或多个分组变量的聚合，然后产生这个维度下的聚合结果，再将每个结果UNION到一起，其实也就是相当于

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    select a,sum(xx) form table1 … group  by a 
    union 
    select b,sum(xx) form table1 … group  by b
    union 
    select sum(xx) form table1 …  group  by a,b 
    union 
    ...
    这个意思

    用法：
    select ga, gb, sum(val) from table
    group by ga, gb
    GROUPING SETS ((ga), (gb), ());

  • with cube : 对变量进行有/无的组合，如果有三个对象，就会产生2^3 = 8种聚合情况

    1
    2
    3
    4
    eg :
         cube (a,b,c) 
         equal 
         grouping sets( (a,b,c), (a,b), (a,c), (a),(b,c), (b), (c),  () )

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

    1
    2
    3
    4
    eg  : 
         rollup(a,b,c)
         equal 
         grouping sets( (a,b,c),(a,b),(a),() )

regexp_extract

字符串正则表达式解析函数

1
2
3
4
5
6
7
eg : 
    select regexp_extract(  'yfd-mkt-0207juzhanYY05-grp-123', 'yfd-mkt-([^\\-]+)-.*', 1) as keyfrom_type;    — res : 0207juzhanYY05
    
 0 : 表示把整个正则表达式对应的结果全部返回
 1 ：表示返回表达式中第一个()对应的结果
 依次类推...  
 相关函数 : regexp_replace()

get_json_object

作用 ： 从复杂json对象中提取元素

1
2
3
‘$’表示对Root对象

eg : get_json_object(t.info,'$.xx')

nvl

NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

时间处理 ：

一种思路 ： 可以把时间转化为一个整数，经过处理之后，再转化为日期格式。

常见hive时间转换函数

  • from_unixtime ： 日期函数UNIX时间戳转日期函数: from_unixtime语法: from_unixtime(bigint unixtime[, string format]),可以是识别到月

  • unix_timestamp(string date, string pattern) : 指定格式日期转UNIX时间戳函数，可以识别到月

    1
    2
    3
    4
    5
    6
    hive>   select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
    1323234063
    hive> select unix_timestamp('2011-12-07 13:05','yyyy-MM-dd HH:mm');
    1323234300
    hive> select unix_timestamp('2011-12','yyyy-MM');
    1322668800

  • date_format : 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    hive> select date_format('2015-04-08', 'y');
    2015
    hive> select date_format('2015-04-08', 'yyyy');
    2015
    hive> select date_format('2015-04-08', 'yyyy-MM');
    2015-04
    hive> select date_format('2015-04-08 10:10:01', 'yyyy-MM');
    2015-04
    hive> select date_format('2015-04-08', 'yyyy-MM-dd');
    2015-04-08

  • to_date

    1
    2
    3
    4
    5
    6
    hive>   select to_date('2011-12-08 10:03:01');
    2011-12-08
    hive> select to_date('2011-12-08');
    2011-12-08
    hive> select to_date('2011-12');
    NULL

  • date_sub | date_add 这个用法还是非常显而易见的