💫 🦁 🤶🏼 条件付きグループ ↔️ 🍰 🐵

定期的に、グループ化に参加していない属性については、フィールドの1つに最大値を持つタプルを取得する必要があるという条件でデータセットをグループ化する必要があるタスクが発生します。

簡単な例を見てみましょう。

表があります：

CREATE TABLE IF NOT EXISTS shop ( id INT NOT NULL AUTO_INCREMENT, article INT(4) ZEROFILL NOT NULL, dealer VARCHAR(45) NOT NULL, price DECIMAL(8,2) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;

すべての記事で最高価格のディーラーを見つける必要があります。

このタスクにはいくつかの明白で簡単な解決策がありますが、そのうちの1つを知っています。これは他のすべてよりもはるかに優れています。

この課題に直面しましたか？それを解決する新しい方法を知りたいですか？猫をお願いします。

公式のmysql.comのドキュメントでさえ、このタスクをhasしみませんでした。3つの解決策が提案されています。

各リクエストの前に、インデックスとその実行時間を示します。 テーブルには100,000エントリが入力されます

 DELIMITER $$ CREATE PROCEDURE InsertRand() BEGIN DECLARE i INT; SET i = 1; START TRANSACTION; WHILE i <= 100000 DO INSERT INTO shop (article, dealer, price) VALUES (CEIL(RAND() * 9999), CEIL(RAND() * 999), RAND() * 9999); SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ;

最初のidx（記事）2,169 c：

 SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);

2番目のIDX（記事、価格）0.203 c

 SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article ) AS s2 ON s1.article = s2.article AND s1.price = s2.price;

3番目のIDX（記事、価格）0.593 c

 SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;

さて、今私のソリューション：

注意！ この方法は自己責任で使用してください！ MySQLの将来のバージョンでは、グループ化の動作が変更される可能性があります。

このソリューションは、指定されたグループ操作ではなく、グループ化に参加しない属性のグループ化は、最初に検出された値を取得するという事実に基づいています。したがって、データセットが事前にソートされている場合、目的の最大値を持つタプルを取得します。

4. idx（価格）0.328 c

 SELECT article, dealer, price FROM ( SELECT article, dealer, price FROM shop ORDER BY price desc) as t GROUP BY article ORDER BY NULL;

なぜなら前の例には並べ替えがなく、group byが自動的に追加するため、データがさらに並べ替えられないようにORDER BY NULLを指定する必要があります。そうしないと、結果が比較できなくなります。

しかし、インデックスを使用してソートされたデータを取得できるため、中間テーブルを作成する必要があるのはなぜですか。

5. idx（記事、価格）0.110 c

 SELECT article, dealer, price FROM shop use index (idx) GROUP BY article DESC ORDER BY NULL;

ボーナスソリューション：

ソリューションはMitch Dickinsonブログで見つかりました。それは最速であると主張していませんが、非常に独創的です。

6. idx（記事）0.202秒

 SELECT article, SUBSTRING_INDEX(GROUP_CONCAT(dealer ORDER BY price DESC),',',1) AS dealer, MAX(price) AS price FROM shop GROUP BY article;

コメントでは、 dm9は別の1ソリューションを提供しました。これは、以前のバージョンのドキュメントで説明されていました。

 SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT(MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop GROUP BY article;

一般に、この記事は特定の問題を解決することではなく、一般に受け入れられているソリューションのフレームワークを超えて代替方法を見つける方法の例を検討することを目的としています。時間と労力を大幅に節約できる興味深い最適化手法を見つける機会があるため、私にとってデータベースは常に興味深いものでした。

PS：注意深い読者は、すべてのサプライヤが戻る最初の方法とは異なり、各記事のメソッド4〜6が最大価格のサプライヤを1つだけ提供することに気付いたでしょう。しかし、この問題を解決するにあたり、私はどのサプライヤーにも興味を持っていたので、この問題は取るに足らないものでした。

PPS：この記事で提案されている代替方法は、中規模のテーブルでうまく機能します。レコードの数が100万を超える場合は、方法2が最適であり、レコードの数が既に非常に多い場合は、この情報を別のテーブルで事前に計算することを強くお勧めします。

条件付きグループ