GROUP BY 절과 HAVING 절 표현식 개선

개요

Oracle 23c부터 GROUP BY 절과 HAVING 절에 칼럼 앨리어스를 사용하거나, GROUP BY 절에 위치 표현식을 사용할 수 있습니다.

You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases. These enhancements make it easier to write GROUP BY and HAVING clauses. It can make SQL queries much more readable and maintainable while providing better SQL code portability.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 DATE, c2 NUMBER);

INSERT INTO t1 VALUES (TO_DATE ('2050-01-01 01', 'YYYY-MM-DD HH24'), 1);
INSERT INTO t1 VALUES (TO_DATE ('2050-01-01 02', 'YYYY-MM-DD HH24'), 2);
COMMIT;

 

칼럼 앨리어스

아래 쿼리는 GROUP BY 절과 HAVING 절에 칼럼 앨리어스를 사용합니다. 23.1 버전은 결과가 반환되지만, 19.3 버전은 에러가 발생합니다.

-- 2-1: 23.2
SELECT   TRUNC (c1) AS c1_n, SUM (c2) AS c2_n
    FROM t1 a
GROUP BY c1_n
  HAVING c2_n > 1;

C1_N                C2_N
------------------- ----
2050-01-01 00:00:00    3

1개의 행이 선택되었습니다.

-- 2-2: 19.3
SELECT   TRUNC (c1) AS c1_n, SUM (c2) AS c2_n
    FROM t1 a
GROUP BY c1_n
  HAVING c2_n > 1;
  
ORA-00904: "C2_N": 부적합한 식별자

 

10053 트레이스에서 변환된 쿼리를 확인할 수 있습니다.

-- 3
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT TRUNC("A"."C1") "C1_N",SUM("A"."C2") "C2_N" FROM "TUNA"."T1" "A" GROUP BY TRUNC("A"."C1") HAVING SUM("A"."C2")>1

 

GROUP BY 절은 ORDER BY 절과 달리 칼럼이 칼럼 앨리어스보다 먼저 해석된다는 점에 주의해야 합니다. 아래 4-1번 쿼리는 TRUNC (c1) 표현식이 아닌 c1 칼럼으로 그룹핑되어 두 행이 반환되었고, 4-2 번 쿼리는 HAVING 절에 SUM (c2) 표현식이 아닌 c2 칼럼이 사용되어 에러가 발생합니다.

-- 4-1
SELECT   TRUNC (c1) AS c1, SUM (c2) AS c2
    FROM t1 a
GROUP BY c1;

C1                  C2
------------------- --
2050-01-01 00:00:00  1
2050-01-01 00:00:00  2

2 행이 선택되었습니다.

-- 4-2
SELECT   TRUNC (c1) AS c1, SUM (c2) AS c2
    FROM t1 a
GROUP BY c1
  HAVING c2 > 1;

ORA-00979: "C2": GROUP BY 표현식과 일치하지 않음

 

위치 표현식

group_by_position_enabled 파라미터를 TRUE로 설정하면 GROUP BY 절에 위치 표현식을 사용할 수 있습니다.

-- 5
SELECT num, name, value, default_value, description
  FROM v$parameter
 WHERE name = 'group_by_position_enabled';

 NUM NAME                      VALUE DEFAULT_VALUE DESCRIPTION
---- ------------------------- ----- ------------- --------------------------------
5393 group_by_position_enabled FALSE FALSE         enable/disable group by position

1개의 행이 선택되었습니다.

 

아래 쿼리는 GROUP BY 절에 위치 표현식을 사용합니다. group_by_position_enabled 파라미터 설정에 따라 에러가 발생하거나 결과가 반환됩니다.

-- 6-1
ALTER SESSION SET group_by_position_enabled = FALSE;

SELECT   TRUNC (c1) AS c1, SUM (c2) AS c2
    FROM t1 a
GROUP BY 1;

ORA-00979: "C1": GROUP BY 표현식과 일치하지 않음

-- 6-2
ALTER SESSION SET group_by_position_enabled = TRUE;

SELECT   TRUNC (c1) AS c1, SUM (c2) AS c2
    FROM t1 a
GROUP BY 1;

C1                  C2
------------------- --
2050-01-01 00:00:00  3

1개의 행이 선택되었습니다.

 

10053 트레이스에서 변환된 쿼리를 확인할 수 있습니다.

-- 7
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT TRUNC("A"."C1") "C1",SUM("A"."C2") "C2" FROM "TUNA"."T1" "A" GROUP BY TRUNC("A"."C1")