*39일차 (2)

@WINDOW FUNCTION

• 행과 행간의 관계를 쉽게 파악/정의하기 위한 ANSI 표준함수
• SELECT절에만 사용가능
• 순위관련처리, 집계관련처리, 순서관련처리, 비율/통계관련처리

 

*순위관련 윈도우 함수

--window_function (args) over ([partition by절][order by절][windowing절])

• args 함수인자(컬럼명) 0 ~ n개
• partition by : 전체집합을 다시 그룹핑하기 위한 구문
• order by  : 행간의 정렬
• windowing절 : 대상행을 지정

 

--rank() over()

• 중복된 값이 있으면 중복된 만큼 건너뛰고 순위부여 

 

--dense_rank() over()

• 중복된 값이 있어도 중복된 만큼 건너뛰지 않고 순위부여

select
    emp_name,
    salary,
    rank() over(order by salary desc) rank,
    dense_rank() over(order by salary desc) rank
from
    employee;

 

-- top-n분석에 활용

select
    *
from(
    select
    emp_name,
    salary,
    rank() over(order by salary desc) rank
from
    employee
)
where
    rank between 6 and 10;

 

--부서별 급여순위 조회

select
    emp_name,
    dept_code,
    salary,
    rank() over(partition by dept_code order by salary desc) rank_by_dept,
    rank() over(order by salary desc) rank_by_all
from
    employee
order by
    dept_code;

 

-- 부서별 입사순서를 조회(사원명, 부서명, 입사일, 부서별 입사순번)

select
    emp_name,
    (select dept_title from department d where dept_id = e.dept_code) dept_title,
    hire_date,
    rank () over(partition by dept_code order by hire_date) rank,
    dense_rank () over(partition by dept_code order by hire_date) rank,
    row_number () over(partition by dept_code order by hire_date) rank
from
    employee e;

 

*집계처리 윈도우 함수

• 집계/누계 관련 처리

 

--sum() over()

• 그룹함수와 일반컬럼은 같이 쓸 수 없지만 윈도우 함수 일반컬럼은 함께 사용 가능

select
    emp_name,
    dept_code,
    salary,
    sum(salary) over() "전사원 급여합계",
    trunc(salary / sum(salary) over() * 100, 1) "전체급여대비%",
    sum(salary) over(partition by dept_code) "부서별 급여합계",
    sum(salary) over(partition by dept_code order by salary) "부서별 누계"
from
    employee;

 

--판매테이블 지난 3개월 제품별 누계

select
    s.*,
    sum(pcount) over(partition by pname order by sale_date) "제품별 누계"
from (
    select * from tb_sales_202201
    union all
    select * from tb_sales_202202
    union all
    select * from tb_sales
) s;

 

-- 사원정보 조회(사번, 사원명, 부서코드, 급여, 부서별평균급여, 전체사원평균급여)

select
    emp_id,
    emp_name,
    dept_code,
    salary,
    trunc(avg(salary) over(partition by dept_code)) avg_sal_by_dept,
    trunc(avg(salary) over()) avg_sal_by_all,
    
    max(salary) over(),
    min(salary) over(),
    count(*) over()
from
    employee;