14.19.3 MySQL Handling of GROUP BY

14.19.3 MySQL의 GROUP BY 처리

SQL-92 및 그 이전 버전에서는 select 목록, HAVING 조건, 또는 ORDER BY 목록이 GROUP BY 절에 이름이 명시되지 않은 비집계(nonaggregated) 열을 참조하는 쿼리를 허용하지 않습니다. 예를 들어, 다음 쿼리는 select 목록에 있는 비집계 열 name이 GROUP BY에 나타나지 않기 때문에 표준 SQL-92에서는 허용되지 않습니다:

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

이 쿼리가 SQL-92에서 합법이 되려면, name 열을 select 목록에서 생략하거나 GROUP BY 절에 명시해야 합니다.

SQL:1999 이후 버전에서는, 선택적 기능 T301에 따라 비집계 열이 GROUP BY 열에 대해 함수적 종속(functionally dependent)인 경우 이를 허용합니다: name과 custid 사이에 그러한 관계가 존재한다면 이 쿼리는 합법입니다. 예를 들어, custid가 customers의 primary key인 경우가 이에 해당합니다.

MySQL은 함수적 종속성을 감지하는 기능을 구현합니다. ONLY_FULL_GROUP_BY SQL mode가 활성화되어 있다면(기본적으로 활성화되어 있음), select 목록, HAVING 조건, 또는 ORDER BY 목록이 GROUP BY 절에 이름이 명시되지 않았고, 동시에 GROUP BY 열에 함수적으로 종속되지도 않은 비집계 열을 참조하는 쿼리를 MySQL은 거부합니다.

또한 MySQL은 SQL ONLY_FULL_GROUP_BY mode가 활성화된 경우에도, GROUP BY 절에 이름이 명시되지 않은 비집계 열을, 그 열이 단일 값으로 제한되는 한 허용합니다. 다음 예는 이를 보여 줍니다:

1mysql> CREATE TABLE mytable (
2    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
3    ->    a VARCHAR(10),
4    ->    b INT
5    -> );
6
7mysql> INSERT INTO mytable
8    -> VALUES (1, 'abc', 1000),
9    ->        (2, 'abc', 2000),
10    ->        (3, 'def', 4000);
11
12mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
13
14mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
15+------+--------+
16| a    | SUM(b) |
17+------+--------+
18| abc  |   3000 |
19+------+--------+

ONLY_FULL_GROUP_BY를 사용할 때, SELECT 목록에 둘 이상의 비집계 열을 포함하는 것도 가능합니다. 이 경우, 각 비집계 열은 모두 WHERE 절에서 단일 값으로 제한되어야 하며, 이러한 모든 제한 조건은 논리적 AND로 결합되어야 합니다. 다음 예는 이를 보여 줍니다:

1mysql> DROP TABLE IF EXISTS mytable;
2
3mysql> CREATE TABLE mytable (
4    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
5    ->    a VARCHAR(10),
6    ->    b VARCHAR(10),
7    ->    c INT
8    -> );
9
10mysql> INSERT INTO mytable
11    -> VALUES (1, 'abc', 'qrs', 1000),
12    ->        (2, 'abc', 'tuv', 2000),
13    ->        (3, 'def', 'qrs', 4000),
14    ->        (4, 'def', 'tuv', 8000),
15    ->        (5, 'abc', 'qrs', 16000),
16    ->        (6, 'def', 'tuv', 32000);
17
18mysql> SELECT @@session.sql_mode;
19+---------------------------------------------------------------+
20| @@session.sql_mode                                            |
21+---------------------------------------------------------------+
22| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
23+---------------------------------------------------------------+
24
25mysql> SELECT a, b, SUM(c) FROM mytable
26    ->     WHERE a = 'abc' AND b = 'qrs';
27+------+------+--------+
28| a    | b    | SUM(c) |
29+------+------+--------+
30| abc  | qrs  |  17000 |
31+------+------+--------+

