데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 6351 게시물 읽기

No. 6351

일자별 누계 어떻게 해야 하나요?
작성자	이중희(iforgod)	작성일	2012-03-27 11:54	조회수	8,787

안녕하십니까.

저는 회사에서 영업자료를 가공하는 업무를 하고 있습니다.

자료가 많아서 ACCESS를 쓰다가 MSSQL express 로 이전하는 중인데요.

조회시간을 줄이기위해 일자별 누계 데이터로 자료를 뽑을려고 합니다.

제가 초보라서 생각되로 쿼리가 안짜집니다.

도움을 요청드립니다.

누계로 해서 작성될 자료는 raw자료에서 일자, 제품코드, 매장코드, 매출수량 만을

뽑아서 누계자료에 있는 필드에 넣을려고 합니다.

한달 raw자료는 대략 3만~5만 record 입니다.

INSERT INTO sum_tbl ( saledate, mktcode, pdtcode, sumqty )
SELECT mv.saledate, mv.mktcode, mv.pdtcode, (select sum(movedata.qty) from movedata where
movedata.saledate between dateserial(2012,2,1) and mv.[saledate] and
movedata.mktcode = mv.[mktcode] and
movedata.pdtcode = mv.[pdtcode]
) AS sumqty
FROM movedata AS mv
WHERE mv.saledate between dateserial(2012,2,1) and dateserial(2012,2+1,0)
GROUP BY mv.saledate, mv.mktcode, mv.pdtcode;

sum_tbl : 누계자료가 저장될 테이블

movedata: raw자료가 있는 테이블

saledate : 거래일자

mktcode: 매장코드

pdtcode: 제품코드

qty: 매출수량

sumqty: 누계 매출수량

----------------------

테이블 구조

movedata: raw데이터가 저장된 테이블

saledate mktcode pdtcode qty 그밖에 필드등

일자 매장코드 제품코드 매출수량

-------------------------------------

sum_tbl : 누계자료가 저장될 테이블

saledate mktcode pdtcode sumqty

일자 매장코드 제품코드 누계매출수량

----------------------

제가 쿼리를 짜 봤는데 동작도 오래 걸리고 결과도 나오지 않습니다.

어떤 오류인지 확인 할 수 없어 전문가분들의 조언을 요청드립니다.

읽어주셔서 감사합니다.

이 글에 대한 댓글이 총 7건 있습니다.

-- 아직 SQL 2012를 사용하시지는 않으실 거라고 생각이 되어 다음과 같이 만들어 봤습니다.

--예제 테이블 생성

create table movedata(

saledate smalldatetime

, mktcode int

, pdtcode int

, qty int

);

create nonclustered index ix_md_sdMcPc ON movedata ( saledate, mktcode, pdtcode );

create table sum_tbl(

saledate smalldatetime

, mktcode int

, pdtcode int

, sumqty int

);

-- test data 부어 넣고

declare @sdt smalldatetime;

set @sdt = '2012-02-01';

while ( 0 <> DATEDIFF(day,'2012-02-29', @sdt) )

begin

insert into movedata ( saledate, mktcode , pdtcode , qty )

select @sdt, 1, 1, cast( RAND() * 10 AS int )

union all select @sdt, 1, 2, cast( RAND() * 10 AS int )

union all select @sdt, 2, 1, cast( RAND() * 10 AS int )

union all select @sdt, 2, 2, cast( RAND() * 10 AS int )

;

set @sdt = DATEADD(day, 1, @sdt)

;

end

-- check test data

select * from movedata

-- 성능 비교를 위한 원래 쿼리

SELECT mv.saledate, mv.mktcode, mv.pdtcode, (select sum(movedata.qty) from movedata where

movedata.saledate between '2012-02-01' and mv.[saledate] and

movedata.mktcode = mv.[mktcode] and

movedata.pdtcode = mv.[pdtcode]

) AS sumqty

FROM movedata AS mv

WHERE '2012-02-01' <= mv.saledate and mv.saledate < '2012-03-01'

GROUP BY mv.saledate, mv.mktcode, mv.pdtcode;

-- 해당 월의 마지막까지 subsum을 구하는 재귀CTE

declare @month1 smalldatetime; --계산할 월의 첫날

declare @nxtMonth1 smalldatetime; --계산할 월의 다음 달 첫날

set @month1 = '2012-02-01';

set @nxtMonth1 = DATEADD( month, 1, @month1 );

-- cte query

