데이터베이스 질문과 답변
쿼리 질문 드립니다.
계속 생각해 봤는데 모르겠어요 ㅠ
아래와 같이 각 CUST_NO 기준,
가장 최근에 발생(PROC_DATE)된 GRADE와 동일한 GRADE의 발생 시점 PROC_DATE를 추출하고자 합니다.
말이 쫌 어려운데, 아래 그림을 보시면 이해가 되실거라 생각됩니다.
동일 GRADE의 최초 PROC_DATE가 아닌,
동일 GRADE로 발생되기 시작한 시점의 PROC_DATE 추출이 필요합니다.
도움 부탁드립니다.
감사합니다.
| CUST_NO | PROC_DATE | GRADE |
| 111 | 201901 | 1 |
| 111 | 201902 | 2 |
| 111 | 201903 | 1 |
| 111 | 201904 | 1 |
| 111 | 201905 | 2 |
| 111 | 201906 | 2 |
| 222 | 201901 | 3 |
| 222 | 201902 | 3 |
| 222 | 201903 | 3 |
| 222 | 201904 | 4 |
| 222 | 201905 | 4 |
| 222 | 201906 | 4 |
by 마농
[2020.05.12 15:36:05]
WITH t AS
(
SELECT 111 cust_no, '201901' proc_date, 1 grade FROM dual
UNION ALL SELECT 111, '201902', 2 FROM dual
UNION ALL SELECT 111, '201903', 1 FROM dual
UNION ALL SELECT 111, '201904', 1 FROM dual
UNION ALL SELECT 111, '201905', 2 FROM dual
UNION ALL SELECT 111, '201906', 2 FROM dual
UNION ALL SELECT 222, '201901', 3 FROM dual
UNION ALL SELECT 222, '201902', 3 FROM dual
UNION ALL SELECT 222, '201903', 3 FROM dual
UNION ALL SELECT 222, '201904', 4 FROM dual
UNION ALL SELECT 222, '201905', 4 FROM dual
UNION ALL SELECT 222, '201906', 4 FROM dual
)
SELECT cust_no
, grade
, MIN(proc_date) sdt
, MAX(proc_date) edt
FROM (SELECT cust_no, proc_date, grade
, ROW_NUMBER() OVER(PARTITION BY cust_no ORDER BY proc_date DESC) rn1
, ROW_NUMBER() OVER(PARTITION BY cust_no, grade ORDER BY proc_date DESC) rn2
FROM t
)
WHERE rn1 = rn2
GROUP BY cust_no, grade
;
by 샤랄라
[2020.05.12 16:13:40]
with t(cust_no, proc_date, grade) as
(select 111, '201901', 1 from dual union all
select 111, '201902', 2 from dual union all
select 111, '201903', 1 from dual union all
select 111, '201904', 1 from dual union all
select 111, '201905', 2 from dual union all
select 111, '201906', 2 from dual union all
select 222, '201901', 3 from dual union all
select 222, '201902', 3 from dual union all
select 222, '201903', 5 from dual union all
select 222, '201904', 5 from dual union all
select 222, '201905', 5 from dual union all
select 222, '201906', 6 from dual
)
select cust_no, proc_date, grade
from (select cust_no
, proc_date
, grade
, rank() over(partition by cust_no order by proc_date desc) rk
from (select cust_no
, proc_date
, grade
, lag(grade) over(partition by cust_no order by proc_date) lg
, lead(grade) over(partition by cust_no order by proc_date) ld
from t
)
where 1=1
and grade = ld
and grade != lg
)
where rk = 1
;
) 버튼을 클릭하여 작성 하시면 됩니다.