mysql统计实战-对比每个用户第一次和第二次下单情况

需求如下：比对每个用户第一次和第二次的下单情况情况，查询两次下单的商家id，gmv，两次是否在同一个商家消费的，两次下单的间隔月数

简化的表结构，数据，查询语句如下

-- 建表语句如下
CREATE TABLE `tb_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `company_id` int(11) NOT NULL DEFAULT '0' COMMENT '公司id',
  `gmv` float(11,2) NOT NULL DEFAULT '0.00' COMMENT '交易额',
  `create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单创建时间',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='订单表';

-- 插入演示数据如下
INSERT INTO `tb_order` VALUES ('1', '1', '1', '100.00', '2017-10-01 15:44:18');
INSERT INTO `tb_order` VALUES ('2', '1', '2', '200.00', '2017-10-02 15:44:24');
INSERT INTO `tb_order` VALUES ('3', '2', '4', '4321.00', '2017-10-03 15:44:28');
INSERT INTO `tb_order` VALUES ('4', '1', '1', '5678.00', '2017-10-04 15:44:33');
INSERT INTO `tb_order` VALUES ('5', '2', '4', '312.00', '2017-10-05 15:44:50');
INSERT INTO `tb_order` VALUES ('6', '2', '3', '134.00', '2017-10-06 16:18:08');

-- 查询的语句如下
SET @num=1;
SET @last_user_id=0;
SELECT user_id, DATE_FORMAT(1_day,'%Y-%m') AS 首次下单年月,1_gmv AS 首次下单金额,IFNULL(DATE_FORMAT(2_day,'%Y-%m'),0) AS 第二次下单年月,
IF(2_gmv!=0,2_gmv,0) AS 第二次下单金额,
if( NOT ISNULL(2_company_id),IF(1_company_id=2_company_id,'Y','N'),0) as 第二单是否是同一机构,IFNULL((TO_DAYS(2_day)-TO_DAYS(1_day))/30,0) AS 间隔月数 FROM (
SELECT 
user_id,
GROUP_CONCAT(if(seq=1,create_date,NULL)) AS 1_day,
SUM(IF(seq=1,gmv,0)) AS 1_gmv,
GROUP_CONCAT(if(seq=1,company_id,NULL)) AS 1_company_id,
GROUP_CONCAT(if(seq=2,create_date,NULL)) AS 2_day,
SUM(IF(seq=2,gmv,0)) AS 2_gmv,
GROUP_CONCAT(if(seq=2,company_id,NULL)) AS 2_company_id
 FROM (
SELECT t1.*,IF(@last_user_id=user_id,@num:=@num+1,@num:=1) AS seq,
(@last_user_id:=user_id) AS tmp FROM (
SELECT user_id,create_date,company_id,gmv
FROM tb_order
GROUP BY user_id ASC,create_date ASC
) t1
) t2 GROUP BY user_id
) t3

这个需求应该包括三步：

第一步是要查询出每个用户第一次和第二次的下单情况并将所在行明确的标记出来。

第二步将不在同一行的每个用户的第一次和第二次下单信息合并到一行。

第三步比较第一次和第二次下单的机构是否是同一个，比较两次的时间差值

这三步分别由三层子查询 t1,t2,t3完成。

我们先来分析第一层。

SELECT t1.*,IF(@last_user_id=user_id,@num:=@num+1,@num:=1) AS seq,
(@last_user_id:=user_id) AS tmp FROM (
SELECT user_id,create_date,company_id,gmv
FROM tb_order
GROUP BY user_id ASC,create_date ASC
) t1

在这个一步骤中，在子查询中将所有的符合条件的订单按照uid和创建时间升序排列，保证uid相同的订单都集中在一段区域，且排在同一个uid组成的区域内订单按照创建时间升序排列。结果如下

将数据传递到t1之后，利用 last_user_id和num这两个用户变量进行跨行预算(这里科普一下mysql的一些基础知识，在除了set语句的其他语句中 “:=”是为用户变量赋值，而“=”是判断)，基本逻辑如下

1.利用 (@last_user_id:=user_id) 将上一行的uid和传递到下一行，利用num将上一行的seq传递到下一行

2.利用if语句判断，如果上一行传递下来的uid和本行的uid相同，那么本行的seq（标记此订单是此用户第几次下单的参数）就累加1，如果不等于那么就说明到了区域的边界，那么就重置seq为1，产生的集合如下（tmp字段没用，请忽略）

第二层

我们的得到的是一个以order_id为唯一标识的结果集，但是还不够，我们需要的是每个用户的下单情况，所以还要用SUM和GROUP_CONCAT进行聚合。

