10. SQL 그룹함수, 절차형 SQL - 2과목 데이터베이스 구축

10. SQL 그룹함수 기본 개념 - 2과목 데이터베이스 구축

이 전 시간에는 join, 집계함수 등을 배웠습니다. 반드시 알아야하는 부분이므로 반복하셔서 보셔야합니다. 정보처리기사 실기에서는 매회 SQL 문제를 꼭 출제 합니다. 1과목을 다 못보더라도 SQL은 여러번 학습해야합니다.

 

앞 선 두개의 포스팅과 같이 이번 시간 역시 SQL에 대한 내용입니다. 앞의 두 포스팅보다 크게 중요하지 않습니다. 다만, 그룹함수 group by는 꼭 익혀 가셔합니다.

목차

그룹함수란?

말 그래로 데이터를 특정 그룹을 만들어서 데이터를 보는 것입니다.

DB에는 학년, 성별등 여러개가 중복되는 키가 있을 수 있습니다. 이러한 속성을 합치기 위한 명령어입니다.

 

그룹 함수는 데이터베이스에서 여러 행을 그룹화하고 그룹에 대한 집계 연산을 수행하는 함수입니다.

주로 GROUP BY 구문과 함께 사용되어 특정 그룹에 속하는 데이터의 합계, 평균, 개수, 최대값, 최소값 등을 계산합니다.

이러한 함수들은 여러 행을 단일 결과 행으로 압축하여 데이터의 요약 정보를 제공합니다.

 

• ROLLUP: ROLLUP은 계층적인 그룹화를 생성하여 다양한 수준의 집계를 제공합니다. GROUP BY 절에서 사용되며, 지정된 열들을 기반으로 다양한 계층을 생성합니다
  • . ROLLUP을 사용하면 지정된 열들의 각 조합에 대한 집계 결과뿐만 아니라 부분합도 포함된 전체 결과를 반환합니다.
  • 예를 들어, ROLLUP을 사용하여 지역, 부서 및 직급에 따른 연간 매출을 집계할 수 있습니다. 결과에는 각 지역별, 지역-부서별, 지역-부서-직급별, 그리고 전체 매출에 대한 합계가 포함됩니다.
• CUBE: CUBE는 가능한 모든 조합에 대한 집계를 생성하여 다차원 큐브를 만듭니다.
  • 마찬가지로 GROUP BY 절에서 사용되며, 지정된 열들을 기반으로 모든 가능한 조합의 집계를 생성합니다. CUBE를 사용하면 ROLLUP과 유사하지만 모든 가능한 조합에 대한 집계를 생성합니다.
  • 예를 들어, CUBE를 사용하여 지역, 부서 및 직급에 따른 연간 매출을 집계할 수 있습니다. 결과에는 각 지역별, 부서별, 직급별, 지역-부서별, 지역-직급별, 부서-직급별, 그리고 전체 매출에 대한 합계가 포함됩니다.
• GROUPING SETS: GROUPING SETS는 여러 그룹화를 하나의 결과 집합으로 결합할 수 있도록 합니다. GROUP BY 절에서 사용되며, 여러 그룹화 수준을 지정할 수 있습니다.
  • GROUPING SETS를 사용하면 ROLLUP 및 CUBE와 유사한 결과를 생성할 수 있지만, 보다 유연하게 사용할 수 있습니다.

GROUP BY 예시

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

위의 쿼리는 "employees" 테이블에서 각 부서별 직원 수를 계산하는데 사용됩니다. 결과는 각 부서의 이름과 해당 부서에 속하는 직원의 수로 그룹화됩니다.

 

ROLLUP 예시

SELECT department, position, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, position);

위의 쿼리는 "employees" 테이블에서 각 부서 및 직급별 총 급여를 계산하는데 사용됩니다. ROLLUP 함수를 사용하여 부서와 직급에 대한 계층적 그룹화를 생성하고, 각 부서별, 각 부서-직급별, 전체 부서 및 전체 부서-직급에 대한 합계를 제공합니다.

CUBE 예시

SELECT department, position, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department, position);

위의 쿼리는 "employees" 테이블에서 각 부서 및 직급별 총 급여를 계산하는데 사용됩니다. CUBE 함수를 사용하여 가능한 모든 조합에 대한 집계를 생성하고, 각 부서별, 각 직급별, 각 부서-직급별, 전체 부서별, 전체 직급별, 전체 부서-직급별 및 전체 합계를 제공합니다

