[번역] 14.19.3 MySQL Handling of GROUP BY

SQL-92 및 이전에서는 GROUP BY 절에 이름이 지정되지 않은 nonaggregated 컬럼을 참조하는 select 리스트(SELECT ... FROM 사이에 명시하는 컬럼 리스트를 말함), HAVING 조건 또는 ORDER BY 리스트의 쿼리는 허용하지 않았습니다. 예를 들어, 다음 쿼리는 select 목록에 nonaggregated name 컬럼이 GROUP BY에 없기 때문에 표준 SQL-92에서 허용하지 않습니다.

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 컬럼은 select 리스트에서 생략되거나 GROUP BY 절에 명명돼야 합니다.

SQL:1999 아상에서는 GROUP BY 컬럼에 funcionally dependent한 경우 선택적 기능 T301(선택적 기능 T301이 무엇인지 나와있지 않아서 해석이 힘듬)당 이러한 비집계를 허용합니다. [역주 : 맥락적으로 해석해볼 때, SQL-92에서는 GROUP BY 절에 명시하지 않은 비집계 컬럼은 사용할 수 없었지만 SQL:1999 부터 GROUP BY에 꼭 명시되어 있지 않더라도 GROUP BY 절의 리스트와 functionally dependent하다면 select 리스트에 사용할 수 있는 것으로 해석됨] : name 과 custid 가 functionally dependent 하다면 위 쿼리는 가능합니다. 예를 들어, custid 가 customers 의 기본 키인 경우가 있습니다.

MySQL은 functional dependence 감지를 구현합니다. 만약 ONLY_FULL_GROUP_BY 모드가 활성화돼 있다면(이게 디폴트임), MySQL은 GROUP BY 절에도 명명되지 않고 functionally dependent 한 비집계 컬럼을 seelct 리스트, HAVAION 조건 또는 ORDER BY 리스트에서 참조하는 쿼리를 거절합니다.

MySQL은 ONLY_FULL_GROUP_BY 모드가 활성화 됐을 때 GROUP BY 절에서 명명되지 않았지만 해당 컬럼이 하나의 값만 있다면 다음 예에서 볼 수 있듯이 비집계 컬럼을 허용합니다:

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 |
+------+--------+

[역주 : a를 'abc' 단일 값으로 제한했기 때문에 허용되었습니다.]

ONLY_FULL_GROUP_BY 를 사용하면 SELECT 리스트에 두 개 이상의 비집계 컬럼이 있을 수도 있습니다. 이 경우 모든 각각의 컬럼이 WHERE 절에서 단일 값으로 제한되야하고 앞선 모든 제한 조건들이 다음 예제처럼 논리적 AND 로 묶여야 합니다:

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 확장은 select 리스트, HAVING 조건, 또는 ORDER BY 리스트가 GROUP BY 컬럼에 functionally dependent 하지 않은 비집계 컬럼을 참조하는 것을 허용합니다. 이 경우 서버는 각 그룹에서 원하는 값을 자유롭게 선택할 수 있으며 이는 사용자가 원하지 않은 값이 아닐 수 있습니다.

ANY_VALUE()를 비집게 컬럼에 사용하면 ONLY_FULL_GROUP_BY 를 비활성화 하는 것 없이 같은 효과를 볼 수 있습니다.

... 위 개념에 대한 예제 설명들은 생략하겠습니다.

쿼리에 집계 함수가 있고 GROUP BY 절이 없는 경우 ONLY_FULL_GROUP_BY 가 활성화 상태에서 SELECT 리스트, HAVING 조건 또는 ORDER BY 리스트에 집계되지 않은 컬럼은 있을 수 없습니다:

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() 를 사용할 수 있습니다.

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

ONLY_FULL_GROUP_BY 는 DISTINCT 와 ORDER BY 를 사용하는 쿼리 처리에도 영향을 미칩니다. 세 개의 열 c1, c2, c3 이 있고 다음 행이 포함된 테이블 t 를 생각해 보겠습니다:

c1 c2 c3
1  2  A
3  4  B
1  2  C

다음 쿼리를 실행하고 결과가 c3에 따라 정렬될 것으로 예상한다고 가정해 보겠습니다:

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

결과를 정렬하려면 먼저 중복된 항목을 제거해야 합니다. 하지만 그러려면 첫 번째 행을 유지해야 할까요, 아니면 세 번째 행을 유지해야 할까요? 이 임의적 선택은 c3 의 유지된 값에 영향을 미치며, 이는 다시 순서에 영향을 미치고 이를 임의적으로 만듭니다. 이 문제를 방지하기 위해 DISTINCT 및 ORDER BY가 있는 쿼리는 ORDER BY 표현식이 이러한 조건 중 하나라도 충족하지 않으면 유효하지 않은 것으로 거부됩니다:

• ORDER BY에 있는 표현식이 SELECT 목록에 있는 표현식과 동일해야 함

• ORDER BY에 있는 표현식이 참조하는 모든 컬럼이 SELECT 목록에 포함되어 있어야 함

표준 SQL에 대한 또 다른 MySQL 확장 기능은 HAVING 절에서 select 목록의 별칭 표현식을 참조할 수 있도록 허용합니다. 예를 들어, 다음 쿼리는 테이블 orders 에서 한 번만 나타나는 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;

표준 SQL은 GROUP BY 절에서 열 표현식만 허용하므로 FLOOR(value/100) 은 열 표현식이 아니므로 다음과 같은 명령문은 유효하지 않습니다:

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

MySQL은 표준 SQL을 확장하여 GROUP BY 절에서 열이 아닌 표현식을 허용하고 이전 명령문을 유효하다고 간주합니다.

표준 SQL은 GROUP BY 절에 별칭을 허용하지 않습니다. MySQL은 표준 SQL을 확장하여 별칭을 허용하므로, 쿼리를 작성하는 또 다른 방법은 다음과 같습니다:

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

별칭 val은 GROUP BY 절의 열 표현식으로 간주됩니다.

GROUP BY 절에 열이 아닌 표현식이 있는 경우 MySQL은 해당 표현식과 select 목록의 표현식이 동일하다고 인식합니다. 즉, ONLY_FULL_GROUP_BY SQL 모드가 활성화된 경우 GROUP BY id, FLOOR(value/100) 을 포함하는 쿼리는 동일한 FLOOR() 표현식이 선택 목록에 있기 때문에 유효합니다. 그러나 MySQL은 GROUP BY noncolunm 표현식에 대한 functional dependence을 인식하지 않으므로 ONLYFULL_GROUP_BY가 활성화된 경우 다음 쿼리는 유효하지 않습니다. 세 번째로 선택된 표현식이 _id 열과 GROUP BY 절의 FLOOR() 표현식의 간단한 수식인 경우에도 마찬가지입니다.

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

해결 방법은 파생 테이블을 사용하는 것입니다.

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;