WITH TB AS (
SELECT 'DDDD' AS COD, 'AAAA' AS NAM, NULL AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'BBBB' AS NAM, '200' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'CCCC' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'DDDD' AS NAM, '100' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'EEEE' AS NAM, '600' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'FFFF' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'GGGG' AS NAM, '400' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'HHHH' AS NAM, '900' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'IIII' AS NAM, NULL AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'JJJJ' AS NAM, '800' AS DPT
FROM DUAL
)
SELECT COD, NAM, DPT
, ROW_NUMBER() OVER (PARTITION BY COD ORDER BY DPT DESC) AS RANKV --그룹별 순위
, MIN(DPT) OVER (PARTITION BY COD) AS MINV --그룹별 최소값
, MAX(DPT) OVER (PARTITION BY COD) AS MAXV --그룹별 최대값
, SUM(DPT) OVER (PARTITION BY COD ORDER BY DPT DESC) AS SUMS --그룹별 누적합
, FIRST_VALUE(NAM) OVER (PARTITION BY COD) AS UROWV --그룹별 첫번째 값을 조회
, LAST_VALUE(NAM) OVER (PARTITION BY COD) AS DROWV --그룹별의 마지막 값을 조회
, LAG(NAM, 1, NULL) OVER (PARTITION BY COD ORDER BY COD) AS LAGV --상위행 NAM 값을 조회
, LEAD(NAM, 1, NULL) OVER (PARTITION BY COD ORDER BY COD) AS LEADV --하위행 NAM 값을 조회
FROM TB;
'DB-Oracle' 카테고리의 다른 글
계층구조 START WITH, 정렬 ORDER SIBLINGS BY (0) | 2023.08.08 |
---|---|
DBMS_RANDOM 렌덤값처리 (0) | 2023.08.08 |
Oracle NVL, NVL2, DECODE, NULLIF, COALESCE 함수 (0) | 2023.08.03 |
PL-SQL join에 대한 이해 (0) | 2023.07.25 |
특정문자가 포함된 스트링을 ROW단위 컬럼으로 출력하기 (0) | 2023.07.25 |