GROUPING SETS

SELECT department, position, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department), (position), (department, position));

위의 쿼리는 "employees" 테이블에서 각 부서 및 직급별 총 급여를 계산하는데 사용됩니다. GROUPING SETS 함수를 사용하여 부서별, 직급별 및 부서-직급별로 개별적으로 그룹화된 결과뿐만 아니라 각 부서별 및 전체 합계, 각 직급별 및 전체 합계, 전체 합계를 포함하는 결과를 제공합니다.

집계함수란?

집계 함수는 데이터베이스에서 여러 행의 값을 집계하거나 요약하는 데 사용되는 함수입니다. 주로 GROUP BY 구문과 함께 사용되어 특정 그룹에 속하는 데이터를 하나의 결과로 결합합니다. 이러한 함수들은 데이터의 통계 및 요약 정보를 제공하는 데 사용됩니다.

집계 함수	설명
COUNT	그룹 내의 행의 수를 계산합니다.
SUM	그룹 내 값의 합계를 계산합니다.
AVG	그룹 내 값의 평균을 계산합니다.
MAX	그룹 내 값 중 최대값을 찾습니다.
MIN	그룹 내 값 중 최소값을 찾습니다.
STDDEV	그룹 내 값의 표준 편차를 계산합니다.
VARIANCE	그룹 내 값의 분산을 계산합니다.

 

윈도우함수

시험에서 나오기 어려운 파트이므로 시험이 코앞이라면 그냥 PASS하셔도 좋습니다.

윈도우 함수는 데이터베이스에서 윈도우 또는 특정 범위의 행을 대상으로 연산을 수행하는 함수입니다. 

이러한 함수들은 일반적으로 데이터 집합 내에서 순위, 합계, 평균, 이동 평균, 누적 합 등을 계산하는 데 사용됩니다. 

윈도우 함수는 일반적으로 SELECT 문의 SELECT 목록에 나열되며, OVER 절을 사용하여 특정 윈도우를 정의합니다.

 

1. PARTITION BY: PARTITION BY 절을 사용하여 데이터를 그룹화하고 각 그룹에 대해 윈도우를 정의합니다. 이를 통해 각 그룹 내에서 함수가 독립적으로 실행됩니다.
2. ORDER BY: ORDER BY 절을 사용하여 데이터를 정렬하고 함수가 계산되는 순서를 정의합니다. 윈도우 함수가 데이터를 처리하는 순서를 결정합니다.
3. ROWS/RANGE: ROWS 또는 RANGE 절을 사용하여 윈도우의 범위를 정의합니다. ROWS는 행의 개수에 따라 범위를 정의하고, RANGE는 값의 범위에 따라 정의합니다.

일반적으로 사용되는 윈도우 함수에는 다음과 같은 것들이 있습니다:

• ROW_NUMBER: 각 행에 번호를 할당합니다.
• RANK: 동일한 값을 가진 행에 대해 같은 순위를 할당하고, 그 다음 값에 대해 순위를 증가시킵니다.
• DENSE_RANK: RANK와 유사하지만 중복된 값에 대해서는 순위를 증가시키지 않습니다.
• SUM, AVG, MAX, MIN: 지정된 범위 내의 합계, 평균, 최댓값, 최솟값을 계산합니다.
• LEAD, LAG: 현재 행의 이전 또는 다음 행의 값을 가져옵니다.
• FIRST_VALUE, LAST_VALUE: 지정된 범위 내에서 첫 번째 또는 마지막 값을 가져옵니다

 

윈도우함수 분류

1. 순위 함수 (Ranking Functions):
   • ROW_NUMBER(): 결과 집합에서 각 행에 번호를 할당합니다.
   • RANK(): 동일한 값을 가진 행에 대해 같은 순위를 할당하고, 그 다음 값에 대해 순위를 증가시킵니다.
   • DENSE_RANK(): RANK와 유사하지만 중복된 값에 대해서는 순위를 증가시키지 않습니다.
   • NTILE(n): 결과를 n개의 구간으로 나누어 각 구간에 대한 순위를 할당합니다.
