用户留存的计算

2023-11-15

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

最初版：

将每个用户的所有交易月份用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月的数据，但是对比之前已经优化了很多了，对于这个问题，还需要继续优化。

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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

用户留存的计算的相关文章

MySQL正则表达式：如何将字符串中的数字与\d匹配？

我有一个专栏release date它以字符串格式存储日期不是 DATETIME 格式因为它们有时可以是任何其他字符串文字我想根据给定的月份和年份查找任意日期的所有记录尝试遵循但对我不起作用 gt Post find all con
MySql 视图脚本中的注释

可以这样做吗我尝试过多个 gui mysql workbench navicat toad for mysql 但没有一个保存这样的注释 something important select something else importan
在mysql连接字符串中添加应用程序名称/程序名称[关闭]

Closed 这个问题需要细节或清晰度 help closed questions 目前不接受答案我正在寻找一种解决方案在连接字符串中添加应用程序名称或程序名称以便它在 MySQL Workbench 中的客户端连接下可见 SQL
如何为 MySQL 中的字段或列添加别名？

我正在尝试做这样的事情但我收到未知的列错误 SELECT SUM field1 field2 AS col1 col1 field3 AS col3 from core 基本上我只想使用别名这样我就不需要执行之前执行的操作这在mys
mysql 不带空字符串和 NULL 的不同值

如何检索没有空字符串值和NULL值的mysql不同值 SELECT DISTINCT CON EMAILADDRESS AS E MAIL FROM STUDENT INNER JOIN CONTACT CON ON STUDENT CON
MySQL NOT IN 来自同一个表中的另一列

我想运行 mysql 查询来选择表中的所有行films其中的值title该列不存在于另一列的所有值中的任何位置 collection 这是我的表格的简化版本其中包含内容 mysql gt select from films id titl
Google Cloud SQL 上的故障转移如何运作？

我打算将 PHP 应用程序从 Google Cloud Platform 外部的服务器连接到 Google Cloud SQL 我想知道如何设计应用程序以正确地对其数据库进行故障转移根据manual https cloud googl
拥有更多列或更多行会更高效吗？

我目前正在重新设计一个可能包含大量数据的数据库我可以选择在数据库中包含许多不同的列或使用大量行如果我在下面做一些大纲可能会更容易 item id user id title description content category t
从表中选择行，其中另一个表中具有相同 id 的行在另一列中具有特定值

在 MySQL 中如果我们有两个表 comments key value 1 foo 2 bar 3 foobar 4 barfoo and meta comment key value 1 1 2 1 3 2 4 1 我想得到来自以下人
MySQL如何获取unix时间戳的时间差

我有一个保存值1506947452的变量需要使用公式从该日期提取分钟 started data now date 但started date采用unix时间戳格式10位int数字我以ajax形式收到并需要放入mysql查询i试试这个 S
奇怪的 MySQL Python mod_wsgi 无法连接到 'localhost' (49) 上的 MySQL 服务器问题

StackOverflow上也有类似的问题但我还没有发现完全相同的情况这是在使用 MySQL 的 OS X Leopard 机器上一些起始信息 MySQL Server version 5 1 30 Apache 2 2 13 Uni
查询为空 Node Js Sequelize

我正在尝试更新 Node js 应用程序中的数据我和邮递员测试过我的开发步骤是从数据库 MySQL 获取ID为10的数据进行更新 gt gt 未处理的拒绝SequelizeDatabaseError 查询为空我认识到我使用了错误的
无法从我的 java 应用程序访问 mysql 数据库（在 AWS 实例上运行）

我有一个在 AWS EC2 Ubuntu 实例上运行的 xampp 服务器并且那里有一个名为 androiddb 的数据库我可以通过在导航器中写入来访问数据库http public ip phpmyadmin http public i
限制分页页数

objConnect mysql connect localhost root or die mysql error objDB mysql select db Test strSQL SELECT FROM UserAddedRecord
MySQL小写自动转换

