Hive-SQL

每天有多少用戶首次流失

select t4.aa,count(*)
from
(
select  t3.role_id role_id,min(t3.date1) aa
from
(
select t2.role_id role_id,t2.date1 date1,t2.date2 date2
from
(
select t1.role_id role_id,t1.date date1,lag(t1.date,1) over (partition by role_id) date2
from
(
select *,row_number() over (partition by role_id order by ta.date asc) as rn
from 
(select distinct role_id,split(time," ")[0] date
from g37.logoutrole
where date between 20160902 and 20161219) ta ) t1) t2
where int(datediff(t2.date2,t2.date1)) > 7 ) t3
group by t3.role_id
) t4
group by t4.aa

分位數(shù)

from(
select t1.date a,percentile(t1.a, array(0,0.25,0.5,0.75,1)) over (partition by t1.date) b
from
  (select date, role_id,sum(cast(int(fn.json(source, "$.num")) as bigint)) a
   from g37.currency_xunzhang
   where date between 20161108 and 20161115
     and int(fn.json(source,"$.code_op"))= 1
   group by date, role_id) t1
) t2

每天和每個月的去重登錄id量
SELECT
month(time),
date,
COUNT(DISTINCT role_id) AS uv,
GROUPING__ID
FROM g17_loginrole_utf8_log
where date >= 20161001
GROUP BY month(time), date
GROUPING SETS (month(time), date)

正則提取

select regexp_extract(source, "level=(\\d+)", 1),count(regexp_extract(source, "level=(\\d+)", 1))
from xxx
where date between 20160902 and 20160925
and key rlike "xxx"
group by regexp_extract(source, "level=(\\d+)", 1)

轉換時間格式

select sum(t1.pj), sum(t1.hp)
from
(
select fn.change_date_format(time, "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH"),regexp_extract(source, "game_server=(\\d+)", 1), regexp_extract(source, "game_uid=(\\d+)", 1), count(pingjia) as pj, sum(pingjia) as hp
from xxx
where date between 20160825 and 20160926
group by 
fn.change_date_format(time, "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH"),regexp_extract(source, "game_server=(\\d+)", 1), regexp_extract(source, "game_uid=(\\d+)",1)
  ) as t1
where t1.pj - t1.hp >= 10

連接字符串

select role_id,split(max(concat(time,"|",get_json_object(source,"$.left_count"))) ,"|")[1]
from xxx as aa
where date between 20160925 and 20160927
group by role_id

最值

select role_id,max(get_json_object(source,"$.left_count")) 
from xxx 
where get_json_object(source,"$.item_name") rlike "aaa" 
and date between 20160925 and 20160926 
group by role_id

時間排序帖池,取最后一條

select t3.server, t3.role_id, t3.cou, t2.cou
from
(
select server,
       role_id,
       max(int(get_json_object(source,"$.left_count"))) as cou
from xxx
where date between 20160915 and 20160926
  and get_json_object(source,"$.item_name") rlike "aaa"
group by server,
         role_id
         ) as t3
join

(
select t1.server, t1.role_id, int(get_json_object(t1.source,"$.left_count")) as cou from 
(
select * , ROW_NUMBER() over (partition by server, role_id ORDER BY time desc) as rn
from xxx
where date between 20160925 and 20160927
and get_json_object(source,"$.item_name") rlike "aaa"
  ) as t1 where t1.rn = 1  ) as t2
on t3.server = t2.server and t3.role_id = t2.role_id

where t3.cou = t2.cou

rlike正則(或)

select player
from xxx
where player rlike "重疊|疊加|沖突|觸發(fā)"
and date between 20161001 and 20161008

創(chuàng)建外部表

create external table table_name (
  name string,
  urs string,
  ccid int,
  uid int,
  hn int,
  role_id int
  )
  row format delimited fields terminated by "\t"
  stored as textfile
  location '/path/file/'

文件引用為表

insert overwrite directory 'hdfs:///path/path/sjsj'
select a.role_id,time,fn.json(source,"$.role_level") 
from xxx as a where date >=20160401 and date <=20160430
and fn.json(source,"$.reason") ="player-upgrade"
and fn.json(source,"$.type") ="lv"
and fn.json(source,"$.role_level")  in ('20','60')
and a.role_id in
(select server from xxx)
===
and a.role_id in
(select server from xxx)

流失天數(shù)

