在MySQL中使用聚合函數檢索分組資料的第一條和最後一筆記錄

在MySQL中，當使用聚合函數從分組記錄中取得資料時，檢索該組的第一個和最後一個記錄可能具有挑戰性。雖然多個查詢可以完成此任務，但對於大型表來說效率可能較低。

為了優化這個過程，MySQL 使用 GROUP_CONCAT 和 SUBSTRING_INDEX 函數提供了更有效的解決方案.

使用 GROUP_CONCAT和SUBSTRING_INDEX

透過一起使用這些函數，您可以連接群組中的值，然後使用 SUBSTRING_INDEX 提取第一個和最後一個值。具體方法如下：

SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close

工作原理：

• ]GROUP_CONCAT將所有開盤或收盤值連接成一個字串，按datetime 列。
• SUBSTRING_INDEX 透過在逗號 (',') 處拆分連接字串來提取連接字串中的第一個和最後一個值。 '1' 參數表示第一次或最後一次出現。

範例：

考慮以下查詢：

SELECT MIN(low_price), MAX(high_price),
SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

此查詢有效地檢索每個群組的第一筆（開啟）和最後一筆（關閉）記錄，從而最大限度地減少大型表格的處理時間。