데이터 누계 질문드립니다.
일별 계산은 되는데... 월별, 분기별, 연도별 누계를 계산하려니 잘 안됩니다.
use tempdb
go
if object_id ('dbo.#t') is not null
drop table dbo.#t
go
print 'begin-------------------------'
--iDate: primary key
--iVal: 매일 매일 연속 누적됨
create table #t(idate smalldatetime, iVal int)
insert #t(idate,ival) values('2018-12-31',90)
insert #t(idate,ival) values('2019-01-01',100)
insert #t(idate,ival) values('2019-01-02',110)
insert #t(idate,ival) values('2019-01-03',120)
insert #t(idate,ival) values('2019-01-04',130)
insert #t(idate,ival) values('2019-01-05',140)
insert #t(idate,ival) values('2019-01-06',250)
insert #t(idate,ival) values('2019-02-10',400)
insert #t(idate,ival) values('2019-02-16',410)
go
print 'result-------------------------'
select x.idate, x.ival as to_day, y.ival as be_day, x.ival-y.ival as day_val
from
(select * , (select count(*) from #t as b where b.idate<a.idate) as cnt1
from #t as a) as x left join
(select * , (select count(*)+1 from #t as b where b.idate<a.idate) as cnt1
from #t as a) as y on x.cnt1=y.cnt1
where x.idate between '2019-01-01' and '2019-12-31'
-- 결과값: 계산 결과는 금일, 전일, 일별 값, (일별누계, 월별누계, 분기별 누계, 연도별 누계) 컬럼을 추가하고 싶습니다.
--idate to_day be_day day_val
----------------------- ----------- ----------- -----------
2019-01-01 00:00:00 100 90 10
2019-01-02 00:00:00 110 100 10
2019-01-03 00:00:00 120 110 10
2019-01-04 00:00:00 130 120 10
2019-01-05 00:00:00 140 130 10
2019-01-06 00:00:00 250 140 110
2019-02-10 00:00:00 400 250 150
2019-02-16 00:00:00 410 400 10
(8개 행이 영향을 받음)
go
print '누계 계산 Select 문 오류가 발생합니다'
select x.idate, x.ival as to_day, y.ival as be_day, x.ival-y.ival as day_total
,(select sum(ival)
from #t
where idate between '2019-01-01' and '2019-12-31'
and idate)<x.idate) as day_total
,(select sum(ival)
from #t
where idate between '2019-01-01' and '2019-12-31'
and datepart(m,idate)=datepart(m,x.idate)) as mon_total
,(select sum(ival)
from #t
where idate between '2019-01-01' and '2019-12-31'
and datepart(q,idate)=datepart(q,x.idate)) as qq_total
from
(select * , (select count(*) from #t as b where b.idate<a.idate) as cnt1
from #t as a) as x left join
(select * , (select count(*)+1 from #t as b where b.idate<a.idate) as cnt1
from #t as a) as y on x.cnt1=y.cnt1
where x.idate between '2019-01-01' and '2019-12-31'
go
조언을 바랍니다.
좋은 주말 되시구요~~
Comment 5
-
건우아빠
2019.01.12 18:00
-
swimtor
2019.01.13 00:52
답변에 감사드립니다. m-.-m결과값은 [월별누계] 컬럼이 추가 집계가 되면 좋겠습니다.idate to_day be_day day_val 월별누계----------------------- ----------- ----------- -----------2019-01-01 00:00:00 100 90 10 10 -- 1월 1일부터 누계계산2019-01-02 00:00:00 110 100 10 202019-01-03 00:00:00 120 110 10 302019-01-04 00:00:00 130 120 10 402019-01-05 00:00:00 140 130 10 502019-01-06 00:00:00 250 140 110 1602019-02-10 00:00:00 400 250 150 150 -- 여기는 2월이므로 다시 누계를 시작 하는 것입니다.2019-02-16 00:00:00 410 400 10 160(8개 행이 영향을 받음)몇 년 sql 손놓고 지냈더니 ... 그만....편안한 밤 되시기 바랍니다. -
건우아빠
2019.01.13 18:39
withres as(select a.idate , a.ival, sum(1) nofrom #t a left join #t b on a.idate >= b.idategroup by a.idate , a.ival),res1 as(select a.no, a.idate , a.ival to_day , b.ival be_day , a.ival - b.ival day_valfrom res a join res b on a.no = b.no + 1)select a.no, a.idate , a.to_day , a.be_day , a.day_val, sum(b.day_val) 누적치, sum( case when left(a.idate ,7) = left( b.idate,7) then b.day_val else 0 end ) [월별 누적], sum( case when datename(qq,a.idate ) = datename(qq, b.idate ) then b.day_val else 0 end ) [분기별 누적], sum( case when left(a.idate ,4) =left( b.idate,4) then b.day_val else 0 end ) [년 누적]from res1 a left join res1 b on a.no >= b.nogroup by a.no,a.idate , a.to_day , a.be_day , a.day_valorder by a.no -
swimtor
2019.01.13 22:53
2008 버전 설치를 해야겠습니다. ^^
답변 덕분에 많은 도움이 되었습니다.
고맙습니다.
Gooooooooo...oooood luck~
-
건우아빠
2019.01.14 18:21
2008보다는 최신버젼으로 하세요..
2000 쿼리라..
with res as
(
select a.idate , a.ival, sum(1) no
from #t a left join #t b on a.idate >= b.idate
group by a.idate , a.ival
), result as
(
select a.no, a.idate , a.ival to_day , b.ival be_day , a.ival - b.ival day_val
from res a join res b on a.no = b.no + 1
)
select a.no, a.idate , a.to_day , a.be_day , a.day_val , sum(b.day_val) 누적치
from result a left join result b on a.no >= b.no
group by a.no,a.idate , a.to_day , a.be_day , a.day_val
좀 간단하게 전일 닶을 가져 올거구요...
------------------------------------------------------------------
월별 누적, 분기별 누적, 년별 누적의 기준이 좀 애매
쿼리 보다는 데이타를 기준으로 표를 그리시는게 이해가 빠를듯 합니다.
월별 , 분기별, 년별 누적은 해당기준 max 일의 값이 누적치 아닌가요.
년의 시작을 초기화 한다면 전년 누적치를 빼시면 될거구요,,
2000에서 CTE가 안되면 with res 부분을 인라인뷰로 묶으시면 됩니다.