HiveSQL 常用函数说明

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。

 转换函数

CONCAT(col, col2…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。

第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。

如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

 炸裂函数

EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW

用法：lateral view  udtf(expression)  tableAlias  as  columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

 开窗函数

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：起点，unbounded preceding 表示从前面的起点， unbounded following 表示到后面的终点

LAG(col,n,default_val)：往前第n行数据

LEAD(col,n, default_val)：往后第n行数据

• 第一个参数为列名，
• 第二个参数为往后/前第n行（可选，默认为1），
• 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

  userid        ctime                url
  
  Peter     2015-10-12 01:10:00     url1
  Peter     2015-10-12 01:15:10     url2
  Peter     2015-10-12 01:16:40     url3
  Peter     2015-10-12 02:13:00     url4
  Peter     2015-10-12 03:14:30     url5
  Marry    2015-11-12 01:10:00     url1
  Marry    2015-11-12 01:15:10     url2
  Marry    2015-11-12 01:16:40     url3
  Marry    2015-11-12 02:13:00     url4
  Marry    2015-11-12 03:14:30    url5
  
  计算当前用户的第一次访问时间，以及上一次访问时间，和下一次访问时间
  
  select
      userid,url,ctime,
      first_value(ctime) over(partition by userid order by ctime) as first_ctime,
      lag(ctime,1) over(partition by userid order by ctime) as last_ctime,
      lead(ctime,1) over(partition by userid order by ctime) as next_ctime
  from ods_user_log;

   

NTILE(n)：把有序分区中的行 分发到指定数据 n 的组中，各个组有编号，编号从1开始，对于每一行，ntile 返回此行所属的组的编号。注意：n 必须为 int 类型。

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算