【解決】データのMAX値を持つレコードをGROUP BY を使わずに絞込み、別のテーブルに結合させると、思うような結果が得られない

Question

前提・実現したいこと

データベース MySQL 5.7.33
開発環境 AWS cloud9 (t2.micro)

** 開発言語 PHP7.0
** フレームワーク Laravel 5.1.6

LaravelでWebアプリケーションの開発を行っているのですが、
クエリビルダの問題というよりもデータ自体に問題がありそうだと考えたため、
SQL言語ベースで質問をさせていただきます。内容に不備があればお知らせいただければ幸いです。

実現したい内容
「ある商品の売上個数が最大だった日を支店ごとに割り出す。」

参考にしたサイト
Qitta - MySQL でグループごとの最大値を持つレコードを取得する
→ このうち、「パターン3 WHERE NOT EXISTS」に沿ってクエリビルダを組んでいきました。
SQL Fiddle
→ Laravelで記述したクエリを toSql()メソッドでSQL文に直し、上記サイト上で検証を行っています。

テーブルの内容

CREATE TABLE `projects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `projects` VALUES
(998,'鉛筆ショップ','2021-07-01 00:00:00','2021-07-01 00:00:00'),
(999,'飴ちゃん屋','2021-07-01 00:00:00','2021-07-01 00:00:00');

CREATE TABLE `shops` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `shops_project_id_index` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shops` VALUES
(1,999,'大阪本店','2021-07-01 00:00:00','2021-07-01 00:00:00'),
(2,999,'東京支店','2021-07-01 00:00:00','2021-07-01 00:00:00');

CREATE TABLE `earnings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `shop_id` int(11) NOT NULL,
  `item_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `earned_date` date NOT NULL,
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  `earning` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `earnings_project_id_index` (`project_id`),
  KEY `earnings_shop_id_index` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `earnings` VALUES 
