hive + tableau 计算留存率 计算留存数

hive + tableau 计算留存率 计算留存数 hive计算留存
原文连接：https://segmentfault.com/a/1190000039736441
背景：需要统计APP近2年每月的留存数据
留存：12个月前下载了APP的用户，在接下来的12个月内有访问过APP的用户
需求图示（图是友盟统计）：

数据：用户访问记录表visit_table_name
字段：用户标识user，访问日期date
12abc，2021-03-29

分析：
1.统计第一次访问的月份

substr(date,1,7) 返回月份格式 如 2021-03-29 => 2021-03

SELECT user,min(substr(date,1,7)) AS first_month
FROM visit_table_name
WHERE date >= "2019-02-01" AND date < "2021-03-01"
GROUP BY user

2.统计用户每月访问情况

SELECT user,substr(date,1,7) AS visit_month
FROM visit_table_name
WHERE date >= "2019-02-01" AND date < "2021-03-01"
GROUP BY user,substr(date,1,7)

原文连接：https://segmentfault.com/a/1190000039736441

3.连接起来，月份相减

SELECT first_table.user,
       first_month,
       visit_month,
       (year(CONCAT(visit_month,"-01"))-year(CONCAT(first_month,"-01")))*12+(month(CONCAT(visit_month,"-01"))-month(CONCAT(first_month,"-01"))) AS gap_month
FROM
  (SELECT user,
          min(substr(date,1,7)) AS first_month
   FROM visit_table_name
   WHERE date >= "2019-02-01"
     AND date < "2021-03-01"
   GROUP BY user) AS first_table
LEFT JOIN
  (SELECT user,
       substr(date,1,7) AS visit_month
FROM visit_table_name
WHERE date >= "2019-02-01"
  AND date < "2021-03-01"
GROUP BY user,
         substr(date,1,7)) AS visit_table ON first_table.user = visit_table.user

查询出来结果，gap_month即用户访问间隔，如果用户第一次是20年2月访问，第二次在20年3月就会有个1gap,

效果展示利用Tableau BI工具

做出来的效果和下面类似

原文连接：https://segmentfault.com/a/1190000039736441