[개미의 걸음 SQLD 2과목] SQL내장함수② 다중행(집계,그룹,윈도우) 함수

다중행 함수

다중행 값이 입력되는 함수[집계 함수, 그룹함수, 윈도우 함수 등]

 

 

다중행 함수1. 집계 함수[Aggregate Function]

여러 행들이 묶인 하나의 그룹에서 단 하나의 결과를 출력하는 다중행 함수 

함수명	설    명	유형별 가능여부
AVG	지정한 열에서 NULL값을 제외한 평균값 반환	수치형
COUNT	테이블의 특정 조건이 맞는 행의 개수를 변환     COUNT(*) : NULL값을 포함한 행의 수를 출력     COUNT(표현식) : NULL값을 제외한 행의 수를 출력        # 표현식에 기본키가 들어가는 경우 위의 두 식은 같은 결과              → 기본키는 NULL값을 가지지 않으므로!!!!	수치형, 문자형
SUM	지정한 열에서 NULL값을 제외한 총합을 반환	수치형
STDDEV	지정한 열의 표준편차를 반환	수치형
VARIAN	지정한 열의 분산을 출력	수치형
MIN	지정한 열의 가장 작은 값을 반환	수치형
MAX	지정한 열의 가장 큰 값을 반환	수치형

 

다중행 함수2. 그룹 함수[Group Function]

데이터를 그룹화하여 하나의 테이블을 한 번만 읽어서 빠른 작업을 가능하게 하는 다중행 함수 

• 윈도우 함수를 사용해 순위, 합계, 평균, 행 위치 등을 조작 가능

함수명	설    명
ROLLUP	지정된 Grouping Columns의 list는 subtotal 생성하기 위해 사용 각 Group별 합계와 전체 합계가 출력[N+1개 출력, N은 Grouping Columns 수]     → GROUP BY함수는 각 Group별 합계만 출력
	ROLLUP(칼럼명1, 칼럼명2) 입력시 칼럼1합계를 칼럼2합계와 함께 출력      ex>  칼럼명1   칼럼명2      SUM                 1            A           100                    1            B           200                 1                         300                   2            A           300                 2                         300                                              600
GROUPING SETS	GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들기 위해 사용 GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있음     → 표시된 인수들은 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 동일     → 계층 구조인 ROLLUP과의 차이점      → 정렬이 필요한 경우 ORDER BY절 사용 "GROUP BY GROUPING SETS(칼럼명, ())"를 사용하면 "GROUP BY ROLLUP(칼럼명)"과 동일     → GROUP BY()가 전체 칼럼을 하나의 그룹으로 처리한다는 의미이므로!
	GROUPING SETS(칼럼명1,칼럼명2)입력시 칼럼명1별 합계와 칼럼명2별 합계를 따로 출력      ex>  칼럼명1   칼럼명2      SUM                              A           400                                 B           200                 1                        300                 2                        300 주의사항!! GROUPING SETS((칼럼명1,칼럼명2))입력시 (칼럼명1,칼럼명2)별 집계테이터를 출력       ex>  칼럼명1   칼럼명2      SUM                 1            A           100                    1            B           200                 2            A           300
CUBE	CUBE함수에 제시한 칼럼에 대해 결합 가능한 모든 집계를 계산     → 다차원 집계를 제공해 다양하게 데이터 분석 가능     → ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 함
	CUBE(칼럼명1,칼럼명2)입력시 전체합계, 칼럼명1합계 칼럼명2합계, 칼럼명1&칼럼명2합계가 출력      ex>  칼럼명1   칼럼명2       SUM                                            600                                A            400                                 B            200                 1                         300                   1            A           100                    1            B           200                 2                         300                   2            A           300

※ CUBE(칼럼1, 칼럼2)는 GROUPING SETS(칼럼1, 칼럼2,(칼럼1,칼럼2),())와 동일

 

 

다중행 함수3. 윈도우 함수[Window Function]

SELECT 윈도우함수(ARGUMENTS)
       OVER (PARTITION BY 칼럼명
             ORDER VY WINDOWING)

행과 행 간의 관계를 정의하는 다중행 함수 

