用户留存的计算

最新推荐文章于 2023-03-29 20:40:40 发布
最新推荐文章于 2023-03-29 20:40:40 发布
阅读量658 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/sinat_20660547/article/details/106801741

用户留存的计算，基本是老生常谈了。可是这么多年下来，关于留存的计算，真的是差异太大了。

最初版：

将每个用户的所有交易月份用group_concat()函数进行连接，把对应的结果导出到excel中，然后再用countifs来进行计算……

这个方法大概持续了半年左右，等到用户量大了之后，这个方法就不太好用了，特别是涉及到跨年的数据，好像也计算的不是特别准确。

第二版：

有天在对比这个月交易下个月流失的用户的时候，发现其实留存就是计算这批用户在后面月份中，有多少还有交易，所以出现了我这里也是巨复杂的第二版；

select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
select trans_month,count(*) from v2.merchant_trans_statis_monthly where sign_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
GROUP BY trans_month order by trans_month asc;
这个版本好复杂，每次也要跑很久并且要把结果贴到对应的地方去。

第三版：

select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 12 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 11 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 10 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 9 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 8 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 7 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 6 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 5 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 4 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m'))
union all
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 3 month),'%Y%m'));
select count(*) from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 month),'%Y%m')
and merchant_no in(select merchant_no from v2.merchant_trans_statis_monthly where trans_month=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 month),'%Y%m'));

复杂到我自己都不太想看了，大概是把前面每个独立的结果自动粘到了一起；

第四版：

select aa.*,a.a,b.a,c.a,d.a,
e.a,
f.a,
g.a,
h.a,
i.a,
j.a,
k.a,
l.a,
m.a,
n.a,
o.a,
p.a,
q.a from (select trans_month,count(*)a from merchant_trans_statis_monthly GROUP BY trans_month) aa,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201701) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)a,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201702) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)b,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201703) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)c,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201704) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)d,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201705) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)e,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201706) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)f,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201707) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)g,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201708) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)h,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201709) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)i,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201710) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)j,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201711) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)k,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201712) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)l,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201801) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)m,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201802) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)n,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201803) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)o,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201804) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)p,
(select trans_month,count(*)a from merchant_trans_statis_monthly a,
(select merchant_no from merchant_trans_statis_monthly where trans_month=201805) b where a.merchant_no=b.merchant_no
GROUP BY trans_month)q
where aa.trans_month=a.trans_month and a.trans_month=b.trans_month and b.trans_month=c.trans_month and
c.trans_month=d.trans_month and 
d.trans_month=e.trans_month and 
e.trans_month=f.trans_month and 
f.trans_month=g.trans_month and 
g.trans_month=h.trans_month and 
h.trans_month=i.trans_month and 
i.trans_month=j.trans_month and 
j.trans_month=k.trans_month and 
k.trans_month=l.trans_month and 
l.trans_month=m.trans_month and 
m.trans_month=n.trans_month and 
n.trans_month=o.trans_month and 
o.trans_month=p.trans_month and 
p.trans_month=q.trans_month ;

进步不够明显，但是比之前的看着要好一些了。

第五版：

select a.trans_month,b.trans_month,count(a.merchant_no) from merchant_trans_statis_monthly a,
merchant_trans_statis_monthly b
where a.merchant_no=b.merchant_no
and a.trans_month<=b.trans_month
GROUP BY a.trans_month,b.trans_month;

这个时候，才发现留存问题的精髓是什么。

第六版：

select a.trans_month,b.trans_month,count(*) from merchant_trans_statis_monthly a,
merchant_trans_statis_monthly b
where a.merchant_no=b.merchant_no and b.trans_month>=a.trans_month
and a.trans_month>=201901
GROUP BY a.trans_month,b.trans_month;

选定某个时间点之后的留存计算，样本太多了，计算量大，本地数据库伤不起。

第七版：

select c.orgnize_id,a.trans_month,b.trans_month,count(DISTINCT a.merchant_no) from merchant_trans_statis_monthly a,
merchant_trans_statis_monthly b,
(select DISTINCT merchant_no,orgnize_id from merchant) c
where a.merchant_no=b.merchant_no
and a.merchant_no=c.merchant_no
and a.trans_month>=201901 and b.trans_month>=a.trans_month
and b.trans_month=201908
GROUP BY c.orgnize_id,a.trans_month,b.trans_month;

