[sql] Oracle“파티션 별”키워드

누군가가 무엇을 설명해 주시겠습니까? partition by 키워드의 기능을 키워드의 실제 예와 사용 이유를 설명해 주시겠습니까? 다른 사람이 작성한 SQL 쿼리가 있으며 그 기능을 파악하려고합니다.

다음에 의한 파티션 예 :

SELECT empno, deptno, COUNT(*)
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp

온라인에서 본 예제는 너무 심도있게 보입니다.

답변

이 PARTITION BY절은 절의 각 “GROUP”에 사용될 레코드 범위를 설정합니다 OVER.

예제 SQL에서 DEPT_COUNT모든 직원 레코드에 대해 해당 부서 내의 직원 수를 리턴합니다. ( emp테이블을 비 노멀 화하는 것처럼 테이블에있는 모든 레코드를 반환 emp합니다.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

다른 열 (예 state:)이있는 경우 해당 주에 몇 개의 부서가 있는지 계산할 수 있습니다.

결과 집합을 집계하지 않고 GROUP BY( SUM, AVG등 의 결과를 얻는 것과 같습니다 (즉, 일치하는 레코드 제거).

당신이 사용하는 경우에 유용 LAST OVER또는 MIN OVER, 예를 들면, 최저 및 최고 부서에서 급여 한 후 사용 얻을 기능을하는이 기록 급여에 대한 계산 없이 훨씬 더 빨리, 이는 선택의 서브.

자세한 내용은 링크 된 AskTom 기사 를 참조하십시오.

답변

이 개념은 받아 들여진 대답으로 잘 설명되어 있지만, 더 많은 예제를 볼수록 더 잘 싱크되는 것을 알 수 있습니다. 다음은 증분 예제입니다.

1) 보스가 말한다 “브랜드별로 그룹화 한 품목 수를 알려줘”

당신은 말한다 : “문제 없음”

SELECT
      BRAND
      ,COUNT(ITEM_ID)
FROM
      ITEMS
GROUP BY
      BRAND;

결과:

+--------------+---------------+
|  Brand       |   Count       |
+--------------+---------------+
| H&M          |     50        |
+--------------+---------------+
| Hugo Boss    |     100       |
+--------------+---------------+
| No brand     |     22        |
+--------------+---------------+

2) 사장님이 말합니다 “이제 모든 브랜드의 아이템과 각 브랜드가 가지고있는 아이템의 개수를 알려주세요”

시도해 볼 수 있습니다 :

 SELECT
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID)
 FROM
      ITEMS
 GROUP BY
      BRAND;

그러나 당신은 얻는다 :

ORA-00979: not a GROUP BY expression

여기가 온 곳 OVER (PARTITION BY BRAND)입니다.

 SELECT
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND)
 FROM
      ITEMS;

Whic 의미 :

COUNT(ITEM_ID) -품목 수를 얻습니다
OVER -행 집합 이상
(PARTITION BY BRAND) -같은 브랜드입니다

결과는 다음과 같습니다.

+--------------+---------------+----------+
|  Items       |  Brand        | Count()  |
+--------------+---------------+----------+
|  Item 1      |  Hugo Boss    |   100    |
+--------------+---------------+----------+
|  Item 2      |  Hugo Boss    |   100    |
+--------------+---------------+----------+
|  Item 3      |  No brand     |   22     |
+--------------+---------------+----------+
|  Item 4      |  No brand     |   22     |
+--------------+---------------+----------+
|  Item 5      |  H&M          |   50     |
+--------------+---------------+----------+

기타…

답변

분석이라는 SQL 확장입니다. select 문의 “over”는 오라클이 해당 기능이 그룹 별 기능이 아니라 분석 기능임을 알려줍니다. 분석 사용의 이점은 하위 선택 또는 더 나쁜 PL / SQL로 데이터를 반복하는 대신 한 번의 데이터 통과로 합계, 개수 및 더 많은 것을 수집 할 수 있다는 것입니다.

처음에는 혼란스러워 보이지만 이것은 빨리 두 번째 자연이 될 것입니다. Tom Kyte보다 더 잘 설명하는 사람은 없습니다. 위의 링크는 훌륭합니다.

물론 문서 를 반드시 읽어야 합니다.

답변

EMPNO     DEPTNO DEPT_COUNT

 7839         10          4
 5555         10          4
 7934         10          4
 7782         10          4 --- 4 records in table for dept 10
 7902         20          4
 7566         20          4
 7876         20          4
 7369         20          4 --- 4 records in table for dept 20
 7900         30          6
 7844         30          6
 7654         30          6
 7521         30          6
 7499         30          6
 7698         30          6 --- 6 records in table for dept 30

여기서 우리는 각각의 deptno를 세고 있습니다. deptno 10의 경우 deptno 20 및 30에 대해서도 비슷한 결과를 표 emp에 4 개의 레코드가 있습니다.

답변

over partition 키워드는 client_id를 생성하여 각 클라이언트 ID의 하위 집합으로 데이터를 분할하는 것처럼

select client_id, operation_date,
       row_number() count(*) over (partition by client_id order by client_id ) as operationctrbyclient
from client_operations e
order by e.client_id;

이 쿼리는 client_id가 수행 한 작업 수를 반환합니다.

답변

이 예는 파티셔닝 작동 방식과 그룹화 작동 방식에 대한 작은 뉘앙스를 제안합니다. 내 예제는 컴파일 버그 인 경우 Oracle 12에서 가져온 것입니다.

나는 시도했다 :

SELECT t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t
group by t.data_key  ---- This does not compile as the compiler feels that t.state isn't in the group by and doesn't recognize the aggregation I'm looking for

그러나 이것은 예상대로 작동합니다.

SELECT distinct t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END)
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t;

외부 키 “data_key”를 기반으로 각 상태의 요소 수를 생성합니다. 따라서 data_key = ‘APPLE’에 상태 ‘A’의 3 행, 상태 ‘B’의 2 행, 상태 ‘C’의 행이있는 경우 ‘APPLE’에 해당하는 행은 ‘APPLE’, 3, 2가됩니다. , 1, 6.