SELECT 
user_id,
-- 第一次下单的时间
GROUP_CONCAT(if(seq=1,create_date,NULL)) AS 1_day,
-- 第一次下单的gmv
SUM(IF(seq=1,gmv,0)) AS 1_gmv,
-- 第一次下单的公司id
GROUP_CONCAT(if(seq=1,company_id,NULL)) AS 1_company_id,
-- 第二次下单的时间
GROUP_CONCAT(if(seq=2,create_date,NULL)) AS 2_day,
-- 第二次下单的gmv
SUM(IF(seq=2,gmv,0)) AS 2_gmv,
-- 第二次下单的公司id
GROUP_CONCAT(if(seq=2,company_id,NULL)) AS 2_company_id
 FROM (
-- 以上的部分为第二层
SELECT t1.*,IF(@last_user_id=user_id,@num:=@num+1,@num:=1) AS seq,(@last_user_id:=user_id) AS tmp FROM (
SELECT user_id,create_date,company_id,gmv
FROM tb_order
GROUP BY user_id ASC,create_date ASC

) t1
-- 以下的部分为第二层
) t2 GROUP BY user_id

SUM函数大家应该都知道，但是 GROUP_CONCAT估计很多人没听过，简单点说就是将指定的列以指定的分隔符区隔后以字符串的形式连接在一起，有点像php的impode和python的split

SELECT GROUP_CONCAT(order_id) FROM tb_order

的结果为

不知道我说明白没，不明白的看文档吧 mysql官方文档

在这一步我们利用IF和上一步产生的seq，

GROUP_CONCAT(if(seq=1,create_date,NULL)) AS 1_day

如果seq等于1那么就将创建时间连接到1_day这个字段内，否则连接NULL，连接NULL就相当于什么也没做

SUM(IF(seq=1,gmv,0)) AS 1_gmv

同理gmv也是如此，如果seq等于1，那么就累加gmv，如果不等于就累加0，相当于什么也没做。

以上通过聚合函数配合IF有选择的聚合，可以将存在于多行的数据调整到一行上去。

（以上是本文第二个核心点，请注意）

结果如下：

分析第三层

比对1_company_id和2_company_id是否相同，再求2_day和1_day的差值就可以了，空值什么的用0的补齐。

SET @num=1;
SET @last_user_id=0;
SELECT user_id, DATE_FORMAT(1_day,'%Y-%m') AS 首次下单年月,1_gmv AS 首次下单金额,IFNULL(DATE_FORMAT(2_day,'%Y-%m'),0) AS 第二次下单年月,
IF(2_gmv!=0,2_gmv,0) AS 第二次下单金额,
if( NOT ISNULL(2_company_id),IF(1_company_id=2_company_id,'Y','N'),0) as 第二单是否是同一机构,IFNULL((TO_DAYS(2_day)-TO_DAYS(1_day))/30,0) AS 间隔月数 FROM (
-- 以上为第三层
SELECT 
user_id,
GROUP_CONCAT(if(seq=1,create_date,NULL)) AS 1_day,
SUM(IF(seq=1,gmv,0)) AS 1_gmv,
GROUP_CONCAT(if(seq=1,company_id,NULL)) AS 1_company_id,
GROUP_CONCAT(if(seq=2,create_date,NULL)) AS 2_day,
SUM(IF(seq=2,gmv,0)) AS 2_gmv,
GROUP_CONCAT(if(seq=2,company_id,NULL)) AS 2_company_id
 FROM (
SELECT t1.*,IF(@last_user_id=user_id,@num:=@num+1,@num:=1) AS seq,(@last_user_id:=user_id) AS tmp FROM (
SELECT user_id,create_date,company_id,gmv
FROM tb_order
GROUP BY user_id ASC,create_date ASC

) t1
) t2 GROUP BY user_id
-- 以下为第三层
) t3

结果如下：

以上。

这是我司的一个临时提数的需求的简单再现，表结构和数据也是我重新弄得，为了保护公司的机密和让大家更容易看明白。有的同学可能觉得我这是旁门左道，因为市面上有更好的工具来完成这类统计，这种套路的好处在于快捷迅速，不需要借助别的工具，直接连接上业务的从库敲SQL，业务库里面存的是啥我就用啥，不需要再在中间进行加工，上头或PM提需求之后过半个小时我这边就能产生数据并验算完毕。

sql的逻辑我基本都说明白了，至于里面的函数具体的用法大家有不懂的就自行百度吧。

咱这次是初次写博客，可能有些文笔不通的地方，大家发现了可以在评论区告知我。赶脚写博客比写sql难多了，文中的sql写和验算的时间不过半个小时，但是这个博客却写了好几小时。

大家如果觉得我写的太墨迹太琐碎就请留言给我，我以后会写的简洁点。

我的知乎文章