데이터베이스 질문과 답변
휴가자가 가장 많은 특정일을 산출하고 싶습니다
1
by 김승현 [2024.06.12 16:31:43]
| EMPL_NO | VCTN_STR_DY | VCTN_END_DY |
| 202722 | 20240101 | 20240331 |
| 202722 | 20240401 | 20240630 |
| 204330 | 20240201 | 20240331 |
| 204312 | 20240101 | 20240331 |
| 204312 | 20240401 | 20240630 |
| 204354 | 20240103 | 20240402 |
이런 데이터 셋에서
사번 휴가시작일 휴가종료일입니다
가장 휴가자가 많은 휴가시작일과 휴가 종료일 사이 특정일을 산출할수 있는 쿼리를 만드려는 데 도움 부탁드립니다.
위 표를 봤을때는 2월1일부터~3월31일까지가 휴가자가 4명으로 휴가자가 가장많은 날인데
쿼리로는 못짜겠네요..
by 마농
[2024.06.12 17:21:19]
WITH t AS
(
SELECT '202722' empl_no, '20240101' vctn_str_dy, '20240331' vctn_end_dy FROM dual
UNION ALL SELECT '202722', '20240401', '20240630' FROM dual
UNION ALL SELECT '204330', '20240201', '20240331' FROM dual
UNION ALL SELECT '204312', '20240101', '20240331' FROM dual
UNION ALL SELECT '204312', '20240401', '20240630' FROM dual
UNION ALL SELECT '204354', '20240103', '20240402' FROM dual
)
SELECT *
FROM (SELECT TO_CHAR(sdt, 'yyyymmdd') sdt
, TO_CHAR(edt, 'yyyymmdd') edt
, cnt
, RANK() OVER(ORDER BY cnt DESC) rk
FROM (SELECT sdt
, LEAD(sdt - 1) OVER(ORDER BY sdt) edt
, SUM(SUM(cnt)) OVER(ORDER BY sdt) cnt
FROM (SELECT DECODE(lv, 1, TO_DATE(vctn_str_dy, 'yyyymmdd')
, TO_DATE(vctn_end_dy, 'yyyymmdd') + 1
) sdt
, DECODE(lv, 1, 1, -1) cnt
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2)
)
GROUP BY sdt
)
WHERE cnt != 0
)
WHERE rk = 1
;
-- http://gurubee.net/lecture/2848
by 김승현
[2024.06.13 11:21:22]
세상은 넓고 고수는 많네요
감사합니다
by 마농
[2024.06.13 14:03:32]
WITH t AS
(
SELECT '202722' empl_no, '20240101' vctn_str_dy, '20240331' vctn_end_dy FROM dual
UNION ALL SELECT '202722', '20240401', '20240630' FROM dual
UNION ALL SELECT '204330', '20240201', '20240331' FROM dual
UNION ALL SELECT '204312', '20240101', '20240331' FROM dual
UNION ALL SELECT '204312', '20240401', '20240630' FROM dual
UNION ALL SELECT '204354', '20240103', '20240402' FROM dual
)
SELECT *
FROM (SELECT TO_CHAR(sdt, 'yyyymmdd') sdt
, TO_CHAR(edt, 'yyyymmdd') edt
, cnt
, RANK() OVER(ORDER BY cnt DESC) rk
FROM (SELECT sdt
, LEAD(sdt - 1) OVER(ORDER BY sdt) edt
, SUM(SUM(cnt)) OVER(ORDER BY sdt) cnt
FROM (SELECT TO_DATE(vctn_str_dy, 'yyyymmdd') sdt
, TO_DATE(vctn_end_dy, 'yyyymmdd') + 1 edt
FROM t
)
UNPIVOT (sdt FOR cnt IN (sdt AS 1, edt AS -1))
GROUP BY sdt
)
WHERE cnt != 0
)
WHERE rk = 1
;
) 버튼을 클릭하여 작성 하시면 됩니다.