[2주 동안 SQL 공부하기] 집계/비집계 함수… 은근 중요할지도?
저번에는 특정 데이터 값들을 불러오는 것을 좀 더 딥하게 배웠으니,
이번에는 데이터를 정렬하는 것에 대해 좀 더 자세히 알아보겠습니다.
집계 함수
Group By와 함께 주로 사용되는 집계 함수(aggregate function):
| 함수명 | 설명 |
| SUM() | 합계 |
| AVG() | 평균값 |
| COUNT() | 행의 개수 출 |
| COUNT(DISTINCT) | 행의 개수 출력(중복되면 계산X) |
| MAX() / MIN() | 최대값 / 최소값 |
| STDDEV() | 표준편차 출력 |
| VARIANCE() | 분산 값 출력 |
비집계 함수는 아래와 같으며 각각의 의미는 천천히 알아보겠다.
CUME_DIST(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), NTH_VALUE(), NTILE(), PERCENT_RANK(), RANK(), ROW_NUMBER() 등
비집계 함수
순위 함수
NTILE(), PRECENT_RANK(), RANK(), ROW_NUMBER() 등이 해당된다.
구문이 단순하다는 장점을 가지고 있으며 코드 수정이 쉬어 효율성이 뛰어나다.
일단 이 테이블을 이용해서 한번 순위 함수를 공부해 보겠습니다.
"키가 큰 순서대로" 데이터를 정렬해 보자.
Select ROW_number() OVER(ORDER BY height desc) "키 내림차순", mem_name "그룹명", debut_date "데뷔 날짜", height "평균 키"
From member;
어라? ORDER BY도 한번 다시 복습해 볼까요
똑같이 키 순서대로 정렬:
Select mem_name, addr, height, debut_date
From member
Order by height desc;
데뷔 날짜 오래된 순으로 정렬:
Select mem_name, addr, height, debut_date
From member
Order by debut_date asc;
다시 순위 함수로…
"키가 큰 순서"를 "지역별" 매겨보자.
Select addr, ROW_number() OVER(PARTITION BY addr
ORDER BY height desc)
"지역별 키 큰 순위", mem_name "그룹명", debut_date "데뷔 날짜", height "평균 키"
From member;
Dense_RANK() 함수: 순위가 같은 경우, 동일한 등수로 출력한다.
분석 함수
LEAD(), FIRST_VALUE(), LAG(), LAST_VALUE(), CUME_DIST() 등이 해당된다.
분석 함수를 공부해 보겠습니다.
1) 특정 데이터와의 차이 값 구하기
"테이블을 키가 큰 순으로 정렬하고 다음 사람과의 키 차이를 구해보자." - LEAD()
LEAD(Column_name, offset, default)
ex) lead(height, 1, 0): 키(컬럼명)에서 현재 행을 기준으로 +1행의 값을 가져온다.(디폴트는 0)
LAG(Column_name, offset, default)
ex) lag(height, 1, 0): 키(컬럼명)에서 현재 행을 기준으로 -1행의 값을 가져온다.(디폴트는 0)
Select mem_name "그룹명", debut_date "데뷔 날짜", height "평균 키",
height - (LEAD(height,1,0) OVER (Order by height desc)) "평균 키 차이"
From member;
2) 주소별(addr)로 가장 키는 큰 사람과의 차이 구하기 - FIRST_VALUE()
Select addr, mem_name "그룹명", height "평균 키",
height - (FIRST_VALUE(height) OVER (PARTITION BY addr ORDER BY height desc)) "지역 최장신과의 키 차이"
From member;
3) 누적 백분율 구하기 - CUME_DIST()
같은 지역의 회원과 비교하고, 키가 크거나 같은 사람이 전체의 몇 % 인지 구할 때 사용한다.
→ 이 함수는 특정 값이 주어진 집합에서 누적 백분위수로 얼마나 높은 위치에 있는지를 나타냅니다.
Select addr, mem_name "그룹명", height "평균 키",
CUME_DIST() OVER (ORDER BY height desc) "누적 백분위"
From member;
Select addr, mem_name "그룹명", height "평균 키",
(CUME_DIST() OVER (ORDER BY height desc)) *100 "누적 백분율"
From member;
이러면 PERCENT_RANK() 함수랑 똑같은 거 아닌가... 아닙니다!~
Select addr, mem_name "그룹명", height "평균 키",
PERCENT_RANK() OVER (ORDER BY height desc) "순위 퍼센트"
From member;
이렇게 PERCENT_RANK() 함수를 사용하면 아래와 같이 나옵니다.
간단히 말해서 순위 함수를 사용하면 "순위"를 중점으로 한다고만 이해하면 될 것 같아요.
그러고 보니 오늘 배운 내용들은 전부 OVER 절이 있네요!
용어(함수) 정리
| 함수명 | 설명 |
| RANK() | 순위 출력 (ex. 1 2 3 3 5 ⇢ ) |
| DENSE_RANK() | 순위가 같은 경우, 동일한 등수로 출력 (ex. 1 2 3 3 4 5 ⇢) |
| PERCENT_RANK() | 상대적인 순위, 즉, 백분위 순위(순위 퍼센트) |
| NTILE() | 나눌 그룹의 개수 |
| LEAD() | 이후 행의 데이터를 현재의 행과 함께 조회 |
| LAG() | 이전 행의 데이터를 현재의 행과 함께 조회 |
| FIRST_VALUE() | 가장 첫 번째 값 |
| CUME_DIST() | 행의 누적 분산을 출력, 0에서 1사이를 출력 |