시진엽
윈도우 함수
- 조회하는 현재 레코드를 기준으로 연관된 레코드 집합의 연산을 수행함
- 집계함수 : GROUP BY 그룹, 또는 전체그룹 별로 하나의 레코드로 묶어서 출력
윈도우함수 : 조건에 일치하는 레코드 건수는 변하지 않고, 그대로 유지
쿼리 각 절의 실행 순서
- FROM, WHERE, GROUO BY, HAVING 절에 의해 레코드가 결정
→ 윈도우함수가 실행
→ SELECT, ORDER BY, LIMIT 에 의해 결과를 보여줌
1번은 조건에 일치하는 17건의 데이터를 모두 가져온 다음 윈도우 함수를 실행 그 후5개만 반환(17건의 평균)
2번은 5개의 평균
위도우 함수 기본 사용법
- 집계함수와는 달리 함수 뒤에 OVER 절을 잉요해 연산 대상을 파티션하기 위한 옵션을 명시 할 수 있음
OVER 절에 의해 만들어진 그룹을 파티션, 위도우라고 정의함
AGGREGATE_FUC() OVER(<parttion> <order>) AS window_fuc_column
전체 결과 집합에서 e.hire_date 칼럼으로 정렬 후 랭킹을 매김
부서별로 랭킹을 매김
- 프레임 : 위도우 함수의 각 파티션 안에서도 연산 대상 레코드별로 연산을 수행할 소그룹 레코드의 순서대로 현재 레코드 기준 앞 뒤 몇건을 연산 범위로 제한하는 역할을 수행함
AGGREGATE_FUC() OVER(<parttion> <order> <frame>) AS window_fuc_column
frame : {ROWS || RANGE} {frame_start || frame_between}
ROWS : 레코드의 위치를 기준으로 프레임을 생성
RANGE : ORDER BY절에 명시된 칼럼을 기준으로 값의 범위로 프레임 생성
frame_btewwn : BETWEEN frame_start AND frame_end
frame_start, frame_end : { CURRENT ROW(현재레코드) ||
UNBOUNDED PRECEDNG(파티션 첫번째레코드) ||
UNBOUNDED FOLLOWING(파티션 마지막레코드) ||
expr PRECEDNG(현재 레코드로부터 n번째 이전 레코드) ||
expr FOLLOWING(현재 레코드로부터 n번째 이후 레코드) }
- 일부 윈도우 함수들은 프레임이 미리 고정됨(파티션 전체 레코드)
CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()
- 집계함수는 GROUP BY절과 함께 사용 할 수 있는 함수
OVER 절 없이 단독으로 사용될 수 있고, OVER 절을 가진 윈도우 함수로 사용 가능 - 비집계함수는 반드시 OVER 절을 가지고 있어야하고, 위도우 함수로만 사용 가능
DENSE_RANK(), RANK(), ROW_NUMBER()
- RANK()는 동점인 레코드가 두건 이상인 경우 그 다음 레코드를 동점인 레코드 수만큼 증가 신 순위 반환
- DENSE_RANK()는 동점인 레코드를 1건으로 가정하고 순위를 매기기 때문에 연속된 순위를 반환
- ROW_NUMBER()는 레코드의 고유한 순번을 반환, 동점에 대한 고려 없이 정렬된 순서대로 레코드 번호 부여
LAG(), LEAD()
- LAG()는 파티션 내에서 현재 레코드를 기준으로 n번째 이전의 레코드 반환
- LEAD()는 n번째 이후 레코드 반환
윈도우 함수 성능
- mySQL 8.0에 처음도입 인덱스 최적화 X
정리..
윈도우 함수는 풀스캔 하고, 인덱스 거의 못타서 group by가 훨씬 좋음(얘가 못하는건 윈도우 함수 쓰라곤 하지만 거의 안쓸것 같음..)
❓ 윈도우 함수 왜 씀..? 왜 정리했지;;
잠금을 사용하는 SELECT
- InnoDB SELECT 시 레코드 잠금을 걸지 않음 : 잠금없는 읽기
하지만, SELECT 후 데이터를 가공해서 업데이트 할 결우 잠금을 걸어야함 이때 FOR SHARE, FOR UPDATE - FOR SHARE : SELECT로 읽은 데이터를 읽기 잠금
- FOR UPDATE : SELECT로 읽은 데이터를 쓰기 잠금
- 자동 커밋(AUTO-COMMIT)이 비활성화 된 상태, BEGIN 명령이나 START TRANSACTION 명령으로 트랜잭션이 시작된 상태에서만 잠금이 유지됨
- 8.0 이전의 잠금은 LOCK IN SHARE MODE 사용 ⇒ FOR SHARE 변경
- 다만, FOR SHARE, FOR UPDATE 문을 가지지 않는 SELECT 문은 대기 없이 바로 실행됨
❓ 읽기 잠금인데 어떻게 읽음..?
잠금 테이블 선택
이렇게 하면 3개 레코드 모두 잠금 됨
- FOR UPDATE 뒤 OF 테이블 로 하나의 레코드만 잠금 걸 수 있음
NOWAIT, SKIP LOCKED
- 누군가 레코드를 잠그고 있다면 해제될때 까지 대기 했음 하지만, 기다리다가 에러 나기도 하고, 잠금 걸린 데이터 만났을때 무시하거나 종료 할때도 필요함
- SELECT 마지막에 NOWAIT을 사용하면 에러를 반환하면서 쿼리 종료
- SELECT 마짐가에 SKIP LOCKED를 사용하면 잠긴 데이터 빼고 반환함
SKIP LOCKED를 사용하면 오래도록 대기 하지 않고, 잠긴 데이터는 건너 뛰고 다음 데이터에 접근 할 수 있어서 시간적 효율성을 높일 수 있음(때에 따라서)
- NOWAIT, SKIP LOCKED는 SELECT 문에서만 사용 가능하고, UPDATE 문에서는 사용 불가능함