MySQLでグループ化対象外のデータ選択

Question

前提・実現したいこと

すごく基本的なことかもしれませんが、SQLのデータから、特定のカラムをグループ化し、別の特定カラムの最小値のデータを持つレコードを抽出したいです。

テストテーブルとして、仕入値の最安を引っ張り出すSQLを作成してみました。

該当のソースコード

CREATE TABLE `question` (
 `serial` INTEGER UNSIGNED auto_increment primary key
,`品名` VARCHAR(50)
,`ID` VARCHAR(50)
,`仕入提示` INTEGER UNSIGNED
,`仕入先` VARCHAR(50)
);

INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A001',200,'青森市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A001',210,'弘前市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A002',320,'長野市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A002',310,'松本市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G001',410,'笛吹市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G001',400,'中野市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G002',370,'甲州市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G002',360,'塩尻市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',510,'天童市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',500,'東根市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',520,'山形市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C002',600,'村山市');


SELECT `serial`,`品名`,`ID`,Min(`仕入提示`) ,`仕入先`
FROM `question`
GROUP BY ID;

発生している問題・エラーメッセージ

これでは、serialと仕入先が合わないデータが発生してしまいます。
serial 品名 ID Min(仕入提示) 仕入先
1 りんご A001 200 青森市
3 りんご A002 310 長野市＜serialと値段があわない
9 さくらんぼ C001 500 天童市＜serialと値段があわない
12 さくらんぼ C002 600 村山市
5 ぶどう G001 400 笛吹市＜serialと値段があわない
7 ぶどう G002 360 甲州市＜serialと値段があわない

これは、この SQL では ID を GROUP 化し、仕入提示を最小にしているまでは良いとして、serialや、品名、仕入先には指定をしていないため、適当なデータを引っ張っているのではないかと思われます。

MySQLで GROUP 化と条件化で絞り込んだレコードに所属する、条件していないカラムを正確に選択する方法はありますか？

Answer 1

mysqlのgroup by には拡張仕様があります。
MySQLのGROUP BYは、寛容すぎて気持ちが悪い。

ID を GROUP 化し、仕入提示を最小にしているまでは良いとして、serialや、品名、仕入先には指定をしていないため、適当なデータを引っ張っているのではないかと思われます。

指定していないのだから、何を取ってきても文句は言えません。
※誤解を生みやすいので、sql_mode に ONLY_FULL_GROUP_BY を設定するのもありかと思います。

ID毎の最安値の仕入先の一覧は以下のようにして取得できます。

SELECT *
FROM question t1
where 仕入提示 = (
        select min(仕入提示) from question where id=t1.ID
      ) 

※yambejpさんの回答がついたので補足しておきます。
上記SQLは最安値が同額のものもその件数分出力されます。
この処理が自動で仕入先を決定するものであれば、発注数が重複しますから、限定するための条件が必要ですし、一旦画面に表示するなどして人的に決定する場合には、最安値が複数あることを示す情報が合ったほうが良いかと思います。

Comments

[ky_46]

こちらでほぼ希望通りの動作となりました。ありがとうございます。実データでは、同一IDで、同じ価格が出たことは記憶の限りありませんので、今回は大丈夫かと思います。ありがとうございました。

Answer 2

同じIDで同じ金額の最低値を持つデータがあるときにどうしたいかという条件が足りません
往々にして2つの仕入先が提案されるのはアプリ側で解釈ができない場合があります。

Comments

[ky_46]

ご回答ありがとうございます。同じIDで、同一価格があった場合は想定してませんでした。実データではまず無いと思いますので、今回は考慮しない事にします。ありがとうございます。

Answer 3

私見ですが本来グループ化は「同一値をまとめる」ために使うものであって
「最小のデータを持っている行がほしい」という案件には向かないと思っています。
グループ化はグループ化として使うほうがいいと思います。

私ならSQLの解釈を変えて
「IDが同じで、仕入提示が自分より小さいデータがないもの」
とします（コードは未検証）。
yambejp氏の仰る通り仕様が足りないので最低値同一の場合、同じIDでも複数行でます。

SELECT *
FROM question as t1
where not exists(
  select 0
  from question as t2
  where t2.ID=t1.ID
    t2.仕入提示<t1.仕入提示
)

Comments

[sazi]

>グループ化は「同一値をまとめる」ために使うものであって
>「最小のデータを持っている行がほしい」という案件には向かないと思っています。
具体的な理由がありましたら、ご教示下さい。

[ky_46]

こちら、私も興味あります。「グループ化して、その中で最小値を探す」のが、唯一の方法と思っていましたので…

[sousuke]

私の言い方が若干過激だったかもしれませんがsazi氏の使い方はオッケーだと思っています。
sazi氏の提示のコードはgroup byをしていません。最終のSQLでももちろんですがwhere句のサブクエリでもgroup byはしていませんよね？
あくまで『group byをする』理由について「最小の『データ』を取得するに当たっては向かない」ということです。
現に質問者さんはgroup byを書こうとして
select ID,min(仕入提示)
from question
group by ID,?????

こうなってしまっています。
このsqlを生かしてserial等を正しく取得するのに向いているとは私は思っていません、ということです。

[sousuke]

ky_46氏
>「グループ化して、その中で最小値を『探す』」
とおっしゃっている通り
『探す』という動作はSQLにおいては本来where句で表現すべき、ということです。
group by自体は『探す』という動作ではないのでそれのために使うべきではない、
という感じで伝わりますでしょうか？
sazi氏のSQLは最小値で『探して』います。

[sazi]

成程、「最小の『データ』(行)を取得」するにあたっては、全体をgroup byして取得するような考え方は適切ではないということですね。