mysql统计实战-查找每个季度新增支付用户数及其在后续季度的留存

题设如下：

A用户第一次支付是在季度B，则这个A用户视为B季度的新增支付用户，A用户在后续的C季度再次支付了订单，则这笔订单的gmv就是B季度新增支付用户在C季度的留存

需求如下：

计算每个月度新增支付用户数，新增支付用户的订单数，新增支付用户的gmv，以及这个这三个数据在后续季度的留存情况

首先创建简化的订单表

CREATE TABLE `order` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '订单ID',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `pay_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '支付时间',
  `gmv` float(11,2) NOT NULL DEFAULT '0.00' COMMENT 'gmv',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

SET @last_uid=-1;
SET @last_q=-100;
#生成表头
SELECT CONCAT('期间,当期,',GROUP_CONCAT(q ORDER BY q ASC)) FROM (SELECT DISTINCT IF(o.pay_date>='2015-01-01',CONCAT(YEAR(o.pay_date),'Q',CEILING(MONTH(o.pay_date) / 3)),'2015Q0') AS q  FROM tb_order AS o WHERE o.pay_date >='2014-01-01' AND o.pay_date <'2017-09-01') AS t
UNION ALL 
#表头结束
#生产表格主体
#第五层，增加当期列
SELECT CONCAT(q1,'	',SUM(IF(q1=q2,cnt,0)),'	',GROUP_CONCAT(cnt ORDER BY q2 ASC)) FROM(
#第四层,按照首个季度q1聚合
SELECT q1,q2,SUM(cnt) AS cnt FROM (
#第三层，按照第一个支付的季度，和实时的支付聚合
SELECT q1,q2,SUM(cnt) AS cnt FROM (
#第二层,利用两个用户变量，向下传递上一行的uid和季度，下一行比较两个用户变量，如果上一行的uid和本行不同，则本行就是这个uid的第一次支付的季度为q1，q2为本条目当前支付的季度，f忽略
SELECT uid,t.q AS q2,IF(@last_uid!=t.uid,(@last_q:=t.q),@last_q) AS q1,(@last_uid:=t.uid) AS f,cnt FROM
 (
#第一层,将订单数据按uid和季度聚合
SELECT IF(o.pay_date>='2015-01-01',CONCAT(YEAR(o.pay_date),'Q',CEILING(MONTH(o.pay_date) / 3)),'2015Q0') AS q,
uid,
#人数
#COUNT(DISTINCT o.uid) AS cnt
#订单数
#COUNT(DISTINCT o.order_id) AS cnt
#gmv
SUM(o.gmv) AS cnt
FROM tb_order AS o 
WHERE  o.pay_date < '2017-09-01'
GROUP BY uid ,q  
ORDER BY uid ASC,q ASC
#第一层结束
)t 
#第二层结束
) t 
WHERE q1 >='2015Q0' AND q1 < '2017Q4' AND q2 >='2015Q0' AND q2 < '2017Q4'
GROUP BY q1,q2
第三层结束
UNION ALL 
#生成完整的占位网格
SELECT ql.q AS q1,qr.q AS q2,0 AS cnt FROM 
(SELECT DISTINCT IF(o.pay_date>='2015-01-01',CONCAT(YEAR(o.pay_date),'Q',CEILING(MONTH(o.pay_date) / 3)),'2015Q0') AS q  FROM tb_order AS o WHERE o.pay_date >='2014-01-01' AND o.pay_date <'2017-09-01') AS ql
INNER JOIN (SELECT DISTINCT IF(o.pay_date>='2015-01-01',CONCAT(YEAR(o.pay_date),'Q',CEILING(MONTH(o.pay_date) / 3)),'2015Q0') AS q  FROM tb_order AS o WHERE o.pay_date >='2014-01-01' AND o.pay_date <'2017-09-01') AS qr
GROUP BY q1,q2
) t GROUP BY q1,q2
#第四层结束
) t GROUP BY q1
#第五层结束
#表格主体结束

结果截图如下

在上面截图中每行的列之间用tab制表符分割，粘贴到notepad并显示所有字符如下

其中橙色的箭头就代表了制表符，将以上字符直接粘贴到excel中为

其中每行的第一个单元格是第一次支付的季度，第一行表头的是随后的每个季度，表格主体的意思是

这样我们就用sql直接生成了一个可以直接粘贴到excel的字符文本。

下面我们开始分析整个过程

在这个需求中主要有以下难点

1.列数不固定，因为从统计的时间区间内每个季度都会占用一列，而mysql不能像progress sql那样构造行

2.需要确定每个用户的第一个季度和以后的其他季度。

3.并不是每个单元格都有数据，上面截图的值为0的单元格其实并没有值需要填充。

下面开始逐一分析每层的逻辑

第一层，将订单信息按照uid和支付的月份聚合，如果超出统计日志的范围就设置为2015Q0，产生一个由uid，q（季度），和cnt组成的结果集，并按照uid和q排序。

第二层，利用两个用户变量向下传递上一条的uid和q，如果本条的uid和上一条的uid不同那么重置，将本条的q赋值给用户变量并向下传递，一直到下一个uid再次终止为止，这样q1就是每个uid的的第一个q，也就是这个uid的首次支付季度。将q直接赋值给q2。

第三层，按照首次支付的季度和后续的季度，也就是q1,q2聚合，产生一个字段为q1，q2，cnt的结果集。在这个过程中，不是每个季度的首次支付用户在后续的每个季度都有留存，这样的话表格上机会有空缺，为了弥补空缺的位置，需要union 一个全量的，但是cnt都为0的一个表格，这样的话就实现了用0占空位。

第四层，按照q1聚合，后面的每一列都用tab隔开，这一行就都是这个q1在后续每个q2的留存数据。

第五层，增加当期，在第二列增加当期，q1=q2时的数据，也就是用户在自己所属的q1的订单数据。

最后需要说明的是在第一层的select中有被注释的几行，分别为人数，订单数，gmv，当统计人数的时候就将订单数和gmv注释掉，其他的两个也一次类推。