ONLY_FULL_GROUP_BY가 비활성화되면, MySQL은 표준 SQL의 GROUP BY 사용법을 확장하여, select 목록, HAVING 조건, 또는 ORDER BY 목록이, GROUP BY 열에 함수적으로 종속되지 않은 비집계 열을 참조하더라도 허용합니다. 이로 인해 MySQL은 앞에서 제시한 쿼리를 허용합니다.

이 경우, 서버는 각 그룹으로부터 임의의 값을 선택할 수 있으므로, 그 값들이 동일하지 않다면 선택되는 값은 비결정적이며, 이는 아마도 바람직한 동작이 아닐 것입니다. 더 나아가, 각 그룹에서 어떤 값이 선택될지에 대해서는 ORDER BY 절을 추가해도 영향을 줄 수 없습니다. 결과 집합의 정렬은 값이 선택된 이후에 수행되며, ORDER BY는 각 그룹 내에서 서버가 선택하는 값에 영향을 주지 않습니다. ONLY_FULL_GROUP_BY를 비활성화하는 것은 주로, 데이터의 어떤 특성 때문에 GROUP BY에 이름이 명시되지 않은 각 비집계 열의 값이, 각 그룹 내에서 모두 동일하다는 사실을 알고 있을 때 유용합니다.

ONLY_FULL_GROUP_BY를 비활성화하지 않고도, 비집계 열을 참조할 때 ANY_VALUE()를 사용하여 동일한 효과를 얻을 수 있습니다.

다음 설명에서는 함수적 종속성, 함수적 종속성이 없을 때 MySQL이 생성하는 error 메시지, 그리고 함수적 종속성이 없는 상황에서 MySQL이 쿼리를 수락하도록 만드는 방법들을 보여 줍니다.

다음 쿼리는 select 목록의 비집계 열 address가 GROUP BY 절에 명시되어 있지 않기 때문에, ONLY_FULL_GROUP_BY가 활성화된 상태에서는 무효일 수 있습니다:

1SELECT name, address, MAX(age) FROM t GROUP BY name;

만약 name이 t의 primary key이거나 unique NOT NULL 열인 경우 이 쿼리는 유효합니다. 이러한 경우, MySQL은 선택된 열이 grouping 열에 함수적으로 종속되어 있다고 인식합니다. 예를 들어, name이 primary key인 경우, 각 그룹은 primary key 값이 하나만 존재하므로 행이 하나뿐이고, 그에 따라 address의 값도 하나만 존재합니다. 결과적으로, 그룹 내에서 어떤 address 값을 선택할지에 대해 임의성이 존재하지 않으므로, 쿼리를 거부할 필요가 없습니다.

반면, name이 t의 primary key도 아니고 unique NOT NULL 열도 아닌 경우에는 쿼리가 유효하지 않습니다. 이 경우 함수적 종속성을 추론할 수 없고, error가 발생합니다:

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

특정 데이터 집합에 대해서는 각 name 값이 실제로 address 값을 고유하게 결정한다는 사실을 알고 있다면, address는 효과적으로 name에 함수적으로 종속됩니다. MySQL에게 이 쿼리를 허용하도록 지시하려면 ANY_VALUE() 함수를 사용할 수 있습니다:

1SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

또는, ONLY_FULL_GROUP_BY를 비활성화할 수도 있습니다.

그러나 앞의 예는 상당히 단순합니다. 특히, 각 그룹에 오직 하나의 행만 포함되므로, single primary key 열에 대해 grouping을 수행하는 경우는 드뭅니다. 보다 복잡한 쿼리에서 함수적 종속성을 보여 주는 추가 예시는 Section 14.19.4, “Detection of Functional Dependence”를 참고하십시오.

쿼리에 aggregate function이 있고 GROUP BY 절이 없는 경우, select 목록, HAVING 조건, 또는 ORDER BY 목록에 비집계 열을 포함할 수 없습니다. 이는 ONLY_FULL_GROUP_BY가 활성화된 경우에 해당합니다:

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

GROUP BY가 없으면 단일 그룹만 존재하고, 그룹에 대해 어떤 name 값을 선택할지는 비결정적입니다. 이 경우에도, MySQL이 어떤 name 값을 선택하든 상관없다면 ANY_VALUE()를 사용할 수 있습니다:

