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 は、すべての開始値または終了値を 1 つの文字列に連結し、次の順序で並べ替えます。 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)

このクエリは、各グループの最初 (オープン) レコードと最後 (クローズ) のレコードを効率的に取得し、大きなテーブルの処理時間を最小限に抑えます。