查詢滿足條件的最新資料（逐步優化，mysql、達夢資料庫）

偏愛枕驚鴻發表於2020-12-08
優化MySql資料庫

1.條件：報警資訊表sensor_warning

2.需求：

查詢當前車廂的、不同裝置的、所有處理未完成的、不同報警原因的、時間最新的資料集合，最後按裝置id或報警時間排序

3.原始sql，不滿足實際情況

SELECT
	a.id,
	a.sensor_type,
	a.device_status,
	a.train_no,
	a.device_id,
	a.sensor_no,
	a.carriage_no,
	a.process_progress,
	a.handle_person,
	a.create_time,
	a.position,
	a.update_time,
	a.reason 
FROM
	sensor_warning AS a
	JOIN (
SELECT
	max( b.device_id ) AS device_id,
	max( b.create_time ) AS create_time 
FROM
	sensor_warning AS b 
WHERE
	b.process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND b.carriage_no = 'CARID-008' 
GROUP BY
	b.device_id 
	) AS m ON a.device_id = m.device_id 
	AND a.create_time = m.create_time 
ORDER BY
	a.device_id

4.為什麼不滿足實際情況？

上面的sql只能查出當前車廂的、不同裝置的、所有處理未完成的、時間最新的資料集合。並沒有根據reason分類。
而且這裡reason欄位中的資料還比較特殊，例舉reason欄位中的個別報警原因就知道了：

轉向架振動超閾值:1.23m/s²;
轉向架振動超閾值:0.48m/s²
車體振動超閾值:-0.24m/s²
車體振動超閾值:-0.55m/s²

我們可以看到reason中存在數值，如果按照上面的sql查詢，那麼上述的4條資料都會被查出來，而實際上只有兩條資料才是我們想要的結果。

5.mysql解決方案

使用字串擷取函式SUBSTRING_INDEX()，按照冒號擷取，因為資料中有中文冒號和英文冒號所以這裡做了兩次擷取。

SELECT
	a.device_id,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.reason, ':', 1 ), '：', 1 ) reason,
	max(a.create_time) AS create_time 
FROM
	(
SELECT
	id,
	sensor_type,
	device_status,
	train_no,
	device_id,
	sensor_no,
	carriage_no,
	process_progress,
	handle_person,
	create_time,
	position,
	update_time,
	reason 
FROM
	sensor_warning
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
ORDER BY
	create_time DESC 
	) a 
GROUP BY
	a.device_id,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.reason, ':', 1 ), '：', 1 ) 
	
	ORDER BY a.device_id

6.達夢資料庫解決方案

（ps：同orcale）
然而達夢資料庫中並不支援SUBSTRING_INDEX()函式，達夢這裡使用substr()函式。

substr函式格式 (字元擷取函式)

　　格式1： substr(string string, int a, int b);

　　格式2：substr(string string, int a) ;

解析：

    格式1：
        1、string 需要擷取的字串
        2、a 擷取字串的開始位置（注：當a等於0或1時，都是從第一位開始擷取）
        3、b 要擷取的字串的長度

    格式2：
        1、string 需要擷取的字串
        2、a 可以理解為從第a個字元開始擷取後面所有的字串。

6.1.達夢資料庫解決方案1

SELECT
	a.id,
	a.device_id,
	a.create_time,
	a.reason 
FROM
	sensor_warning AS a
	JOIN (
SELECT
	b.device_id device_id,
	substr( b.reason, 1, 7 ) reason,
	max( b.id ) AS id,
	max( b.create_time ) AS create_time 
FROM
	sensor_warning AS b 
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
GROUP BY
	b.device_id,
	substr( b.reason, 1, 7 ) 
	) AS m ON a.id = m.id 
ORDER BY
	a.create_time

6.2.達夢資料庫解決方案2

SELECT
	a.device_id,
	substr( a.reason, 1, 7 ) reason,
	max( a.create_time ) AS create_time 
FROM
	(
SELECT
	id,
	device_id,
	carriage_no,
	create_time,
	reason 
FROM
	sensor_warning 
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
	) a 
GROUP BY
	a.device_id,
	substr( a.reason, 1, 7 ) 
ORDER BY
	a.create_time

相關文章