[SQL 쿡북] Chapter 10 : 범위 관련 작업 (MYSQL)

10.2 같은 그룹 또는 파티션의 행 간 차이 찾기 

같은 부서, 즉 deptno이 같은 사원간의 급여 차이를 나타내려고 한다. 그리고 고용된 순으로 나타내고, 마지막으로 고용된 사원의 급여 차액은 'N/A로 반환하려고 한다. 

with next_sal_tab (deptno, ename, sal, hiredate, next_sal)
as
(select deptno, ename, sal, hiredate, 
	lead(sal) over(partition by deptno order by hiredate) as next_sal
  from emp)
  
    select deptno, ename, sal, hiredate,
			coalesce(cast(sal - next_sal as char), 'N/A') as diff
	from next_sal_tab

cast는 하지 않아도 같은 결과 셋이 나오는 것을 확인했다. next_sal이 null인 경우 diff를 N/A로 표기시켜준다. 

[SQL] COALESCE 함수로 NULL 값 변환하기

cast 참고

 

10.4 값 범위에서 누락된 값 채우기 

사원을 고용하지 않은 해에는 0을 반환하고 나머지는 고용한 사원의 수를 반환한다.  테이블에 연도가 없으면 어떻게 0을 반환할 수 있을까? 이를 해결하기 위해 외부 조인을 사용한다. 

select y.yr, coalesce(x.cnt, 0) as cnt
	from (
 select min_year - mod(min_year,10) + rn as yr
	from (
 select (select min(extract(year from hiredate))
			from emp) as min_year,
		id -1 as rn
	from t10
		) a
        ) y
	left join
		(
  select extract(year from hiredate) as yr, count(*) as cnt
	from emp
   group by extract(year from hiredate)
         ) x
         on(y.yr = x.yr)

MySQL의 EXTRACT () 함수