• 윈도우 함수를 사용해 순위, 합계, 평균, 행 위치 등을 조작 가능
• GROUP BY절의 집합을 원본으로 하는 데이터를 윈도우 함수와 함게 사용한다면 GROUP BY와 함께 사용 가능
• 윈도우 함수 적용 범위는 Partition을 넘을 수 없음

윈도우 함수 구조	설    명
윈도우 함수	* 순위, 집계, 행순서, 비율 등의 함수      - 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER함수      - 집계 함수 : COUNT, MAX, MIN, SUM, AVG 함수      - 행 순서    : FIRST_VALUE, LAST_VALUE, LAG, LEAD함수      - 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE 함수 * 결과에 대한 처리함수이므로 결과 건수가 줄어드는 것은 아님!
OVER	윈도우 함수를 적용하기 전에 행 집합의 분할과 순서를 결정     → 쿼리 결과 집합 내의 창 또는 사용자 지정 행 집합을 정의
ARGUMENTS	* 0~N개의 인수/ 칼럼명 등
PARTITION BY	* 전체 집합을 기준에 의해 소모임으로 나눔     → GROUP BY구문과 의미적으로 유사     → PARTITON BY를 작성하지 않으면 전체 집합을 하나의 그룹으로 본다는 의미
WINDOWING	* 행 기준의 범위를 지정 * ROWS, RANGE, BETWEEN~AND, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW등이 있음

WINDOWING종류

ROWS	물리적 결과의 행 수    * 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	논리적인 값에 의한 범위    * 논리적인 주소에 의해 행 집합을 지정
BETWEEN 시작점 AND 끝점	윈도우의 시작과 끝의 위치를 지정
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행임을 의미
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행임을 의미
CURRENT ROW	윈도우 시작 위치가 현재 행임을 의미

ex> RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            → 첫번째 행부터 마지막 행까지 범위
            → default값은 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"

ex> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            → 첫번째 행부터 현재 행까지

 

 

순위함수[RANK Function]

RANK	특정항목 및 파티션에 대해 순위를 계산 동일한 순위는 동일한 값이 부여됨
	ex>  1        2        2        4
DENSE_RANK	동일한 순위를 하나의 건수로 계산
	ex>  1        2        2        3
ROW_NUMBER	동일한 순위에 대해 고유의 순위를 부여
	ex>  1        2        3        4

 

행순서 관련 함수

FIRST_VALUE	파티션에서 가장 처음에 나오는 값을 구하는 함수     → MIN함수를 사용해 같은 결과를 구할 수 있음
LAST_VALUE	파티션에서 가장 나중에 오는 값을 구하는 함수     → MAX함수를 사용해 같은 결과를 구할 수 있음
LAG	현재 읽혀진 데이터의 이전 행의 값을 가져오는 함수
	ex> LAG(칼럼명) → 이전 칼럼 데이터의 값을 가져옴
LEAD	윈도우에서 특정 위치의 행을 가져온 함수     → 기본값 1     → 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져오는 함수     → SQL Server에서는 지원하지 않는 함수
	ex> LEAD(칼럼명) → 이후 칼럼 데이터의 값을 가져옴

 

비율 관련 함수

CUME_DIST	파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대해 누적 백분율을 조회     → 0~1사이의 값으로 출력됨
PERCENT_RANK	파티션별로 전체 건수를 ARGUMENT값으로 N등분한 결과를 조회
NTILE	파티션별로 전체 건수를 ARGUMENT값으로 N등분한 결과를 조회
RATIO_TO_REPORT	파티션 내에 전체 SUM(칼럼)에 대한 행별 칼럼값의 백분율을 소주점까지 조회

 

예제

  EX1>
SEELCT 추천경로, 추천인, 피추천인, 추천점수
FROM (SELECT 추천경로, 추천인 피추천인, 추천점수,
           ROW_NUMBER() OVER(PARTITION BY 추천경로
               ORDER BY 추천점수 DESC) AS RNUM
      FROM 추천내역)
WHERE RNUM=1;

• 추천경로에 따라 그룹화한뒤 추천 점수를 정렬
• ROW NUMBER는 정렬된 데이터에 고유의 값을 부여
• WHERE절에서 RNUM=1인 데이터라고 조건을 부여했으므로 추천경로 별로 하나의 데이터가 출력