7-2 집계함수, 그룹함수, 윈도 함수, 기출문제

집계성 SQL 작성

1. 데이터 분석 함수의 개념

• 총합, 평균 등의 데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수
• 데이터 분석을 위한 다중 행 함수의 공통적인 특성
  • 단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아놓고 그룹당 단일 계산 결과를 반환
  • GROUP BY 구문을 활용하여 복수 행을 그룹핑한다.
  • SELECT, HAVING, ORDER BY 등의 구문을 활용한다.

2. 다중 행 연산자

1. 다중 행 연산자의 개념
   • 다중 행 연산자는 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자입니다.
2. 다중 행 연산자의 종류
   • 다중 행 연산자로 IN, ANY, SOME, ALL, EXISTS를 사용합니다.
   • 다중 행 비교 연산자는 단일 행 비교 연산자(＜, ＞, =, ≠)와 결합하여 사용할 수 있다.

연산자	설명	예시
IN	리턴되는 값 중에서 조건에 해당하는 값이 있으면 참이다.	이건 알아서 패쓰
ANY	서브쿼리가 반환한 여러 값들 중 하나라도 조건을 만족하면 전체가 참이 됩니다.	SELECT employee_name FROM employees WHERE salary > ANY (     SELECT salary      FROM employees      WHERE department = 'IT' );
ALL	ALL 조건은 서브쿼리에서 반환된 모든 값을 만족해야 참이 됩니다.	SELECT product_name FROM products WHERE price > ALL (     SELECT AVG(price)     FROM products     GROUP BY category );
EXISTS	EXISTS 조건은 서브쿼리가 하나 이상의 행을 반환하면 참이 됩니다.	SELECT customer_name FROM customers c WHERE EXISTS (     SELECT 1     FROM orders o     WHERE o.customer_id = c.customer_id     AND o.order_date > '2023-01-01' );

ANY는 하나 이상 만족, ALL은 모두 만족, EXISTS는 결과 존재 여부를 확인합니다.

2. 데이터 분석 합수의 종류

• SQL 표준에서는 데이터 튜플 간의 상호 연관 및 계산 분석을 위한 세 가지 함수가 있다.

함수	설명
집계 함수	여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
그룹 함수	소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
윈도 함수	데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

3. 집계 함수

1. 집계 함수 구문

• WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시에 HAVING을 사용하는 것이다.
• GROUP BY 구문 뒤에는 테이블을 구분하는 컬럼을 기재하여 그룹화한다.
• HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용하고, 상수나 집약 함수, 집약 키를 사용할 수 있다.

1. GROUP BY 구문

• SQL에서는 WHERE 구문을 활용하여 조건별 대상 ROW를 선택 => 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있다.
• GROUP BY는 그와 같은 경우에 사용하며, 아래와 같은 특성을 가진다.
  • NULL값 제외
  • ALIAS 사용이 불가하다. (별칭 out)
  • WHERE 구문 안에 포함되지 않는다.
  • WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 한다
    • 순서 : where -> group by
• GROUP BY 구문은 실제 구체적 데이터 분석값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며, 이 부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해 준다.

2. HAVING 구문

• HAVING 구문은 WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다.
• GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용한다.
• 쉽게 생각하면 GROUP BY 및 집계 함수에 대한 WHERE 구문

<참고>

HAVING 구문은 WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다

왜? ->
HAVING과 WHERE의 차이점은 데이터베이스의 처리 순서와 관련이 있습니다:

1. WHERE는 개별 행을 필터링하는 데 사용되며, 그룹화하기 전에 적용됩니다.

2. GROUP BY로 데이터를 그룹화한 후에야 집계 함수(SUM, AVG, COUNT 등)를 사용할 수 있습니다.

3. HAVING은 GROUP BY 후에 적용되므로, 집계된 결과에 대한 조건을 지정할 수 있습니다.

따라서 HAVING은 그룹화와 집계 후의 결과를 필터링하는 데 사용되고, WHERE는 그 전에 개별 행을 필터링하는 데 사용됩니다. 이런 처리 순서 때문에 WHERE에서는 집계 함수를 사용할 수 없고, HAVING에서만 가능한 것입니다.

2. 집계 함수 구문

FROM 테이블명 
WHERE 조건 
GROUP BY 컬럼1, 컬럼2, .. 
    HAVING 조건식(집계함수 포함);

3. 집계 함수의 종류

집계 함수	내용	예시
COUNT	복수 행의 줄 수를 반환하는 함수	- COUNT(*) : NULL 값을 포함한 모든 행을 계산에 포함합니다. - COUNT(column_name) : NULL 값은 무시됩니다. - COUNT(DISTINCT column_name) : 지정된 열의 중복 x NULL x
SUM	복수 행의 해당 컬럼 간의 합계를 계산하는 함수
AVG	복수 행의 해당 컬럼 간의 평균을 계산하는 함수
MAX	복수 행의 해당 컬럼 중 최댓값을 계산하는 함수
MIN	복수 행의 해당 컬럼 중 최솟값을 계산하는 함수
STDDEV	복수 행의 해당 컬럼 간의 표준편차를 계산하는 함수	SELECT STDDEV(salary) AS salary_stddev FROM employee_salaries;
VARIANCE	복수 행의 해당 컬럼 간의 분산을 계산하는 함수	SELECT VARIANCE(salary) AS salary_variance FROM employee_salaries;

4. 그룹 함수

• 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다

그룹 함수의 유형

1. ROLLUP 함수 개념