算各组织的留存情况。这个数据有个问题，每次都要把对应月份的数据用巨复杂的excel公司处理之后，汇总归纳求平均值。逼得我又想办法找到了后面的版本。

第八版：

create table trans_remain_source as
select b.orgnize_id,b.trans_month tm1,c.trans_month tm2,count(DISTINCT b.merchant_no),count(DISTINCT c.merchant_no),
left(b.trans_month,4)*12+right(b.trans_month,2)-left(c.trans_month,4)*12-right(c.trans_month,2) '第N月' from 
merchant_trans_statis_monthly b,
merchant_trans_statis_monthly c
where b.merchant_no=c.merchant_no
and b.trans_month>=c.trans_month
GROUP BY 1,2,3;

-- 新增交易留存月度数据源
insert into trans_remain_source 
select b.orgnize_id,b.trans_month tm1,c.trans_month tm2,count(DISTINCT b.merchant_no),count(DISTINCT c.merchant_no),left(b.trans_month,4)*12+right(b.trans_month,2)-left(c.trans_month,4)*12-right(c.trans_month,2) from 
(select * from merchant_trans_statis_monthly where trans_month=202005)b,
(select * from merchant_trans_statis_monthly where trans_month>=201812)c
where b.merchant_no=c.merchant_no
and b.trans_month>=c.trans_month
GROUP BY 1,2,3;

 

select orgnize_id,avg(N),avg(if(`N+1`>0,`N+1`,null)),
avg(if(`N+2`>0,`N+2`,null)),
avg(if(`N+3`>0,`N+3`,null)),
avg(if(`N+4`>0,`N+4`,null)),
avg(if(`N+5`>0,`N+5`,null)),
avg(if(`N+6`>0,`N+6`,null)),
avg(if(`N+7`>0,`N+7`,null)),
avg(if(`N+8`>0,`N+8`,null)),
avg(if(`N+9`>0,`N+9`,null)),
avg(if(`N+10`>0,`N+10`,null)),
avg(if(`N+11`>0,`N+11`,null)),
avg(if(`N+12`>0,`N+12`,null)),
avg(if(`N+13`>0,`N+13`,null)),
avg(if(`N+14`>0,`N+14`,null)),
avg(if(`N+15`>0,`N+15`,null)),
avg(if(`N+16`>0,`N+16`,null))
from (select orgnize_id,tm2,1 'N',
sum(if(第N月=1,用户数,0))/sum(if(第N月=0,用户数,0))'N+1',
sum(if(第N月=2,用户数,0))/sum(if(第N月=0,用户数,0))'N+2',
sum(if(第N月=3,用户数,0))/sum(if(第N月=0,用户数,0))'N+3',
sum(if(第N月=4,用户数,0))/sum(if(第N月=0,用户数,0))'N+4',
sum(if(第N月=5,用户数,0))/sum(if(第N月=0,用户数,0))'N+5',
sum(if(第N月=6,用户数,0))/sum(if(第N月=0,用户数,0))'N+6',
sum(if(第N月=7,用户数,0))/sum(if(第N月=0,用户数,0))'N+7',
sum(if(第N月=8,用户数,0))/sum(if(第N月=0,用户数,0))'N+8',
sum(if(第N月=9,用户数,0))/sum(if(第N月=0,用户数,0))'N+9',
sum(if(第N月=10,用户数,0))/sum(if(第N月=0,用户数,0))'N+10',
sum(if(第N月=11,用户数,0))/sum(if(第N月=0,用户数,0))'N+11',
sum(if(第N月=12,用户数,0))/sum(if(第N月=0,用户数,0))'N+12',
sum(if(第N月=13,用户数,0))/sum(if(第N月=0,用户数,0))'N+13',
sum(if(第N月=14,用户数,0))/sum(if(第N月=0,用户数,0))'N+14',
sum(if(第N月=15,用户数,0))/sum(if(第N月=0,用户数,0))'N+15',
sum(if(第N月=16,用户数,0))/sum(if(第N月=0,用户数,0))'N+16'
from (select orgnize_id,tm2,第N月,用户数 from trans_remain_source where orgnize_id in(200010,100070,100090,300020,100080,110010,110030))a
where tm2>=201812
GROUP BY tm2,orgnize_id)a
GROUP BY orgnize_id;