WITH rcur ( saledate, mktcode, pdtcode, qty, subSumQty )

(

SELECT saledate, mktcode, pdtcode, qty, qty AS subsum

FROM movedata

WHERE saledate = @month1

UNION ALL

SELECT nd.saledate, nd.mktcode, nd.pdtcode, nd.qty, nd.qty + rc.subSumQty

FROM movedata AS nd inner JOIN rcur AS rc ON DATEADD(day,1,rc.saledate) = nd.saledate

AND nd.mktcode = rc.mktcode

AND nd.pdtcode = rc.pdtcode

AND DATEADD(day,1,rc.saledate) < @nxtMonth1

)

-- 실제 insert하기 위해서는 아래의 주석을 풀면 insert가 됩니다.

-- insert into sum_tbl ( saledate , mktcode, pdtcode, sumqty )

select saledate, mktcode , pdtcode , subSumQty from rcur

우욱님이 2012-03-27 19:24에 작성한 댓글입니다. Edit Ｘ

답글 주셔서 감사합니다.

적어주신것이 제가 모르는 부분이 많아서 ^^;

아직 결과를 확인해보지 못했습니다.

적어주신 것을 보고 테스트하고 공부하겠습니다.

아무튼 성심껏 답변해주셔서 감사합니다.

이중희님이 2012-03-27 22:45에 작성한 댓글입니다. Edit Ｘ

다른 곳에 썼었던 글을 블로그에 복사하여 보여드립니다.

http://blog.naver.com/uook_/130134813052

도움이 되시길.. ㅋ

우욱님이 2012-03-28 01:30에 작성한 댓글입니다. Edit Ｘ

감사합니다 ^^

잘 읽겠습니다.

이중희(iforgod)님이 2012-03-28 11:16에 작성한 댓글입니다.

우욱님 위에 알려주신대로 했는데요.

결과가 2월1일~ 2월3일 까지만 생성이되는데요.

어디에 문제가 있는건지 모르겠습니다.

그런데 성능을 비교하기위한 "원래 쿼리"는 시간이 걸리기는 하는데요.

정상적으로 동작합니다.

확인 부탁드립니다.

감사합니다.

이중희(iforgod)님이 2012-03-29 00:57에 작성한 댓글입니다.
이 댓글은 2012-03-29 01:03에 마지막으로 수정되었습니다.

실제 data가 어떻게 생겼는지를 몰라서 임의로 sample data를 생성해서 돌려봤습니다.

위에 있는 코드처럼 data가 있는 경우에는 동일한 결과를 내는 것으로 확인했습니다만...

실제로 돌려보셨을때 동일하지 않게 나온다는 얘기는 sample data와는 data의 양상이 다르다고 생각이 됩니다.

우욱님이 2012-03-29 11:58에 작성한 댓글입니다. Edit Ｘ

우욱님 바쁘신데 감사드리고요

데이터의 일자는 1~29, 매장코드는 2000개정도, 제품코드는 300개정도 인데요.

이러다 보니 경우의 수가 29*2000*300=17,400,000 인데요.

혹시 재귀횟수가 너무 많아서 생기는 문제 아닐까요?

제가 샘플 데이터가 필요하시면 보내드릴 수 있는데요.

iforgod@nate.com 으로 메일을 알려주시면 샘플데이터를 보내드리겠습니다.

감사드립니다 ^^

이중희(iforgod)님이 2012-03-29 13:02에 작성한 댓글입니다.
이 댓글은 2012-03-29 14:15에 마지막으로 수정되었습니다.

[Top]

No.	제목	작성자	작성일	조회
6355	쿼리문을 짜야되는데요 좋은 방법있는지 부탁드려요 [3]	난해해	2012-04-03	7318
6354	sql2008에서 이미지 화일 db에 저장하는 방법 sql문부탁	김길동	2012-04-01	6513
6353	반각문자들로 구성된 데이터 사이에 전각문자가 존재할 경우 추출 [2]	김종문	2012-03-29	9923
6351	일자별 누계 어떻게 해야 하나요? [7]	이중희	2012-03-27	8787
6350	ldf 파일용량 관련질문입니다. [1]	고영민	2012-03-23	7280
6348	mssql2005에서 DB생성 문제(다른사람 DB가 보임) [1]	권혁진	2012-03-21	6945
6347	[MS SQL 2005] Server IP 변경 후 생성한 계정 Lock ?! [2]	초보	2012-03-20	7421