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	name	pay_time
1	张三	2020-01-01 12:50:01
2	李四	2020-01-02 12:46:38
3	李四	2020-01-04 08:23:06
4	王五	2020-02-06 09:47:42
5	王五	2020-02-07 18:42:23
6	王五	2020-03-12 20:22:45
7	赵六	2020-08-01 10:36:49
8	赵六	2020-08-04 12:37:09
9	赵六	2020-08-14 06:20:33
10	赵六	2020-08-27 10:38:02

将上面的临时表左连接自己（@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`

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

过滤掉空数据，使用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

name	afterTime	beforeTime	diff
李四	2020-01-04 08:23:06	2020-01-02 12:46:38	1
王五	2020-02-07 18:42:23	2020-02-06 09:47:42	1
王五	2020-03-12 20:22:45	2020-02-07 18:42:23	34
赵六	2020-08-04 12:37:09	2020-08-01 10:36:49	3
赵六	2020-08-14 06:20:33	2020-08-04 12:37:09	9
赵六	2020-08-27 10:38:02	2020-08-14 06:20:33	13

优化(2021/11/10)

1. LEFT JOIN可以使用INNER JOIN替代，这样就不用加WHERE条件WHERE temp2.pay_time IS NOT NULL去除空白数据了。

2. 序号可以使用窗口函数row_number() over()来替代。
   最终sql如下：

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
		row_number() over(partition by name order by pay_time) AS r,
		`name`,
		pay_time
	FROM
		order_info
	ORDER BY
		`name`,
		pay_time
	) temp
	INNER JOIN (
	SELECT
		row_number() over(partition by name order by pay_time) AS r,
		`name`,
		pay_time
	FROM
		order_info
	ORDER BY
		`name`,
		pay_time
	) temp2 ON temp.r = temp2.r + 1
	AND temp.`name` = temp2.`name`

优化(2025/04/01)

朋友要找同一个用户两次点击时间差或者多次时间差，给我发了一个lead函数，后来发现和这个类似，于是
用窗口函数lead替换INNER JOIN

SELECT 
    name,
    pay_time AS afterTime,
    prev_pay_time AS beforeTime,
    TIMESTAMPDIFF(DAY, prev_pay_time, pay_time) AS diff
FROM (
    SELECT 
        name,
        pay_time,
        LAG(pay_time) OVER (PARTITION BY name ORDER BY pay_time) AS prev_pay_time
    FROM order_info
) t
WHERE prev_pay_time IS NOT NULL
ORDER BY name, pay_time;

最早发布于 2020/08/27
版权声明：本文为xuchl的原创文章，修改或转载请附上原文出处链接或CSDN链接
原文链接：原文链接：https://xuchunlei.top/2020/08/27/MySQL购买间隔时间计算/

微信公众号链接