SQL 복수행 함수에 대해

SQL 복수행 함수에 대해

• GROUP함수
• GROUP BY절을 사용해 특정 조건으로 그룹화하기
• HAVING절을 사용해 그룹핑한 조건으로 검색하기
• Window함수
• PARTITION BY절과 그룹함수
• ORDER BY절과 그룹함수
• 다양한 분석함수의 활용
• 문자의 크기 비교

GROUP함수

그룹함수는 여러 행의 데이터를 하나의 결과값으로 요약하는데 사용된다.

보통 GROUP BY절과 함께 사용된다.

특정 Column을 기준으로 데이터를 그룹화하고, 각 그룹에 대한 집계를 수행한다.

• COUNT: 입력되는 데이터의 총 건수를 반환
• SUM: 입력되는 데이터의 총 합계 값을 반환
• AVG: 입력된 값들의 평균값을 반환
• MAX: 주어진 데이터 중에서 가장 큰 값을 반환
• MIN: 주어진 데이터 중에서 가장 작은 값을 반환
• STTDDEV: 주어진 값들의 표준편차를 반환
• VARIANCE: 주어진 값들의 분산을 반환
• ROLLUP: 입력되는 데이터들의 계층적 집계를 생성하여, 소계값을 출력
• CUBE: 입력되는 데이터의 모든 가능한 조합에 대한 집계를 생성하여, 소계 및 전체 총계를 출력
• GROUPINGSSET: 한 번의 쿼리로 여러 개의 함수들을 그룹으로 수행 가능
• LISTAGG: 문자열 값들을 결합하여 하나의 문자열로 반환하는 함수
• PIVOT: 행을 열로 변환하여 집계함수를 적용하는 함수
• LAG: 현재 행 이전의 행에 있는 데이터를 가져오는 함수
• LEAD: 현재 행 다음의 행에 있는 데이터를 가져오는 함수
• RANK: 집계된 결과에 대해 순위를 매기는 함수
• DENSE_RANK: 중복 순위를 제거하고 순위를 매기는 함수
• ROW_NUMBER: 동일한 값을 가지더라도 ROWID가 작은 Row에 고유한 순위를 매기는 함수

GROUP BY절을 사용해 특정 조건으로 그룹화하기

GROUP BY 절 뒤에 오는 컬럼값을 기준으로 먼저 모아놓고 SELECT 절에 있는 그룹함수를 적용한다.

오라클 10g R1까지는 GROUP BY의 결과가 정렬되어서 나왔지만(성능 저하),

오라클 10g R2부터는 해쉬 기반의 New inMemory Sort 알고리즘으로 변경되어 정렬되지 않는다.

만약 자동정렬되던 오라클 10g R1 이전 방식을 사용하고 싶다면, 초기화 파라미터파일(pfile이나 spfile)에 “_gby_hash_aggregation_enabled”=FALSE로 설정해주면 된다.

GROUP BY절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias는 사용하면 안 된다.

HAVING절을 사용해 그룹핑한 조건으로 검색하기

그룹함수를 조건으로 사용하고 싶을 경우, GROUP BY절 이전이든 이후든 HAVING절을 사용해야 한다.

일반적인 조건은 WHERE절에 사용하면 되고, 만약 그룹함수를 조건으로 사용하고 싶다면 HAVING절을 사용해야 한다.

Window함수

윈도우함수는 데이터의 특정 “창” 또는 부분집합에 대해 계산을 수행하지만, 전체 데이터 구조를 유지하면서 각 행에 대한 계산결과를 반환한다.

윈도우함수는 OVER절을 사용하며, 필요에 따라 PARTITION BY절과 ORDER BY절을 사용하여 데이터를 세분화하고 정렬할 수 있다.

GROUP함수와 WINDOW함수의 관계

그룹함수는 데이터를 요약하여 그룹별로 하나의 결과를 도출하는 반면, 윈도우함수는 각 행에 대해 계산을 수행하여 모든 행을 유지한 결과를 제공한다.

그룹함수는 결과로 그룹의 개수만큼의 행을 반환하는 반면, 윈도우함수는 입력 행 수만큼 결과 행을 반환한다.

그룹함수는 report나 dashboard에 사용하는 핵심지표를 집계할 때 유용하며, 윈도우함수는 데이터의 순위를 매기거나 이동 평균을 계산하는 등의 분석에 유용하다.

PARTITION BY절과 그룹함수

PARTITION BY절은 데이터를 논리적으로 그룹화하여 각 그룹에 대한 원하는 작업을 수행할 수 있도록 한다.

PARTITION BY절이 적용된 윈도우함수는 전체 데이터를 소그룹으로 나누고, 각 그룹에 대해 독립적으로 함수를 적용한다.

ORDER BY절과 그룹함수

ORDER BY절은 Query의 결과를 원하는 순서대로 정렬할 수 있다.

ORDER BY절은 윈도우함수 내에서 데이터를 특정 순서로 처리하여 누적 계산, 순위 등의 그룹함수를 사용하는 기준이 된다.

다양한 분석함수의 활용

오라클은 행끼리 연산이나 비교를 쉽게 지원해주기 위한 함수를 제공한다.

이를 분석함수(Analytic Function) 또는 윈도함수(Window Function)이라 한다.

이를 잘 활용하면 복잡한 로직을 비교적 간단한 SQL 문장으로 해결할 수 있다.

경우에 따라 SQL실행계획을 통해 확인할 수 있는 테이블 조회 횟수를 감소시킬 수도 있다.(성능 상승)

문자의 크기 비교

크기의 비교는 숫자만 할 수 있다.

문자의 크기를 비교하는 함수를 사용하면, 해당 문자를 ASCII 코드로 변환을 해서 나오는 값으로 비교를 한다.

만약 문자가 두 글자 이상의 길이일 경우 앞부분에서 첫번째 문자만 변환을 해서 비교를 한다.

예를 들어 ‘2’와 ‘29’를 크기비교하는 경우 둘 다 ‘2’의 ASCII 코드를 비교하기 때문에 동일하다.

마찬가지로 ‘A’와 ‘AB’의 ASCII 코드도 둘다 ‘A’의 ASCII 코드를 비교하기 때문에 동일하다.