目前这个版本，看着感觉还行，有一个问题是每个月要把汇总表更新，另外是要加多第n月的数据，但是对比之前已经优化了很多了，对于这个问题，还需要继续优化。

每一个问题，都是未完待续。每一个问题的结束，又是另一个问题的开端……

 

一种计算用户留存的方法
木东居士
11-10 7233
0x00 概述 用户留存分析是互联网时代常用的一种数据分析方法。而很多快速发展的公司并没有相应的方法论沉淀，这就导致了在计算用户留存的时候会出现下面的一些问题：1）用户留存的定义不明确，不同的研发有自己的理解；2）没有保留计算过程的中间表，数据可复用程度低；3）不同研发的开发习惯不同，导致计算过程和表设计不统一。 鉴于以上问题，本文将指出一种通用的用户留存定义，并提供通用的计算流程以及具体的表结构...
大数据开发中如何计算用户留存及SQL示例
一个8年大数据开发工程师的碎碎念
06-30 3053
留存是指用户在某一时间点（如注册日、首次使用日）后，在后续特定时间段内继续使用该产品的行为。留存率则是这些留存用户占初始用户的比例。留存率越高，说明产品越能吸引并保持用户的兴趣。评估产品吸引力：留存率高的产品通常意味着用户对产品有较高的满意度和忠诚度。指导产品优化：通过分析不同时间段的留存率，可以识别产品改进的方向，如用户体验、功能设计等。制定市场策略：根据留存数据，可以制定更有针对性的营销策略，提高用户粘性和活跃度。用户留存率是衡量产品健康度和用户粘性的重要指标。
如何高效计算用户留存率
Time flys
10-24 5283
如何高效计算用户留存率 简单介绍留存率的概念，说明数仓建设中对留存率计算的优化思路 什么是留存率 在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。 留存率就是留存用户与全部用户的比值，计算公式 留存率 = 留存用户数 / 用户数 * 100% 比如昨天来了100个人，今天这100个人里面的60个人又来了，那么留存率就是60%。 留存率...
php计算用户留存,留存率到底有几种计算方式
weixin_35049411的博客
03-19 663
对于留存率这个概念，相信对每一位游戏运营和分析人员并不陌生，甚至搜索“留存率”也会发现很多关于留存率的解释，留存率的定义，以及留存率的指标表现等等文章。实在是没有什么新意可言。但是笔者前两天和朋友讨论关于渠道对于产品评级标准的时候，发现渠道在制定评级数据指标时，对于留存的释义很少会考虑的周全，而作为CP，上线测试调试数据的时候，如果没有理解指标的真正含义，而是一味的追寻达到这个标准，往往弄巧成拙，...
sql留存用户率练习题
weixin_39669739的博客
07-20 1271
sql练习题 手机中的相机是深受大家喜爱的应用之一，现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据： 某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数） 指标定义 某日活跃用户数，某日活跃的去重用户数。 N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。 N日活跃留存率，N日留存用户数/某日活跃用户数 例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日
用户留存率计算.pdf
09-30
在提供的文件内容中，我们看到了一个通过MySQL数据库存储过程实现用户留存率计算的示例。首先，我们将通过分析存储过程中的各个部分来理解用户留存率的计算原理。 存储过程开始部分声明了一些变量，它们用于在存储...
MySql 练习- 留存率计算
m0_58963214的博客
08-18 597
最近开始每天抽空刷几道MySQL的题，还是要多实战练练，不然容易忘。思路分析3：计算留存率，其实就是count(device_id(1))/count(device_id)left join的on里加的and，左表的数据一直会在，右表里，不符合条件的部分会直接为null。题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。思路分析2：结合题目只要次日留存率，其实就是date(1)要比date往后延。left join的on里加的where，是对两表结合后的结果做筛选；...
数据留存的计算-sql boy必备
wenqi
04-19 2453
留存率是用户在某段时间内开始使用网站/应用（一般定义是注册），一段时间后，仍然使用的人被认作是留存用户。 留存率计算：留存率=登录用户数/新增用户数*100% 一般会计算如次日留存，三日留存，七日，30日等等 面试题： 手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据 用户 id：用户唯一标识； 应用名称：是手机中的某个应用，例如相机、微信、大众点评等。 启动时长：某一天中使用某应用多长时间（分钟）。 启动次数：某一天中启动了某应用多少次。 登陆时间：使用手机的日期
大厂面试题集锦：关于用户留存率的计算
it325的博客
08-13 1197
最近，好几个小伙伴都拿着关于用户留存的面试题来问我，所以今天单独开一篇文章讲一下留存问题。 首先看一下留存是什么，简单来说，我和你今天在一家超市购物了，明天我来购物了，你没来，那么我就是这个超市的留存用户。就是在设定的时间间隔都进行购物的客户，一般是计算七日留存，就是七天后谁来购物了。 接着我们看题目，我就直接上原图了： ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813222832771.jpg?x-oss-process=image/watermark,t
Excel 利用同期群分析计算新用户留存率
我的博客
03-23 2578
业务分析方法中常用的有同期群分析，本文使用Excel和MySQL，结合同期群计算新注册用户的留存率，并放在实际的应用场景中进行分析。
Excel 分析用户复购情况 嵌套数据透视
我的博客
03-29 5305
使用嵌套数据透视的方法，从多角度分析复购情况。提供excel和MySQL两种计算方法。
用户留存率详解与计算方法案例
热门推荐
caodongfang126的博客
10-19 2万+
留存率，越来越受到大家的关注，从网站用户到客户端产品，游戏产品，无线 APP 产品， 都非常重视这一指标，留存率成为衡量一个产品是否健康成长的重要指标之一。       留存率的“ 40 – 20 – 10 ”规则       Facebook 平台流传出留存率“ 40 – 20 – 10 ”规则，
用户留存率问题计算详解
浅笑古今的博客
04-30 4266
数据分析工作中经常会遇到计算用户留存的相关问题，例如我们现在遇到这样一个场景， 某APP,它的新增用户的次日留存、7日留存、30日留存为60%、30%、15%,请模拟出来，每天如果日新增5万用户，那么30天后，它的日活数会达到多少? 根据问题，我们已知次留，7留和月留，可以在excel里生成一个留存率预估模型。通过现有数据插入图表，然后点击图表，选择“添加元素”→“趋势线”→“指数”，针对...
app运营：用户留存率详解与计算案例
每天积累一点，一年后你会发现，自己变化很大
10-10 1万+
摘要: 文/BLUES留存率，越来越受到大家的关注，从网站用户到客户端产品，游戏产品，无线APP产品，都非常重视这一指标，留存率成为衡量一个产品是否健康成长的重要指标之一。留存率的“40–20–10”规则Facebook平台流传出留 ... 文/BLUES 留存率，越来越受到大家的关注，从网站用户到客户端产品，游戏产品，无线APP产品，都非常
用户留存（app）统计
woshihaiyong168的博客
04-17 3927
一个产品的用户留存关系到该产品是否健康的发展 实现效果： 表详情： 注册表：d_user_register201704  以月份分表  （uid 唯一） CREATE TABLE `d_user_register201704` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` bigint(20) NOT NULL DEFAULT
活跃用户数怎么计算_留存率计算
weixin_39640909的博客
12-10 3722
数据源：来自猴子老师的留存率分析教程链家面试题：如何分析留存率？​mp.weixin.qq.com【面试题】手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。用户id：用户唯一标识；应用名称：是手机中的某个应用，例如相机、微信、大众点评等。启动时长：某一天中使用某应用多长时间（分钟）。启动次数：某一天中启动了某应用多少次。登陆时间：使用手机的日期。例如...
要成为运营大牛的你，还在用Excel做数据？
pipimob的博客
06-26 2144
一个合格的好运营是数据驱动运营，而非运营驱动数据。数据分析作为运营最基础的一项技能，如何发挥出数据的价值？这些运营的数据和公式你都知道吗？基础数据每日注册并登陆的用户数（DNU: Daily New Users）每日登陆用户数（DAU: Daily Active Users）新登且只有一次会话的用户（DOSU: Daily One Session Users）7天内登陆用户数（WAU: Weekl...
mysql 计算留存
最新发布
03-18
1. **多日留存计算** 修改`INTERVAL`值可计算7日/30日留存： ```sql -- 7日留存 AND DATE(a.activity_date) = DATE_ADD(DATE(u.registration_date), INTERVAL 7 DAY) ``` 2. **周/月级留存** 使用`WEEK()`...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值