MySQLにおけるGROUP BYの挙動について

Question

teratailの皆様にはいつもお世話になっております。
MySQLのGROUP BYの挙動についてご教授頂きたい点があるため、今回質問させて頂きました。

###前提・実現したいこと
下記のようなデータが入ったicという名のテーブルがあります。

id	use_date	in_station	out_station	use	is_request
1	2017/05/26	品川	秋葉原	130	0
2	2017/05/26	京急川崎	品川	300	0
3	2017/05/25	京成上野	日暮里	150	0
4	2017/05/25	秋葉原	上野	150	0
5	2017/05/25	品川	秋葉原	130	0
6	2017/05/25	京急川崎	品川	300	0
7	2017/05/26	品川	秋葉原	130	1
8	2017/05/26	京急川崎	品川	300	1
9	2017/05/25	京成上野	日暮里	150	0
10	2017/05/25	秋葉原	上野	150	0
11	2017/05/25	品川	秋葉原	130	1
12	2017/05/25	京急川崎	品川	300	1
13	2017/05/26	品川	秋葉原	130	0
14	2017/05/26	京急川崎	品川	300	0
15	2017/05/25	京成上野	日暮里	150	0
16	2017/05/25	秋葉原	上野	150	0
17	2017/05/25	品川	秋葉原	130	0
18	2017/05/25	京急川崎	品川	300	0

ここから、SQLで抽出した結果として下記のようになることを想定しています。

1. use_dateを昇順にした上で、use_date、in_station、out_stationが同一の重複レコードはまとめる
2. use_date、in_station、out_stationが同一だが、is_request=1とis_request=0が混在している場合は、is_request=1になっているレコードを優先する

id	use_date	in_station	out_station	use	is_request
12	2017/05/25	京急川崎	品川	300	1
11	2017/05/25	品川	秋葉原	130	1
4	2017/05/25	秋葉原	上野	150	0
3	2017/05/25	京成上野	日暮里	150	0
8	2017/05/26	京急川崎	品川	300	1
7	2017/05/26	品川	秋葉原	130	1

一応下記のようなSQLで、想定通りの結果を取得出来てはいるのですが、いまいち自信が持てないでいます。

SELECT * FROM (
	(
		SELECT * FROM `ic`
		WHERE `ic`.`use_date` LIKE "2017-05%"
			AND `ic`.`is_request` = 1
	)
	UNION
	(
		SELECT * FROM `ic`
		WHERE `ic`.`use_date` LIKE "2017-05%"
			AND `ic`.`is_request` = 0
		GROUP BY `ic`.`use_date`, `ic`.`in_station`, `ic`.`out_station`
	)
) AS `ic_data`
GROUP BY `ic_data`.`use_date`, `ic_data`.`in_station`, `ic_data`.`out_station`
ORDER BY `ic_data`.`use_date`, `ic_data`.`id` DESC;

ご教授頂きたい点

今回教えて頂きたい点は以下の通りです。
######1. is_request=1のレコードが優先して表示されるのは、UNIONで先に記述しているから、GROUP BYで優先されたのか

2017/05/26品川->秋葉原となっているレコードはid=1、id=7、id=13の3つが存在します。
UNIONで先にis_request=1を指定したとはいえ、GROUP BYしたら確実にis_request=1であるid=7のレコードになる、という確証は、そういえばどこから得られるのだろうと思いました。

######2. GROUP BYされる際に、代表して表示されるレコードを選ぶ基準

id=4、id=10、id=16はidが違うだけで他が同一ですが、その3つからid=4のレコードが選ばれている理由は何なのでしょうか。

以上2点です。

---

上記SQLを書いた時には上手くハマったな、と思ったのですが、上記のようなことを考え始めたらだんだん自信無くなってしまいました。

MySQLにご見識お持ちの皆様、ご教授頂けると非常に有り難いです。
何卒、よろしくお願い致します。

###補足
######動作確認環境
MariaDB

######上記テーブル、レコード作成用SQL

