윈도우 함수
1) 의미 : 서로 다른 행의 비교나 연산을 위해 만든 함수
2) 특징 : group by를 쓰지 않고 연산 가능
3) 종류 : LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK
rank over는 대상이 안 나와도 됨
문법
1) partition by : 연산할 그룹을 묶는 것
2) order by 정렬 순서
3) rows, range, between 범위 설정에 따라 누적합의 결과가 달라짐
연산 범위를 쓰려면 꼭 정렬인 ORDER BY 절을 사용해야 함
순서를 꼭 맞춰야 함
예시)
over절을 사용하여 윈도우 함수로 사용 가능
공통 범위 설정
- 범위 설정을 한다면, ORDER BY 절 필수로 써야 함
- ROWS : 정렬을 할 때 값이 같더라도 각 행씩 연산
- RANGE : 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산 (DEFAULT)
- BETWEEN A AND B
- 시작점
- current row : 현재 행부터
- unbounded preceding : 처음부터(default)
- N preceding : n 이전부터
- 마지막 시점
- current row : 현재 행까지
- unbounded following : 마지막까지
- n following : n 이후까지
- 시작점
순위 관련 함수
- ORDER BY 절 필수
- RANK WITHIN GROUP
- 특정값에 대한 순위 확인
- RANK() OVER()
- 전체 중이나 특정 그룹 중 값의 순위 확인
- 그룹 내에 순위를 구할 땐 PARTITION BY 절 사용
- 동순위가 있을 경우, 그 다음 순위가 결정이 됨
- DENSE_RANK() OVER()
- 누적 순위로, 값이 같을 때 동일한 순위 부여우 다음 순위가 바로 이어짐
- ex) 1등이 5명이더라도 다음 순위는 2등이 됨
- ROW_NUMBER() OVER()
- 연속된 행 번호
- 동일한 순위는 없고 단순히 순서대로 나열한대로의 순서 값 리턴
LAG, LEAD
- 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
- ORDER BY 절 필수
- 보통 한 행과 다른 한 행의 정보를 같이 출력할 수 없음 그걸 해결하기 위한 함수
SELECT LAG(컬럼, [N])
OVER ([PARTITION BY 컬럼]
ORDER BY 컬럼 [ASC|DESC]);- 가져올 컬럼명 입력
- 몇 번째 값 가져올지 적는데 생략 시 default는 1
- partition by는 생략 가능하지만, order by는 필수
FIRST_VALUE, LAST_VALUE
- 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
- 순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
- PARTITION BY, ORDER BY 절 생략 가능
- LAST_VALUE
- 기본적으로 처음부터 현재 행까지의 마지막 값을 의미 그래서 범위 설정을 안하면 항상 자기 자신을 리턴
- 원하는 값을 설정하려면 처음부터 마지막까지의 범위 설정이 필요함
between unbounded preceding and unbounded following
NTILE
- 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
- 그룹 번호가 리턴되며, ORDER BY절 필수
- PARTITION BY를 사용하여 특정 그룹을 원하는 수만큼 분리가 가능함
- 총 행의 수가 명확히 나누어지지 않을 경우, 앞 그룹의 크기가 더 크게 분리됨
ex) 14명 3개 그룹 분리 : 5, 5, 2로 나뉨
SELECT NTILE(N)
OVER ([PARTITION BY 컬럼]
ORDER BY 컬럼 [ASC|DESC]);- N자리에 나눌 그룹의 수를 적어주면 됨
비율관련 함수
- RATIO_TO_REPORT
- 각 값의 비율을 리턴 (전체 또는 특정 그룹 내 비율 가능)
- ORDER BY 사용 불가
- CUME_DIST
- 각 행의 수에 대한 누적 비율
- 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력
- ORDER BY 필수이며 누적 비율 구하는 순서를 정할 수 있음
- PERCENT_RANK
- 행의 위치를 분위수를 출력
- 전체 COUNT 중 상대적 위치를 출력(0 첫번째 행~1 마지막 행 범위 내)
- ORDER BY 필수
TOP N QUERY
- 페이징 처리를 효과적으로 수행하기 위해 사용
- 전체 결과에서 특정 N개 추출
- ROWNUM
- 출력된 데이터 기준으로 행 번호 부여
- = 연산 불가
- 시작값이 정의되어야 다음 행번호를 추출할 수 있음
- RANK
- FETCH
- 출력될 행의 수를 제한하는 절
- ORDER BY 절 뒤에 사용
- 문법
OFFSET N: 필수는 아님, 건너뛸 행의 수
FETCH : 출력할 행의 수를 전달 (first or next를 선택해야 함)
FIRST : OFFSET을 쓰지 않았을 때 처음부터 N행 출력 명령
NEXT : OFFSET을 사용했을 경우 제외한 행 다음부터 N행 출력 명령
ROWS ONLY로 마무리
- TOP N(SQL Server)
- 상위 n개 행 추출
- 서브쿼리 사용없이 하나의 쿼리로 정렬된 순서대로 상위 n개 출력 가능
- WITH TIES를 사용하여 동순위까지 함께 출력 가능
계층형 질의
- 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결 고리를 통해 행과 행 사이의 계층(depth)을 표현하는 기법
- PRIOR의 위치에 따라 연결하는 데이터가 달라짐
- 문법
- START WITH : 데이터를 출력할 시작조건
- CONNECT BY PRIOR : 행을 이어나갈 조건
- CONNECT BY에 전달하는 조건은 시작 조건이 아닌 추가적인 연결 조건임 - NOCYCLE : 순환이 발생하면 푸한 루프가 될 수 있기 때문에 이를 방지하고자 사용
- 가상 컬럼
- LEVEL : 각 DEPTH를 표현 (시작점이 1레벨)
- CONNECT_BY_ISLEAF : LEAF NODE(최하위노드) 여부 (참 1, 거짓 0)
- 가상 함수
- CONNECT_BY_ROOT 컬럼명 : 루트 노드의 해당하는 컬럼값
- SYS_CONNECT_BY_PATH(컬럼, 구분자) : 이어지는 경로 출력
- ORDER SIBLINGS BY 컬럼 : 같은 LEVEL일 경우 원하는 컬럼으로 정렬 수행
- CONNECT_BY_ISCYCLE : 계층형 쿼리의 결과에서 순환이 발생했는지 여부
데이터 구조 변경
1. PIVOT : 교차표를 만드는 기능 LONG -> WIDE
SELECT *
FROM 테이블명 또는 서브쿼리
PIVOT (VALUES컬럼명 FOR UNSTACK컬럼명 IN (VAR1, VAR2, VAR3))- 반드시 FROM절에 STACK, UNSTACK, VALUE 모두 명시
- FROM 절에 선언된 컬럼 중 UNSTACK과 VALUE 컬럼 제외하고 모두 STACK 컬럼으로 사용함
- STACK 컬럼 : 왼쪽에 고정할 컬럼
- UNSTACK 컬럼 : 펼칠 컬럼
- VALUE 컬럼 : 값 전달
2. UNPIVOT : WIDE -> LONG
SELECT *
FROM 테이블명 또는 서브쿼리
UNPIVOT (VALUES컬럼명 FOR STACK컬럼명 IN (VAR1, VAR2, VAR3))- STACK 컬럼 : 이미 UNSTACK 되어있는 여러 컬럼을 하나의 컬럼으로 STACK시 새로 만들 컬러이름 정의
- VALUE 컬럼 : 교차표에서 셀 자리의 값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명 정의
정규 표현식
문법
- 대상, 찾을 문자열, [바꿀 문자열], [검색위치], [발견횟수], [옵션]
댓글