MySQL购买间隔时间计算

因为一些原因被要求按照客户消费金额分类披露客户购买间隔时间，并对其合理性进行补充分析。网上查出来的方法都是你抄我的我抄你的，于是乎自己开始思考如何去实现。

数据准备

#MySQL80018

CREATE TABLE `order_info`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `pay_time` datetime(0) NULL DEFAULT NULL COMMENT '付款时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单信息' ROW_FORMAT = Dynamic;

INSERT INTO `order_info` VALUES (1, '张三', '2020-01-01 12:50:01');
INSERT INTO `order_info` VALUES (2, '李四', '2020-01-02 12:46:38');
INSERT INTO `order_info` VALUES (3, '李四', '2020-01-04 08:23:06');
INSERT INTO `order_info` VALUES (4, '王五', '2020-02-06 09:47:42');
INSERT INTO `order_info` VALUES (5, '王五', '2020-02-07 18:42:23');
INSERT INTO `order_info` VALUES (6, '王五', '2020-03-12 20:22:45');
INSERT INTO `order_info` VALUES (7, '赵六', '2020-08-01 10:36:49');
INSERT INTO `order_info` VALUES (8, '赵六', '2020-08-04 12:37:09');
INSERT INTO `order_info` VALUES (9, '赵六', '2020-08-14 06:20:33');
INSERT INTO `order_info` VALUES (10, '赵六', '2020-08-27 10:38:02');

实现思路

可能不是最优思路，以下是我的实现方法。订单间隔时间只比较同一用户的订单。

将原来的表需要的字段查出，添加行号字段，按照名字和时间升序排序，当作临时表用。

SELECT
 @r := @r + 1 AS 'r',
 `name`,
 pay_time
FROM
 order_info,
 ( SELECT @r := 0 ) t
ORDER BY
 `name`,
 pay_time

将上面的临时表左连接自己（@r要换名字，不然无法查出后面的数据），连接条件为名字相同，序号错位一格。只有用户有两单以上的时候，才会出现后面的数据。

SELECT
 *
FROM
 (
 SELECT
  @r := @r + 1 AS 'r',
  `name`,
  pay_time
 FROM
  order_info,
  ( SELECT @r := 0 ) t
 ORDER BY
  `name`,
  pay_time
 ) temp
 LEFT JOIN (
 SELECT
  @rownum := @rownum + 1 AS 'r',
  `name`,
  pay_time
 FROM
  order_info,
  ( SELECT @rownum := 0 ) t
 ORDER BY
  `name`,
  pay_time
 ) temp2 ON temp.r = temp2.r + 1
 AND temp.`name` = temp2.`name`

过滤掉空数据，使用TIMESTAMPDIFF函数计算时间间隔

TIMESTAMPDIFF(unit,begin,end); unit
可以是MICROSECOND
,SECOND
,MINUTE
,HOUR
,DAY
,WEEK
,MONTH
,QUARTER
,YEAR
begin
和end
是DATE或DATETIME表达式

SELECT
 temp.`name`,
 temp.pay_time AS afterTime,
 temp2.pay_time AS beforeTime,
 TIMESTAMPDIFF( DAY, temp2.pay_time, temp.pay_time ) AS diff
FROM
 (
 SELECT
  @r := @r + 1 AS 'r',
  `name`,
  pay_time
 FROM
  order_info,
  ( SELECT @r := 0 ) t
 ORDER BY
  `name`,
  pay_time
 ) temp
 LEFT JOIN (
 SELECT
  @rownum := @rownum + 1 AS 'r',
  `name`,
  pay_time
 FROM
  order_info,
  ( SELECT @rownum := 0 ) t
 ORDER BY
  `name`,
  pay_time
 ) temp2 ON temp.r = temp2.r + 1
 AND temp.`name` = temp2.`name`
WHERE
 temp2.pay_time IS NOT NULL