(1,999,1,'1','リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
(2,999,1,'2','ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
(3,999,1,'1','リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');

このうち、earnings テーブルには、ある店舗(shop_id)のある日(earned_date)におけるある商品(item_id)の総売上個数(earning)を、それひとつのみ入れていく形になります。
(テーブルの定義でunique系の制約もなく、分かりにくくて申し訳ありません…。)

なお、itemに関するテーブルもあるのですが、必要な情報はearningsテーブルに織り込んでいて結合にも使用しないため、ここでは割愛します。

実行内容

SELECT
  `shops`.`id` AS `shop_id`,
  `E1`.`id` AS `earning_id`,
  `E1`.`earning` AS `max_earning`,
  `E1`.`earned_date` AS `earned_date`,
  `E1`.`comment` AS `comment`
FROM
  `shops`
LEFT JOIN
  `earnings` AS `E1`
ON
  `E1`.`shop_id` = `shops`.`id`
AND
  `E1`.`item_id` = 2    # ** POINT **
WHERE
  `shops`.`project_id` = 999
AND
  NOT EXISTS(
    SELECT
      1
    FROM
      `earnings` AS `E2`
    WHERE
      E1.shop_id = E2.shop_id
    AND
      E1.earning < E2.earning
  )
;

発生している問題

上記コードの** POINT **行にて、値を1にした場合と2にした場合で実行結果が変わってしまいます。
<E1.item_id = 1の場合>

<E1.item_id = 2の場合>

shop_id=1でかつitem_id=2のレコードは一つ用意されているはずなのに、それがスキップされてしまいます。

試したこと

● item_id のデータ型が varcharになっている点に着目

[1] データ型とINSERTを修正する → 実行結果変わらず

CREATE TABLE `earnings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `shop_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
 # `item_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,  ** ここを上記に修正
  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `earned_date` date NOT NULL,
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  `earning` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `earnings_project_id_index` (`project_id`),
  KEY `earnings_shop_id_index` (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `earnings` VALUES 
(1,999,1,1,'リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
(2,999,1,2,'ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
(3,999,1,1,'リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');
#********↑ ここの''を外す

[2] WHEREクエリを文字列検索向けに修正する → 実行結果変わらず

SELECT
  `shops`.`id` AS `shop_id`,
  `E1`.`id` AS `earning_id`,
  `E1`.`earning` AS `max_earning`,
  `E1`.`earned_date` AS `earned_date`,
  `E1`.`comment` AS `comment`
FROM
  `shops`
LEFT JOIN
  `earnings` AS `E1`
ON
  `E1`.`shop_id` = `shops`.`id`
AND
  `E1`.`item_id` LIKE '2'    # ** ここを修正 **
WHERE
  `shops`.`project_id` = 999
AND
  NOT EXISTS(
    SELECT
      1
    FROM
      `earnings` AS `E2`
    WHERE
      E1.shop_id = E2.shop_id
    AND
      E1.earning < E2.earning
  )
;

● いじった中での発見…

[3] 15,16行目の project_idによる絞込み制約を消すと、中身は(null)だが一応shop_id=1の列が立つ

SELECT
  `shops`.`id` AS `shop_id`,
  `E1`.`id` AS `earning_id`,
  `E1`.`earning` AS `max_earning`,
  `E1`.`earned_date` AS `earned_date`,
  `E1`.`comment` AS `comment`
FROM
  `shops`
LEFT JOIN
  `earnings` AS `E1`
ON
  `E1`.`shop_id` = `shops`.`id`
AND
  `E1`.`item_id` = 2    # ** POINT **
AND
  NOT EXISTS(
    SELECT
      1
    FROM
      `earnings` AS `E2`
    WHERE
      E1.shop_id = E2.shop_id
    AND
      E1.earning < E2.earning
  )
;

→ 実行結果

追記

必要であればLaravelのクエリビルダを掲載することも可能です。
ただし、このページとは別の質問としてお伺いする可能性もあります。予めご了承ください。
↑ この質問ページ内でご回答を頂く中で、Laravelでの問題も解消できると確信を持てました。本当にありがとうございました。

また、念のため申し上げますと、ここでは自分の手元にあるコードを別の例に書き換えて記載しております。
データの内容などは架空のものになりますので、安心してご検証いただければ幸いです。

Comments

[neonemo]

要件を確認したいのですが、
店舗（shops）毎の最大の利益？（earning）の品目を取り出したい
って事ですか？

で、それを具体化する為に
①2つのテーブル「shops」を「earnings」を「shop_idが等しい」という条件で連結し
②earningsの最大値maxを求めたい

という事でしょうか？

ちなみに、問題点としている【`E1`.`item_id` = 2 # ** POINT **】ですが
連結条件を【item_id=2】とした為に、【item_id=1】のレコードが除外されているだけに見えます。
where句を外せばそもそも候補に入っていなかった確認できると思います。

[hika-rakuyo]

初めまして、ご質問ありがとうございます。
貴殿の仰っている要件とは少し異なります。
1商品に対して、最大売上個数を出した「日にち」を店舗ごとに割り出したいと思っています。(データの格納ルールを記載しておらず、混乱を招いてしまいました。申し訳ありません。記事に要件とルールを追加しておきます。)

簡単で恐縮ですが、回答とさせていただきます。

Answer 1

EXISTSのサブクエリにAND E1.item_id = E2.item_idの条件が抜けているのが問題のように見えます。

Comments

[hika-rakuyo]

初めまして、ご回答いただきありがとうございます。
頂いたアドバイスのように条件を足したところ、確かにうまくいきました。

なるほど、テーブルE2内ではあくまで同一shop内のearning最大値(item_idを問わない)を探していたに過ぎないのですね。E1側でitem_idの条件を書いた時点で何故か「これで充分」と思っていた自分がいました…。サブクエリにいるのはあくまで別のテーブルですものね。

見過ごしていた問題に気付かせていただいたので、こちらをBAとさせていただきます。

Answer 2

これでご期待の結果になりませんでしょうか？

SElECT
    `E1`.`shop_id`
  , `E1`.`id` AS `earning_id`
  , `E1`.`earning` AS `max_earning`
  , `E1`.`earned_date` AS `earned_date`
  , `E1`.`comment` AS `comment`
FROM `earnings` AS `E1`
INNER JOIN (
     # shop毎、item毎の最大売上個数を求めるサブクエリ
     SELECT
       `shop_id`
     , `item_id`
     , MAX(`earning`) AS `MAXearning`
    FROM `earnings`
    GROUP BY
       `shop_id`
     , `item_id`
     ) AS `E2` 
ON `E1`.`shop_id` = `E2`.`shop_id` 
AND `E1`.`item_id` = `E2`.`item_id`
AND `E1`.`earning` = `E2`.`MAXearning`
WHERE 
    `E1`.`item_id` = ? #必要に応じて値を入れる

Comments

[hika-rakuyo]

初めまして、ご回答ありがとうございます。
shop・itemごとにearningのMAX値だけ(MAXearning)を抽出したサブクエリを用意して、元のテーブルのearningのうちサブクエリで生成したテーブルのMAX値と一致するものを持ってくる、というような意味合いですね。これを採用することもできそうです。

[hika-rakuyo]

ただ、ベタ打ちで試して気になったのは、今回あえて何もレコードを入れていなかった shop_id=2 の場合が入ってこない部分ですね…。earningテーブルに該当するレコードが入っていないのだから、集計にかけられなくて当たり前です。
「売上が一切発生しなかった商品についても、earnings = 0 を必ず入力する」というルールを徹底できればいい話ですよね…。