SQL [10] - LAG, LEAD, RANK OVER, SUM OVER, LISTAGG

1. lag(컬럼명, offset, 기본값) over (정렬방식). 정렬 방식에 따른 이전 항목 조회 

select name, hiredate, pay, lag(pay,1,0) over (order by hiredate)
from professor
;

마지막 컬럼은 이전 행의 PAY값을 출력하고 있다.

 

2. lead(컬럼명, offset, 기본값) over (정렬방식). 정렬 방식에 따른 이후 항목 조회  
select name, hiredate, pay, lead(pay,1,0) over (order by hiredate)
from professor
;

마지막 컬럼은 다음 행의 PAY값을 출력하고 있다.

3. rank 함수 : 순위 지정 함수
-- rank (값) within group (정렬 방식) : 정렬 방식에 따른 값의 순위를 출력
-- rank() over (정렬 방식) : 각각의 레코드의 정렬 방식에 따른 순위
-- rank() over (partition by 그룹 컬럼 order by 정렬방식)  : 그룹 컬럼별 각각의 레코드의 정렬방식에 따른 순위

Quiz. 김도형 교수가 이름 순으로 정렬시 몇번째인지 출력하기

select rank('김도형') within group(order by name) 순위
from professor
;

-- select rank('특정값') within group(order by 특정값이 포함된 특정 컬럼) 
-- from professor
-- ;

-- 교수의 이름 급여 급여 순위 출력하기
select name,pay,rank() over (order by pay) 급여순위
from professor
;

-- 교수의 이름, 급여, 내림차순 급여 순위 출력하기
select name,pay,rank() over (order by pay desc) 급여순위
from professor
;

-- 교수의 이름, 급여, 이름 순위 출력하기 
select name, pay, rank() over (order by name) "이름 순위"
from professor
;

-- PARTITION BY : 순위 함수를 그룹 마다 나누워서 적용하기
-- emp 테이블에서 직원들의 사번, 이름, 급여, 부서별 급여 순위(내림차순)
select empno, ename, sal, deptno, rank() over(partition by deptno order by sal desc) 급여순위
from emp
;

-- 파티션 없을 시 
select empno, ename, sal, deptno, rank() over(order by sal desc) 급여순위
from emp
;

4. sum() over() : 누계. 누적 값 표시
-- Quiz 교수 테이블에서 교수이름, 급여, 보너스, 급여 합계(급여 + 보너스), 급여가 많은 순으로 정렬한 누계를 출력하기
-- 보너스는가 null인 경우는 0으로 처리
select name, pay, bonus, pay + nvl(bonus,0) "급여 합계", sum(pay+nvl(bonus,0)) over(order by pay desc)
from professor
;

-- Quiz 교수 테이블에서 교수이름, 급여, 보너스, 급여합계
-- 부서별 급여가 많은 순으로 정렬한 누계 출력하기
-- 보너스 null = 0

select name, pay, bonus, pay+nvl(bonus,0), deptno, sum(pay+nvl(bonus,0)) over(partition by deptno order by pay desc) 부서별누계
from professor
;

 

5.LISTAGG

-- listagg : 그룹 컬럼을 기준으로 컬럼 값들의 목록 리턴해줍니다. select deptno, listagg(name) within group(order by hiredate) listagg
from professor
group by deptno
;

이름을 listagg 컬럼에 hiredate 순서대로 출력하되 deptno 컬럼으로 그룹으로 묶어준다.