251017_SQLD의 활용(2)

1. 그룹 함수(Group Function)

그룹 함수는 집계 함수를 제외하고, 그룹화된 항목들 간에 다양한 소계를 계산할 수 있는 ROLLUP, CUBE, CROUPING SETS 함수 등이 있다.

ROLLUP	소그룹 간의 소계를 구하는 함수 칼럼으로 그룹을 만든 후, 각 칼럼의 중간 합계를 만들기 위해 사용하는 함수 ROLLUP의 그룹이 N개면 소계는 N+1개 생성된다.
CUBE	결합 가능한 모든 값에 대해 다차원 집계를 구하는 함수 CUBE의 그룹이 N개면 소계는 2^N개 생성된다.
GROUPING SETS	특정 컬럼들을 지정해 소계를 구할 수 있는 함수

1) ROLLUP

칼럼으로 그룹을 만든 후 각 칼럼의 중간 합계를 만드는 함수.

ROLLUP(A, B);

이때 A와 B의 순서가 바뀌게 되면 결과가 바뀌기 때문에 유의해야 한다.

ROLLUP(A, B) ➡️ A별 소계, A,B별 소계, 전체 소계

ROLLUP(B, A) ➡️ B별 소계, B,A별 소계, 전체 소계

SELECT name, job,
	   COUNT(*) AS total_employees
       SUM(salary) AS total_salary
FROM employees, departments
WHERE employees.deptno = departments.deptno
GROUP BY ROLLUP(name, job);

해당 코드에서 ROLLUP 함수는

- GROUP BY에 의한 표준 집계

- name별 모든 job의 소계

- 전체 집계

총 3종류의 결과값을 도출하게 된다.

 

2) CUBE

표시된 그룹화된 칼럼에 대한 계층별 집계를 만드는 함수

CUBE(A, B);

그룹화된 데이터의 모든 가능한 조합에 대해 합계를 계산한다.

이때 모든 조합을 구하기 때문에 순서는 바뀌어도 상관이 없다.

SELECT name, job,
	   COUNT(*) AS total_employees
       SUM(salary) AS total_salary
FROM employees, departments
WHERE employees.deptno = departments.deptno
GROUP BY CUBE(name, job);

해당 코드에서 CUBE 함수는

- name별 소계

- job별 소계

- name, job별 소계

- 전체 집계

총 4종류의 결과값을 도출하게 된다.

 

3) GROUPING SETS

GROUPING SETS는 해당 함수에 표시된 모든 칼럼들에 대한 개별 집계를 구할 수 있다.

GROUPING SETS(A, B);

다만 표기가 없는 경우, 전체 집계는 나오지 않는다.

SELECT name, job,
	   COUNT(*) AS total_employees
       SUM(salary) AS total_salary
FROM employees, departments
WHERE employees.deptno = departments.deptno
GROUP BY GROUPING SETS(name, job);

해당 코드에서 GROUPING SETS 함수는

- name별 소계

- job별 소계

총 2종류의 결과값을 도출하게 된다.

 

2. 윈도우 함수

윈도우 함수는 행과 행 간의 관계를 정의하기 위해 만들어진 함수이다.

기존 SQL 방식으로는 행과 행 간의 관계를 알기 위해 서브쿼리문을 여러 개 만들어서 복잡한 쿼리를 짜야 했지만, 윈도우 함수의 등장으로 메인 쿼리 하나만으로도 행과 행 간의 관계를 표시할 수 있게 되었다.

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY column][ORDER BY clause][WINDOWING clause])
FROM table_name;

윈도우 함수는 PARTITON BY, ORDER BY에 대해 이해를 해야 한다.

= PARTITION BY : 전체 데이터를 기준에 의해 소그룹으로 나눌 수 있다.

= ORDER BY : 어떤 항목에 대해 순위를 지정할 수 있다.

= WINDOWING : 행 기준의 범위를 지정할 수 있다.

