Cohe
7-2 집계함수, 그룹함수, 윈도 함수, 기출문제 본문
728x90
반응형
집계성 SQL 작성
1. 데이터 분석 함수의 개념
- 총합, 평균 등의 데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수
- 데이터 분석을 위한 다중 행 함수의 공통적인 특성
- 단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아놓고 그룹당 단일 계산 결과를 반환
- GROUP BY 구문을 활용하여 복수 행을 그룹핑한다.
- SELECT, HAVING, ORDER BY 등의 구문을 활용한다.
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 함수 개념
- 목적:
ROLLUP은 계층적 방식으로 그룹화된 결과를 생성하는 데 사용됩니다. 지정된 컬럼들에 대해 총계와 소계를 한 번에 계산할 수 있습니다. - 동작 방식:
- 지정된 컬럼들의 모든 조합에 대한 소계를 생성합니다.
- 마지막엔 전체 총계도 계산합니다.
- N개의 컬럼을 ROLLUP에 지정하면, N+1 레벨의 결과가 생성되며, ROLLUP 내 컬럼 순서에 따라 계층 구조가 결정되므로, 순서 변경 시 결과가 달라집니다.
- 예시
- 이 쿼리는 부서별, 직무별 급여 합계와 함께 부서별 소계, 전체 총계를 제공합니다.
SELECT
department, job, SUM(salary) as total_salary
FROM
employees
GROUP BY
ROLLUP(department, job)- 소계 대상:
- ROLLUP 안에 넣은 컬럼들이 소계 대상이 됩니다.
- GROUP BY에만 넣은 컬럼들은 소계 대상에서 제외됩니다.
- 규칙
- 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 |
이 데이터를 바탕으로 다양한 그룹 함수를 사용한 예시
- 기본 GROUP BY
SELECT 년도, 부서, SUM(판매액) as 총판매액
FROM sales
GROUP BY 년도, 부서
ORDER BY 년도, 부서;결과:
| 년도 | 부서 | 총판매액 |
|---|---|---|
| 2023 | 가전제품 | 3300 |
| 2023 | 전자제품 | 2200 |
| 2024 | 가전제품 | 3600 |
| 2024 | 전자제품 | 2400 |
- 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 |
- 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 |
그룹 함수들의 차이점
- 기본 GROUP BY: 지정된 컬럼들의 조합에 대해서만 집계를 수행합니다.
- ROLLUP: 지정된 컬럼들의 계층적 관계를 고려하여 서브토탈을 생성합니다. 왼쪽에서 오른쪽으로 계층을 만들어 집계합니다.
- 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_name3. 순위 함수
- 순위 함수에는 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위, -) |
728x90
반응형
'자격증 공부 > 정보처리기사 실기' 카테고리의 다른 글
| 1-1 소프트웨어 개발방법론, 프로젝트 관리 (1) | 2024.09.30 |
|---|---|
| 오답 - 키워드 정리만 해놓음 (2) | 2024.09.29 |
| 7-1. SQL 응용 - 기출문제 (1) | 2024.09.26 |
| 7-1. SQL 응용 - DCL (1) | 2024.09.25 |
| 7-1. SQL 응용 - DML (0) | 2024.09.24 |