关于大数据:大数据开发Hive常用日期函数日期连续题sql套路

后面是罕用日期函数总结，前面是一道间断日期的 sql 题目及其解法套路。

1. 以后日期和工夫

select current_timestamp
-- 2020-12-05 19:16:29.284 

2. 获取以后日期，以后是 2020-12-05

SELECT current_date; 
## OR 
SELECT current_date(); 
-- 2020-12-05 

3. 获取 unix 零碎下的工夫戳

SELECT UNIX_TIMESTAMP();
-- 1524884881 

4. 以后是 2020-12-05

select substr(current_timestamp, 0, 10);
-- current_timestamp 

5. 以后是 2020-12-05

select date_sub(current_date, 1);
--2020-12-04 

6.yyyy-MM-dd HH:MM:ss 截取日期

select to_date("2017-10-22 10:10:10");
-- 2017-10-22 
select date_format("2017-10-22" "yyyy-MM")
-- 2017-10 

7. 两个日期之间的天数差

select datediff("2017-10-22", "2017-10-12");
-- 10

select datediff("2017-10-22 10:10:10", "2017-10-12 23:10:10");

-- 10

select datediff("2017-10-22 01:10:10", "2017-10-12 23:10:10");

-- 10 

8. 工夫截取

select from_unixtime(cast(substr("1504684212155", 0,10) as int)) dt;
-- 2017-09-06 15:50:12 

9. 工夫戳转日期

语法: to_date(string timestamp)

select to_date(from_unixtime(UNIX_TIMESTAMP()));
-- 2018-04-28

select FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd 10:30:00');

-- 2018-04-28 10:30:00

select concat(date_sub(current_date,1),'20:30:00');

-- 2018-04-27 20:30:00

-- hive version 1.2.0

select date_format(date_sub(current_date,1),'yyyy-MM-dd 20:30:00'); 

10. 日期减少

留神：原始日期格局只反对两种：yyyy-MM-dd yyyy-MM-dd HH:mm:ss

否则都须要 date_format 来转

date_add
next_day 

11. 附加题

有一个沉闷会员表，每天分区维度是会员 id，能够用 device_id 来代替，问怎么计算最近七天间断三天沉闷会员数，其中表 (dws.dws_member_start_day) 构造如下表（dt 是分区, 日期格局 yyyy-MM-dd，每个分区有惟一device_id）：

device_id             string                                                                      
dt                    string                

解法套路

1. 首先思考能够用到的日期函数 datediff, date_sub/date_add

2. 间断日期，间断问题都会用到一个排名函数，然而排名函数的值是数值，要与日期的连续性做到映射，才不便分组，比方能够把日期映射到间断数字，或者数字映射到间断日期，实现这两个的操作就是通过后面的 datedff 和 date_sub 组合，原理就是日期与日期相减即可失去间断整数，整数轻易与某个日期做相减即可失去间断的日期, 其中 date_sub 能够是反向排序失去间断日期。

3. 通过间断的排序日期或者排序 id 相减，而后分组即可解决此类问题

1. 在原表根底上减少一列排序序号

SELECT device_id,
       dt,
       row_number() over(PARTITION BY device_id
                         ORDER BY dt) ro
FROM dws.dws_member_start_day

2. 将序号转为间断日期，或者把日期转为间断数字，后成为 gid

-- 2.1 序号转为间断日期
SELECT device_id,
    dt,
    datediff(dt, date_add('2020-07-20', row_number() over(PARTITION BY device_id
        ORDER BY dt))) gid
FROM dws.dws_member_start_day 

-- 2.2 日期转为间断序号
SELECT device_id,
    dt,
    (datediff(dt, '2020-07-21') - row_number() over(PARTITION BY device_id
        ORDER BY dt)) gid
FROM dws.dws_member_start_day 

3. 分组筛选

SELECT device_id,count(1)
FROM
    (SELECT device_id,
        dt,
        datediff(dt, date_add('2020-07-20', row_number() over(PARTITION BY device_id
            ORDER BY dt))) gid
        FROM dws.dws_member_start_day
        WHERE datediff(dt, CURRENT_DATE) BETWEEN -7 AND 7 ) tmp
GROUP BY device_id,
    gid
HAVING count(1) < 3  

吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注