고급 SQL 쿼리: 윈도우 함수의 모든 것

윈도우 함수는 데이터베이스에서 데이터를 분석하고 집계하는 데 있어 매우 유용하고 강력한 도구입니다. 이 함수들은 각 행에 대해 계산을 수행하면서, 다른 행들과의 관계를 고려하여 결과를 생성하는 독특한 방식으로 작동합니다. 일반적인 집계 함수와의 주요 차이점은, 윈도우 함수를 사용할 때 전체 결과 집합을 그룹화하지 않고 각 개별 행에 대한 값을 유지할 수 있다는 점입니다. 이러한 특성 덕분에 데이터 분석가와 개발자들은 더 정교하고 유연한 쿼리를 작성할 수 있습니다.

1. 윈도우 함수의 기본 구조

윈도우 함수는 다음과 같은 기본 구문으로 작성됩니다:

함수명() OVER (PARTITION BY 열1, 열2 ORDER BY 열3)

• 함수명: 사용할 윈도우 함수를 지정합니다 (예: SUM, AVG, ROW_NUMBER 등).
• PARTITION BY: 데이터를 서브그룹으로 나누는 기준이 되는 컬럼을 지정하여, 각 그룹 내에서 독립적인 계산을 가능하게 합니다.
• ORDER BY: 각 파티션 내에서 데이터의 순서를 정하는 데 사용되는 컬럼을 지정하여, 결과의 정렬 방식을 결정합니다.

2. 주요 윈도우 함수

1. ROW_NUMBER()

• 각 행에 대해 고유한 번호를 부여하여, 데이터의 순서를 명확히 할 수 있습니다. 이 함수는 특히 데이터의 순위를 매기거나 특정 조건에 따라 행을 식별할 때 유용합니다.

SELECT 
    직원_ID,
    이름,
    부서,
    ROW_NUMBER() OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 순위
FROM 직원;

2. RANK()

• 동일한 값에 대해 동일한 랭크를 할당하며, 다음 랭크에는 건너뛰기를 적용합니다. 이 함수는 동점자가 있을 경우 순위를 매길 때 유용하게 사용됩니다.

SELECT 
    직원_ID,
    이름,
    급여,
    RANK() OVER (ORDER BY 급여 DESC) AS 급여_랭크
FROM 직원;

3. DENSE_RANK()

• 동일한 값에 대해 동일한 랭크를 할당하지만, 다음 랭크는 건너뛰지 않습니다. 이 함수는 동점자가 있을 때도 연속적인 순위를 유지하고자 할 때 유용합니다.

SELECT 
    직원_ID,
    이름,
    급여,
    DENSE_RANK() OVER (ORDER BY 급여 DESC) AS 밀집_랭크
FROM 직원;

4. SUM(), AVG(), COUNT() 등의 집계 함수

• 특정 범위 내에서 누적 합계나 평균 등을 계산할 수 있으며, 이를 통해 데이터의 흐름을 분석하고 인사이트를 도출할 수 있습니다.

SELECT 
    주문_ID,
    고객_ID,
    금액,
    SUM(금액) OVER (PARTITION BY 고객_ID ORDER BY 주문일자) AS 누적_금액
FROM 주문;

3. 실용적인 예제

가상의 "판매" 테이블이 있다고 가정해보겠습니다:

판매ID	판매원	지역	매출
1	A	서울	1000
2	B	부산	1500
3	A	서울	2000
4	C	대전	1200
...

위 테이블에서 각 판매원이 지역별로 얼마나 많은 매출을 올렸는지를 알아보기 위해서는 다음과 같이 쿼리를 작성할 수 있습니다:

SELECT 
    판매원, 
    지역, 
    매출, 
    SUM(매출) OVER (PARTITION BY 지역 ORDER BY 매출 DESC) AS 지역별_누적매출
FROM 판매;

이 쿼리는 각 지역 내에서 매출이 높은 순서대로 누적된 매출값을 보여주며, 이를 통해 판매원들의 성과를 비교하고 분석하는 데 큰 도움이 됩니다.

결론

윈도우 함수는 복잡한 분석 작업을 단순하게 만들어 주며, 다양한 비즈니스 인사이트를 추출하는 데 큰 도움이 됩니다. 이를 통해 데이터베이스 사용자들은 더 빠르고 효율적으로 정보를 처리하고 의사 결정을 지원할 수 있습니다. 이러한 기능은 특히 대량의 데이터를 다루는 환경에서 더욱 빛을 발하며, 데이터 분석의 정확성과 신뢰성을 높이는 데 기여합니다.