大資料開發-Hive-常用日期函式&&日期連續題sql套路

前面是常用日期函式總結，後面是一道連續日期的sql題目及其解法套路。

1.當前日期和時間

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 

吳邪，小三爺，混跡於後臺，大資料，人工智慧領域的小菜鳥。 更多請關注