常用HIve函数

hive日期函数

- 1、hive取得当前日期时间：

-- 1.1) 取得当前日期：
select current_date();

-- 1.2) 取得当前日期时间：
select current_timestamp();

-- 1.3) hive取得当前时间戳：
select unix_timestamp();

-- 1.4) 时间戳转日期：
select from_unixtime(1517725479,'yyyy-MM-dd HH:dd:ss');

-- 1.5) 日期转unix时间戳：
select to_nuix_timestamp('2017-01-01 12:12:12','yyyy-MM-dd HH:dd:ss');

-- 1.6) hive取得当前时间：
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss');

-- 2、hive自动计算其他日期（昨天，今天）：
-- hive中日期加减函数：date_add(start_date,num_days)

-- 2.1) 取得昨天日期：
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);
select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
select date_format(date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);


-- 2.2) 取得明天日期：
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);

-- 2.3)hive取得两个日期之间差值（差值为天数）：
-- datediff(date1,date2):date1大于date2，返回值为正，否则，返回值为负。

select datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-10));
select datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),10));

-- 2.4) 字符串转时间（字符串必须为：yyyy-MM-dd格式）

select to_date('2017-01-01 12:12:12');

-- 2.5) 日期、时间戳、字符串类型格式化输出标准时间格式：

select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(),'yyyyMMdd');
select date_format('2017-01-01','yyyy-MM-dd HH:mm:ss');       --字符串必须满足yyyy-MM-dd格式

-- 2.6) utc时间转换:

select from_utc_timestamp(current_timestamp(),8);
select to_utc_timestamp(current_timestamp(),8);

Hive-SQL查询连续活跃登陆的用户

---连续活跃登陆的用户指至少连续2天都活跃登录的用户

---解决类似场景的问题
CREATE TABLE test5active(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';

INSERT INTO TABLE test5active VALUES 
('2019-02-11','user_1',23),('2019-02-11','user_2',19),
('2019-02-11','user_3',39),('2019-02-11','user_1',23),
('2019-02-11','user_3',39),('2019-02-11','user_1',23),
('2019-02-12','user_2',19),('2019-02-13','user_1',23),
('2019-02-15','user_2',19),('2019-02-16','user_2',19);

思路一:

1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4、按照id和日期分组并求和，筛选大于等于2的即为连续活跃登陆的用户。

第一步：用户登录日期去重

select DISTINCT dt,user_id from test5active;

 第二步：用row_number() over()函数计数


select 
t1.user_id,t1.dt,
row_number() over(partition by t1.user_id order by t1.dt) day_rank
from 
(
select DISTINCT dt,user_id from test5active
)t1;



 第三步：日期减去计数值得到结果


select
t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis
from 
(
select 
t1.user_id,t1.dt,
row_number() over(partition by t1.user_id order by t1.dt) day_rank
from 
(
select DISTINCT dt,user_id from test5active
)t1
)t2;



 第四步：根据id和结果分组并计算总和，大于等于2的即为连续登陆的用户，得到  用户id，开始日期，结束日期 ，连续登录天数


select 
t3.user_id,min(t3.dt),max(t3.dt),count(1)
from
(
select
t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis
from 
(
select 
t1.user_id,t1.dt,
row_number() over(partition by t1.user_id order by t1.dt) day_rank
from 
(
select DISTINCT dt,user_id from test5active
)t1
)t2
)t3 group by t3.user_id,t3.dis having count(1)>1;




 最后：连续登陆的用户


select distinct t4.user_id
from
(
select 
t3.user_id,min(t3.dt),max(t3.dt),count(1)
from
(
select
t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis
from 
(
select 
t1.user_id,t1.dt,
row_number() over(partition by t1.user_id order by t1.dt) day_rank
from 
(
select DISTINCT dt,user_id from test5active
)t1
)t2
)t3 group by t3.user_id,t3.dis having count(1)>1
)t4;

思路二：
使用lag(向前)或者lead(向后)
select 
user_id,t1.dt,
lead(t1.dt) over(partition by user_id order by t1.dt) as last_date_id
from 
(
select DISTINCT dt,user_id from test5active
)t1;

选出差异等于1的数据
select
distinct t2.user_id
from 
(
select 
user_id,t1.dt,
lead(t1.dt) over(partition by user_id order by t1.dt) as last_date_id
from 
(
select DISTINCT dt,user_id from test5active
)t1
)t2 where datediff(last_date_id,t2.dt)=1;

row_number()、rank()和dense_rank()的区别以及具体使用

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