原創聲明：求連續90日每日的用戶留存率 -- 全網唯一

   業務端需要求連續90日每日的用戶留存率改怎麼計算呢？？？

   一： 本文采用MySQL8.0環境

  1: 建表數據
    CREATE TABLE `user_login` (
  `user_id` int NOT NULL,
  `login_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;   MySQL8.0  或者 hive 執行


表裡的數據如下：

user_id login_date
"1001" "2021-12-12"
"1002" "2021-12-12"
"1001" "2021-12-13"
"1001" "2021-12-14"
"1001" "2021-12-16"
"1002" "2021-12-16"
"1001" "2021-12-19"
"1002" "2021-12-17"
"1001" "2021-12-20"
"1002" "2021-12-14"
"1002" "2021-12-20"
"1003" "2021-12-12"
"1004" "2021-12-18"
"1005" "2021-12-20"
"1006" "2021-12-14"
"1007" "2021-12-14"
"1007" "2021-12-19"

 二：執行SQL

   select a.login_date 日期,
       datediff(b.login_date , a.login_date) as days,   
count(distinct if(datediff(a.login_date , a.login_date_min) = 0 ,a.user_id,null)) as 註冊用戶數,
count(distinct if(datediff(b.login_date , a.login_date) != 0  ,if(datediff(b.login_date , a.login_date) != 0 and a.user_id = b.user_id, b.user_id,null),
if(datediff(a.login_date , a.login_date_min) = 0  ,a.user_id,null))) as  days日留存用戶數
from (
select
user_id,login_date,case when rn =1 then login_date else null end login_date_min
from
(select
user_id,login_date,
row_number() over(partition by user_id order by login_date) rn
from
user_login
) tmp
)  a
left join user_login b on  b.login_date >=a.login_date  and datediff(b.login_date ,a.login_date) <=90
 
group by a.login_date,days
 having 註冊用戶數 != 0 
order by 日期,days asc

 三：執行結果

    日期        days   註冊用戶數   days日留存用戶數
2021-12-12  0         3           3
2021-12-12  1         3           1
2021-12-12  2 　　　　 3           2
2021-12-12  4         3           2
2021-12-12  5         3           1
2021-12-12  6         3           0
2021-12-12  7         3           1
2021-12-12  8         3           2
2021-12-14  0         2           2
2021-12-14  2         2           2
2021-12-14  3         2           1
2021-12-14  4         2           0
2021-12-14  5         2           2
2021-12-14  6         2           2
2021-12-18  0         1           1
2021-12-18  1         1           0
2021-12-18  2         1           0
2021-12-20  0         1           1

  參考博客：感謝大佬分享

  https://blog.csdn.net/qq_25002995/article/details/104928397?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-104928397-blog-120935637.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-104928397-blog-120935637.pc_relevant_default&utm_relevant_index=1