SQL 활용 (2)

• 이번에는 그룹 함수 중에서도 전에 배운 집계 함수를 제외한 소계(총계) 함수, 그리고 윈도우 함수들에 대해 알아보겠습니다!

 

그룹 함수란?

• 그룹 함수는 SQL에서 다양한 수준의 요약 데이터(합계, 평균 등)를 생성하는 데 사용되는 고급 함수입니다.
• 다음은 주요 그룹 함수 중에서도 소계 함수들에 대한 설명입니다.

 

✅ ROLLUP

• 집계 데이터를 여러 레벨의 소계로 제공하여, 데이터의 계층적 구조를 보여줍니다.
• ROLLUP을 사용하면 최종 합계와 함께 각 단계의 소계를 쉽게 계산할 수 있습니다.
• 아래는 ROLLUP의 예시로, 컬럼1과 컬럼2를 기준으로 그룹화하고 각 그룹 및 서브 그룹의 합계와 전체 합계를 계산합니다.

SELECT 컬럼1, 컬럼2, SUM(컬럼3)
FROM 테이블
GROUP BY ROLLUP(컬럼1, 컬럼2);

 

✅ CUBE

• ROLLUP과 유사하지만, 지정된 모든 컬럼 조합에 대한 소계와 총계를 계산하여, 다차원적인 데이터 분석을 가능하게 합니다.
• 특정 컬럼들이 묶여서 소계가 계산된다면, 그 컬럼들은 괄호에 묶여있을 가능성이 있습니다.

 

✅ GROUPING SETS

• 특정 그룹화 세트에 대한 집계만을 명시적으로 계산할 수 있습니다.
• ROLLUP과 CUBE보다 더 세밀한 제어가 가능하며, 특정 조합의 소계를 생성하고자 할 때 사용됩니다.
• 기본적으로 총계가 제공되지 않기 때문에, 인자값으로 ( )를 추가하면 총합계까지 구할 수 있습니다.
• 또한 인자값으로 ROLLUP이나 CUBE를 사용하여 총합계를 구할 수도 있습니다.
• 추가적으로 GROUP BY 절과 UNION ALL을 이용하여 GROUPING SETS 함수와 동일한 결과를 출력하도록 SQL 작성이 가능합니다.

 

✅ GROUPING

• ROLLUP, CUBE, GROUPING SETS와 함께 사용되며, 결과 집합에서 특정 행이 소계 또는 총계 행인지를 식별합니다.
• GROUPING 함수는 지정된 컬럼이 소계/총계 행에 대한 그룹화에서 집계된 것인지 여부를 0 또는 1로 반환합니다.
• CASE 문이나 DECODE 문으로 NULL 값으로 표현된 속성값을 텍스트로 변환하고 싶을 때 주로 사용합니다.
• 아래 예제에서는 ROLLUP을 사용하여 컬럼1과 컬럼2 기준으로 데이터를 그룹화하고, 각 그룹의 합계를 계산합니다.
• GROUPING 함수의 결과가 1이면 해당 컬럼이 소계 또는 총계를 위해 집계되었다는 것을 의미합니다.

SELECT 
  CASE 
    WHEN GROUPING(컬럼1) = 1 THEN '전체 합계'
    ELSE 컬럼1 
  END AS 컬럼1,
  CASE 
    WHEN GROUPING(컬럼2) = 1 THEN '소계'
    ELSE 컬럼2 
  END AS 컬럼2,
  SUM(컬럼3) AS 합계

 

윈도우 함수란?

• 윈도우 함수는 SQL에서 특정 '윈도우' 또는 데이터의 부분 집합에 대해 계산을 수행하는 강력한 도구입니다.
• 이 함수들은 복잡한 계산을 수행하면서 원본 데이터를 변형하지 않고, 다양한 요약 정보, 순위 및 비율 계산을 제공합니다.
• OVER 키워드와 함께 사용됩니다.

 

✅ 순위 함수 3가지!

1. RANK: 동일한 값에 대해 동일 순위를 부여하고, 다음 순위는 동일 순위 개수만큼 건너뛰게 됩니다. 예를 들어, 1, 2, 2, 4, 5, 5, 7과 같은 형태입니다.
2. DENSE_RANK: RANK와 유사하지만, 건너뛰는 순위 없이 연속적으로 순위를 부여합니다. 동점자가 있더라도 다음 순위는 바로 다음 번호가 됩니다.
3. ROW_NUMBER: 각 행에 대해 고유한 순서 번호를 부여합니다. 동점자가 있어도 순서는 중복되지 않으며, 지정된 정렬 순서에 따라 번호가 매겨집니다.

 

✅ 집계 함수 5가지!

• 집계 함수는 이전에 공부했기에 간단히 하고 넘어가겠습니다!

1. SUM: 데이터의 합계를 구하는 함수입니다. 인자값으로는 숫자형만 올 수 있습니다.
2. MAX: 데이터의 최댓값을 구하는 함수입니다.
3. MIN: 데이터의 최솟값을 구하는 함수입니다.
4. AVG: 데이터의 평균값을 구하는 함수입니다.
5. COUNT: 데이터의 건수를 구하는 함수입니다.

• 이와 더불어 SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주게 되면 RANGE UNBOUNDED PRECEDING 구문이 없이도 누적합이 집계됩니다.
• RANGE 옵션은 동일한 데이터가 있을 경우 모두 합한 값을 출력해줍니다.
• 다음은 윈도우 함수의 WINDOWING 절을 이용하여, 현재 행을 기준으로 앞뒤로 1행씩 포함하는 범위에 대한 데이터 수를 구하는 예제입니다.

SELECT
  컬럼1,
  컬럼2,
  COUNT(*) OVER (
    ORDER BY 컬럼1
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS count_within_range
FROM
  테이블명;

 

✅ 행 순서 함수 4가지!

1. FIRST_VALUE: 지정된 윈도우 내에서 첫 번째 값(또는 행)을 반환합니다.
2. LAST_VALUE: 지정된 윈도우 내에서 마지막 값(또는 행)을 반환합니다. LAST_VALUE 사용 시 윈도우 범위를 주의 깊게 설정해야 합니다.
3. LAG: 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수입니다. 예를 들어, LAG(컬럼, 1)은 이전 행의 값을 가져옵니다.
4. LEAD: 파티션 별로 특정 수만큼 뒤에 있는 데이터를 구하는 함수입니다.

• WINDOWING 절의 default는 RANGE UNBOUNDED PRECEDING입니다.
• 그렇기에 MIN 함수나 LAST_VALUE 함수를 사용할 때에는 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 구문을 추가해주면 좋습니다.

 

✅ 비율 함수 4가지!

1. RATIO_TO_REPORT: 파티션 별 합계에서 차지하는 비율을 구하는 함수입니다.
2. PERCENT_RANK: 현재 행의 백분율 순위를 계산합니다. 가장 낮은 값이 0, 가장 높은 값이 1로 계산됩니다.
3. CUME_DIST: 해당 파티션에서의 누적 백분율을 구하는 함수입니다.
4. NTILE: 데이터를 지정된 수의 동등한 부분으로 나눕니다. 할당할 행이 남았을 경우 맨 앞 그룹부터 하나씩 더 채워집니다. 예를 들어, NTILE(4)는 데이터를 4개의 분위로 나눕니다.