數(shù)據(jù)倉庫之拉鏈表(hive實現(xiàn))

預備知識：

了解hive窗口函數(shù)：LAG 和 LEAD

數(shù)據(jù)準備：

cookie1,2015-04-10 10:00:02,url2

cookie1,2015-04-10 10:00:00,url1

cookie1,2015-04-10 10:03:04,1url3

cookie1,2015-04-10 10:50:05,url6

cookie1,2015-04-10 11:00:00,url7

cookie1,2015-04-10 10:10:00,url4

cookie1,2015-04-10 10:50:01,url5

cookie2,2015-04-10 10:00:02,url22

cookie2,2015-04-10 10:00:00,url11

cookie2,2015-04-10 10:03:04,1url33

cookie2,2015-04-10 10:50:05,url66

cookie2,2015-04-10 11:00:00,url77

cookie2,2015-04-10 10:10:00,url44

cookie2,2015-04-10 10:50:01,url55

CREATE EXTERNAL TABLE lxw1234 (

cookieid string,

createtime string,? --頁面訪問時間

url STRING? ? ? --被訪問頁面

) ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

stored as textfile location '/tmp/lxw11/';

hive> select * from lxw1234;

OK

cookie1 2015-04-10 10:00:02? ? url2

cookie1 2015-04-10 10:00:00? ? url1

cookie1 2015-04-10 10:03:04? ? 1url3

cookie1 2015-04-10 10:50:05? ? url6

cookie1 2015-04-10 11:00:00? ? url7

cookie1 2015-04-10 10:10:00? ? url4

cookie1 2015-04-10 10:50:01? ? url5

cookie2 2015-04-10 10:00:02? ? url22

cookie2 2015-04-10 10:00:00? ? url11

cookie2 2015-04-10 10:03:04? ? 1url33

cookie2 2015-04-10 10:50:05? ? url66

cookie2 2015-04-10 11:00:00? ? url77

cookie2 2015-04-10 10:10:00? ? url44

cookie2 2015-04-10 10:50:01? ? url55

LAG

