대용량 데이터베이스2권 <2-17 page> 예시의 나만의 조회방법

WITH TEMP
AS  
( SELECT '1101' AS 일련번호,  '19970501' AS 발생일,  '19970520' AS 시작일,  '19970925' AS 종료일,  5000000 AS 선수금  FROM DUAL
  UNION ALL
  SELECT '1102' AS 일련번호,  '19970601' AS 발생일,  '19970615' AS 시작일,  '19970805' AS 종료일,  3800000 AS 선수금  FROM DUAL
  UNION ALL
  SELECT '1103' AS 일련번호,  '19970701' AS 발생일,  '19970712' AS 시작일,  '19971020' AS 종료일,  6780000 AS 선수금  FROM DUAL
  UNION ALL
  SELECT '1104' AS 일련번호,  '19970501' AS 발생일,  '19970507' AS 시작일,  '19970718' AS 종료일,  2890000 AS 선수금  FROM DUAL
)

SELECT  일련번호
,       TO_CHAR(월초, 'YYYYMM')                             AS 적용월
,       월초
,       월말
,       TO_CHAR((월말 - 월초 + 1) * 일금, 'FM099,999,999')  AS 일금
FROM (SELECT  일련번호
      ,       GREATEST(TRUNC(ADD_MONTHS(시작일, NO), 'MM'), 시작일) AS 월초
      ,       LEAST(LAST_DAY(ADD_MONTHS(시작일, NO)), 종료일)       AS 월말
      ,       선수금 / 일수                                         AS 일금
      FROM (SELECT  일련번호
            ,       TO_DATE(시작일, 'YYYYMMDD')                                                             AS 시작일
            ,       TO_DATE(종료일, 'YYYYMMDD')                                                             AS 종료일
            ,       선수금
            ,       TO_CHAR(TO_DATE(종료일, 'YYYYMMDD'), 'MM') - TO_CHAR(TO_DATE(시작일, 'YYYYMMDD'), 'MM') AS 월수
            ,       TO_DATE(종료일, 'YYYYMMDD') - TO_DATE(시작일, 'YYYYMMDD') + 1                           AS 일수
            FROM TEMP) T
      JOIN (SELECT LEVEL - 1 AS NO
            FROM DUAL
            CONNECT BY LEVEL <= 12) C ON C.NO <= T.월수)
ORDER BY 일련번호, 적용월;





일련번호    적용월  월초        월말        일금         
----        ------  --------    --------    ------------
1101        199705  97/05/20    97/05/31    000,465,116
1101        199706  97/06/01    97/06/30    001,162,791
1101        199707  97/07/01    97/07/31    001,201,550
1101        199708  97/08/01    97/08/31    001,201,550
1101        199709  97/09/01    97/09/25    000,968,992

1102        199706  97/06/15    97/06/30    001,169,231
1102        199707  97/07/01    97/07/31    002,265,385
1102        199708  97/08/01    97/08/05    000,365,385

1103        199707  97/07/12    97/07/31    001,342,574
1103        199708  97/08/01    97/08/31    002,080,990
1103        199709  97/09/01    97/09/30    002,013,861
1103        199710  97/10/01    97/10/20    001,342,574

1104        199705  97/05/07    97/05/31    000,989,726
1104        199706  97/06/01    97/06/30    001,187,671
1104        199707  97/07/01    97/07/18    000,712,603

15개의 행이 선택됨 










-- 특정 기간 쪼개기
SELECT  D.NO
,       T.FR_DATE
,       T.TO_DATE
,       TRUNC(ADD_MONTHS(T.FR_DATE, D.NO), 'MM')
,       LAST_DAY(ADD_MONTHS(T.FR_DATE, D.NO))

,       GREATEST(TRUNC(ADD_MONTHS(T.FR_DATE, D.NO), 'MM'), T.FR_DATE)   AS ST_DATE
,       LEAST(LAST_DAY(ADD_MONTHS(T.FR_DATE, D.NO)), T.TO_DATE)         AS ED_DATE

FROM   (SELECT  TO_DATE('20200131', 'YYYYMMDD') AS FR_DATE
        ,       TO_DATE('20200423', 'YYYYMMDD') AS TO_DATE
        FROM DUAL) T
        
JOIN   (SELECT LEVEL - 1 AS NO
        FROM DUAL
        CONNECT BY LEVEL <= 12) D ON D.NO <= (TO_CHAR(TO_DATE, 'MM') - TO_CHAR(FR_DATE, 'MM'))
;