데이터베이스 질문과 답변
분기별 신청가능건수 : 10건일 때, 지난달 초과건수를 조회할 수 있는 쿼리를 짜고 싶습니다.
WITH T (DT, EMPNO, MAINK) AS (
SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '123456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '223456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '323456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '423456789' FROM DUAL UNION ALL
SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '523456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '623456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '723456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '823456789' FROM DUAL UNION ALL
SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '923456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '193456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '183456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '173456789' FROM DUAL UNION ALL
SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '163456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '153456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '143456789' FROM DUAL UNION ALL
SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '133456789' FROM DUAL
)
SELECT DT, EMPNO, COUNT(*)
FROM T
GROUP BY DT, EMPNO
위 쿼리로 봤을 때,
1월 4건, 2월 4건, 3월 4건으로
1월 초과 0건, 2월 초과 0건, 3월 초과 2건으로 출력하고 싶어요.
만약에 1월 4건 2월 8건, 3월 4건이라면,
1월 초과 0건, 2월 초과 2건, 3월 초과 4건으로 출력이 가능하게끔 쿼리를 짜려면 어떻게 해야할까요?
도움 부탁드립니다!
by 동동동
[2021.06.03 17:49:34]
예시 with에는 4월까지 있는데 결과가
1월에 0
2월에 0
3월에 2
4월에 4
로 나와야 하는 건가요?
만약 5월에 1건이 있다면 5월 초과 건수는 1로..??
by 마농
[2021.06.03 18:11:19]
WITH t (dt, empno, maink) AS
(
SELECT DATE '2020-01-01', '1001', '123456789' FROM dual UNION ALL
SELECT DATE '2020-02-01', '1001', '223456789' FROM dual UNION ALL
SELECT DATE '2020-03-01', '1001', '323456789' FROM dual UNION ALL
SELECT DATE '2020-04-01', '1001', '423456789' FROM dual UNION ALL
SELECT DATE '2020-01-01', '1001', '523456789' FROM dual UNION ALL
SELECT DATE '2020-02-01', '1001', '623456789' FROM dual UNION ALL
SELECT DATE '2020-03-01', '1001', '723456789' FROM dual UNION ALL
SELECT DATE '2020-04-01', '1001', '823456789' FROM dual UNION ALL
SELECT DATE '2020-01-01', '1001', '923456789' FROM dual UNION ALL
SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL
SELECT DATE '2020-03-01', '1001', '183456789' FROM dual UNION ALL
SELECT DATE '2020-04-01', '1001', '173456789' FROM dual UNION ALL
SELECT DATE '2020-01-01', '1001', '163456789' FROM dual UNION ALL
SELECT DATE '2020-02-01', '1001', '153456789' FROM dual UNION ALL
SELECT DATE '2020-03-01', '1001', '143456789' FROM dual UNION ALL
--SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL
--SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL
--SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL
--SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL
SELECT DATE '2020-04-01', '1001', '133456789' FROM dual
)
SELECT empno
, TO_CHAR(dt, 'yyyy-q') yq
, TO_CHAR(dt, 'yyyy-mm') ym
, COUNT(*) cnt
, LEAST(GREATEST(
SUM(COUNT(*)) OVER(PARTITION BY empno, TO_CHAR(dt, 'yyyy-q') ORDER BY TO_CHAR(dt, 'yyyy-mm')) - 10
, 0), COUNT(*)) over_cnt
FROM t
GROUP BY empno
, TO_CHAR(dt, 'yyyy-q')
, TO_CHAR(dt, 'yyyy-mm')
;
by Fortran
[2021.06.05 22:58:11]
오래 고민하다가 안되서 질문했는데, 빠르게 답변해주셔서 정말 감사드립니다!
잘 사용하고, 더 공부하겠습니다. 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.