[ORACLE] 22일차 : SQL함수 (분석 함수)
분석함수
행크룹을 기반으로 집계 깞을 계산하며, 각 그룹에 대해 여러 행을 반환한다는 점에서 집계 함수와 다르다. 분석 함수가 처리되기 전에 모든 조인과 모든 WHERE,GROUP BY 및 HAVING절이 완료된다. 따라서 분석 함수는 선택 목록 또는 ORDER BY 절에만 나타날 수 있다.
순위 관련 함수
RANK()OVER()
- 각 그룹에서 값의 순위를 계산한다. 반환 타입은 NUMBER이다.
순위를 오름차순으로
SELECT name,sal,
RANK()OVER(ORDER BY sal)순위
FROM emp;
순위를 내림차순으로
SELECT name,sal,
RANK()OVER(ORDER BY sal DESC)순위
FROM emp;
동일한 sal은 bonus에 따라 순위 부여
SELECT name,sal,
RANK()OVER(ORDER BY sal DESC,bonus DESC)순위
FROM emp;
부서별로 순위 구하기
SELECT name,dept,sal,
RANK()OVER(PARTITION BY dept ORDER BY sal DESC)부서순위
FROM emp;
상위 1~10등 출력
SELECT*FROM(
SELECT empNO,name,sal,dept,pos,
RANK() OVER(ORDER BY sal DESC)rank
FROM emp
)WHERE rank<=10;
상위 10% 출력
SELECT*FROM(
SELECT empNO,name,sal,dept,pos,
RANK() OVER(ORDER BY sal DESC)rank
FROM emp
)WHERE rank <=(SELECT COUNT(*)FROM emp)*0.1;
DENSE_RANK() OVER()
댄스 랭크는 RANK 함수와 동일하지만 동일한 순위가 발생할 경우 연속적으로 순위를 부여한다. "100,100,90"의 순위를 DESC순으로 RANK 함수로 부여하면 1,1,3이 되지만 DENSE_RANK 함수는 1,1,2가 된다.
SELECT name,sal,
DENSE_RANK()OVER(ORDER BY sal DESC)순위
FROM emp;
ROW_NUMBER()OVER()
partition별로 정렬된 결과에 대해 순위를 부여하는 기능. partition은 전체 행을 특정 컬럼을 기준으로 분리하는 기능으로 GROUP BY절에서 그룹화 하는 방법과 같은 개념이다. 동일한 값이 있는 경우 RANK함수는 1,1,3 형식으로 출력하지만, ROW_NUMBER는 1,2,3순으로 출력
SELECT name,sal,
ROW_NUMBER()OVER(ORDER BY sal DESC)순위
FROM emp;
RANK()WITHIN GROUP()
SELECT RANK(3000000) WITHIN GROUP(ORDER BY sal DESC)순위
FROM emp;
3000000이면 몇등?
분석함수
COUNT() OVER()
OVER절 <query_partition_clause>인수에 의해 지정된 그룹들의 행수를 반환한다. 이 절이 생략되면 모든 행을 하나의 그룹으로 처리한다.
SELECT empNO,name,dept,sal,
COUNT(*)OVER(ORDER BY dept)cnt
FROM emp;
부서별로 인원수를 누적하여 앞 부서와 누적 출력되며 마지막은 전체 인원수
SELECT empNO,name,dept,sal,
COUNT(*)OVER(PARTITION BY dept)cnt
FROM emp;
'PARTITION BY dept'만 기술하면 dept 별 인원수가 출력되고 앞에 것을 누적하지 않음
SUM()OVER()
OVER절 <query_partition_clause> 인수에 의해 지정된 그룹들의 expr합을 반환한다. 이 절이 생략되면 모든 행을 하나의 그룹으로 처리한다.
SELECT empNO,name,dept,sal,
SUM(sal)OVER()합
FROM emp; -- 전체 합
SELECT empNO,name,dept,sal,
SUM(sal)OVER(PARTITION BY dept)합
FROM emp; -- 부서별로 전체 합
각 급여에 대한 부서별 급여의 백분율
SELECT name,dept,sal,
ROUND(sal/SUM(sal)OVER(PARTITION BY dept)*100)백분율
FROM emp;
AVG() OVER()
OVER절 <query_partition_clause> 인수에 의해 지정된 그룹들의 expr 평균을 반환한다. 이 절이 생략되면 모든 행을 하나의 그룹으로 처리한다.
전체 평균
SELECT name,dept,sal,ROUND(AVG(sal)OVER())평균
FROM emp;
부서별 평균
SELECT name,dept,sal,ROUND(AVG(sal)OVER(PARTITION BY dept))평균
FROM emp;
전체 평균과의 차이
SELECT name,dept,sal,ROUND(AVG(sal)OVER())-sal 차이
FROM emp;
MAX()OVER() / MIN()OVER()
OVER절 <query_partition_clause> 인수에 의해 지정된 그룹들의 expr에 대한 최대/최소값을 반환한다. 이절이 생략되면 모든 행을 하나의 그룹으로 처리한다.
부서 최대, 부서 최대와 차이
SELECT name,dept,sal,
MAX(sal)OVER(PARTITION BY dept)부서최대,
sal-MAX(sal)OVER(PARTITION BY dept)부서와차이
FROM emp;
RATIO_TO_REPORT()OVER()
레이쇼 함수는 값의 세트의 합에 대한 값의 비율을 계산한다. 만약 expr이 NULL이라면, RATIO_TO_REPORT 값은 NULL 이다.
전체에 대한 인원수 비율
SELECT dept,ROUND(RATIO_TO_REPORT(COUNT(*))OVER()*100)비율
FROM emp
GROUP BY dept;
LISTAGG () WITHIN GROUP()
ORDER BY절에 지정된 각 그룹 내에서 데이터를 정렬 한 다음 측정 값 열의 값을 연결한다.
SELECT dept,
LISTAGG (name,',') WITHIN GROUP(ORDER BY empNO)부서별이름
FROM emp
GROUP BY dept;
LAG () OVER() 함수와 LEAD() OVER()
래그(LAG)함수는 해당 위치 이전에 지정된 물리적 오프셋에서 행에 대한 엑세스를 제공하며, 리드(LEAD) 함수는 LAG 함수의 반대로 이후의 값을 가져오는 함수. offset을 지정하지 않으면 기본값은 1이며, offset이 창의 범위를 벗어나면 선택적 기본값이 반환된다. 기본값을 지정하지 않으면 기본값은 null이다.
SELECT name,sal,LAG(sal,1,0)OVER(ORDER BY sal DESC)lag
FROM emp;
SELECT name,sal,LEAD(sal,1,0)OVER(ORDER BY sal DESC)lead
FROM emp;
NTILE() OVER()
정렬된 데이터 세트를 expr로 표시된 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당한다. 버킷의 번호는 1부터 expr까지 이다. ex) 10개의 결과를 2그룹으로 나누는 경우 1~5까지 1번 그룹, 6부터 10까지는 2번 그룹
6개의 그룹으로 나누기
SELECT name,sal,
NTILE(6)OVER(ORDER BY sal DESC)그룹
FROM emp;
무작위로 5개로 나누기
SELECT name,sal,
NTILE(5)OVER(ORDER BY DBMS_RANDOM.VALUE)그룹
FROM emp;
FIRST_VALUE() OVER()
정렬된 값 중에서 첫번째 값을 반환한다. 만약 첫번째가 NULL이라면, IGNORE NULLS를 지정하지 않았다면 NULL을 반환하게 된다. 즉, 윈도우에서 정렬된 값 중에서 첫번째 값을 반환하는 함수
SELECT name,dept,sal,
FIRST_VALUE(sal)OVER(PARTITION BY dept ORDER BY sal DESC)
FROM emp;
LAST_VALUE() OVER() 함수
정렬된 값 중에서 마지막 값을 반환한다. 만약 마지막 값이 NULL이라면 IGNORE NULLS를 지정하지 않았다면 NULL을 반환하게 된다 즉, 윈도우에서 정렬된 값 중에서 마지 값을 반환하는 함수. 윈도우절을 지정하지 않으면 디폴트 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 적용되어 끝값으로 고정되어 있지 않으므로 예기치 않는 결과가 출력 되므로 반드시 윈도우절을 지정해야 한다.
SELECT name,dept,sal,
LAST_VALUE(sal)OVER() --가장 마지막 값 출력
FROM emp;
SELECT name,dept,sal,
LAST_VALUE(sal)OVER() --가장 큰 값 출력 ORDER BY sal로 인하여
FROM emp
ORDER BY sal;