2. 집계 함수 (Aggregate Functions):
   • SUM(): 지정된 열의 합계를 계산합니다.
   • AVG(): 지정된 열의 평균을 계산합니다.
   • MAX(): 지정된 열의 최댓값을 찾습니다.
   • MIN(): 지정된 열의 최솟값을 찾습니다.
   • COUNT(): 지정된 열의 개수를 계산합니다.
3. 순서 함수 (Ordering Functions):
   • LEAD(): 현재 행의 이후 n번째 행의 값을 가져옵니다.
   • LAG(): 현재 행의 이전 n번째 행의 값을 가져옵니다.
   • FIRST_VALUE(): 결과 집합에서 지정된 열의 첫 번째 값을 가져옵니다.
   • LAST_VALUE(): 결과 집합에서 지정된 열의 마지막 값을 가져옵니다.
4. 비율 함수 (Analytic Ratio Functions):
   • CUME_DIST(): 현재 행의 값이 어디에 위치하는지의 비율을 계산합니다.
   • PERCENT_RANK(): 각 행의 백분위 순위를 계산합니다.
   • PERCENTILE_CONT(): 지정된 백분위에 해당하는 값의 보간을 계산합니다.
   • PERCENTILE_DISC(): 지정된 백분위에 해당하는 값의 이산형 값을 계산합니다.

 

윈도우 함수 문법

1. 순위 함수 (Ranking Functions):
   • ROW_NUMBER(): 결과 집합에서 각 행에 번호를 할당합니다.
   • RANK(): 동일한 값을 가진 행에 대해 같은 순위를 할당하고, 그 다음 값에 대해 순위를 증가시킵니다.
   • DENSE_RANK(): RANK와 유사하지만 중복된 값에 대해서는 순위를 증가시키지 않습니다.
   • NTILE(n): 결과를 n개의 구간으로 나누어 각 구간에 대한 순위를 할당합니다.
2. 집계 함수 (Aggregate Functions):
   • SUM(): 지정된 열의 합계를 계산합니다.
   • AVG(): 지정된 열의 평균을 계산합니다.
   • MAX(): 지정된 열의 최댓값을 찾습니다.
   • MIN(): 지정된 열의 최솟값을 찾습니다.
   • COUNT(): 지정된 열의 개수를 계산합니다.
3. 순서 함수 (Ordering Functions):
   • LEAD(): 현재 행의 이후 n번째 행의 값을 가져옵니다.
   • LAG(): 현재 행의 이전 n번째 행의 값을 가져옵니다.
   • FIRST_VALUE(): 결과 집합에서 지정된 열의 첫 번째 값을 가져옵니다.
   • LAST_VALUE(): 결과 집합에서 지정된 열의 마지막 값을 가져옵니다.
4. 비율 함수 (Analytic Ratio Functions):
   • CUME_DIST(): 현재 행의 값이 어디에 위치하는지의 비율을 계산합니다.
   • PERCENT_RANK(): 각 행의 백분위 순위를 계산합니다.
   • PERCENTILE_CONT(): 지정된 백분위에 해당하는 값의 보간을 계산합니다.
   • PERCENTILE_DISC(): 지정된 백분위에 해당하는 값의 이산형 값을 계산합니다.

 

그룹 내 순위(rank) 관련함수

함수	설명
RANK()	동일한 값을 가진 행에 대해 같은 순위를 할당하고, 그 다음 값에 대해 순위를 증가시킵니다. 중복된 값이 있으면 같은 순위를 부여하고 다음 순위는 건너뜁니다.
DENSE_RANK()	RANK()와 유사하지만 중복된 값에 대해서도 순위를 증가시킵니다.
ROW_NUMBER()	결과 집합에서 각 행에 고유한 순번을 할당합니다. 중복된 값이 있어도 순서대로 번호를 부여합니다.

 

그룹 내 행순서 관련 함수

함수	예시
FIRST_VALUE()	결과 집합에서 지정된 열의 첫 번째 값을 가져옵니다.
LAST_VALUE()	결과 집합에서 지정된 열의 마지막 값을 가져옵니다.
LAG()	현재 행의 이전 n번째 행의 값을 가져옵니다.
LEAD()	현재 행의 다음 n번째 행의 값을 가져옵니다.

 

그룹 내 비율 함수

