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

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

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

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

（3）存在可排序的字段，如以id進行排序

2.思路

2.1 sql

1>查詢前一項，查詢小于當前id的項逆序取第一個

2>查詢后一項，查詢大于當前id的項正序取第一個

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

2.2 頁面邏輯

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

（2）切換數(shù)據(jù)展示時同樣再次獲取當前項兩相鄰數(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ù)查詢或計算

用戶下相鄰訂單的時間差舉例

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

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

查詢用戶首單和第二單的時間間隔：

SELECT
	m.customer_id,
	sfo.created_at as '首單時間',
	m.created_at as '第二單時間',
	(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é)果是：

整個原理如下：

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

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

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 '兩單相差天數(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)計數(shù)為2的，也就是用戶的第二單和第三單的時間間隔計算，因為用戶10和12只有兩單所以結(jié)果中無這兩個用戶；

整個原理如下：

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

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

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

最新評論