概要
SQLにargmaxやargminは無いです。
なのでmaxをSELECTするサブクエリとINNER JOINすることによってがんばります。
argmaxやargminってなぁに
argmaxというのは、何か値を最大にするものを得るときに使うものです。
maxは最大でいくつになるかを得るときに使う点で異なります。
逆にargminというのは、何か値を最小にするものを得るときに使うものです。
これもminは最小でいくつになるかを得るときに使う点で異なります。
たとえば、記事がたくさんあったとき、「いいねの一番多い記事」はargmaxですが、その「一番多くついたいいねの数」はmaxです。
やってみる
今回は記事の著者ごとに一番いいねの多い記事をSELECTしようと思います。
面倒なのでSQLite3で試してみます。
お試し用データ
BEGIN EXCLUSIVE TRANSACTION;
CREATE TABLE IF NOT EXISTS `articles` (`id` INTEGER PRIMARY KEY AUTOINCREMENT,`author_id` INTEGER NOT NULL,`likes` INTEGER DEFAULT 0);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (1,120);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (1,120);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (1,100);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (2,100);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (2,80);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (2,80);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (3,100);
INSERT INTO `articles` (`author_id`,`likes`) VALUES (4,120);
COMMIT;
ふつうに全部SELECTするとこんな感じです。
id author_id likes
-- --------- -----
1 1 120
2 1 120
3 1 100
4 2 100
5 2 80
6 2 80
7 3 100
8 4 120
今回の結論です。
SELECT `a`.* FROM `articles` AS `a` INNER JOIN (
SELECT `author_id`,max(`likes`) AS `likes` FROM `articles` GROUP BY `author_id`
) AS `b` ON `a`.`author_id`=`b`.`author_id` WHERE `a`.`likes`=`b`.`likes`;
では試してみます。
id author_id likes
-- --------- -----
1 1 120
2 1 120
4 2 100
7 3 100
8 4 120
ちゃんとauthor_idごとにlikesが最大のものがすべて得られましたしました🎉
author_id=1のものもちゃんと２つあります🎉
結論
サブクエリでmaxをGROUP BYして、それとINNER JOINするだけでした。