HIve 常用函数

常用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:排名相同的名次一样,且后面名次不跳跃
上一篇:python之第三方库pendulum库:处理日期、时间等数据


下一篇:PAT (Basic Level) Practice (中文)--1009.说反话 Python3