3장. SQL 복수행 함수

1. GROUP 함수의 종류

함수 이름	의미	사용 예
COUNT	입력되는 데이터들의 총 건수를 출력	COUNT(데이터)
SUM	입력되는 데이터들의 합계값 구해서 출력	SUM(데이터)
AVG	입력되는 데이터들의 평균값 구해서 출력	AVG(데이터)
MAX	입력되는 데이터들 중 가장 큰 값을 출력	MAX(데이터)
MIN	입력되는 데이터들 중 가장 작은 값을 출력	MIN(데이터)
STDDEV	입력되는 데이터 값들의 표준 편차 값 출력	STDDEV(데이터)
VARIANCE	입력되는 데이터들의 분산값 출력	VARIANCE(데이터)
ROLLUP	입력되는 데이터들의 소계값을 자동으로 계산해서 출력	본문 예제 참고
CUBE	입력되는 데이터들의 소계 및 전체 총계를 자동 계산 후 출력	본문 예제 참고
GROUPINGSET	한 번의 쿼리로 여러 개의 함수들을 그룹으로 계산 후 출력	본문 예제 참고
LISTAGG	본문 예제 참고	본문 예제 참고
PIVOT	본문 예제 참고	본문 예제 참고
LAG	본문 예제 참고	본문 예제 참고
LEAD	본문 예제 참고	본문 예제 참고
RANK	본문 예제 참고	본문 예제 참고
DENSE_RANK	본문 예제 참고	본문 예제 참고
뉴계 집계하기	본문 예제 참고	본문 예제 참고

 

 

 

1) COUNT() 함수  /  SUM() 함수  /  AVG() 함수

COUNT : 전체 건수 검사할 때 사용. 데이터가 있는 전체 건수 검사. 특정 컬럼명을 넣으면 null이 아닌 것만 검사해서 출력

SUM : 합계를 수하는 함수. 특정 컬럼명을 넣으면 null을 제외하고 구한다.

AVG : 'average'의 약자. null인 데이터를 제외하고 출력된 값의 평균을 구한다.

 

# 총 건수
COUNT(데이터)

# 합
SUM(데이터)

# 평균
AVG(데이터)

 

 

 

2) MAX() 함수  /  MIN() 함수

가장 큰 날짜 = 가장 최근 날짜 / 가장 작은 날짜 = 가장 옛날 날짜

 

# 가장 큰 값
MXA(데이터)

# 가장 작은 값
MIN(데이터)

 

 

 

3) STDDEV() 함수  /  VARIANCE() 함수

# 표준편차
STDDEV(데이터)

# 분산값
VARIANCE(데이터)

 

 

 

 

---

 

 

 

 

2. 특정 조건으로 세부적인 그룹화하기 (GROUP BY절 사용하기)

GROUP BY: 특정 조건으로 여러 소그룹으로 나눈 다음에 계산을 하는 절. 순차적으로 여러 개를 묶어서 계산한다.

 

 

GROUP BY 절 사용 시 주의사항

1. SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다. 그렇지 않을 경우 에러가 발생한다.
   그룹이 3개로 나뉘어지면 총 3개의 한 줄짜리 결과가 나온다. 뭉쳐진 값이 나와야 하는데 세부적인 데이터가 출력된다. 이 때문에 하나의 값으로 출력되지 않는다.
2. GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias는 사용하면 안된다. 반드시 명시적으로 작성해야 구분이 확실하게 된다.

 

 

 

 

---

 

 

 

 

3. 그룹핑한 조건으로 검색하기 (HAVING 절 사용하기)

SELECT 컬럼1, AVG(NUL(컬럼2, 숫자))
FROM 테이블
WHERE 조건식
GROUP BY 컬럼1
HAVING AVG(NVL(컬럼2, 숫자)) 부호 숫자;

 

 

 

 

---

 

 

 

 

4. 반드시 알아야 하는 집계용 함수들

 

자동으로 소계외 합계를 구해주는 함수

 

