HiveSQL中date

2023-11-17 13:05| 来源: 网络整理| 查看: 265

语法：

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-15 2017-09-15 null

场景案例：

-- 编写连续7天登录的总人数

t1表 Uid dt login_status(1登录成功,0异常) 1 2019-07-11 1 1 2019-07-12 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-13 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-14 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/datas/t1.txt' into table c_t1; -- 思路一 select 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,dt.rm) 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,t1.rm) 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 ;

【本文地址】

公司简介

联系我们