Hive計算最大連續登陸天數

大資料技術派發表於2021-11-04
Hive

目錄

強哥說他發現了財富密碼，最近搞了一套股票演算法，其中有一點涉及到股票連續漲停天數的計算方法，我們都知道股票週末是不開市的，這裡有個斷層，需要一點技巧。我問是不是時間序列，他說我瞎扯，我也知道自己是瞎扯。問他方法，他竟然不告訴我，這麼多年的兄弟情誼算個屁。真當我沒他聰明嗎，哼！

靠人不如靠自己，我決定連夜研究一下在Hive裡面計算最大連續天數的計算方法。

一、背景

在網站平臺類業務需求中使用者的「最大登陸天數」，需求比較普遍。

原始資料：

u0001 2019-10-10
u0001 2019-10-11
u0001 2019-10-12
u0001 2019-10-14
u0001 2019-10-15
u0001 2019-10-17
u0001 2019-10-18
u0001 2019-10-19
u0001 2019-10-20
u0002 2019-10-20

說明：資料是簡化版，兩列分別是user_id,log_in_date。現實情況需要從採集資料經過去重，轉換得到以上形式資料。

我們先建表並且將資料匯入Hive：

create table test.user_log_1 (user_id string, log_in_date string) row format delimited fields terminated by ' ';

load data local inpath '/var/lib/hadoop-hdfs/data/user_log.txt' into table test.user_log_1 ;

檢視一下資料：

hive> select * from test.user_log_1 ;
OK
u0001	2019-10-10
u0001	2019-10-11
u0001	2019-10-12
u0001	2019-10-14
u0001	2019-10-15
u0001	2019-10-17
u0001	2019-10-18
u0001	2019-10-19
u0001	2019-10-20
u0002	2019-10-20
Time taken: 0.076 seconds, Fetched: 10 row(s)

二、演算法

核心是按訪問時間排序，登陸時間列減去排序後的序列號，得到一個日期值，按這個值分組計數即可。

1. 第一步：排序

按照user_id分組，並且按照日期log_in_date排序：

select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1;

結果：

u0001	2019-10-10	1
u0001	2019-10-11	2
u0001	2019-10-12	3
u0001	2019-10-14	4
u0001	2019-10-15	5
u0001	2019-10-17	6
u0001	2019-10-18	7
u0001	2019-10-19	8
u0001	2019-10-20	9

u0002	2019-10-20	1

這裡可以看出，u0001這個使用者最大連續登入天數是4天，使用後面計算方法計算後可以驗證。

2. 第二步：第二列與第三列做日期差值

可以看出規律，日期小的，行號也小；如果將日期跟行號做差值，連續登入的差值應該是一樣的。

select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m;

結果：

u0001	2019-10-09
u0001	2019-10-09
u0001	2019-10-09
u0001	2019-10-10
u0001	2019-10-10
u0001	2019-10-11
u0001	2019-10-11
u0001	2019-10-11
u0001	2019-10-11
u0002	2019-10-19

顯然可以看出，最大連續連續登入是4次。

3. 第三步:按第二列分組求和

select user_id, dts, count(1) num from (select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m)m2 group by user_id, dts;

結果：

u0001	2019-10-09	3
u0001	2019-10-10	2
u0001	2019-10-11	4
u0002	2019-10-19	1

4. 第四步：求最大次數

已經算出了，每個使用者連續登入天數序列，接下取每個使用者最大登入天數最大值即可：

select user_id, max(num) from (select user_id, dts, count(1) num from (select user_id, date_sub(log_in_date, rank) dts from (select user_id, log_in_date, row_number() over(partition by user_id order by log_in_date) as rank from test.user_log_1)m)m2 group by user_id, dts)m3 group by user_id;

結果跟我們的預期是一致的，使用者u0001最大登入天數是4。

u0001	4
u0002	1

三、擴充套件（股票最大漲停天數）

我們知道股票市場，比如我們們的A股，週末是不開盤的，那麼一隻股票如果上週五漲停，本週一接著漲停，這算是連續2天漲停，使用上面這種方法是不行的，使用lead函式試試：

select user_id, log_in_date, lead(log_in_date) over(partition by user_id order by log_in_date) end_date from test.user_log_1;

結果

u0001	2019-10-10	2019-10-11
u0001	2019-10-11	2019-10-12
u0001	2019-10-12	2019-10-14
u0001	2019-10-14	2019-10-15
u0001	2019-10-15	2019-10-17
u0001	2019-10-17	2019-10-18
u0001	2019-10-18	2019-10-19
u0001	2019-10-19	2019-10-20
u0001	2019-10-20	NULL
u0002	2019-10-20	NULL

哈哈，是不是有思路了。

思路：上面結果一共有3列，第一列是uid，通過lead函式，後面兩列都是日期，那麼兩列日期都取值週一到週五之間，也就是說資料裡面只有工作日日期，沒有周末的資料，可以提前過濾使得資料滿足，既然要連續，那麼：

  1. 如果第三列的日期，減去第二列的日期，差值等於1，顯然是連續的；
  2. 如果第三列的日期，減去第二列的日期，差值等於3，但是第三列日期是星期一，那麼也算是連續了；

以上兩種條件綜合，就能計算出股票的最大連續漲停天數了，你學廢了嗎。

猜你喜歡

HDFS的快照講解

Hadoop 資料遷移用法詳解

Hbase修復工具Hbck

數倉建模分層理論

一文搞懂Hive的資料儲存與壓縮

大資料元件重點學習這幾個

相關文章