함수	설명
CUME_DIST()	현재 행의 값이 어디에 위치하는지의 비율을 계산합니다.
PERCENT_RANK()	각 행의 백분위 순위를 계산합니다.
NTILE(n)	결과를 n개의 구간으로 나누어 각 구간에 대한 순위를 할당합니다.
RATIO_TO_REPORT()	지정된 열의 값이 전체 값 중 어느 정도를 차지하는지의 비율을 계산합니다.

 

 

절차형 SQL란?

절차형 SQL은 프로시저, 트리거, 함수와 같은 데이터베이스 개체를 사용하여 일련의 SQL 문을 포함하는 방식을 의미합니다. 이러한 SQL은 일련의 단계나 절차를 수행하여 특정 작업을 수행합니다. 

주로 데이터베이스에서 복잡한 작업을 수행하거나 비즈니스 로직을 구현하는 데 사용됩니다.

저장프로시저 (Stored Procedure) 란?

저장 프로시저(Stored Procedure)란 데이터베이스에 미리 컴파일되어 저장된 SQL 쿼리 집합이며, 특정 작업을 수행하기 위한 프로그래밍적 로직이 포함되어 있습니다.

저장 프로시저는 데이터베이스에 저장되어 있으므로 클라이언트 애플리케이션에서 필요할 때 호출되어 실행됩니다.

 

• 입력값 출력값이 여러개인 것이 저장 프로시저이며, 저장함수는 Return 값이 하나.
• 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

개념

• 저장 프로시저는 일련의 SQL 문과 프로그래밍적 로직으로 구성되어 있습니다. 이것은 데이터베이스에 저장되어 있으며 필요할 때 호출하여 사용됩니다.
• 주로 데이터 조작, 비즈니스 로직, 보안 정책 적용 등을 위해 사용됩니다.
• 데이터베이스 시스템에 따라 다양한 언어로 작성할 수 있으며, 주로 SQL, PL/SQL, T-SQL 등이 사용됩니다

장점

1. 성능 향상: 저장 프로시저는 데이터베이스에 미리 컴파일되어 있으므로 실행 시간이 단축되고 네트워크 부하가 감소합니다.
2. 보안 강화: 클라이언트가 직접 데이터베이스에 접근하는 것을 제한하고, 저장 프로시저를 통해 데이터에 접근하도록 함으로써 보안을 강화할 수 있습니다.
3. 재사용성: 동일한 작업을 수행하는 여러 애플리케이션에서 동일한 저장 프로시저를 호출하여 코드의 중복을 줄일 수 있습니다.
4. 유지보수성: 저장 프로시저는 데이터베이스에 집중되어 있으므로 데이터베이스 스키마의 변경이 애플리케이션에 미치는 영향을 줄일 수 있습니다.

장점

1. 복잡성: 저장 프로시저를 작성하고 유지보수하는 데는 일정한 학습 곡선이 필요합니다.
2. 의존성: 저장 프로시저가 데이터베이스에 의존하기 때문에 데이터베이스 이전이나 교체가 어려울 수 있습니다.
3. 디버깅 어려움: 저장 프로시저의 오류를 추적하고 디버그하기 어려울 수 있습니다.

구조

CREATE FUNCTION AddTwoNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    DECLARE @result INT;
    SET @result = @num1 + @num2;
    RETURN @result;
END;

CALL CalculateAverageSalary(1); 와 같은 명령어를 써서 부서 ID가 1인 부서의 평균 연봉을 계산하고 출력하는 저장 프로시저를 호출할 수 있습니다.

구조의 순서별 명령어

1. BEGIN: 저장 프로시저의 시작을 나타내는 블록입니다.
2. BEGIN ... END: 예외 처리를 위한 블록으로, 실제 예외가 발생할 수 있는 SQL 문이나 작업을 포함합니다.
3. EXCEPTION: 예외 처리를 위한 블록의 시작을 나타냅니다. 이 블록은 실제 예외가 발생하면 실행됩니다.
4. PRINT '예외가 발생했습니다. 저장 프로시저를 종료합니다.';: 예외가 발생한 경우 실행되는 코드입니다. 이 경우에는 단순히 메시지를 출력하는 예시입니다.
5. END: 예외 처리를 위한 블록의 종료를 나타냅니다.
6. PRINT '저장 프로시저가 정상적으로 실행되었습니다.';: 예외 처리 이후에 실행되는 로직입니다. 예외가 발생하지 않았을 경우에만 실행됩니다.

