# Oracle Analytic Functions 누적합계 : Oracle, Analytic, Functions, 누적합계
### # 누적합계
- 100개 로우를 1시간단위로 생성합니다.
- 조회된 데이터를 이용하여, PARTITION BY SUBSTR(YYYYMMDDHH24,1,8) - 일자별로 파티셔닝합니다.
- ORDER BY YYYYMMDDHH24 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
윈도우의 시작위치부터 현재로우까지의 누적 합계
```sql
SELECT
SUBSTR(YYYYMMDDHH24,1,8) YYYYMMDD
, SUBSTR(YYYYMMDDHH24,9,2) HH
, COUNT(*) AS CNT
, SUM(COUNT(*)) OVER(PARTITION BY SUBSTR(YYYYMMDDHH24,1,8) ORDER BY YYYYMMDDHH24 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS TOTAL
FROM (
-- CREATE 100 ROWS : INTERVAL TIME 1 HOUR
SELECT
sysdate + 1/24*level REG_DTTM
,TO_CHAR(sysdate + 1/24*level,'YYYYMMDDHH24') YYYYMMDDHH24
FROM DUAL
CONNECT BY level <= 100
) A
--WHERE SUBSTR(YYYYMMDDHH24,1,8) = TO_CHAR(sysdate,'YYYYMMDD')
--WHERE TO_CHAR(REG_DTTM,'YYYYMMDD') = '20190809'
GROUP BY YYYYMMDDHH24
;
```
댓글