同一日に複数レコードがある場合には、任意カラムが最大のレコードを選択したい

いつもお世話になっています。
1日に複数件のレコードがある場合があり、その場合には任意カラムが最大の行を抽出したいです。
カラムの構成は頭から、
日付 SMALLINT,
銘柄 CHAR(4),
市場 TINYINT,
始 MEDIUMINT,
高 MEDIUMINT,
安 MEDIUMINT,
終 MEDIUMINT,
出来高 INT UNSIGNED,
PRIMARY KEY(日付, 銘柄, 市場)
となっており、
例えば、次の4行のレコードが格納されている場合に、
5567 '4298' 11 21650 21680 21630 21650 438500
5568 '4298' 11 21650 21660 21480 21650 531300
5568 '4298' 30 0 0 0 0 0
5569 '4298' 11 21650 21650 21320 21460 124200

日付5568が重複しているので、出来高が大きい方の行のみ選択し、
5567 '4298' 11 21650 21680 21630 21650 438500
5568 '4298' 11 21650 21660 21480 21650 531300
5569 '4298' 11 21650 21650 21320 21460 124200
の3行を得るSQLをご教示ください。
データが見にくくスミマセン。

No.2 回答者： ありものがたり 回答日時： 2025/04/16 20:44

SELECT *

FROM テーブル名
OVER(
　　PARTITION BY 日付
　　ORDER BY 出来高 DESC
　　LIMIT 1
　　)

かなあ...

この回答へのお礼

ウィンドウ関数について少し調べました。
しかし私の環境ではなぜか使えませんでした。
使い方が良く分かっていないだけかもしれません。

何か良い方法がないか、私も同時進行で考えていたのですが、副問合せを使ってなんとか出来ました。

SELECT * FROM 株価, (
SELECT 日付, MAX(出来高) AS mx
FROM 株価
WHERE 銘柄 = ?
GROUP BY 日付
) AS mx
WHERE 株価.日付 = mx.日付 AND 銘柄 = ? AND 出来高 = mx

このクエリーだと、同一日に出来高の同じ行が複数あった場合には、それらが全て選択されてしまうので少しダサいですが、少なくとも例のようなヘンテコなレコードを除外する役は果たせますし、速度もまずまずでしたので、当面の運用には耐えうると思いました。

お礼日時：2025/04/17 04:52

No.1 回答者： kmee 回答日時： 2025/04/16 17:16

MySQL であってますか?

MySQL8.0以上 (あるいは他のDBMS)なら、ウィンドウ関数の ROW_NUMBERで「同一日付 内での順位」を表わす列を追加する
→ 「順位」=1 を抽出する
というのが定番の一つ。

「グループ内で最大の行 SQL」で検索すれば他の方法等も見つかる。

この回答へのお礼

MySQL8.0以上ですが、なぜかROW_NUMBERが使えませんでした。
使い方が良く分かっていないだけかもしれません。

何か良い方法がないか、私も同時進行で考えていたのですが、サブクエリーを使ってなんとか出来ました。

SELECT * FROM 株価, (
SELECT 日付, MAX(出来高) AS mx
FROM 株価
WHERE 銘柄 = ?
GROUP BY 日付
) AS mx
WHERE 株価.日付 = mx.日付 AND 銘柄 = ? AND 出来高 = mx

このクエリーだと出来高が同じだった場合には、複数件のレコードが選択されてしまうので少しダサいですが、少なくとも例のようなヘンテコなレコードを除外する役は果たせますし、速度もまずまずでしたので、当面の運用には耐えうると思いました。

お礼日時：2025/04/17 04:29