留存率：
首次登錄算作當(dāng)天新增，第二天也登錄了算作一日留存。可以理解為，在10月1號登陸了。在10月2號也登陸了，那這個人就可以算是在1號留存
今日留存率 = （今日登錄且明天也登錄的用戶數(shù)） / 今日登錄的總用戶數(shù) * 100%
解決思路：
? 這類問題主要借助left join，根據(jù)原表的數(shù)據(jù)，先去找到每個用戶最先登錄的時間，這個就是通過對用戶group by，然后搜索date_format(min(login_ts),‘yyyy-MM-dd’)。這樣就得到一個表，第一列是用戶id，第二列就是每一個用戶第一次登錄的日期。
? 之后用這個新表，left join原表數(shù)據(jù)，但是這個條件不僅是id相等，還要有個t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,‘yyyy-MM-dd’),t1.first_login)=1
說實話，這個多條件join的還確實沒遇到過。這樣符合條件的就會被增加到后面，不符合的賦空值。【當(dāng)然也可以不多條件join，直接就是兩個表的user_id一樣，那這樣再計算新增人數(shù)和留存人數(shù)的時候，就不能直接通過count(列名字)來計算了，還需要去重和判斷天數(shù)關(guān)系】比如下圖這樣
? 這樣就可以根據(jù)first_login分組group by計算count（t1.id），就是每日新增人數(shù)，然后count(連接上的表的列信息)，就是后續(xù)一天也登陸的人數(shù)。
代碼：
select
t3.first_login,
t3.register,
t3.remain_1/t3.register retention
from
(
select
t1.first_login,
count(t1.user_id) register,
count(t2.user_id) remain_1
from
(
select
user_id,
date_format(min(login_ts),'yyyy-MM-dd') first_login
from
user_login_detail
group by
user_id
)t1
left join
user_login_detail t2
on
t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1
group by
t1.first_login
)t3
總結(jié)一下：
其實這類問題的關(guān)鍵在于，你要想辦法將每個用戶的最初登錄時間和第二天登錄時間這兩個信息，放到一行中。這就是先求出來初次登陸時間后，然后借助這個表進(jìn)行l(wèi)eft join，之后再此基礎(chǔ)上以最初登錄時間進(jìn)行分組group by，再用聚合函數(shù)即可。文章來源：http://www.zghlxwxcb.cn/news/detail-856881.html
但是還有一種思路，就是直接進(jìn)行開窗排序，然后用row_number排序，找到前兩名的日期，這個用where篩選，并且在查詢條件里面用max,和min聚合函數(shù)可以找到首日和第二日，第一個日期就是首日，第二個就是第二次登錄日期，只要看這個第二次登錄日期是不是首日的第二天就行文章來源地址http://www.zghlxwxcb.cn/news/detail-856881.html
上代碼：
SELECT
concat(round(sum(if(datediff(f2, f1) = 1, 1, 0)) / count(*) * 100, 1), '%') percentage
-- 注意round保留一位小數(shù)的用法
from(
select
user_id,
min(create_date) f1,
max(create_date) f2
from(
SELECT
user_id,
create_date,
row_number() over(partition by user_id order by create_date) num
from(
select
user_id,
create_date
FROM order_info
group by user_id,create_date
)t1
)t2
where num<=2
group by user_id
)t3
到了這里，關(guān)于Hive SQL必刷練習(xí)題：留存率問題（*****）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
