MAX関数を使用する際に商品名や人物名を使うことで何が起こっているのかを知りたい

SQLの教本をやっていてたびたびSELECT内でMAX(Name)といった例が出てくるのですが、MAXを使わなくても結果が変わらなかったので、使うことで何が起こるのかとそのメリットについて教えていただきたいです。

問題　テーブルSales、Products、Categoriesを連結し、SalesをCategoryIDでグループ化、CategoryID、CategoryName、Quantityの合計を表示しなさい。

私の解答↓

SELECT
  P.CategoryID
, C.CategoryName AS カテゴリ名 /*ここ*/
, SUM( S.Quantity ) AS 数量合計
FROM
 Sales AS S
 JOIN
 Products AS P
 ON S.ProductID = P.ProductID
 JOIN
 Categories AS C
 ON P.CategoryID = C.CategoryID
GROUP BY
 P.CategoryID
;

問題の解答例↓

SELECT
  P.CategoryID
, MAX( C.CategoryName ) AS カテゴリ名 /*ここ*/
, SUM( S.Quantity ) AS 数量合計
FROM
 Sales AS S
 JOIN
 Products AS P
 ON S.ProductID = P.ProductID
 JOIN
 Categories AS C
 ON P.CategoryID = C.CategoryID
GROUP BY
 P.CategoryID
;

gentaro

2021/03/13 17:00 編集

そのクエリを質問文に貼るのが最低限必要だと思うが。

退会済みユーザー

2021/03/13 17:05

貼り忘れてました。修正します。

m.ts10806

2021/03/13 21:26

テーブル定義とサンプルデータを提示してください。

xail2222

2021/03/13 23:08

GROUP BYに記載のないフィールドをSELECT句に持ってくることって構文的に大丈夫なんでしたっけ

行動規範の内容に同意します

回答1件

ベストアンサー

これについては MySQL8のリファレンス「12.20.3 MySQL Handling of GROUP BY」がわかりやすいと思います。

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

とあるように、GROUP BYを使っている場合、GROUP BYに指定していないカラムを非集約の状態でSELECTリストに指定することはSQL92では認められていませんでした。「私の解答」はC.CategoryNameが非集約の状態でSELECTリストにあるので、SQL92では不正なクエリになります。

正常なクエリにしたい場合、「問題の解答例」のようにCategoryNameを集約してしまえばよいわけです。CategoryIDとCategoryNameは1対1で紐づいているのであれば、MAX(CategoryName)はCategoryIDに紐づくCategoryNameを返すはずです。もっともここでは集約関数であればなんでもよいわけで、MINやLISTAGGでも問題ありません (個人的な感覚で言うと、こういう場合MAXが使われることが多いような気がしますが)。

では「私の解答」のクエリが想定通りに動いたのはなぜかというと、これも同じドキュメントに説明があって

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

SQL99(およびT301)では、GROUP BYに指定していないカラムでも、値が一意に特定されることが明らかなら非集約な状態でも問題ないということですね。「私の解答」の場合はCategoryIDからCategoryNameが一意に特定できるので、MAXを使わない(集約しない)でも正しく動いたというわけです。

SQL92とSQL99はともに標準SQLですが、DBMSやそのバージョンごとにどれだけ準拠しているかはまちまちですし、DBMSごとに独自拡張されていることもよくあります。「SQLの教本」がベースとしている環境と質問者さんが利用されている環境が違うのかと思います。

投稿2021/03/13 23:24