HIVE常用函数

常用函数======================================================================
不等于 <> != 
空值 IS NULL
昨日日期：select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);
日期相减：select datediff('2019-03-12','2019-01-01');
第一个非null的值（忽略大小写）select coalesce(null,NUll,1,0);
判断函数：select if(1>0,"a","b");
case when 函数：select (case when 1>0 then "a" else "b" end);
长度：length("abc")
反转字符串：reverse("abc")
字符串连接：concat("a","b")
select length("abc"),reverse("abc"),concat("a","b"),concat_ws(",","a","b");   ==>>   | 3    | cba  | ab   | a,b  |  
select substring("abcdef",3),substring("abcdef",3,2);

字符串转为数字cast(id as double)

数组转为列：select explode(split("abtcdtef","t"));  ==>>  ab  cd  ef (结果是竖直的三行记录，而不是一行里面的数组)
大小写函数、去除首尾空格函数（注意中间的不会去除）：select upper("AbC"),lower("AbC"),trim(" A b C ");   ==>>   | ABC  | abc  | A b C  |
分割字符串取其中一部分：select split('abtcdtef','t')[1];

求某一列的中位数、分位数等：percentile_approx(up,0.5)

select percentile_approx(up,array(0.05,0.5,0.95)) from setup_cleanup;

查找函数（注意是从1开始，找不到返回）select find_in_set('ab','ef,ab,de');

聚合函数(注意sum/avg也可以配合distinct)：
count(col) count(distinct col)
sum(col) sum(distinct col)
avg(col) avg(distinct col)

多行收集为数组：collect_set()/collect_list()  去重/不去重

select type,concat_ws(',',collect_list(cast(qty as string))) from temp.x group by type;

a       1
a       2          a  1,2,6  
b       3   ==>>   b  3
c       5          c  5,3
a       6
c       3

窗口函数======================================================================

SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY pv ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---当前行+往后所有行  
FROM test1;

解释：
PARTITION BY 根据字段分区（相当于group by）
ORDER BY 在分区内排序
ROWS BETWEEN AND指定窗口范围
unbounded preceding 到上界为止
3 preceding 前3行
current row 当前行
1 following 下一行
unbounded following 到下界为止
如果不指定ROWS BETWEEN，默认统计窗口为从起点到当前行
不指定order by将会按原来记录的顺序放置，将会影响所有窗口包含的数据，容易导致大部分函数出现无意义的随机结果！！
查询中有多个排序的窗口函数时，最终记录的默认摆放顺序为最后一个窗口函数的排序，在本例中就是结果按ORDER BY pv的顺序显示。

sum+partition =sum() group by 直接就是每组的总值，没有窗口效果！
sum+partition+order 有窗口效果

ntile(n)：分组排序后每组切分成n片，返回当前行数据在本组的第几个切片（在此基础上筛选可以得到当前组中前1/n的数据、后1/n的数据之类的）
排名函数：分组排序后当前行的排名
rank      ：1,2,3,3,5,5,7
dense_rank：1,2,3,3,4,5,6
row_number：1,2,3,4,5,6,7（常用！！）

lag(col,n,default)：分组排序后当前组内当前行前面第n行的col字段的数据，没有则返回默认值，默认值可以不设置
lead(col,n,default)： 分组排序后当前组内当前行后面第n行的col字段的数据，没有则返回默认值，默认值可以不设置，

first_value(col) ：分组排序后当前组内第一行记录的col字段的值！！（求日期最早的记录的消费！！） 
last_value(col) ：分组排序后当前组内最后一行记录的col字段的值！！

 

查询优化======================================================================

拆分查询、分区、分桶