1) ROLLUP() 함수

복잡한 내용을 한 번에 처리할 수 있는 함수. 포지션을 뭉뚱그려서 계산해주는 함수.

위로 말아 올리다, 한 뭉테기로 만들다(그룹화).

 

ROLLUP(컬럼1, 컬럼2, ···)

• 소괄호 안에 적은 컬럼들을 그룹화한다.

 

 

 

2) CUBE 함수 - 소계와 전체 합계까지 출력하는 함수

ROLLUP() 함수보다 더 좋은 정보 제공이 가능

 

CUBE(컬럼1, 컬럼2, ···)

• 소괄호 안에 적은 컬럼들을 그룹화한다.

 

 

 

3) GROUPING SETS

한 개의 테이블이 출력된다. 조금 더 직관적으로 볼 수 있게끔 그룹핑을 할 때 집합 개념으로 친다.

 

GROUPING SETS(컬럼1, 컬럼2)

• 소괄호 안의 컬럼들은 특정 그룹화 조합을 나타낸다.
• 2차로 나누어 그룹핑한다.
• 두 개의 테이블로 나눈다.
• 변할 수 있는 데이터는 나오지 않을 수도 있다.

 

GROUPING BY GROUPING SETS(컬럼1, 컬럼2)

• GROUPING SETS는 하나의 그룹이기 때문에 1차 개념으로 그룹핑이 된다.
• 연속으로 2번 그룹핑한다. 이를 하나의 테이블로 만든다.
• 변할 수 있는 데이터를 한 덩어리로 묶었기 때문에 나올 수 있다.

 

 

 

4) LISTAGG 함수 (11g에서 추가됨)

집계된 것을 리스트로 출력하는 함수. 한 줄로 출력해준다.

 

LISTAGG(컬럼, '특수-문자열')

 

주의 : 출력되는 값이 4000 byte가 넘을 경우 오류가 발생한다. 4000 byte가 넘을 경우 XMLAGG XML 함수를 쓴다.

 

 

 

5) PIVOT 함수 (11g 버전에서 추가된 함수)

 SQL에서 아래쪽으로 줄단위로 출력된 내용을 90도 돌린다. 표의 가로, 세로를 바꿔준다. '회전해서 바꾼다'라는 내용이다. 즉 SQL의 결과를 가로 방향에서 세로 방향으로 바꾸는, 결과물의 출력 방식을 바꾸는 함수이다.

 

PIVOT (
    aggregate_function(집계할-열)
    FOR 피벗열 IN (피벗값1, 피벗값2, ···)
) AS 별칭;

 

• 'aggregate_function(집계할-열)'은 피벗할 열에 적용할 집계 함수이다.
• 피벗열은 피벗할 열이다.
• 피벗값들은 피벗 열의 각기 다른 값들이다.
• 별칭은 피벗 테이블의 별칭이다.

 

 

 

6) LAG 함수

 이전 값을 현재 컬럼에서 출력하는 함수이다. SQL에서 이전 값을 한 줄 단위로 가져오기 때문에 이전 값을 가져오는 것이 힘들다. 따라서 LAG 함수를 사용하여 이 문제점을 보완한다.

 

LAG(출력할-컬럼명, OFFSET, 기본-출력값)
OVER (Query_partition-구문, ORDER-BY 정렬할-컬럼)

• OFFSET : 해당 값만큼 뛴 후 넣고자 하는 값을 넣는다.
• 기본-출력값 : OFFSET만큼 띈 칸에 카피해서 올 값이 없다면 해당 값으로 채워 넣는다.
• 'Query_partition-구문'은 'PARTITION BY 분할-컬럼'으로, 이때의 이 구문은 데이터를 파티션으로 나누는 기준 열이다. 생략할 수 있다.

 

 

 

7) LEAD 함수

LAG 함수의 반대. 문법도 비슷하다. 반대방향으로 복사된다.

 

LEAD(출력할-컬럼명, OFFSET, 기본-출력값)
OVER (Query_partition-구문 ORDER-BY-정렬할-컬럼)

