[SQL 쿡북] 11. 고급 검색

11.1 결과셋을 페이지로 매기기

11.2 테이블에서 n개 행 건너뛰기

11.3 외부 조인을 사용할 때 OR 로직 통합하기

11.4 역수 행 확인하기

select *
  from V
--------------------------------------------------------------------
select distinct v1.*
  from V v1, V v2
 where v1.test1 = v2.test2
   and v1.test2 = v2.test1
   and v1.test1 <= v1.test2
--------------------------------------------------------------------
select v1.*
  from V v1, V v2
 where v1.test1 = v2.test2
   and v1.test2 = v2.test1

11.5 상위 n개 레코드 선택하기

11.6 최댓값과 최솟값을 가진 레코드 찾기

<DB2, Oracle, SQL Server>
  select ename
    from (
  select ename, sal,
         min(sal)over() min_sal,
         max(sal)over() max_sal
    from emp
         ) x
   where sal in (min_sal,max_sal)

11.7 이후 행 조사하기

최근 고용된 사원보다 수입이 적은 사원을 찾는다.

  select ename, sal, hiredate
    from (
  select ename, sal, hiredate,
         lead(sal)over(order by hiredate) next_sal
    from emp
         ) alias
   where sal < next_sal

윈도우 함수 LEAD OVER을 사용하여 채용된 다음 사원의 급여를 액세스할 수 있습니다. 그 뒤에 급여가 더 큰지 비교한다.

'

select ename, sal, hiredate
  from (
select ename, sal, hiredate,
       lead(sal,cnt-rn+1)over(order by hiredate) next_sal
  from (
select ename,sal,hiredate,
       count(*)over(partition by hiredate) cnt,
       row_number()over(partition by hiredate order by empno) rn
  from emp
       )
       )
 where sal < next_sal

현재 행에서 비교해야 하는 행까지의 거리를 찾는 것입니다. 

CNT 값은 HIREDATE가 중복된 각 사원의 HIREDATE에 대한 총 중복 수를 나타냅니다. RN 값은 DEPTNO 10의 사원에 대한 순위를 나타냅니다. 순위는 HIREDATE로 분할되므로 다른 사원과 중복되는 HIREDATE가 있는 사원만 1보다 큰 값을 갖습니다. 이때 순위는 임의로 EMPNO로 정렬합니다.

 

11.8 행 값 이동하기

가각 사원명과 급여를 다음으로 높은 급여와 가장 낮은 급여와 함께 반환하려고 합니다.

  select ename,sal,
         coalesce(lead(sal)over(order by sal),min(sal)over()) forward,
         coalesce(lag(sal)over(order by sal),max(sal)over()) rewind
    from emp

윈도우 함수 LAG OVER와 LEAD OVER를 사용하여 현재 행을 기준으로 이전 및 다음 행에 액세스 합니다. 정렬은 SAL을 기준으로 하고 MIN과 MAX를 이용해 가장 높은 급여와 가장 낮은 급여를 반환합니다.

 

select ename,sal,
       lead(sal,3)over(order by sal) forward,
       lag(sal,5)over(order by sal) rewind
  from emp

3개 행을 앞으로 이동하고 5개 행을 뒤로 이동합니다.

 

11.9 순위 결과

동점을 허용하면서 EMP 테이블에서 급여 순위를 매겨 결과셋을 반환합니다.

DENSE_RANK OVER, ROW_NUMBER OVER 및 RANK OVER의 세 가지 윈도우 함수는 순위 지정에 특히 유용합니다.

 

11.10 중복 방지하기

 select job
    from (
  select job,
         row_number()over(partition by job order by job) rn
    from emp
         ) x
   where rn = 1

DISTINCT와 GROUP BY는 전통적인 방법

select distinct job
  from emp
--------------------------------------------------------------------
select job
  from emp
 group by job

 

11.11 기사값 찾기

각 부서에서 각 사원명, 근무하는 부서, 급여, 고용된 날짜 및 마지막으로 고용된 사원의 급여가 포함된 결과셋을 반환하려고 합니다. 

LATEST_SAL 값은 이를 찾는 경로가 체스 게임에서 기사가 움직이는 경로와 유사하여 '기사값'이라 합니다. 

<Oracle>
  select deptno,
          ename,
          sal,
          hiredate,
           max(sal)
             keep(dense_rank last order by hiredate)
             over(partition by deptno) latest_sal
    from emp
  order by 1, 4 desc

KEEP 절을 사용하면 그룹/파티션에서 반환된 행의 순위를 매기고 그룹의 첫 번째 또는 마지막 행에 대해 작업할 수 있습니다.

 

11.12 간단한 예측 생성하기