select t1.server,t1.account_id,t1.liushi,t1.cost,t2.role_id,t2.udid
from
(select server,account_id,(20161013-max(date)) liushi,max(int(fn.json(source, "$.total_cost"))) cost
from loginrole
where date between 20161006 and 20161013
 group by server,account_id
) t1
join
(
 select fn.json(source, "$.account_id") account_id,fn.json(source, "$.role_id") role_id,fn.json(source, "$.udid") udid,fn.json(source, "$.server") server
from createrole
where date between 20161006 and 20161012) t2
on t1.server = t2.server
where t1.account_id = t2.account_id

實際登錄天數(shù)

select t4.server,t4.account_id,count(cc)
from
 (
 select server,account_id,split(time, " ")[0] cc
 from loginrole
 where date between 20161006 and 20161015
 group by server,account_id,split(time, " ")[0]
  ) t4
  group by t4.server,t4.account_id

key是數(shù)組

select role_id,count(*)
from xxx
where fn.to_array(fn.json(source, "$.cost[0]"))[0] in ("490001", "490003")
and date between 20160902 and 20160916
group by role_id

SUBSTRING ( expression, start, length )

select distinct aa.account_id
from xxx aa
where date between 20161006 and 20161017
and int(aa.server)<=20000
and int(fn.json(source,"$.level"))>5
and concat(aa.account_id,"|",concat(substring(aa.date,1,4),"-",substring(aa.date,5,2),"-",substring(aa.date,7,2)))
in (
select concat(account_id,"|",date_add(concat(substring(date,1,4),"-",substring(date,5,2),"-",substring(date,7,2)),3)) host_iid_seven
from xxx
where date between 20161006 and 20161017
and int(server)<=20000
)
最后編輯于
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 序言:七十年代末坛吁,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌妖泄,老刑警劉巖,帶你破解...
    沈念sama閱讀 216,470評論 6 501
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件算灸,死亡現(xiàn)場離奇詭異癞志,居然都是意外死亡,警方通過查閱死者的電腦和手機登舞,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,393評論 3 392
  • 文/潘曉璐 我一進店門贰逾,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人菠秒,你說我怎么就攤上這事疙剑。” “怎么了践叠?”我有些...
    開封第一講書人閱讀 162,577評論 0 353
  • 文/不壞的土叔 我叫張陵言缤,是天一觀的道長。 經(jīng)常有香客問我禁灼,道長管挟,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 58,176評論 1 292
  • 正文 為了忘掉前任弄捕,我火速辦了婚禮僻孝,結果婚禮上导帝,老公的妹妹穿的比我還像新娘。我一直安慰自己穿铆,他們只是感情好您单,可當我...
    茶點故事閱讀 67,189評論 6 388
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著悴务,像睡著了一般睹限。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上讯檐,一...
    開封第一講書人閱讀 51,155評論 1 299
  • 那天羡疗,我揣著相機與錄音,去河邊找鬼别洪。 笑死叨恨,一個胖子當著我的面吹牛,可吹牛的內容都是我干的挖垛。 我是一名探鬼主播痒钝,決...
    沈念sama閱讀 40,041評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼痢毒!你這毒婦竟也來了送矩?” 一聲冷哼從身側響起,我...
    開封第一講書人閱讀 38,903評論 0 274
  • 序言:老撾萬榮一對情侶失蹤哪替,失蹤者是張志新(化名)和其女友劉穎栋荸,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體凭舶,經(jīng)...
    沈念sama閱讀 45,319評論 1 310
  • 正文 獨居荒郊野嶺守林人離奇死亡晌块,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 37,539評論 2 332
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了帅霜。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片匆背。...
    茶點故事閱讀 39,703評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖身冀,靈堂內的尸體忽然破棺而出钝尸,到底是詐尸還是另有隱情,我是刑警寧澤搂根,帶...
    沈念sama閱讀 35,417評論 5 343
  • 正文 年R本政府宣布珍促,位于F島的核電站,受9級特大地震影響兄墅,放射性物質發(fā)生泄漏。R本人自食惡果不足惜澳叉,卻給世界環(huán)境...
    茶點故事閱讀 41,013評論 3 325
  • 文/蒙蒙 一隙咸、第九天 我趴在偏房一處隱蔽的房頂上張望沐悦。 院中可真熱鬧,春花似錦五督、人聲如沸藏否。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,664評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽副签。三九已至,卻和暖如春基矮,著一層夾襖步出監(jiān)牢的瞬間淆储,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,818評論 1 269
  • 我被黑心中介騙來泰國打工家浇, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留本砰,地道東北人。 一個月前我還...
    沈念sama閱讀 47,711評論 2 368
  • 正文 我出身青樓钢悲,卻偏偏與公主長得像点额,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子莺琳,可洞房花燭夜當晚...
    茶點故事閱讀 44,601評論 2 353

推薦閱讀更多精彩內容