• 'PARTITION BY 분할-컬럼'은 데이터를 파티션으로 나누는 기준 열이다. 생략할 수 있다.
• 'Query_partition-구문'은 'PARTITION BY 분할-컬럼'으로, 이때의 이 구문은 데이터를 파티션으로 나누는 기준 열이다. 생략할 수 있다.

 

 

 

8) RANK 함수 - 순위 출력 함수

순위 출력 함수. 특정 데이터의 순위 확인하기. 내가 원하는 데이터가 순위 상 몇 번째인가 확인하는 함수.

 

RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ ASC | DESC ])

• 조건값 = 원하는 값
• ORDER BY해서 데이터 값을 거지고 있다가 원하는값을 리턴한다.
• WITHIN GROUP → 단답식으로 출력

 맞는 타입을 넣는다면 에러가 발생한다. RANK 뒤에 나오는 데이터와 ORDER BY 뒤에 나오는 데이터는 같은 컬럼이어야 한다. 데이터를 정렬한 후 이름의 순위를 구해야 하는데 다른 컬럼일 경우 에러가 발생한다.

 

 

전체 순위 보기: RANK() 뒤가 OVER로 바뀐다.

RANK () OVER (ORDER BY 조건컬럼명 [ ASC | DESC ])

• 전체 순위 보기 → OVER
• 빈칸으로 두면 전체 순위를 매겨준다(전체적으로 순위를 다르게 준다).
• ASC : RANK를 매길 때 오름차순으로 매긴다.
• DESC : 정렬을 하는데, 내림차순으로 정렬한다.

 

 

9) DENSE_RANK 순위 함수

 통상적으로 순위를 따질 때는 공동 순위 다음에는 그 다음 숫자가 나와야 한다.

 RANK OVER로 순서를 정했을 때, 없는 순서 순위가 있을 때 있다. RANK() OVER은 공동 순위 후 그 다음 숫자가 나와야 하는데, 그 숫자를 건너 뛰고 다음 숫자를 쓴다. 그냥 RANK는 전체 숫자를 사용해서 숫자를 나열한다. 순위가 점프된다.

 

 공동으로 나오는 경우에 순위를 연속적으로 가져간다. 빈 순위가 없도록 한다. DENSE → 꽉꽉 채워준다.

 

DENSE_RANK() OVER (ORDER BY 컬럼 [ ASC | DESC ])

 

 

 

10) ROW_NUMBER() 순위 함수

 중복되는 값 후 건너뛰는 값이 없도록 연속적으로 숫자가 나올 수 있게끔하는 DENSE_RANK 함수. 하지만 이 함수는 공동 순위가 되어야 할 것이 그냥 숫자가 순차적으로 나열된다.

 컴퓨터 상에서는 이 순위를 RwaID로 사용한다. 데이터가 물리적으로 저장되는 엔진에 저장되는 순서로 순번이 매겨진다. 이것을 바탕으로 순위를 매긴다.

 

ROW_NUMBER() OVER (ORDER BY 컬럼 [ ASC | DESC ])

 

 

 

11) SUM() OVER를 활용한 누계 구하기

 누계를 구할 때가 있다. 하나의 데이터가 있을 때 하나의 데이터가 쌓일 때마다 그 데이터를 처음의 데이터와 더한다.

 

SUM(column_name) OVER (Query_partition-구문 ORDER BY 정렬-컬럼 ROWS 프레임-절)

 

• '컬럼'은 합계를 계산할 열이다.
• 'Query_partition-구문'은 'PARTITION BY 분할-컬럼'으로, 이때의 이 구문은 데이터를 파티션으로 나누는 기준 열이다. 생략할 수 있다.
• 'ORDER BY 정렬-컬럼'은 각 파티션 내에서 행의 순서를 지정하는 기준 열이다. 생략할 수 있다.
• 'ROWS frame_clause'는 윈도우 프레임을 지정하는 구문입니다. 생략할 수 있다.