CREATE TABLE `ic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `use_date` datetime DEFAULT NULL,
  `in_station` varchar(32) DEFAULT NULL,
  `out_station` varchar(32) DEFAULT NULL,
  `use` int(11) DEFAULT NULL,
  `is_request` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','品川','秋葉原',130,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','京急川崎','品川',300,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京成上野','日暮里',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','秋葉原','上野',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','品川','秋葉原',130,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京急川崎','品川',300,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','品川','秋葉原',130,'1');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','京急川崎','品川',300,'1');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京成上野','日暮里',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','秋葉原','上野',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','品川','秋葉原',130,'1');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京急川崎','品川',300,'1');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','品川','秋葉原',130,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-26T00:00:00','京急川崎','品川',300,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京成上野','日暮里',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','秋葉原','上野',150,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','品川','秋葉原',130,'0');
INSERT INTO `ic` (`use_date`,`in_station`,`out_station`,`use`,`is_request`) VALUES ('2017-05-25T00:00:00','京急川崎','品川',300,'0');

###お礼
ベストアンサーとても迷ったのですが、疑問点をストレートにご回答頂いた上で具体例まで提示くださったKiyoshiMotoki様をベストアンサーとさせて頂きました。
他の皆様のご回答も、非常に勉強になりました。
訳あって仕事ではほぼ1人でコードを書いているので、経歴長い皆様の具体例など非常に参考になりました。
皆様有難うございました。

Answer 1

GROUP BYをかけて、その対象でない列を、集計関数を挟まずに取ってくる場合、どの行が取られるかは全く保証されません（MySQLマニュアル）。標準SQLでは、本来取得すらできません。つまり、1も2も「たまたまそうなっているだけ」です。

「値が1通り」とわかっている場合でも、MAX()などの集計関数を挟むのが適切です。

Comments

[saken649]

maisumakun様

ご回答頂き有難うございます。

公式の
> サーバーは各グループから任意の値を自由に選択できるため、同じ値でなければ、選択した値は不確定です。
といった記述について、全く理解出来ていない状態だったのですが、maisumakun様の説明でストンと落ちました。
ということは、今回のような用途で集計関数を使わずにGROUP BYするのは適切では無さそうですね。

集計関数についても正直あまり使ったことが無かったのですが、他の方がたくさんSQLの具体例を下さっているので、それを見て学んでいこうと思います。
有難うございます。

Answer 2

結論から言うと、

ご教授頂きたい点

の'1.'と'2.'のいずれも、偶然の結果に過ぎません。

MariaDB のマニュアルに、以下のように説明されているからです。
https://mariadb.com/kb/en/mariadb/select/#group-by

If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from.

もし、非集約カラムまたは非集約カラムから導かれた値を`SELECT`した場合、どの行の値が選択されるかは未定義です

---

若干 複雑ですが、以下の SQLで（偶然に頼ることなく）意図する結果を得られるかと思います。

SELECT ic1.*
FROM `ic` AS ic1
LEFT OUTER JOIN `ic` AS ic2
ON ic1.use_date = ic2.use_date
  AND ic1.in_station = ic2.in_station
  AND ic1.out_station = ic2.out_station
  AND ic1.is_request = ic2.is_request
  AND ic1.id > ic2.id
LEFT OUTER JOIN `ic` AS ic3
ON ic1.use_date = ic3.use_date
  AND ic1.in_station = ic3.in_station
  AND ic1.out_station = ic3.out_station
  AND ic1.is_request < ic3.is_request
WHERE ic2.id IS NULL
  AND ic3.id IS NULL
ORDER BY ic1.use_date, ic1.id DESC;

動作結果
http://sqlfiddle.com/#!9/ac891/3

※ ただし、is_requestカラムに 1より大きい値が存在し、「それでも 1 を優先したい」場合、もうひと工夫する必要があります。

Comments

[saken649]

KiyoshiMotoki様

ご回答頂き有難うございます。
他の方の回答でもありましたが、やはりGROUP BYはグループ対象にしたカラム以外の値は保証しないということなのですね。。
ということは一意のレコードが取れる保証が無い以上、今回のような用途で使うのは良くないようですね。
ご提示頂いたSQLでもGROUP BYされていませんし。。

ご提示頂いたSQLは正直まだ理解しきれていないのですが、動きを紐解いてみたいと思います。
なぜこの用途でJOINを使っているのか、そういったところから理解が出来ていないのが正直なところなので、もっと勉強します。

有難うございます。

[Orlofsky]

環境を用意していないので動作確認していませんが、DISTINCTを追加して条件に IN にすればSELECT文1つで足りそうな気がします。

SELECT DISTINCT * FROM `ic`
WHERE `ic`.`use_date` LIKE "2017-05%"
AND `ic`.`is_request` IN(0, 1)
ORDER BY ....

[KiyoshiMotoki]

saken649様

レスありがとうございます。

> ご提示頂いたSQLは正直まだ理解しきれていないのですが、動きを紐解いてみたいと思います。

私の回答の SQL文は、以下のページの 3つの SQL のうち、
"LEFT JOIN:"バージョンを応用したものになります。

https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html

理解の参考になれば幸いです。

Orlofsky様

ご提案ありがとうございます。
具体的な SQL文がわかりましたら、ご教示願います。

Answer 3

id=4、id=10、id=16はidが違うだけで他が同一ですが、その3つからid=4のレコードが選ばれている理由は何なのでしょうか。

同じ時は、idが小さい方を優先としました。

SELECT t4.* FROM `ic` t4 JOIN
  (SELECT min(`id`) id FROM `ic` t1 JOIN
    (SELECT `use_date`, `in_station`, `out_station`, max(`is_request`) AS is_request from ic
    GROUP BY 1,2,3) t2
  ON t1.`use_date`=t2.`use_date` AND t1.`in_station`=t2.`in_station`
  AND t1.`out_station`=t2.`out_station` AND t1.`is_request`=t2.`is_request`
GROUP BY t2.`use_date`,t2.`in_station`,t2.`out_station`,t2.`is_request`) t3
USING(`id`) ORDER BY t4.`use_date` DESC, t4.`id` DESC

Comments

[saken649]

A.Ichi様

SQLの具体例をご提示頂き有難うございます。
サブクエリと併用するような形でJOINを使ったことが無かったので、とても参考になりました。
こうした書き方出来るようになりたいです。。
有難うございます。

Answer 4

group BYの範囲は実際にデータを用してSQLを実行して確認していただくとして、、

GROUP BYを使う時はグループ関数とセットで使うのがマナーです。
SELECT結果を一意にしたいなら DISTINCT を使いましょう。

また、UNIONとUNION ALLの違い もちゃんと勉強しましょう。

Comments

[saken649]

Orlofsky様

ご回答頂き有難うございます。
GROUP BYとDISTINCTの使い分け方が正直あまり分かっていなかったので、今一度勉強し直してみます。

今回重複を消し込みたい意図もあってUNION ALLでなくUNIONで書いていましたが、意図が伝わらなかったということはまだ自分の書き方が甘いということですね。。
まだまだSQLに関しては初心者なので、今一度勉強致します。
有難うございます。

[Orlofsky]

最初のSELECTの条件に
AND `ic`.`is_request` = 1
とあり、2つ目のSELECTの条件が
AND `ic`.`is_request` = 0
とあるので、UNIONではなくUNION ALLの方が無駄なリソースを使わないと判断できます。
この回答にマイナスの評価をする人がいたのはショックです。SQLはきちんと勉強しないと後でパフォーマンス・チューニングで時間も費用も余計にかかります。

[saken649]

> UNIONではなくUNION ALLの方が無駄なリソースを使わないと判断できます。
よくよく考えてみたらそうですね、重複しようが無いので。。
それなら重複を気にしないUNION ALLの方が速いのは自明ですね。。

こうしたところがまだ自分は甘いと思うので、もっと勉強します。

Answer 5

おそらくsql_modeでONLY_FULL_GROUP_BYが指定されていません。
maisumakunさんの言う通り、MySQLがいい感じに行を返しています。

Comments

[saken649]

szk.様

ご回答頂き有難うございます。

そこは完全に盲点でした。
確かに確認してみたところ、指定されていませんでした。
maisumakun様が「標準SQLでは、本来取得すらできません。」と仰っていましたが、その挙動になるのですね。
こうした設定まで気にしたことが無かったので、非常に勉強になりました。
有難うございます。

Answer 6

命題の指示通り冗長にやるならこんな感じでは？

select coalesce(id1,id0) as id,t1.use_date,t1.in_station,t1.out_station
,t4.use,t4.is_request from(
select distinct use_date,in_station,out_station
from ic) as t1
left join
(select use_date,in_station,out_station,min(id) as id1 from ic
where is_request=1
group by use_date,in_station,out_station
) as t2 using(use_date,in_station,out_station)
left join
(select use_date,in_station,out_station,min(id) as id0 from ic
where is_request=0
group by use_date,in_station,out_station
) as t3 using(use_date,in_station,out_station)
inner join ic as t4 on coalesce(id1,id0)=id

改良版

select * from ic
where id in (
select min(id) from ic
where (use_date,in_station,out_station,is_request) in (
select use_date,in_station,out_station,max(is_request) as is_request from ic
where is_request in (0,1)
group by use_date,in_station,out_station
)
group by use_date,in_station,out_station
);

Comments

[yambejp]

ちなみに、ご提示の方法は「なんとなく」項目が選ばれているので
SQL文としては正しくありません
与えたSQL文に対して常に結果がひとつになるような記述をしてください

[saken649]

yambejp様

SQLの具体例をご提示頂き有難うございます。
他の方の回答にもありましたが、GROUP BYの性質的に「「なんとなく」項目が選ばれている」状態なのですね。。

最初ご提示頂いた例は正直まだ理解しきれていないのですが、COALESCEというのは初めて知った上、何かの機会に使えそうなので、そういった意味では非常に良い収穫となりました。
改良版に関しても、こういった書き方も出来るのかと非常に勉強になりました。
有難うございます。