== 기본적으로 WINDOWING 부분에 아무 표시도 없으면, RANGE 값이 되며 논리적인 값에 의한 범위를 지정한다.

== RANGE는 같은 값일 경우, 해당 값들을 하나의 범위로 보고 묶어서 한 번에 계산한다.

== 임의로 범위를 지정해야 하는 경우 ROWS를 쓸 수 있다. ROWS를 쓰면 같은 값일 경우 각 행씩 따로 계산한다.

=== BETWEEN A AND B : 윈도우 범위의 시작과 끝 위치를 지정

=== PRECEDING : 특정 행 이전 범위. UNBOUNDED PRECEDING은 첫 번째 행부터 설정한다는 의미

=== FOLLOWING : 특정 행까지의 범위. UNBOUNDED PRECEDING은 마지막 행까지 설정한다는 의미

=== CURRENT ROW : 현재 위치. 기본적으로 범위의 끝은 CURRENT ROW

 

1) 순위 함수

특정한 그룹 내의 순위를 계산할 수 있는 함수

RANK	특정 항목 및 파티션에 대해 순위를 계산 동일한 순위는 동일한 값이 부여 (3위가 2명이면 다음 순위는 5위가 됨)
DENSE_RANK	특정 항목 및 파티션에 대해 순위를 계산 동일한 순의를 하나의 건수로 계산(3위가 2명이면 다음 순위는 4위가 됨)
ROW_NUMBER	동일한 순위에 대해 고유의 순위를 부여함

 

2) 집계 함수

SUM	파티션 별로 합계 계산
AVG	파티션 별로 평균 계산
COUNT	파티션 별로 행 수 계산
MAX, MIN	파티션 별로 최대값과 최소값 계산

= 파티션 별 합을 함께 보고 싶을 때

SELECT mgr, name, salary,
	   SUM(salary) OVER (PARTITON BY mgr) AS mgr_sal_sum
FROM employees;

= 누적합을 보고 싶을 때(같은 데이터를 따로 계산하고 싶을 때)

== 누적합에서는 반드시 ORDER BY를 사용해야 한다.

SELECT mgr, name, salary,
       SUM(salary) OVER (ORDER BY salary
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mgr_sal_sum
FROM employees
ORDER BY salary;

 

3) 그룹 내 행 순서 함수

FIRST_VALUE	파티션에서 가장 처음에 나오는 값을 구한다.
LAST_VALUE	파티션에서 가장 나중에 나오는 값을 구한다.
LAG	특정 위치 이전의 행을 가지고 올 수 있다.
LEAD	특정 위치 이후의 행을 가지고 올 수 있다.

i. FIRST_VALUE

파티션별로 윈도우에서 가장 먼저 나온 값을 구할 때 사용하는 함수. 무조건 처음 나온 행을 표기함.

SELECT deptno, name, salary,
       FIRST_VALUE(name) OVER (PARTITION BY deptno ORDER BY salary DESC ROWS UNBOUNDED PRECEDING)
       AS dept_top_salary
FROM employees;

<쿼리 해석>

= deptno 별로 파티션 생성

== 그룹별로 salary의 내림차순 정렬

=== 파티션별로 첫 번째 행에서 윈도우가 시작

(LAST_VALUE는 FIRST_VALUE의 반대)

 

ii. LAG

현재 값을 기준으로 이전 값들 중 원하는 위치의 값을 가져오는 함수.

LAG(column[, location, default])
LAG(player, 5, 0)

player 컬럼에서 5행 앞의 값을 가져오고, 가져올 값이 없으면 0으로 처리

(LEAD는 LAG의 반대)

 

4) 그룹 내 비율 함수

RATIO_TO_REPORT	파티션 내 전체 합계에 대한 행 별 백분율을 조회
PRECENT_RANK	파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 하여 행의 순서별 백분율 조회
CUME_DIST	파티션 내 전체 건수에서 주어진 그룹에 대한 상대적인 누적 백분율 조회
NTILE	인자 값으로 N등분한 결과를 조회