このページは機械翻訳したものです。

12.20.3 MySQL での GROUP BY の処理

SQL-92 以前では、選択リスト、HAVING 条件または ORDER BY リストが GROUP BY 句で指定されていない非集計カラムを参照するクエリーは許可されません。 たとえば、このクエリーは、選択リストの非集計 name カラムが GROUP BY に表示されないため、標準 SQL-92 では無効です:

Press CTRL+C to copy
 
SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

クエリーを SQL-92 で有効にするには、選択リストから name カラムを省略するか、GROUP BY 句で名前を指定する必要があります。

SQL:1999 以降では、GROUP BY カラムに機能的に依存している場合、オプション機能 T301 ごとにこのような非集計が許可されます: このような関係が name と custid の間に存在する場合、クエリーは有効です。 たとえば、これは custid が customers の主キーであった場合です。

MySQL は、関数従属性の検出を実装しています。 ONLY_FULL_GROUP_BY SQL モードが有効な場合 (デフォルト)、MySQL は、選択リスト、HAVING 条件または ORDER BY リストが GROUP BY 句で名前が付けられておらず、機能的に依存していない非集計カラムを参照するクエリーを拒否します。

次の例に示すように、SQL ONLY_FULL_GROUP_BY モードが有効になっている場合、MySQL では、GROUP BY 句で指定されていない非集計カラムも許可されます:

Press CTRL+C to copy
 
mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

ONLY_FULL_GROUP_BY を使用する場合は、SELECT リストに複数の非集計カラムを含めることもできます。 この場合、次に示すように、このようなカラムはすべて WHERE 句の単一の値に制限する必要があり、このような制限条件はすべて論理 AND によって結合する必要があります:

Press CTRL+C to copy
 
mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

ONLY_FULL_GROUP_BY が無効になっている場合、GROUP BY の標準 SQL 使用に対する MySQL 拡張機能を使用すると、カラムが GROUP BY カラムに機能的に依存していなくても、選択リスト、HAVING 条件または ORDER BY リストで集計されていないカラムを参照できます。 これにより、MySQL は前述のクエリーを受け入れます。 この場合、サーバーは各グループから任意の値を自由に選択できるため、それらが同じでないかぎり、選択される値は決定的ではなく、必要な値ではない可能性があります。 さらに、ORDER BY 句を追加しても、各グループからの値の選択が影響を受ける可能性はありません。 結果セットのソートは値が選択された後に行われ、ORDER BY はサーバーが選択する各グループ内の値には影響しません。 ONLY_FULL_GROUP_BY の無効化は、主に、データの一部のプロパティのために、GROUP BY で指定されていない集計されていない各カラムのすべての値がグループごとに同じであることがわかっている場合に役立ちます。

ANY_VALUE() を使用して非集計カラムを参照することで、ONLY_FULL_GROUP_BY を無効にせずに同じ効果を得ることができます。

次の説明は、関数従属性、関数従属性が存在しない場合に MySQL が生成するエラーメッセージ、および関数従属性が存在しない場合に MySQL がクエリーを受け入れる方法を示しています。

選択リストの非集計 address カラムが GROUP BY 句で指定されていないため、ONLY_FULL_GROUP_BY が有効な場合、このクエリーは無効である可能性があります:

Press CTRL+C to copy
 
SELECT name, address, MAX(age) FROM t GROUP BY name;

このクエリーは、name が t の主キーであるか、一意の NOT NULL カラムである場合に有効です。 このような場合、MySQL は、選択されたカラムが機能的にグループ化カラムに依存していることを認識します。 たとえば、name が主キーの場合、各グループには主キーの値が 1 つのみであるため、その値によって address の値が決まります。 そのため、グループ内の address 値の選択にランダム性はなく、クエリーを拒否する必要はありません。

name が t の主キーまたは一意の NOT NULL カラムでない場合、クエリーは無効です。 この場合、関数従属性を推測できず、エラーが発生します:

Press CTRL+C to copy
 
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

