12.19.3 MySQL での GROUP BY の処理

標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない選択リスト内の非集約カラムを参照できません。たとえば、このクエリーは、選択リスト内の name カラムが GROUP BY に表示されていないため、標準 SQL では不正です。

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

このクエリーを正当にするには、name カラムを選択リストから削除するか、GROUP BY 句で名前を指定する必要があります。

MySQL では、選択リストが GROUP BY 句で名前が指定されていない非集約カラムを参照できないように、GROUP BY の使用が拡張されています。つまり、上記のクエリーは MySQL では正当です。この機能を使用すると、不要なカラムのソートおよびグループ化が回避されるため、パフォーマンスを改善できます。ただし、これは主に、GROUP BY で名前が指定されていない各非集約カラム内のすべての値がグループごとに同じである場合に役立ちます。サーバーは各グループから任意の値を自由に選択できるため、同じ値でなければ、選択した値は不確定です。さらに、ORDER BY 句を追加しても、各グループからの値の選択が影響を受ける可能性はありません。値が選択されたあとに結果セットのソートが発生しますが、ORDER BY によって、サーバーで選択された各グループ内の値は影響を受けません。

同様の MySQL 拡張が HAVING 句に適用されます。標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない HAVING 句の非集約カラムを参照できません。MySQL 拡張では、計算を簡単にするために、このようなカラムへの参照が許可されます。この拡張では、グループ化されていないカラムに同じグループに関する値が含まれると仮定されます。それ以外の場合は、結果が不確定です。

MySQL GROUP BY の拡張を無効にするには、ONLY_FULL_GROUP_BY SQL モードを有効にします。これにより、標準 SQL の動作が有効になります。GROUP BY 句で名前が指定されていないカラムは、集約関数で囲まなければ、選択リストまたは HAVING 句で使用できません。

また、ONLY_FULL_GROUP_BY によって、HAVING 句のエイリアスの使用も影響を受けません。たとえば、次のクエリーは、orders テーブルで 1 回だけ発生する name 値を返します。

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

MySQL では、集約カラム用に HAVING 句でエイリアスを使用することが許可されるように、この動作が拡張されています。

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

ONLY_FULL_GROUP_BY を有効にすると、この MySQL 拡張が無効になり、HAVING 句の c カラムが集約関数で囲まれていないため (これは、集約関数です)、「non-grouping field 'c' is used in HAVING clause」というエラーが発生します。

選択リストの拡張は、ORDER BY にも適用されます。つまり、GROUP BY 句に表示されていない ORDER BY 句の非集約カラムは参照できません。(ただし、すでに説明したように、ORDER BY によって、非集約カラムからどの値が選択されるのかは影響を受けません。選択されたあとにはじめてソートされます。)ONLY_FULL_GROUP_BY SQL モードが有効になっている場合は、この拡張が適用されません。

一部のケースでは、MIN() および MAX() を使用すると、一意でない場合でも特定のカラム値を取得できます。sort カラムに 6 桁以内の整数が含まれている場合、次のクエリーは、最小の sort 値を含む行から column の値を取得します。

SUBSTR(MIN(CONCAT(LPAD(sort,6,'0'),column)),7)

セクション3.6.4「特定のカラムのグループごとの最大値が格納されている行」を参照してください。

標準 SQL に従おうとすると、GROUP BY 句で式を使用できません。回避策として、式のエイリアスを使用します。

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

MySQL では、GROUP BY 句で式を使用することが許可されているため、エイリアスは必要ありません。

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);