HiveSQL中date_sub的⽤法
语法：
VARCHAR DATE_SUB(VARCHAR startdate, INT days)
VARCHAR DATE_SUB(TIMESTAMP time, INT days)　
参数：
参数数据类型
startdate　VARCHAR
time TIMESTAMP
days INT
定义：返回startdate减去days天数的⽇期。返回VARCHAR类型的yyyy-MM-dd⽇期格式。若有参数为null或解析错误，返回null。
测试案例：
SELECT DATE_SUB(date1, 30) as var1,
DATE_SUB(TIMESTAMP '2017-10-15 23:00:00',30) as var2,
DATE_SUB(nullstr,30) as var3
FROM T1
测试结果：
var1(VARCHAR)var2(VARCHAR)var3(VARCHAR)
2017-09-152017-09-15null
场景案例：
-- 编写连续7天登录的总⼈数
t1表
Uid dt login_status(1登录成功,0异常)
1 2019-07-11 1
1 2019-07-1
2 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-1
3 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-1
4 1
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1
-- 建表
create table if not exists c_t1(
uid int,
dt string,
login_status int
)
row format delimited fields terminated by ' '
;
-- 导⼊数据
load data local inpath '/opt/' into table c_t1; -- 思路⼀
select
exists的用法uid,
dt，
row_number() over(distribute by uid sort by dt) rm from c_t1
where login_status = 1
;  t1
-- 思路⼆
select
t.uid,
date_sub(t1.) dt
from
(
select
uid,
dt
row_number() over(distribute by uid sort by dt) rm from c_t1
where login_status = 1
)t1
;
t2
-- 思路三
select
uid,
dt
from
(
select
t1.uid,
date_sub(t1.) dt
from
(
select
uid,
dt,
row_number() over(distribute by uid sort by dt) rm from c_t1
where login_status = 1
)t1
)t2
group by
uid,
dt
having
count(uid) > 7
;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。