[Database] SQLD 자격검정 실전문제 개념 정리 5편(p88-p108)

이번 포스팅에서는 이전 포스팅에 이어서 SQL 자격검정 실전문제 책을 보며 익힌 내용들을 정리하는 시간을 가져보도록 하겠습니다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 ROWS CURRENT ROW

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 ROWS CURRENT ROW는 SQL에서 윈도우 함수의 사용을 지정하는 구문입니다. 이 구문은 주로 집계 함수와 함께 사용되어, 데이터의 특정 범위에 대한 계산을 수행하는데 도움을 줍니다.

1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

이 구문은 현재 행과 그 이전의 모든 행을 포함하는 범위를 정의합니다. 즉, 윈도우 함수는 현재 행을 포함하여 그 이전의 모든 행에 대해 계산을 수행합니다.

예제 코드

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

해당 쿼리는 employee_id로 정렬된 각 직원의 salary에 대해 누적 합계를 계산합니다. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 구문 덕분에 첫 번째 행부터 현재 행까지의 모든 행의 salary가 합산됩니다.

2.ROWS CURRENT ROW

ROWS CURRENT ROW 구문은 윈도우 함수가 계산을 수행할 때, 오직 현재 행만 포함하도록 합니다. 즉, 윈도우 함수는 현재 행의 값만을 사용하여 계산을 수행합니다.

예제 코드

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id ROWS CURRENT ROW) AS current_salary
FROM
    employees;

해당 쿼리는 employee_id로 정렬된 각 직원의 salary를 단순히 현재 행의 salary 값으로 반환합니다. ROWS CURRENT ROW 구문은 현재 행의 값만을 포함하기 때문에 결과는 salary와 동일하게 나옵니다.

Grouping 함수

GROUPING 함수는 GROUP BY 절의 결과에서 열이 실제 값인지 아니면 집계된 값인지 확인하는 데 사용됩니다. 집계된 값일 경우 1을, 그렇지 않으면 0을 반환합니다.

예제 코드

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All Regions'
        ELSE region
    END AS region,
    CASE
        WHEN GROUPING(product) = 1 THEN 'All Products'
        ELSE product
    END AS product,
    SUM(amount) AS total_amount
FROM
    sales
GROUP BY
    ROLLUP(region, product);

region	product	total_amount
North	Product1	100.00
North	Product2	150.00
North	All Products	250.00
South	Product1	200.00
South	Product2	250.00
South	All Products	450.00
All Regions	All Products	700.00

결과는 GROUP BY ROLLUP(region, product)로 생성된 부분 합계와 총합계를 포함합니다. CASE 문과 GROUPING 함수를 사용하여 각 부분 합계와 총합계 행을 적절히 표시할 수 있습니다.

LAG / LEAD 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다. 인자 값이 하나이면 이전 1번째 행의 값을 가져오게 됩니다.

LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있습니다. 인자 값이 하나이면 이후 1번째 행의 값을 가져오게 됩니다. SQL Server에서는 지원하지 않는 함수입니다.

REVOKE

REVOKE 문을 사용하여 권한을 취소하면 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 모두 연쇄적으로 취소됩니다.

RESTRICT

RESTRICT는 SQL 데이터베이스에서 주로 외래 키 제약 조건을 설정할 때 사용되는 옵션입니다. RESTRICT 옵션은 특정 동작이 제한되어야 함을 지정합니다. 특히, 외래 키가 참조하는 데이터가 삭제되거나 업데이트될 때 참조 무결성을 유지하기 위한 옵션으로 사용됩니다.

RESTRICT는 스키마가 공백인 경우에만 삭제가 됩니다.