HiveSQL 整理

一、DDL

1、新增表字段

ALTER TABLE test.dim_project ADD COLUMNS(status bigint  COMMENT '状态');

2、常规操作

（1）字符串截取：

substr(from_unixtime(unix_timestamp()),1,10)

（2）字符串时间相互转换

① 固定日期转换成时间戳

select unix_timestamp('2016-08-16','yyyy-MM-dd') --1471276800
select unix_timestamp('20160816','yyyyMMdd') --1471276800
select unix_timestamp('2016-08-16T10:02:41Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --1471312961

16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
select from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))

② 时间戳转换程固定日期

select from_unixtime(1471276800,'yyyy-MM-dd') --2016-08-16
select from_unixtime(1471276800,'yyyyMMdd') --20160816
select from_unixtime(1471312961) -- 2016-08-16 10:02:41
select from_unixtime( unix_timestamp('20160816','yyyyMMdd'),'yyyy-MM-dd') --2016-08-16

日期字符串指定格式：
select date_format('2016-08-16','yyyyMMdd') --20160816

③ 返回日期时间字段中的日期部分

select to_date('2016-08-16 10:03:01') --2016-08-16
取当前时间
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
select from_unixtime(unix_timestamp(),'yyyy-MM-dd')
返回日期中的年
select year('2016-08-16 10:03:01') --2016
返回日期中的月
select month('2016-08-16 10:03:01') --8
返回日期中的日
select day('2016-08-16 10:03:01') --16
返回日期中的时
select hour('2016-08-16 10:03:01') --10
返回日期中的分
select minute('2016-08-16 10:03:01') --3
返回日期中的秒
select second('2016-08-16 10:03:01') --1

返回日期在当前的周数
select weekofyear('2016-08-16 10:03:01') --33

返回结束日期减去开始日期的天数
select datediff('2016-08-16','2016-08-11')

返回开始日期startdate增加days天后的日期
select date_add('2016-08-16',10)

返回开始日期startdate减少days天后的日期
select date_sub('2016-08-16',10)

返回当天三种方式
SELECT CURRENT_DATE;
--2017-06-15
SELECT CURRENT_TIMESTAMP;--返回时分秒
--2017-06-15 19:54:44
SELECT from_unixtime(unix_timestamp());
--2017-06-15 19:55:04
返回当前时间戳
Select current_timestamp--2018-06-18 10:37:53.278

返回当月的第一天
select trunc('2016-08-16','MM') --2016-08-01
返回当年的第一天
select trunc('2016-08-16','YEAR') --2016-01-01

④相差日期天数差  datediff()

计算时间差：SELECT datediff('2016-12-15','2016-12-08')， 前面的时间减去后面的时间。

二、数据处理  函数

1、COALESCE()     // [ˌkəʊəˈles] 结合；联合

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式

  -- 遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

使用场景：

1）假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数 

SELECT COALESCE(field_name,0) as value from table;

2）select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null。

使用实例：

比如我们要登记用户的电话，数据库中包含他的person_tel,home_tel,office_tel,我们只要取一个非空的就可以，则我们可以写查询语句

select COALESCE(person_tel,home_tel,office_tel) as contact_number from Contact；

1.1 类似函数：nvl()

desc function nvl 查看官方解释：
nvl(value,default_value) - Returns default value if value is null else returns value

【说明】nvl（expr1，expr2）： 若expr1为Null，则返回expr2，否则返回expr1。

注意：expr1和expr2的数据类型必须为相同类型。

【示例】

select nvl(null,0);
结果是0.

类似用法的函数还有：

1. select coalesce(name,0)
2. select id,if(name is null,0,name) from test;
3. select case name = null then 0 else name end;

三、高级函数

1、查看表信息

1.1  查看分区

show partitions test.tmp_relation_base

1.2 查看表描述

desc test.tmp_relation_base

1.3 查看建表信息

show create test.tmp_relation_base

4、Hive 的collect_set使用详解

1. 对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
2. 使用数字下标，可以直接访问数组中的元素； 

 select a,collect_set(b) as bb

from t

where b<='xxxxxx' 

group by a

会按照a分组 通过collect_set会把每个a所对应的b构建成一个以逗号分隔的数组返回。上述SQL返回：

a1,["b1","b2"]

a2,["b1","b2","b3","b4"] 

 可以按照这个返回的数组做文章，即为

select * from (select a,collect_set(b) as bb from t where b<='xxxxxx' group by a)  where size(tmp.bb)=1 and tmp.bb[0]='xxxxxxxx';

表示某bb所对应的数组长度为1 并且第一个bb为xxxxxxxx的a

示例：字段去重，并拼接成字符串

concat_ws(',' ,  collect_set(cast( pay_channel as string)) )

5、HIVE like和rlike

rlike是正则，正则的写法与java一样。'\'需要使用'\\',例如'\w'需要使用'\\w'

--NAME中包含数字的

select name from wlh_dm_sz_3 where rlike (name,'[0-9]') limit 100

select name from wlh_dm_sz_3 where name rlike '[0-9]' limit 100 结果相同

--NAME中以数字开头的

select name from wlh_dm_sz_3 where name rlike '^[0-9].*' limit 100

--NAME中包含A B C的

select name from wlh_dm_sz_3 where rlike (name,'1|2|3') limit 100

hive 中没有not like 而是用 not col_name like '%a%'                 not col_name rlike 'a' 

--多个条件

如查找name字段中含有 Li 和 Jin的信息

select * from emp where name rlike '.*(Li|Jin).*';

select '105,101' rlike '(105|106)' 

点号.：表示和任意的字符匹配

星号*：表示重复“左边的字符串”零次到无数次

表达式（x|y）：表示和x或者y匹配

6、ntile(5) over() 数据均等分割

insert overwrite table test._temp_in__0622_0722

select order_id,user_id,driver_id,create_time, '2020-11-20' as dt, ntile(5) over(order by create_time) as silce_id

from test.temp_0622_0722

where to_date(create_time) ='2020-11-20';

7、regexp_extract提取指定数据

示例：

① select  REGEXP_EXTRACT('DOM_10GB_mth','(10GB|mth)') 

输出：10GB

② select  REGEXP_EXTRACT('DOM_10B_mth','(10GB|mth)') 

输出：mth

正则匹配提取数字字母：
select regexp_extract('660BFE5A-F59A-407测试C-87B1-3949251D39EE','([a-zA-Z0-9:-]+)') 

四、复杂业务SQL

1.按某几个字段区分排序并取第一条

select * from (
    select *,row_number() over (partition by user_id order by dt desc) num 
    from dws.dws_user_fact 
    where dt between date_sub('2021-01-10' ,30 ) and '2021-01-10'
)tmp 
where num=1 

2.相邻两行/条数据做差：

Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

http://lxw1234.com/archives/2015/04/190.htm

3.hive中的lateral view 与 explode函数 联合使用：

将array类型字段 -> explode行转列，并组合原字段展示。 

hive中的lateral view 与 explode函数的使用_weixin_30648587的博客-CSDN博客

select user_id,exp_table.* from test.dwm_person_df f 
lateral view explode(user_list)  exp_table AS same_user_id
WHERE f.dt = '2021-01-01'

4.上下行做减

lag()

select 
    stat_date
    ,loc_num
    ,lag(loc_num) over ( order by  stat_date) t_1_num
    , loc_num - lag(loc_num) over ( order by  stat_date)  add_num 
    ,round((loc_num - lag(loc_num) over ( order by  stat_date) )/loc_num,2)  add_rate
from test.kafka_position_stat 
order by stat_date desc 
limit 100