それがわかっている場合、実際には特定のデータセットの各 name 値によって address 値が一意に決定され、address は事実上 name に依存します。 クエリーを受け入れるように MySQL に指示するには、ANY_VALUE() 関数を使用します:

Press CTRL+C to copy
 
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

または、ONLY_FULL_GROUP_BY を無効にします。

ただし、前述の例は非常に単純です。 特に、すべてのグループに 1 つの行のみが含まれるため、単一の主キーカラムでグループ化することはほとんどありません。 より複雑なクエリーでの関数従属性を示す追加の例は、セクション12.20.4「機能依存性の検出」 を参照してください。

クエリーに集計関数があり、GROUP BY 句がない場合、ONLY_FULL_GROUP_BY が有効になっている選択リスト、HAVING 条件または ORDER BY リストに非集計カラムを含めることはできません:

Press CTRL+C to copy
 
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

GROUP BY がない場合、単一のグループが存在し、どの name 値をグループに選択するかは非決定的です。 ここでも、MySQL が選択する name 値が重要でない場合は、ANY_VALUE() を使用できます:

Press CTRL+C to copy
 
SELECT ANY_VALUE(name), MAX(age) FROM t;

ONLY_FULL_GROUP_BY は、DISTINCT および ORDER BY を使用するクエリーの処理にも影響します。 次の行を含む c1、c2 および c3 の 3 つのカラムを含むテーブル t の場合を考えてみます:

Press CTRL+C to copy
 
c1 c2 c3
1  2  A
3  4  B
1  2  C

次のクエリーを実行し、結果を c3 で順序付けするとします:

Press CTRL+C to copy
 
SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

結果を並べ替えるには、最初に重複を排除する必要があります。 ただし、そのためには、最初の行または 3 番目の行を保持する必要がありますか。 この任意の選択は c3 の保持された値に影響し、これは順序付けに影響を与え、任意にすることもできます。 この問題を回避するために、いずれかの ORDER BY 式が次のいずれかの条件を満たさない場合、DISTINCT および ORDER BY を含むクエリーは無効として拒否されます:

• 式が選択リストの式と等しい

• 式によって参照され、クエリーで選択されたテーブルに属するすべてのカラムは、選択リストの要素です

標準 SQL に対する別の MySQL 拡張機能では、選択リスト内のエイリアス式への HAVING 句内の参照が許可されます。 たとえば、次のクエリーは、orders テーブルで 1 回だけ発生する name 値を返します。

Press CTRL+C to copy
 
SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

MySQL 拡張機能では、集計カラムの HAVING 句でエイリアスを使用できます:

Press CTRL+C to copy
 
SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

標準 SQL では GROUP BY 句のカラム式のみが許可されるため、FLOOR(value/100) は非カラム式であるため、このようなステートメントは無効です:

Press CTRL+C to copy
 
SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

MySQL は、標準 SQL を拡張して GROUP BY 句でカラム以外の式を許可し、前述のステートメントが有効であるとみなします。

標準 SQL では、GROUP BY 句でエイリアスを使用することもできません。 MySQL は標準 SQL を拡張してエイリアスを許可するため、クエリーを記述する別の方法は次のとおりです:

Press CTRL+C to copy
 
SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

エイリアス val は、GROUP BY 句のカラム式とみなされます。

GROUP BY 句にカラム以外の式が存在する場合、MySQL はその式と選択リスト内の式の等価性を認識します。 つまり、ONLY_FULL_GROUP_BY SQL モードが有効になっている場合、選択リストに同じ FLOOR() 式が出現するため、GROUP BY id, FLOOR(value/100) を含むクエリーは有効です。 ただし、MySQL は GROUP BY の非カラム式への関数従属性を認識しないため、3 番目に選択された式が id カラムと GROUP BY 句の FLOOR() 式の単純な式であっても、ONLY_FULL_GROUP_BY が有効な場合は次のクエリーは無効です:

Press CTRL+C to copy
 
SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

回避策は、導出テーブルを使用することです:

Press CTRL+C to copy
 
SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;