데이터베이스 질문과 답변
with t as ( select '20171025'as dt ,'1' id from dual union all select '20171025','1' from dual union all select '20171025','2' from dual union all select '20171025','2' from dual union all select '20171026','1' from dual union all select '20171026','1' from dual union all select '20171026','1' from dual union all select '20171027','1' from dual union all select '20171027','1' from dual union all select '20171027','1' from dual ) select dt,SUM(count(*)) OVER(ORDER BY dt) 누적접속건수 from t group by dt
DT 누적CNT
20171025 4
20171026 7
20171027 10
원하는 결과가 위와같습니다.
SUM OVER 함수를 사용하니 쉽게 원하는결과를 얻었는데
SUM OVER 함수를 안쓰고 할수있는 방법을 찾아보라고하시는데..혹시 다른방법이 있는지 많은 조언부탁드립니ㅐ다.
by 우리집아찌
[2017.10.27 16:18:47]
-- 근데 왜 안쓰시나요?
with t as
( select '20171025'as dt ,'1' id from dual union all
select '20171025','1' from dual union all
select '20171025','2' from dual union all
select '20171025','2' from dual union all
select '20171026','1' from dual union all
select '20171026','1' from dual union all
select '20171026','1' from dual union all
select '20171027','1' from dual union all
select '20171027','1' from dual union all
select '20171027','1' from dual
)
SELECT DT
, (SELECT COUNT(*) FROM T B WHERE B.DT <= A.DT )
FROM T A
GROUP BY DT
ORDER BY DT
by DB초보자입니다
[2017.10.27 16:22:50]
저희..회사 요상한 사수분께서..괜한 딴지를 걸고싶으신가봐요..
by jkson
[2017.10.27 17:07:24]
딴지라기 보다.. 윈도우 함수 사용하지 말고 해보라는 것 같은데..
상대적으로 윈도우 함수가 얼마나 편하고 성능상 이점이 많은 함수인지 느껴보라는 것 아닐까요?ㅎㅎ
by 마농
[2017.10.30 13:49:05]
-- Scalar SubQuery --
SELECT a.dt
, COUNT(*) cnt
, (SELECT COUNT(*) FROM t b WHERE b.dt <= a.dt) cnt2
FROM t a
GROUP BY a.dt
ORDER BY dt
;
-- Self Join 1 --
SELECT a.dt
, a.cnt
, COUNT(*) cnt2
FROM (SELECT dt, COUNT(*) cnt FROM t GROUP BY dt) a
, t b
WHERE a.dt >= b.dt
GROUP BY a.dt, a.cnt
ORDER BY dt
;
-- Self Join 2 --
SELECT a.dt
, a.cnt
, SUM(b.cnt) cnt2
FROM (SELECT dt, COUNT(*) cnt FROM t GROUP BY dt) a
, (SELECT dt, COUNT(*) cnt FROM t GROUP BY dt) b
WHERE a.dt >= b.dt
GROUP BY a.dt, a.cnt
ORDER BY dt
;
-- Model --
SELECT *
FROM (SELECT dt, COUNT(*) cnt FROM t GROUP BY dt ORDER BY dt)
MODEL
DIMENSION BY (ROWNUM rn)
MEASURES (dt, cnt, cnt cnt2)
RULES (cnt2[rn>1] = cnt2[cv()-1] + cnt[cv()])
;
by DB초보자입니다
[2017.10.30 17:45:09]
jkson 말씀드대로 윈도우함수가 편하고/성능상 이점있으며
윈도우함수를 못쓰게되었을대 어떻게 처리할수있는지 고민해보라고 내주셨던것같습니다!
여러가지 방법을 알려주신 마농님께도 감사드립니다!!!!!
) 버튼을 클릭하여 작성 하시면 됩니다.