hive + tableau 计算留存率计算留存数 hive数据分析

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

文章图片

数据：用户访问记录表visit_table_name
字段：用户标识user，访问日期date
12abc，2021-03-29
【hive + tableau 计算留存率计算留存数】分析：
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,