MySQLで最大の累積値とその出現回数を取得するクエリの書き方

カラム同士を掛け合わせた値（累積値）の中から最大値と、その出現回数を取得したい場合は、集計関数 COUNT(*) をサブクエリと組み合わせて使用します。あわせて GROUP BY 句も活用します。

テーブルの作成

まず、以下のコマンドでテーブルを作成しましょう。

mysql> create table demo23
-> (
-> id int not null auto_increment primary key,
-> value1 int,
-> value2 int
-> );
Query OK, 0 rows affected (1.65 sec)

レコードの挿入

続いて、INSERTコマンドを使ってテーブルにレコードを追加していきます。

mysql> insert into demo23(value1,value2) values(5,600);
Query OK, 1 row affected (0.20 sec)

mysql> insert into demo23(value1,value2) values(20,800);
Query OK, 1 row affected (0.06 sec)

mysql> insert into demo23(value1,value2) values(7,400);
Query OK, 1 row affected (0.20 sec)

mysql> insert into demo23(value1,value2) values(6,500);
Query OK, 1 row affected (0.17 sec)

mysql> insert into demo23(value1,value2) values(10,300);
Query OK, 1 row affected (0.12 sec)

mysql> insert into demo23(value1,value2) values(11,500);
Query OK, 1 row affected (0.14 sec)

登録データの確認

SELECT文を実行して、テーブルに格納されたレコードを表示してみましょう。

mysql> select *from demo23;

すると、次のような出力が得られます。

+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
| 1  | 5      | 600    |
| 2  | 20     | 800    |
| 3  | 7      | 400    |
| 4  | 6      | 500    |
| 5  | 10     | 300    |
| 6  | 11     | 500    |
+----+--------+--------+
6 rows in set (0.00 sec)

最大の累積値を取得するクエリ

それでは本題です。value1とvalue2の積（累積値）の中で最大となる値と、その出現回数を取得するには、以下のようなクエリを実行します。

mysql> select total_value, count(*) as number_of_occurrences
-> from (
-> select value1*value2 as total_value
-> from demo23
-> ) t
-> group by total_value
-> order by total_value desc
-> limit 1 ;

実行結果は以下の通りです。

+-------------+-----------------------+
| total_value | number_of_occurrences |
+-------------+-----------------------+
| 16000       |                     1 |
+-------------+-----------------------+
1 row in set (0.00 sec)

クエリの仕組み

このクエリでは、まずサブクエリ内で各行の value1 × value2 の結果を total_value として計算しています。外側のクエリでは、その結果を GROUP BY でグループ化し、同じ累積値ごとの出現回数を COUNT(*) で数えています。最後に ORDER BY total_value DESC で降順に並べ替え、LIMIT 1 によって最大の累積値だけを取り出しています。

上記の例では、id=2のレコード（20 × 800）による「16000」が最大の累積値であり、その出現回数は1回という結果になっています。同じ積を持つ行が複数存在する場合は、number_of_occurrencesにその回数が反映されます。