1SELECT ANY_VALUE(name), MAX(age) FROM t;

ONLY_FULL_GROUP_BY는 또한 DISTINCT와 ORDER BY를 사용하는 쿼리의 처리에도 영향을 줍니다. c1, c2, c3 세 개의 열을 가진 테이블 t가 있고, 여기에 다음과 같은 행이 있다고 가정해 봅시다:

1c1 c2 c3
21  2  A
33  4  B
41  2  C

이제 다음 쿼리를 실행하여 결과가 c3에 따라 정렬되기를 기대한다고 가정합니다:

1SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

결과를 정렬하려면 먼저 중복을 제거해야 합니다. 하지만 중복을 제거하는 과정에서 첫 번째 행을 남겨 둘지, 세 번째 행을 남겨 둘지를 어떻게 결정해야 할까요? 이러한 임의적인 선택은 유지되는 c3 값에 영향을 미치며, 이는 다시 정렬 순서에 영향을 주어 정렬 결과를 임의적으로 만듭니다.

이 문제를 방지하기 위해, DISTINCT와 ORDER BY를 모두 사용하는 쿼리에서, ORDER BY expression이 다음 조건들 중 하나라도 만족하지 못하면 쿼리는 무효로 간주되고 거부됩니다:

• 해당 expression이 select 목록의 expression 중 하나와 동일할 것
• expression이 참조하는, 쿼리에서 선택된 테이블의 모든 열이 select 목록의 요소일 것

표준 SQL에 대한 또 다른 MySQL 확장은, select 목록에 있는 alias expression을 HAVING 절에서 참조할 수 있도록 허용한다는 점입니다. 예를 들어, 다음 쿼리는 orders 테이블에서 한 번만 등장하는 name 값을 반환합니다:

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

MySQL 확장 덕분에, 집계된 열에 대해 HAVING 절에서 alias를 사용할 수 있습니다:

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

표준 SQL에서는 GROUP BY 절에 column expression만을 허용하므로, 다음과 같은 statement는 FLOOR(value/100)이 column expression이 아니기 때문에 유효하지 않습니다:

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

MySQL은 표준 SQL을 확장하여 GROUP BY 절에 noncolumn expression을 허용하며, 위 statement를 유효한 것으로 간주합니다.

표준 SQL은 또한 GROUP BY 절에 alias를 허용하지 않습니다. MySQL은 표준 SQL을 확장하여 alias를 허용하므로, 이 쿼리를 다음과 같이 다른 방식으로 작성할 수 있습니다:

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

alias val은 GROUP BY 절에서 column expression으로 간주됩니다.

GROUP BY 절에 noncolumn expression이 존재하는 경우, MySQL은 그 expression과 select 목록에 있는 expression 간의 동일성을 인식합니다. 이는 ONLY_FULL_GROUP_BY SQL mode가 활성화된 상태에서도, GROUP BY id, FLOOR(value/100)를 포함하는 쿼리가 유효하다는 의미입니다. 같은 FLOOR() expression이 select 목록에도 존재하기 때문입니다.

그러나 MySQL은 GROUP BY의 noncolumn expression에 대한 함수적 종속성을 인식하려고 하지 않으므로, 다음 쿼리는 ONLY_FULL_GROUP_BY가 활성화되어 있는 한 무효입니다. 비록 세 번째로 선택된 expression이 id 열과 GROUP BY 절에 있는 FLOOR() expression에 대한 단순한 formula에 불과하더라도 마찬가지입니다:

1SELECT id, FLOOR(value/100), id+FLOOR(value/100)
2  FROM tbl_name
3  GROUP BY id, FLOOR(value/100);

이 문제에 대한 해결 방법은 derived table을 사용하는 것입니다:

1SELECT id, F, id+F
2  FROM
3    (SELECT id, FLOOR(value/100) AS F
4     FROM tbl_name
5     GROUP BY id, FLOOR(value/100)) AS dt;