1. 목적:
   ROLLUP은 계층적 방식으로 그룹화된 결과를 생성하는 데 사용됩니다. 지정된 컬럼들에 대해 총계와 소계를 한 번에 계산할 수 있습니다.
2. 동작 방식:
   • 지정된 컬럼들의 모든 조합에 대한 소계를 생성합니다.
   • 마지막엔 전체 총계도 계산합니다.
3. N개의 컬럼을 ROLLUP에 지정하면, N+1 레벨의 결과가 생성되며, ROLLUP 내 컬럼 순서에 따라 계층 구조가 결정되므로, 순서 변경 시 결과가 달라집니다.
4. 예시

• 이 쿼리는 부서별, 직무별 급여 합계와 함께 부서별 소계, 전체 총계를 제공합니다.

SELECT 
    department, job, SUM(salary) as total_salary
FROM 
    employees
GROUP BY
    ROLLUP(department, job)

5. 소계 대상:
   • ROLLUP 안에 넣은 컬럼들이 소계 대상이 됩니다.
   • GROUP BY에만 넣은 컬럼들은 소계 대상에서 제외됩니다.
6. 규칙
   • SELECT 문 규칙:SELECT 절에 있는 컬럼들은 반드시 GROUP BY나 ROLLUP에 포함되어야 합니다.
   • ORDER BY 활용: ORDER BY를 사용하여 결과를 정렬하면 계층 구조를 더 명확하게 볼 수 있습니다.

사용 예

SELECT 
    column_name1,
    column_name2,
    ...
    aggregate_function(column_name)
FROM 
    table_name
GROUP BY
    ROLLUP(column_name1, column_name2, ...)

2. CUBE 함수

• CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이다.
• 연산이 많아 시스템에 부담을 준다.

SELECT 
    column_name1,
    column_name2,
    ...
    aggregate_function(column_name)
FROM 
    table_name
GROUP BY
    CUBE(column_name1, column_name2, ...)

3. GROUPING SETS 함수

• 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있는 그룹 함수
• GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있다.
• ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있다

SELECT 
    column_name1,
    column_name2,
    ...
    aggregate_function(column_name)
FROM 
    table_name
GROUP BY
    GROUPING SETS (
        (column_name1),
        (column_name2),
        ...
    )

그룹함수 전체 예시

년도	분기	부서	판매액
2023	Q1	전자제품	1000
2023	Q1	가전제품	1500
2023	Q2	전자제품	1200
2023	Q2	가전제품	1800
2024	Q1	전자제품	1100
2024	Q1	가전제품	1600
2024	Q2	전자제품	1300
2024	Q2	가전제품	2000

이 데이터를 바탕으로 다양한 그룹 함수를 사용한 예시

1. 기본 GROUP BY

SELECT 년도, 부서, SUM(판매액) as 총판매액
FROM sales
GROUP BY 년도, 부서
ORDER BY 년도, 부서;

결과:

년도	부서	총판매액
2023	가전제품	3300
2023	전자제품	2200
2024	가전제품	3600
2024	전자제품	2400

2. ROLLUP

SELECT 년도, 부서, SUM(판매액) as 총판매액
FROM sales
GROUP BY ROLLUP(년도, 부서)
ORDER BY 년도, 부서;

결과:

년도	부서	총판매액
2023	가전제품	3300
2023	전자제품	2200
2023	NULL	5500
2024	가전제품	3600
2024	전자제품	2400
2024	NULL	6000
NULL	NULL	11500

3. CUBE

SELECT 년도, 부서, SUM(판매액) as 총판매액
FROM sales
GROUP BY CUBE(년도, 부서)
ORDER BY 년도, 부서;

결과:

년도	부서	총판매액
2023	가전제품	3300
2023	전자제품	2200
2023	NULL	5500
2024	가전제품	3600
2024	전자제품	2400
2024	NULL	6000
NULL	가전제품	6900
NULL	전자제품	4600
NULL	NULL	11500

그룹 함수들의 차이점

1. 기본 GROUP BY: 지정된 컬럼들의 조합에 대해서만 집계를 수행합니다.
2. ROLLUP: 지정된 컬럼들의 계층적 관계를 고려하여 서브토탈을 생성합니다. 왼쪽에서 오른쪽으로 계층을 만들어 집계합니다.
3. CUBE: 지정된 컬럼들의 모든 가능한 조합에 대해 집계를 수행합니다.

5. 윈도 함수

1. 윈도 함수의 개념

• 윈도 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수이다.
• 윈도 함수를 OLAP( Online Analytical Processing )함수라고도 한다.

2. 윈도 함수의 구문

SELECT 
    column1,
    column2,
    ...
    OVER (
        [PARTITION BY column_name1, column_name2, ...]
        [ORDER BY column_name3 [ASC | DESC]]
        [ROWS | RANGE frame_specification]
    ) AS result_column_name
FROM 
    table_name

3. 순위 함수

• 순위 함수에는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다.

순위 함수	설명
RANK	특정 항목(컬럼)에 대한 순위를 구하는 함수 동일 순위의 레코드 존재 시 후순위는 넘어감 (2위가 3개인 레코드인 경우: 2위, 2위, 2위, 5위, 6위, -)
DENSE_RANK	레코드의 순위를 계산 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음 (2위가 3개인 레코드인 경우: 2위, 2위, 2위, 3위, 4위, …)
ROW_NUMBER	레코드의 순위를 계산 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여 (2위가 3개인 레코드인 경우: 2위, 3위, 4위, 5위, 6위, -)