通過當(dāng)前排序字段獲取相鄰數(shù)據(jù)項(xiàng)

1.業(yè)務(wù)場(chǎng)景

（1）需要專門以一個(gè)彈窗頁(yè)面展示一項(xiàng)數(shù)據(jù)的所有字段值.其中一些字段值長(zhǎng)度較大。

（2）能夠左右切換上一項(xiàng)下一項(xiàng)數(shù)據(jù)

（3）存在可排序的字段，如以id進(jìn)行排序

2.思路

2.1 sql

1>查詢前一項(xiàng)，查詢小于當(dāng)前id的項(xiàng)逆序取第一個(gè)

2>查詢后一項(xiàng)，查詢大于當(dāng)前id的項(xiàng)正序取第一個(gè)

3>連接兩項(xiàng)結(jié)果

2.2 頁(yè)面邏輯

（1）在展示當(dāng)前項(xiàng)時(shí)獲取好兩相鄰的數(shù)據(jù)，在做切換時(shí)直接填充數(shù)據(jù)

（2）切換數(shù)據(jù)展示時(shí)同樣再次獲取當(dāng)前項(xiàng)兩相鄰數(shù)據(jù)

以此（1）（2）往復(fù)

3.sql

例：查詢id為40兩相鄰的數(shù)據(jù)

( 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ù)來舉例，如下：

查詢用戶首單和第二單的時(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é)果是：

整個(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ù)如下：

這里判斷的是統(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語(yǔ)句進(jìn)行查詢的方法。 

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

最新評(píng)論