MySQL购买间隔时间计算

八月 27, 2020 阅读量

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

实践

具体sql要根据自己的业务需求来写，实际使用效率在接受范围内吧，不是很快。
比如我最后写的sql就是一坨……

查看评论