( SELECT * FROM [表名] WHERE id < 40 ORDER BY id DESC LIMIT 1 ) UNION
(
?? ?SELECT
?? ??? ?*?
?? ?FROM
?? ??? ?[表名]
?? ?WHERE
?? ??? ?id > 40?
?? ?ORDER BY
?? ??? ?id?
?? ?LIMIT 1
)

同表相鄰數(shù)據(jù)查詢或計(jì)算

用戶下相鄰訂單的時(shí)間差舉例

這里主要介紹一下，在一張數(shù)據(jù)表下對(duì)相鄰的數(shù)據(jù)進(jìn)行一個(gè)相關(guān)查詢和計(jì)算；

拿一個(gè)在電商中最常見的情況，計(jì)算一下用戶首單和第二單的時(shí)間間隔這樣的數(shù)據(jù)來舉例，如下：

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

查詢用戶首單和第二單的時(shí)間間隔：

SELECT
	m.customer_id,
	sfo.created_at as '首單時(shí)間',
	m.created_at as '第二單時(shí)間',
	(unix_timestamp(m.created_at) - unix_timestamp(sfo.created_at))/86400 as '兩單相差天數(shù)'
	
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

查詢結(jié)果是：

customer_id	首單時(shí)間	第二單時(shí)間	兩單時(shí)間差
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

整個(gè)原理如下：

將一張表查詢兩次得到兩組數(shù)據(jù)，分別為別名m和別名n的兩組數(shù)據(jù)；
以m為主，用n的數(shù)據(jù)和m的數(shù)據(jù)作對(duì)比，通過created_at的判斷過濾掉一些無用數(shù)據(jù)；
使用count()函數(shù)統(tǒng)計(jì)滿足條件的數(shù)據(jù)個(gè)數(shù)；
統(tǒng)計(jì)數(shù)為1時(shí)說明n表中比m表中時(shí)間小的只有1條，m中的該條數(shù)據(jù)也就是該用戶下的第二筆訂單；
通過LEFT JOIN聯(lián)表，通過created_at找到比第二單更早的一單也就是用戶的首單；
利用unix_timestamp把得到的兩條數(shù)據(jù)的created_at做差，得到了兩筆訂單的時(shí)間間隔；

下面做了一下拓展，可以查詢?nèi)我庀噙B的兩筆訂單的時(shí)間間隔：

SELECT
	m.customer_id,
	m.created_at as '后一單時(shí)間',
	SUBSTRING_INDEX(
		GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
		',',
		1
	) as '前一單時(shí)間',
	(unix_timestamp(m.created_at) - unix_timestamp(
		SUBSTRING_INDEX(
			GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
			',',
			1
		)
	))/86400 as '兩單相差天數(shù)'
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;

得到數(shù)據(jù)如下：

customer_id	后一單時(shí)間	前一單時(shí)間	兩單時(shí)間差
1	2017-07-27 10:21:37	2017-07-27 01:27:22	0.3710

這里判斷的是統(tǒng)計(jì)數(shù)為2的，也就是用戶的第二單和第三單的時(shí)間間隔計(jì)算，因?yàn)橛脩?0和12只有兩單所以結(jié)果中無這兩個(gè)用戶；

整個(gè)原理如下：

將一張表查詢兩次得到兩組數(shù)據(jù)，分別為別名m和別名n的兩組數(shù)據(jù)；
以m為主，用n的數(shù)據(jù)和m的數(shù)據(jù)作對(duì)比，通過created_at的判斷過濾掉一些無用數(shù)據(jù)；
使用count()函數(shù)統(tǒng)計(jì)滿足條件的數(shù)據(jù)個(gè)數(shù)；
篩選之后m中得到的是第三筆訂單；
通過LEFT JOIN聯(lián)表，通過created_at找到比第三筆訂單時(shí)間早的訂單，這里會(huì)從sfo中得到兩筆訂單；
利用GROUP_CONCAT函數(shù)每組訂單中各得到的兩筆訂單利用created_at進(jìn)行降序排序，然后得到通過‘，’連接的兩條數(shù)據(jù)的時(shí)間，如下：2017-07-27 01:27:22,2017-07-21 09:43:02
使用SUBSTRING_INDEX函數(shù)通過’，'將數(shù)據(jù)拆分再拿到第一條數(shù)據(jù)，也就是第二筆訂單的時(shí)間了；
利用unix_timestamp對(duì)created_at作差，得到兩筆訂單的時(shí)間間隔；

這只是我想到的應(yīng)對(duì)這種場(chǎng)景通過SQL語句進(jìn)行查詢的方法。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: