1. 그룹함수

1

SELECT count(field1) AS cnt FROM table1 WHERE field1 >= 3

- 테이블의 특정 컬럼에 관한 여러 통계 지표 등을 계산하는 함수를 그룹함수(aggregate function)라 하며, 그룹함수를 포함하는 쿼리를 grouping query(또는 aggregate query)라 한다. 함수명(통계 지표를 계산할 컬럼 이름)의 형식을 갖는다.

- 그룹함수는 쿼리에서 SELECT와 함께 그 바로 옆 컬럼 위치에 쓴다. 이때, 이처럼 그룹함수와 함께 SELECT 쿼리를 썼을 때 이 SELECT 쿼리의 컬럼 위치에는 그 그룹함수 외에 다른 컬럼을 쓸 수 없다. 이는, 그룹함수를 사용하는 컬럼은 여러 개의 레코드에 대한 종합적인 정보로 이루어진 컬럼이기에 단 하나의 레코드만 갖기 때문이다. (즉, SELECT와 함께 단 하나의 그룹함수를 컬럼으로 쓰는 쿼리는 스칼라를 리턴한다.) 단, 다음 경우에는 그룹함수 컬럼의 레코드가 여럿일 수 있어 쿼리에서 그룹함수 외에 다른 컬럼을 함께 쓸 수 있다.

  • 그룹함수를 ORDER() 키워드와 함께 사용하는 경우

  • 쿼리에서 GROUP BY 키워드와 함께 사용하는 경우

1) COUNT(field1)

- 기존 테이블의 레코드들 중에서, field1 컬럼의 각 값들을 기준으로 레코드의 개수를 세서 그 개수를 값으로 하는 컬럼을 만들어 가져온다.

- field1 컬럼의 값 중 그 값이 null인 튜플이 있다면 그 튜플은 제외하고 개수를 센다. 단, COUNT(*)의 경우 값이 null인 튜플을 포함해 개수를 센다.

- COUNT 함수의 특수 인자들

  • COUNT(1) 또는 COUNT(*): 전체 행의 개수를 세서 리턴한다.

2) SUM(field1), MAX(field1), MIN(field1)

- field1 컬럼의 값들을 모두 합한 값/최댓값/최솟값을 값으로 하는 컬럼을 만들어 가져온다.

3) ROW_NUMBER()

- 기존 테이블에서 각 레코드의 행번호를 가져온다.

4) FIRST_VALUE(field1), LAST_VALUE(field1)

- field1 컬럼의 첫 번째 레코드에서의 값, 마지막 레코드에서의 값을 가져온다.

5) LISTAGG(field1, ', ')

- field1 컬럼의 모든 값들을 순서 상관 없이 이어 하나의 문자열로 만든다. 두 번째 인자가 각 값 사이에 들어가는 구분 문자가 된다. 이는 생략 가능하다.

  • LISTAGG로 결합시킬 때 그 값들의 순서를 정하려면 LISTAGG() 함수 뒤에 WITHIN GROUP(ORDER BY field2) 구문을 추가하면 된다.

2. GROUP BY

1

SELECT field1, SUM(field2) AS sum FROM table1 WHERE field1 >= 3 GROUP BY field1 HAVING sum >= 2 ORDER BY field1

- SELECT 쿼리에 GROUP BY 키워드를 컬럼 이름과 함께 쓸 경우, 해당 컬럼의 각 값을 기준으로 테이블의 모든 레코드들을 그룹화하고, 각 그룹에 대해 SELECT 키워드 옆 컬럼에 해당하는 값을 갖는 레코드 하나씩, 전체적으로 기존 테이블이 나뉜 그룹 개수만큼의 레코드를 갖고 있는 새로운 테이블이 리턴된다.

  • 각 그룹에 해당하는 레코드의 각 컬럼에 어떤 값이 들어갈지는 RDBMS의 구현마다 다른데, 만약 SELECT 키워드 옆에 GROUP BY와 함께 쓴 컬럼을 쓴다면 그룹화가 이루어진 각 값들이 리턴될 것이고, 그룹함수를 쓴다면 각 그룹에 속하는 레코드만을 대상으로 계산된 함수값이 리턴될 것이다. 이처럼 GROUP BY 키워드는 보통 그룹함수와 함께 쓰는 편이다.
field1 field2
1 10
2 20
3 30
3 10
4 40
4 20
5 10
  • 구체적으로, 위 쿼리는 table1이 위와 같을 때 다음 테이블을 가져온다.
field1 sum
3 40
4 60

- GROUP BY 뒤에 쓰는 컬럼 이름은 기존 테이블의 컬럼 이름을 그대로 쓰지 않고, “SELECT 옆 컬럼들 중 몇 번째 컬럼을 기준으로 그룹화를 할 것인지” 숫자를 적어도 된다. (즉, field1 대신 field1에 해당하는 숫자인 1을 적어도 된다.)

- GROUP BY 뒤에 쓰는 컬럼 이름은 둘 이상일 수 있다. 이 경우 GROUP BY 뒤에 쓰인 컬럼 이름에 해당하는 값이 모두 일치하는 경우를 기준으로 grouping이 된 테이블을 만들어 가져온다.

2. HAVING과 WHERE의 차이

- WHERE는 테이블에서 특정 컬럼을 가져오거나 GROUP BY, ORDER BY 등의 연산을 하기 전에 먼저 테이블 내 전체 레코드에서 특정 조건을 만족하는 레코드를 필터링하기 위해 사용되는 조건절이라 할 수 있다. 반면, HAVING은 그 앞의 모든 연산을 다 끝마쳐서 테이블을 만든 후에 그 결과 테이블에서 일부 레코드를 filtering하길 원할 때 사용되는 조건절이라 할 수 있다.

- grouping query에서 WHERE는 GROUP BY 연산을 하기 전에 filtering을 하기 때문에, GROUP BY 키워드를 통해 집계된 결과 테이블의 레코드를 필터링하는 조건절로 기능하지 못한다. 이에 따라, grouping query에서는 이를 통해 얻어온 테이블의 레코드를 filtering하려면 HAVING을 사용해야 한다.

- grouping query를 작성할 때에는 WHERE와 HAVING의 기능이 서로 다르다는 점을 명확히 알고 있어야 목적에 맞는 쿼리를 작성할 수 있다. 예를 들어, table1에서 field1의 값이 3 이상인 레코드들에 대해서만 평균이나 합을 구하고 싶다 같은 명확한 목적이 있는 경우에는 일단 WHERE를 통해 적절히 table1 내 레코드들을 필터링해야 목적에 맞으면서 불필요한 연산 낭비를 하지 않는 쿼리를 쓸 수 있다.

  • 단, HAVING이 모든 연산이 마친 뒤에 적용된다는 점을 이용해, grouping query가 아니더라도 예를 들어 SELECT절의 컬럼에 연산식과 alias가 있는 경우 이에 대한 filtering을 하기 위해 HAVING을 쓸 수 있다.

4. OVER

- 그룹함수를 통해 얻어오는 컬럼의 레코드 수는 기존 테이블의 레코드 수와 다르기 때문에, grouping query가 아니라면 SELECT 쿼리의 컬럼 위치에 그룹함수를 쓸 수 없다. 단, 그룹함수에 OVER 키워드를 함께 쓰면 grouping query가 아닌 쿼리에서도 그룹함수를 쓸 수 있으며, 이 경우 OVER 키워드를 통해 지정된 행 범위(window)에 해당하는 레코드를 대상으로 그룹함수를 적용한 결과값이 그 컬럼의 값이 된다.

- OVER 키워드는 인자로 다른 컬럼에 관한 여러 조건을 받는다.

  • OVER(): 인자를 비울 수 있다. 이 경우 기존 테이블의 맨 위 레코드에서부터 현재 레코드까지의 범위를 대상으로 OVER 키워드 앞에 쓴 그룹함수를 적용한다.

  • OVER(ORDER BY field2 ASC): 기존 테이블의 모든 레코드를 field2 컬럼의 값을 기준으로 오름차순 정렬한 뒤, 맨 위 레코드에서부터 현재 레코드까지의 범위를 대상으로 OVER 키워드 앞에 쓴 그룹함수를 적용한다. (단, 이렇게 해서 가져온 쿼리의 결과 테이블이 field2를 기준으로 정렬돼 있는 것은 아님에 유의한다.)

  • OVER(PARTITION BY field2): 기존 테이블의 모든 레코드들을 field2 컬럼의 값이 동일한 레코드들끼리 각각 다른 그룹으로 나눈 다음에, 각 그룹에 속하는 레코드들끼리를 기준으로 위에서 한 것과 같은 방식으로 그룹함수를 적용한다.