oracle sql partition by 사용 예문

Colored By Color Scripter™

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT 1 AS COL1, 10 AS COL2, 'A' AS COL3 FROM DUAL UNION ALL SELECT 2 AS COL1, 20 AS COL2, 'A' AS COL3 FROM DUAL UNION ALL SELECT 3 AS COL1, 30 AS COL2, 'A' AS COL3 FROM DUAL UNION ALL SELECT 4 AS COL1, 40 AS COL2, 'A' AS COL3 FROM DUAL UNION ALL SELECT 5 AS COL1, 50 AS COL2, 'B' AS COL3 FROM DUAL UNION ALL SELECT 6 AS COL1, 60 AS COL2, 'B' AS COL3 FROM DUAL UNION ALL SELECT 7 AS COL1, 70 AS COL2, 'C' AS COL3 FROM DUAL ;

예를 들기 위해 위와같이 임의의 테이블을 만들었습니다.

Colored By Color Scripter™

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT COL3, SUM(COL2), AVG(COL2) FROM (     SELECT 1 AS COL1, 10 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 2 AS COL1, 20 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 3 AS COL1, 30 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 4 AS COL1, 40 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 5 AS COL1, 50 AS COL2, 'B' AS COL3 FROM DUAL     UNION ALL     SELECT 6 AS COL1, 60 AS COL2, 'B' AS COL3 FROM DUAL     UNION ALL     SELECT 7 AS COL1, 70 AS COL2, 'C' AS COL3 FROM DUAL ) TEMP GROUP BY COL3 ;

GROUP BY 를 사용해서 그룹별 합계와 평균을 위처름 구할 수 있습니다.

 

Colored By Color Scripter™

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT COL1, COL2, COL3       ,SUM(COL2) OVER (PARTITION BY COL3) AS "그룹합계"       ,AVG(COL2) OVER (PARTITION BY COL3) AS "그룹평균"       ,AVG(COL2) OVER (PARTITION BY COL3) - COL2 AS "평균과의차" FROM  (     SELECT 1 AS COL1, 10 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 2 AS COL1, 20 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 3 AS COL1, 30 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 4 AS COL1, 40 AS COL2, 'A' AS COL3 FROM DUAL     UNION ALL     SELECT 5 AS COL1, 50 AS COL2, 'B' AS COL3 FROM DUAL     UNION ALL     SELECT 6 AS COL1, 60 AS COL2, 'B' AS COL3 FROM DUAL     UNION ALL     SELECT 7 AS COL1, 70 AS COL2, 'C' AS COL3 FROM DUAL     ) TEMP ;

그룹별 합계와 평균을 해당 row에 사용할 때, 위처럼 사용할 수 있습니다.

번외로, DISTINCT가 걸려있는 데이터들에 대해서 PARTITION BY를 사용하면, DISTINCT를 무시한 모든 DATA에 대해서 PARTITION BY를 수행합니다. DISTINCT보다 PARTITION BY가 먼저 수행된다는 이야기.. 복잡한 SQL수정 하면서 데이터가 이상하게 나왔는데 그게 DISTINCT 때문인 것을 발견한 적이 있네요.