각 구조 설명

• 헤더(Header): 저장 프로시저의 이름, 매개변수, 반환 값 등이 정의됩니다.
• 바디(Body): 실제로 작업을 수행하는 SQL 문과 프로그래밍 로직이 구현됩니다.
• 매개변수(Parameter): 저장 프로시저가 호출될 때 입력되는 값이나 변수입니다.
• 변수(Variable): 저장 프로시저 내에서 사용되는 임시 변수입니다.
• 제어 구조(Control Structure): 조건문(IF-ELSE), 반복문(WHILE, FOR) 등이 포함될 수 있습니다.

트리거(Trigger)란?

트리거(Trigger)는 데이터베이스에서 특정 이벤트가 발생할 때 자동으로 실행되는 일련의 SQL 문입니다. 

이벤트는 데이터베이스 테이블에 대한 INSERT, UPDATE, DELETE 등의 작업을 수행할 때 발생할 수 있습니다. 

트리거는 데이터베이스의 상태를 모니터링하고 데이터의 일관성과 무결성을 유지하기 위해 사용됩니다.

 

상품의 재고 수는 수도 없이 변하게 됩니다. 늘어나거나 줄어들거나 0개가 되기도 합니다. 주문 테이블에 insert가 일어나면 재고의 숫자가 변해야되기 때문에 Trigger가 쓰입니다.

 

테이블에 대한 이벤트가 발생하면 자동으로 실행됩니다.

 

개념

• 트리거는 특정 테이블에서 발생하는 이벤트(INSERT, UPDATE, DELETE)에 자동으로 반응하여 정의된 작업을 수행합니다.
• 주로 데이터의 일관성 유지, 데이터의 품질 향상, 보안 강화 등을 위해 사용됩니다.
• 트리거는 데이터베이스 상태를 모니터링하고 데이터 변경 시 특정 동작을 수행하는 데에 중점을 둡니다.

유형(행, 문장)

1. 행 트리거(Row Trigger): 특정 행이 변경될 때만 트리거가 실행됩니다.
   • 따라서 트리거 내에서 참조하는 행의 컨텍스트 정보를 쉽게 사용할 수 있습니다.
2. 문장 트리거(Statement Trigger): 특정 SQL 문장이 실행될 때 트리거가 실행됩니다.
   • 따라서 해당 문장에 대한 조건만을 검사하여 트리거가 실행됩니다.
   • insert, update, delete 문에 대해 단 한번만 수행

실행시기

• BEFORE: 이벤트가 발생하기 전에 트리거가 실행됩니다. 즉, 작업이 수행되기 전에 트리거가 작동합니다.
• AFTER: 이벤트가 발생한 후에 트리거가 실행됩니다. 즉, 작업이 수행된 후에 트리거가 작동합니다

트리커생성예시

CREATE TRIGGER log_new_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_log (order_id, order_date, customer_id)
    VALUES (NEW.order_id, NEW.order_date, NEW.customer_id);
END;

사용자 정의함수 (User-Defined Function, UDF) 란?

사용자 정의 함수(User-Defined Function, UDF)는 사용자가 직접 정의한 함수로, 데이터베이스에서 특정 작업을 수행하기 위해 사용됩니다. 사용자 정의 함수는 일련의 SQL 문과 프로그래밍 로직으로 구성되어 있으며, 데이터베이스 내에서 호출하여 사용할 수 있습니다.

 

개념

• 사용자 정의 함수는 데이터베이스에 미리 정의된 내장 함수와 달리 사용자가 필요에 따라 직접 정의하여 만들 수 있습니다.
• 주로 반복적으로 사용되는 복잡한 계산이나 비즈니스 로직을 간소화하고 중복을 줄이기 위해 사용됩니다.
• 데이터베이스 종류에 따라 다양한 언어로 작성할 수 있으며, 주로 SQL, PL/SQL, T-SQL 등이 사용됩니다

구조 예시

CREATE FUNCTION AddTwoNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    DECLARE @result INT;
    SET @result = @num1 + @num2;
    RETURN @result;
END;