window절을 이용한 쿼리 연습

window 절을 이용한 쿼리 연습

책의 내용이라 그런데,,,이게 저작권에 저촉 되는건가...

	SELECT *
	FROM departments;
	--Window 절
	SELECT department_id
	,emp_name
	,hire_date
	,salary
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS all_salary
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS first_current_sal
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
	) AS current_end_sal
	FROM employees
	WHERE department_id IN ( 30, 90 );
	SELECT department_id
	,emp_name
	,hire_date
	,salary
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS all_salary
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	--RANGE에서 ORDER BY 컬럼에 한해서 상수의 범위를 지정 할 수 있다.
	RANGE 365 PRECEDING
	) AS range_sal1
	,SUM(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	RANGE BETWEEN 365 PRECEDING AND CURRENT ROW
	) AS range_sal2
	FROM employees
	WHERE department_id = 30;
	--FIRST_VALUE/LAST_VALUE절
	SELECT department_id
	,emp_name
	,hire_date
	,salary
	,FIRST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS all_salary
	,FIRST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS fr_st_to_current_sal
	,FIRST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS fr_current_to_end_sal
	FROM employees
	WHERE department_id IN ( 30, 90 );
	SELECT department_id
	,emp_name
	,hire_date
	,salary
	,NTH_VALUE(salary, 2) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS all_salary
	,NTH_VALUE(salary, 2) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS fr_st_to_current_sal
	,NTH_VALUE(salary, 2) OVER ( PARTITION BY department_id ORDER BY hire_Date
	ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
	) AS fr_curreunt_to_end_sal
	FROM employees
	WHERE department_id IN ( 30, 90 );
	SELECT department_id
	,emp_name
	,salary
	,NTILE(4) OVER ( PARTITION BY department_id ORDER BY salary ) AS NTILES
	,WIDTH_BUCKET(salary, 1000, 10000, 4) widthbucket
	-- 1000 ~ 10000까지를 4 분할을 한다는 의미
	-- NTILE는 파티션에 속한 로우의 수를 기준으로 나눈다.
	-- WIDTH_BUCKET은 매개변수에 따라 나뉜다.
	FROM employees
	WHERE department_id = 60;
	WITH basis AS ( SELECT period
	,region
	,SUM(loan_jan_amt) jan_amt
	FROM kor_loan_status
	GROUP BY period, region
	),
	basis2 as ( SELECT period
	,MIN(jan_amt) min_amt
	,MAX(jan_amt) max_amt
	FROM basis
	GROUP BY period
	)
	SELECT a.period
	,b.region "최소지역"
	,b.jan_amt "최소금액"
	,c.region "최대지역"
	,c.jan_amt "최대금액"
	FROM basis2 a, basis b, basis c
	WHERE a.period = b.period
	AND a.min_amt = b.jan_amt
	AND a.period = c.period
	AND a.max_amt = c.jan_amt
	ORDER BY 1, 2;
	--위의 쿼리를 FIRST와 LAST 함수를 이용해 간단하게 변경하기
	WITH basis AS (
	SELECT period
	,region
	,SUM(loan_jan_amt) jan_amt
	FROM kor_loan_status
	GROUP BY period, region
	)
	SELECT a.period
	,MIN(a.region) KEEP ( DENSE_RANK FIRST ORDER BY jan_amt ) "최소지역" -- 정렬해서 가장 첫 번째 값 가져옴
	,MIN(jan_amt) "최소금액"
	,MAX(a.region) KEEP ( DENSE_RANK LAST ORDER BY jan_amt ) "최대지역" -- 정렬해서 가장 마지막 값 가져옴
	,MAX(jan_amt) "최대금액"
	FROM basis a
	GROUP BY a.period
	ORDER BY 1, 2;
	SELECT department_id
	,emp_name
	,hire_date
	,salary
	--RATIO_TO_REPROT는 주어진 그룹에 대해 EXPR 값의 합을 기준으로 각 로우의 상대적 비율을 반환한다.
	,ROUND(RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) , 2) * 100 AS salary_percent
	FROM employees
	WHERE department_id IN ( 30 , 90 );

view raw windowWithFunction.sql hosted with ❤ by GitHub