我有多个在数据库表中写入数据的 Web 服务我想针对特定字段自动将大写字符串转换为小写字符串 mysql 有没有执行此任务的函数假设这是表 id name language 有时在语言字段内 Web 服务会写入大写字符串 IT 我想直
使用php插入sql数据库时出错

我有一个带有 MySQL 插入查询的程序 sql INSERT INTO people person id name username password email salt VALUES person id name username p
如何检查一个值是否已经存在以避免重复？

我有一个 URL 表但我不想要任何重复的 URL 如何使用 PHP MySQL 检查给定 URL 是否已在表中如果您不想重复可以执行以下操作添加唯一性约束 use REPLACE http dev mysql com doc ref
连接 3 三张表

我有这个图表应该可以解释我的情况我需要一些关于连接 3 个表的帮助我不知道如何做这种事情因此我可以通过执行以下操作来经历一段检索记录的 while 循环 img src alt Album AlbumID 使用内部联接 http w
您可以使用 MySQL 查询来完整创建数据库的副本吗

我有一个包含 5 个表的 MySQL 数据库的实时版本和一个测试版本我不断使用 phpMyAdmin 将实时版本中的每个表复制到测试版本有谁有mysql查询语句来制作数据库的完整副本吗查询字符串需要考虑结构数据自动增量值以及与需要
MYSQL 按喜欢/不喜欢和受欢迎程度排序

我有评论表其中包括喜欢和不喜欢的内容现在我在正确的顺序上遇到了问题实际上我的系统在顶部显示了最多点赞的评论我正在 youtube 上寻找类似系统的东西这意味着 100like 100dislikes 的评论的顺序高于 1 1 我

随机推荐

每次都忘记：vscode把空格替换成换行符并换行显示

勾选正则表达式空格直接打换行用 n
Cocos2d-x简单游戏<捕鱼达人>代码实现

这个简单的捕鱼游戏Demo只是完成了简单的 1 场景切换数据加载武器等级更换 lt 大炮的升级 gt 2 鱼的随机游动大炮发射子弹撒网捕鱼 3 子弹鱼网的碰撞检测等 4 场景及背景音乐的定时更换碰撞时得音效仅供参考入门练
100m光纤测速多少正常_光纤收发器的六个指示灯代表是什么意思？

对光纤收发器这块了解的朋友应该知道光纤收发器有6个LED指示灯它们分别显示了收发器的工作状态根据LED所示我们就能判断出收发器是否工作正常和可能有什么问题从而能帮助找出故障那么光纤收发器的六个指示灯分别代表什么意思有哪些作用
2015-2023年全球医疗器械公司100强（附细分领域排行）

目录 2023全球医疗器械企业100强榜单 12家中国企业上榜 2022年度全球医疗器械公司100强排行榜 2021年全球医疗器械百强排行榜 2020年全球医疗器械企业100强公布年份待定 2020全球医疗器械100强 2019年度全球医
JWT原理解析与实现

1 Token与Session优缺点概述 1 1 Session的由来在登录一个网站进行访问时由于HTTP协议是无状态的就是说一次HTTP请求后他就会被销毁比如我在www a com login里面登录了然后你就要访问别的了比如要访问
PicoDet论文译读笔记

PP PicoDet A Better Real Time Object Detector on Mobile Devices 摘要在目标检测中如何实现更好的精度速度均衡是一个具有挑战性的问题在本文中作者致力于目标检测中关键组件的优
yolov6论文翻译

摘要多年来 YOLO系列一直是高效物体检测的事实上的工业级标准 YOLO社区以压倒性的优势丰富了它在众多硬件平台和丰富场景中的应用在这份技术报告中我们努力把它的极限推到一个新的水平以坚定不移的心态向行业应用迈进考虑到现实环境中对速
uniapp封装request请求

在基础文件里面创建一个api文件在创建两个 js文件 http js 里面封装 request 请求 let baseUrl https white 511 toponet cn 基地址 export const request opti
头歌（C语言）-数据结构与算法-排序-第2关：实现快速排序

