Oracle에서 유지 여부와 상관없이 파티션 분할

Oracle에서 유지 여부와 상관없이 파티션 분할

파티션에 집계 함수를 적용하는 두 가지 쿼리를 발견했습니다.

저는 이 두 가지 질문 사이에 차이가 있는지 궁금합니다.

SELECT empno,
   deptno,
   sal,
   MIN(sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

SELECT empno,
   deptno,
   sal,
   MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

첫 번째 버전은 논리적이지만 두 번째 버전은 특별한 경우일 수도 있고 성능 최적화일 수도 있습니다.

MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

문은 (대략) 오른쪽에서 왼쪽 순서로 고려할 수 있습니다.

• OVER (PARTITION BY deptno)행을 구별되는 그룹으로 분할하는 것을 의미합니다.deptno그 때에
• ORDER BY sal평균, 각 파티션에 대해 행 순서 지정sal(반드시 사용)ASC종료 순서); 그러면
• KEEP (DENSE_RANK FIRST각 파티션에 대해 순서가 지정된 행에 (연속적인) 순위를 부여하고(순서 열에 대한 값이 동일한 행에는 동일한 순위가 부여됨), 마지막으로 순위가 지정되지 않은 모든 행을 삭제합니다.
• MIN(sal)각 파티션의 나머지 행에 대해 최소 급여를 반환합니다.

이 경우에는MIN그리고.DENSE_RANK FIRST둘 다 운영 중입니다.sal칼럼 그래서 같은 일을 할 것이고 그리고.KEEP (DENSE_RANK FIRST ORDER BY sal)중복됩니다.

그러나 최소값에 대해 다른 열을 사용하면 다음과 같은 효과를 볼 수 있습니다.

SQL 피들

Oracle 11g R2 스키마 설정:

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

쿼리 1:

SELECT DISTINCT
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test

결과:

| MIN_SAL_FIRST_SAL | MAX_SAL_FIRST_SAL | MIN_NAME_FIRST_SAL | MAX_NAME_FIRST_SAL | MIN_NAME_LAST_SAL | MAX_NAME_LAST_SAL | DEPTNO |
|-------------------|-------------------|--------------------|--------------------|-------------------|-------------------|--------|
|                 1 |                 1 |                  a |                  c |                 e |                 f |      1 |
|                 4 |                 4 |                  g |                  h |                 i |                 j |      2 |

이 예제에서는 집계가 정렬하는 열과 동일한 열에 있기 때문에 차이가 없습니다."KEEP"의 실제 포인트/파워는 서로 다른 열을 집계하고 정렬할 때입니다.예를 들어, (다른 답변에서 "테스트" 표를 빌림)...

SELECT deptno,  min(name) keep ( dense_rank first order by sal desc, name  ) ,
max(sal)
FROM test
group by deptno

;

이 쿼리는 각 부서에서 가장 높은 급여를 받는 사람의 이름을 가져옵니다."KEEP" 조항이 없는 대안을 고려해 보십시오.

SELECT deptno, name, sal
FROM test t
WHERE not exists ( SELECT 'person with higher salary in same department'
                                            FROM test t2  
                                            WHERE t2.deptno = t.deptno
                                            and ((  t2.sal > t.sal )
                                            OR ( t2.sal = t.sal AND t2.name < t.name ) ) )

KEEP 절은 더 쉽고 효율적입니다(이 간단한 예에서는 3개의 일관된 get 대 34개의 get).

@MT0의 답변에 언급된 한 가지 차이점에 대해 자세히 설명합니다.첫 번째 쿼리에서는 집계 함수 MIN과 MAX가 작업을 수행하고 두 번째 쿼리에서는 실제 행이 FIRST, LAST 및 KEEP에 의해 선택됩니다.

두 번째 예제에서는 MAX를 MIN으로 대체할 수도 있으며 이는 여전히 정답(최고 연봉)을 제공합니다.

자세한 내용은 다음 문서를 참조하십시오.

두 개의 열을 기준으로 순서를 지정하고 두 열 중 하나를 가져오거나 둘 다 가져오는 경우에 유용할 수 있습니다.

CREATE TABLE test (name, sal, deptno) AS
SELECT 'adam', 100, 1 FROM DUAL
UNION ALL SELECT 'bravo', 500, 1 FROM DUAL
UNION ALL SELECT 'coy', 456, 1 FROM DUAL
UNION ALL SELECT 'david', 50, 1 FROM DUAL
UNION ALL SELECT 'ethan', 50, 1 FROM DUAL
UNION ALL SELECT 'feral', 300, 1 FROM DUAL;

이제 사람의 급여뿐만 아니라 급여가 가장 낮은 직원을 선택하려고 합니다.조건은 두 명의 직원이 동일한 최저 임금을 받는 경우 이름이 알파벳 순으로 첫 번째인 직원을 데려오는 것입니다.

  select o.deptno
,min(o.sal) keep 
  (dense_rank first order by o.sal, o.name) least_salary
,min(o.name) keep 
  (dense_rank first order by o.sal, o.name) least_salary_person
 from test o
  group by 
 o.deptno;

출력:

DEPTNO  LEAST_SALARY    LEAST_SALARY_PERSON
1        50             david

이 쿼리는 각 부서에서 가장 높은 급여를 받는 사람의 이름을 가져옵니다.

select MIN(ename),sal,deptno
from emp where sal in
   (
    select max(sal) from emp group by deptno
   )
GROUP BY sal,deptno;

언급URL : https://stackoverflow.com/questions/20145952/partition-by-with-and-without-keep-in-oracle