LAG(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往上第n行值

第一個參數(shù)為列名沾谜，第二個參數(shù)為往上第n行（可選，默認為1），第三個參數(shù)為默認值（當往上第n行為NULL時候，取默認值，如不指定椒涯，則為NULL）

SELECT cookieid,

createtime,

url,

ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,

LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time

FROM lxw1234;

cookieid createtime? ? ? ? ? ? url? ? rn? ? ? last_1_time? ? ? ? ? ? last_2_time

-------------------------------------------------------------------------------------------

cookie1 2015-04-10 10:00:00? ? url1? ? 1? ? ? 1970-01-01 00:00:00? ? NULL

cookie1 2015-04-10 10:00:02? ? url2? ? 2? ? ? 2015-04-10 10:00:00? ? NULL

cookie1 2015-04-10 10:03:04? ? 1url3? 3? ? ? 2015-04-10 10:00:02? ? 2015-04-10 10:00:00

cookie1 2015-04-10 10:10:00? ? url4? ? 4? ? ? 2015-04-10 10:03:04? ? 2015-04-10 10:00:02

cookie1 2015-04-10 10:50:01? ? url5? ? 5? ? ? 2015-04-10 10:10:00? ? 2015-04-10 10:03:04

cookie1 2015-04-10 10:50:05? ? url6? ? 6? ? ? 2015-04-10 10:50:01? ? 2015-04-10 10:10:00

cookie1 2015-04-10 11:00:00? ? url7? ? 7? ? ? 2015-04-10 10:50:05? ? 2015-04-10 10:50:01

cookie2 2015-04-10 10:00:00? ? url11? 1? ? ? 1970-01-01 00:00:00? ? NULL

cookie2 2015-04-10 10:00:02? ? url22? 2? ? ? 2015-04-10 10:00:00? ? NULL

cookie2 2015-04-10 10:03:04? ? 1url33? 3? ? ? 2015-04-10 10:00:02? ? 2015-04-10 10:00:00

cookie2 2015-04-10 10:10:00? ? url44? 4? ? ? 2015-04-10 10:03:04? ? 2015-04-10 10:00:02

cookie2 2015-04-10 10:50:01? ? url55? 5? ? ? 2015-04-10 10:10:00? ? 2015-04-10 10:03:04

cookie2 2015-04-10 10:50:05? ? url66? 6? ? ? 2015-04-10 10:50:01? ? 2015-04-10 10:10:00

cookie2 2015-04-10 11:00:00? ? url77? 7? ? ? 2015-04-10 10:50:05? ? 2015-04-10 10:50:01

last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'?

? ? ? ? ? ? cookie1第一行，往上1行為NULL,因此取默認值 1970-01-01 00:00:00

? ? ? ? ? ? cookie1第三行钾恢，往上1行值為第二行值，2015-04-10 10:00:02

? ? ? ? ? ? cookie1第六行吕喘，往上1行值為第五行值赘那，2015-04-10 10:50:01

last_2_time: 指定了往上第2行的值，為指定默認值

cookie1第一行氯质，往上2行為NULL

cookie1第二行募舟，往上2行為NULL

cookie1第四行，往上2行為第二行值闻察，2015-04-10 10:00:02

cookie1第七行拱礁，往上2行為第五行值，2015-04-10 10:50:01

LEAD

與LAG相反

LEAD(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往下第n行值

第一個參數(shù)為列名辕漂，第二個參數(shù)為往下第n行（可選呢灶，默認為1），第三個參數(shù)為默認值（當往下第n行為NULL時候钉嘹，取默認值鸯乃，如不指定，則為NULL）

SELECT cookieid,

createtime,

url,

ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,

LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time

FROM lxw1234;

cookieid createtime? ? ? ? ? ? url? ? rn? ? ? next_1_time? ? ? ? ? ? next_2_time

-------------------------------------------------------------------------------------------

cookie1 2015-04-10 10:00:00? ? url1? ? 1? ? ? 2015-04-10 10:00:02? ? 2015-04-10 10:03:04

cookie1 2015-04-10 10:00:02? ? url2? ? 2? ? ? 2015-04-10 10:03:04? ? 2015-04-10 10:10:00

cookie1 2015-04-10 10:03:04? ? 1url3? 3? ? ? 2015-04-10 10:10:00? ? 2015-04-10 10:50:01

cookie1 2015-04-10 10:10:00? ? url4? ? 4? ? ? 2015-04-10 10:50:01? ? 2015-04-10 10:50:05

cookie1 2015-04-10 10:50:01? ? url5? ? 5? ? ? 2015-04-10 10:50:05? ? 2015-04-10 11:00:00

cookie1 2015-04-10 10:50:05? ? url6? ? 6? ? ? 2015-04-10 11:00:00? ? NULL

cookie1 2015-04-10 11:00:00? ? url7? ? 7? ? ? 1970-01-01 00:00:00? ? NULL

cookie2 2015-04-10 10:00:00? ? url11? 1? ? ? 2015-04-10 10:00:02? ? 2015-04-10 10:03:04

cookie2 2015-04-10 10:00:02? ? url22? 2? ? ? 2015-04-10 10:03:04? ? 2015-04-10 10:10:00

cookie2 2015-04-10 10:03:04? ? 1url33? 3? ? ? 2015-04-10 10:10:00? ? 2015-04-10 10:50:01

cookie2 2015-04-10 10:10:00? ? url44? 4? ? ? 2015-04-10 10:50:01? ? 2015-04-10 10:50:05

cookie2 2015-04-10 10:50:01? ? url55? 5? ? ? 2015-04-10 10:50:05? ? 2015-04-10 11:00:00

cookie2 2015-04-10 10:50:05? ? url66? 6? ? ? 2015-04-10 11:00:00? ? NULL

cookie2 2015-04-10 11:00:00? ? url77? 7? ? ? 1970-01-01 00:00:00? ? NULL

--邏輯與LAG一樣跋涣，只不過LAG是往上缨睡，LEAD是往下鸟悴。

進入主題，hive實現(xiàn)拉鏈表示例：

-----目標表

create external table existing_time_series_table

(

primary_key string,?---業(yè)務主鍵（字段個數(shù)不限）

effective_dt bigint,?----開始日期

expired_dt bigint,??----失效日期

event_value string----業(yè)務員度量值

? )

? stored as parquet

? location?

? ?'hdfs://nameservice/it/ods/erp/existing_time_series_table';

?----增量結(jié)果集 ??

create external table new_time_series_table

(

primary_key string,---業(yè)務主鍵（字段個數(shù)不限）

effective_dt bigint,?----開始日期

event_value string----業(yè)務員度量值

? )

? stored as parquet

? location?

? ?'hdfs://nameservice/it/ods/erp/new_time_series_table';

-----邏輯實現(xiàn)：lead函數(shù)實現(xiàn)了取下個日期作為本記錄的失效日期

insert overwrite table?existing_time_series_table

?select primary_key,

? ? ? ? ?effective_dt,

?case

? ? ? ? ? ?when lead(effective_dt, 1, null)

? ? ? ? ? ? over(partition by primary_key order by effective_dt) is null then

? ? ? ? ? ? null

? ? ? ? ? ?else

? ? ? ? ? ? lead(effective_dt, 1, null)

? ? ? ? ? ? over(partition by primary_key order by effective_dt)

? ? ? ? ?end as expired_dt,

? ? ? ? ?event_value

? ? from (select primary_key, effective_dt, event_value

? ? ? ? ? ? from existing_time_series_table

? ? ? ? ? ?where expired_dt is null

? ? ? ? ? union all

? ? ? ? ? select primary_key, effective_dt, event_value

? ? ? ? ? ? from new_time_series_table) sub_1

? union all ??

-----歷史已經(jīng)失效的記錄

? select primary_key, effective_dt, expired_dt, event_value

? ? from existing_time_series_table

? ?where expired_dt is not null

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末奖年，一起剝皮案震驚了整個濱河市细诸，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌陋守，老刑警劉巖震贵，帶你破解...
沈念sama閱讀 211,042評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異水评，居然都是意外死亡猩系，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,996評論 2贊 384
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門中燥，熙熙樓的掌柜王于貴愁眉苦臉地迎上來蝙眶，“玉大人，你說我怎么就攤上這事褪那∮姆祝” “怎么了？”我有些...
開封第一講書人閱讀 156,674評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵博敬，是天一觀的道長友浸。經(jīng)常有香客問我，道長偏窝，這世上最難降的妖魔是什么收恢？我笑而不...
開封第一講書人閱讀 56,340評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮祭往，結(jié)果婚禮上伦意，老公的妹妹穿的比我還像新娘。我一直安慰自己硼补，他們只是感情好驮肉，可當我...
茶點故事閱讀 65,404評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著已骇，像睡著了一般离钝。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上褪储，一...
開封第一講書人閱讀 49,749評論 1贊 289
城市分裂傳說
那天卵渴，我揣著相機與錄音，去河邊找鬼鲤竹。笑死浪读，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播碘橘，決...
沈念sama閱讀 38,902評論 3贊 405
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼论泛，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了蛹屿？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,662評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤岩榆，失蹤者是張志新（化名）和其女友劉穎错负，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體勇边，經(jīng)...
沈念sama閱讀 44,110評論 1贊 303
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡犹撒，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 36,451評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了粒褒。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片识颊。...
茶點故事閱讀 38,577評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖奕坟，靈堂內(nèi)的尸體忽然破棺而出祥款，到底是詐尸還是另有隱情，我是刑警寧澤月杉，帶...
沈念sama閱讀 34,258評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布刃跛，位于F島的核電站，受9級特大地震影響苛萎，放射性物質(zhì)發(fā)生泄漏桨昙。R本人自食惡果不足惜际度，卻給世界環(huán)境...
茶點故事閱讀 39,848評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一啥容、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧怎披，春花似錦翘盖、人聲如沸桂塞。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,726評論 0贊 21
一樁弒父案馍驯，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽藐俺。三九已至，卻和暖如春泥彤，著一層夾襖步出監(jiān)牢的瞬間欲芹，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,952評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工吟吝，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留菱父，地道東北人。一個月前我還...
沈念sama閱讀 46,271評論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長得像浙宜，于是被迫代替她去往敵國和親官辽。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 43,452評論 2贊 348

數(shù)據(jù)倉庫之拉鏈表(hive實現(xiàn))

推薦閱讀更多精彩內(nèi)容