任务描述相关知识编程要求评测说明任务描述本关要求通过补全快速排序私有函数QSort 来供函数QuickSort调用以此来实现快速排序的功能相关知识快速排序的基本过程是从待排序记录中任选一个记录以它的排序码作为中心值将其
关于单链表的函数

关于单链表一些基础功能函数的总结文章目录关于单链表一些基础功能函数的总结 0 这个链表的结构体 1 输出单链表 2 创建单链表头插法 3 链表其中一个的删除 4 链表的插入 5 单链表的快排 6 单链表变成循环链表尾节点连上头结
《Vision-Language Pre-Training with Triple Contrastive Learning》/《具有三重对比学习的视觉语言预训练》

一摘要视觉语言表示学习很大程度上受益于通过对比损失例如 InfoNCE损失的图像文本对齐这种对齐策略能够最大化图像与其匹配文本之间的互信息 MI 然而简单地执行跨模态对齐 CMA 不能确保来自相同模态的相似输入保持接近这可能
Greenplum元数据信息

1 元数据重要的表 pg database 所有的数据库信息 pg namespace 所有的schema信息 pg class 所有的表信息 pg attribute 所有的属性信息 pg proc 函数信息包括自定义函数以上都可以以
spec文件说明

spec文件概述 spec文件中主要包含4个class Analysis PYZ EXE和COLLECT Analysis以py文件为输入它会分析py文件的依赖模块并生成相应的信息修改的主要是这个部分 PYZ是一个 pyz的压缩包包
#循循渐进学51单片机#定时器与数码管#not.4

1 熟练掌握单片机定时器的原理和应用方法 1 时钟周期单片机时序中的最小单位具体计算的方法就是时钟源分之一 2 机器周期我们的单片机完成一个操作的最短时间 3 定时器打开定时器储存寄存器的值经过一个机器周期自动加一也就是说机
常用文件读写&CSV文件读写&对象的序列化和反序列化

前言意识 python中一切皆是对象常见文件的读写分类 1 普通文本文件 txt py md html doc等 2 二进制文件图片音频视频压缩包等 3 csv文件 csv 需要借助于系统模块csv 4 对象的序列化和反序列化
使用 sCrypt 实现数独游戏合约

我们在 BSV 区块链上实现了一个数独游戏智能合约利用之前介绍过的一种合约范式可以将游戏中寻找解题方案的过程外包上链因为求解数独问题的计算工作量会随着其行列数快速增长实际上它也是一个 NP 完全问题不过我们可以借由 sCrypt
概率论中伯努利分布(bernoulli distribution)介绍及C++11中std::bernoulli_distribution的使用

Bernoulli分布 Bernoulli distribution 是单个二值随机变量的分布它由单个参数 0 1 给出了随机变量等于1的概率它具有如下的一些性质 P x 1 P x 0 1 P x x x 1 1 x Ex x Var
ad如何设置pcb板子形状_AD18软件的PCB制作步骤

作为一名电子工程师我们将设计转化为实物的时候一定会经历的步骤就是就是制作pcb 现在使用较多的都是Altium公司推出的一款AD软件本人使用的就是这款AD18 当然也有使用Protel99SE软件的我身边有的工作者还在使用这款软件
合并排序（归并排序）

合并排序大致思想便是先将数组中的元素拆分成若干小部分然后再将这些小部分按照顺序进行重新组合从而实现排序很明显这里用到了分治法的思想即将一个大问题分成若干个相同的小问题因为问题规模变小了所以解决问题的难度也随之减小试想一下
用户留存的计算

用户留存的计算基本是老生常谈了可是这么多年下来关于留存的计算真的是差异太大了最初版将每个用户的所有交易月份用group concat 函数进行连接把对应的结果导出到excel中然后再用countifs来进行计算这个方法大概

用户留存的计算

用户留存的计算 的相关文章

随机推荐

热门标签

用户留存的计算的相关文章