집 > 기사 > 데이터 베이스 > Oracle 쿼리에서 OVER (PARTITION BY ..) 사용법에 대한 자세한 설명

Oracle 쿼리에서 OVER (PARTITION BY ..) 사용법에 대한 자세한 설명

小云云원래의: 2017-12-11 13:23:118497검색

이 글은 주로 Oracle 쿼리에서의 OVER (PARTITION BY ..) 사용법을 소개하고 있으니, 내용과 코드를 참고하시면 도움이 되실 것 같습니다. 모든 사람의 학습과 테스트를 용이하게 하기 위해 모든 예제는 Oracle의 자체 사용자 Scott으로 생성되었습니다.

참고: 제목의 빨간색 "주문 기준"은 이 방법을 사용할 때 주문 기준이 포함되어야 함을 의미합니다.

1. Rank()/dense_rank() over(partition by ...order by ...)

이제 고객은 각 부서에서 가장 높은 연봉을 받는 직원의 정보를 쿼리해야 할 필요가 있다고 생각합니다. Oracle 응용 지식이 있다는 것을 학생들은 다음과 같은 SQL 문을 작성할 수 있습니다.

select e.ename, e.job, e.sal, e.deptno 
 from scott.emp e, 
    (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me 
 where e.deptno = me.deptno 
  and e.sal = me.sal;

고객 요구 사항을 충족하는 동안 모든 사람은 습관적으로 다른 방법이 있는지 생각해야 합니다. 확실히 그렇습니다. 이 섹션의 제목에 Rank() over(partition by...) 또는density_rank() over(partition by...) 구문을 사용하면 됩니다. SQL은 다음과 같습니다.

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        dense_rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

위 문장과 같은 결과가 나오는 이유는 무엇인가요? 다음은 Rank()/dense_rank() over(partition by e.deptno order by e.sal desc) 구문에 대한 보충 설명입니다.

over: 어떤 조건에서.

partition by e.deptno: 부서 번호(파티션)로 나눕니다.

order by e.sal desc: 급여를 높은 것에서 낮은 것으로 정렬합니다(rank()/dense_rank()를 사용할 때 order by를 포함해야 하며 그렇지 않으면 불법입니다)

rank()/dense_rank(): grading

전체 설명의 의미는 다음과 같습니다. 부서별 부서를 기준으로 직원은 급여에 따라 높음에서 낮음으로 등급이 매겨집니다. "레벨"은 작은 것부터 큰 것까지의 숫자로 표시됩니다(최소값은 1이어야 함). ).

그럼 Rank()와density_rank()의 차이점은 무엇인가요?

rank(): 점프 정렬, 첫 번째 레벨이 두 개 있으면 다음 레벨이 세 번째 레벨입니다.

dense_rank(): 연속 정렬, 두 개의 첫 번째 레벨이 있는 경우 다음 레벨은 여전히 두 번째 레벨입니다.

소과제 : 해당 부서의 최저임금에 대한 직원정보를 조회합니다.

2.min()/max() over(partition by...)

이제 부서의 최고/최저 급여를 쿼리하여 얻었으니 직원 정보를 쿼리하는 동안 다시 고객 요구가 왔습니다. 직원 급여와 부서 최대 급여/최저 급여 차이도 계산할 수 있습니다. 이는 첫 번째 섹션의 groupby 문을 기반으로 다음과 같이 수정합니다.

select e.ename, 
     e.job, 
     e.sal, 
     e.deptno, 
     e.sal - me.min_sal diff_min_sal, 
     me.max_sal - e.sal diff_max_sal 
  from scott.emp e, 
     (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal 
      from scott.emp e 
      group by e.deptno) me 
  where e.deptno = me.deptno 
  order by e.deptno, e.sal;

위에서 min()과 max()를 사용했는데 전자는 최소값을 찾고 후자는 최대값. 이 두 가지 방법을 over(partition by...)와 함께 사용하면 어떤 효과가 있을까요? 다음 SQL 문을 살펴보세요.

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, 
    nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal 
 from scott.emp e;

이 두 문의 쿼리 결과는 min()과 max()가 실제로 최소값과 최대값을 찾는 것을 볼 수 있지만 이는 파티션별로.

작은 숙제: 이 예에 order by를 추가하면 어떤 결과가 나올까요?

3.lead()/lag() over(partition by ... order by ...)

중국 사람들은 비교를 좋아하고 체면을 세우는 것을 좋아하며 세계적으로 유명합니다. 고객은 최고/최저 급여와 비교한 후에도 여전히 부족하다고 느꼈습니다. 이번에는 자신의 급여와 개인 급여의 차액을 계산하라는 다소 왜곡된 요구를 내놓았습니다. 그 사람보다 높거나 낮은 사람. 이 요구 사항은 정말 당황스럽습니다. groupby 문에서 이를 구현하는 방법을 모르겠습니다. 하지만. . . . 이제 over(partition by...)가 있으므로 모든 것이 매우 단순해 보입니다. 다음과 같습니다:

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, 
    lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, 
    nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 
      0) diff_lead_sal, 
    nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal 
 from scott.emp e;

위 문장을 읽고 나면, 여러분도 이것이 허위 경보(식은땀을 흘리다가 갑자기 얼어붙어 감기에 걸리기 쉬워진다)라는 생각이 드시나요? 위에서 사용된 두 가지 새로운 방법을 설명하겠습니다.

lead(열명,n,m): 현재 레코드 이후 n번째 행에 기록된 8a25169a0e8817d4abdb443af86cbbef의 값입니다. 매개변수가 없으면 기본값은 m입니다. m, 현재 레코드를 검색합니다. 첫 번째 행에는 895a828a5767d0ff83c19d6a93c7da7c의 값이 기록됩니다. 그렇지 않은 경우 기본값은 null입니다.

lag(열명,n,m): 현재 레코드 앞의 n번째 행에 기록된 8a25169a0e8817d4abdb443af86cbbef의 값입니다. 매개변수가 없으면 기본값은 m입니다. m, 현재 레코드 이전의 값을 검색하면 첫 번째 행에 2510af6ebb6ca97b2031807eae7f5aab의 값이 기록됩니다. 그렇지 않은 경우 기본값은 null입니다.

다음은 이 문법에 사용되는 몇 가지 일반적인 방법을 나열합니다(참고: order by 절이 있는 방법은 이 방법을 사용할 때 order by가 포함되어야 함을 나타냅니다). 즉, OVER (PARTITION BY ..) 가 GROUP BY 보다 낫다는 점은 전자가 후자를 대체할 수 없고, 전자의 실행 효율성이 후자만큼 높지는 않다는 점이다. 더 많은 기능을 제공할 뿐이므로 사용하실 때 필요에 따라 선택하시면 좋겠습니다.

Oracle 쿼리에서 OVER (PARTITION BY ..) 사용법에 대한 자세한 설명

관련 기사