MySQL同表相邻数据查询或计算（用户下相邻订单的时间差举例）

这里主要介绍一下，在一张数据表下对相邻的数据进行一个相关查询和计算；
拿一个在电商中最常见的情况，计算一下用户首单和第二单的时间间隔这样的数据来举例，如下：

id	customer_id	created_at
1	1	2017-07-21 09:43:02
2	12	2017-07-25 11:37:48
3	10	2017-07-25 11:43:41
4	1	2017-07-27 01:27:22
5	10	2017-07-27 07:46:45
6	1	2017-07-27 10:21:37
7	12	2017-07-27 13:26:19

查询用户首单和第二单的时间间隔：

SELECT
	m.customer_id,
	sfo.created_at as '首单时间',
	m.created_at as '第二单时间',
	(unix_timestamp(m.created_at) - unix_timestamp(sfo.created_at))/86400 as '两单相差天数'
	
FROM
	sales_flat_order m 
LEFT JOIN 
	sales_flat_order sfo on m.customer_id = sfo.customer_id and sfo.created_at < m.created_at
WHERE
	(
		SELECT
			count(*)
		FROM
			sales_flat_order n
		WHERE
			m.customer_id = n.customer_id
			AND m.created_at > n.created_at
	) = 1
GROUP BY m.customer_id

查询结果是：

customer_id	首单时间	第二单时间	两单时间差
1	2017-07-21 09:43:02	2017-07-27 01:27:22	5.6558
12	2017-07-25 11:37:48	2017-07-27 13:26:19	2.0754
10	2017-07-25 11:43:41	2017-07-27 07:46:45	1.8355

整个原理如下：

1. 将一张表查询两次得到两组数据，分别为别名m和别名n的两组数据；
2. 以m为主，用n的数据和m的数据作对比，通过created_at的判断过滤掉一些无用数据；
3. 使用count()函数统计满足条件的数据个数；
4. 统计数为1时说明n表中比m表中时间小的只有1条，m中的该条数据也就是该用户下的第二笔订单；
5. 通过LEFT JOIN联表，通过created_at找到比第二单更早的一单也就是用户的首单；
6. 利用unix_timestamp把得到的两条数据的created_at做差，得到了两笔订单的时间间隔；

下面做了一下拓展，可以查询任意相连的两笔订单的时间间隔：

SELECT
	m.customer_id,
	m.created_at as '后一单时间',
	SUBSTRING_INDEX(
		GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
		',',
		1
	) as '前一单时间',
	(unix_timestamp(m.created_at) - unix_timestamp(
		SUBSTRING_INDEX(
			GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
			',',
			1
		)
	))/86400 as '两单相差天数'
FROM
	sales_flat_order m 
LEFT JOIN 
	sales_flat_order sfo on m.customer_id = sfo.customer_id and sfo.created_at < m.created_at
WHERE
	(
		SELECT
			count(*)
		FROM
			sales_flat_order n
		WHERE
			m.customer_id = n.customer_id
			AND m.created_at > n.created_at
	) = 2
GROUP BY m.customer_id;

得到数据如下：

customer_id	后一单时间	前一单时间	两单时间差
1	2017-07-27 10:21:37	2017-07-27 01:27:22	0.3710

这里判断的是统计数为2的，也就是用户的第二单和第三单的时间间隔计算，因为用户10和12只有两单所以结果中无这两个用户；

整个原理如下：

1. 将一张表查询两次得到两组数据，分别为别名m和别名n的两组数据；
2. 以m为主，用n的数据和m的数据作对比，通过created_at的判断过滤掉一些无用数据；
3. 使用count()函数统计满足条件的数据个数；
4. 筛选之后m中得到的是第三笔订单；
5. 通过LEFT JOIN联表，通过created_at找到比第三笔订单时间早的订单，这里会从sfo中得到两笔订单；
6. 利用GROUP_CONCAT函数每组订单中各得到的两笔订单利用created_at进行降序排序，然后得到通过‘，’连接的两条数据的时间，如下：

2017-07-27 01:27:22,2017-07-21 09:43:02

7. 使用SUBSTRING_INDEX函数通过’，'将数据拆分再拿到第一条数据，也就是第二笔订单的时间了；
8. 利用unix_timestamp对created_at作差，得到两笔订单的时间间隔；

这只是我想到的应对这种场景通过SQL语句进行查询的方法，如果有小伙伴有更好的方法或者改进的方法，欢迎小伙伴留言，谢谢啦！