database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6351 게시물 읽기
No. 6351
일자별 누계 어떻게 해야 하나요?
작성자
이중희(iforgod)
작성일
2012-03-27 11:54
조회수
9,363

안녕하십니까.

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

자료가 많아서 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 )
AS
(
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
7897
6354sql2008에서 이미지 화일 db에 저장하는 방법 sql문부탁
김길동
2012-04-01
7032
6353반각문자들로 구성된 데이터 사이에 전각문자가 존재할 경우 추출 [2]
김종문
2012-03-29
10577
6351일자별 누계 어떻게 해야 하나요? [7]
이중희
2012-03-27
9363
6350ldf 파일용량 관련질문입니다. [1]
고영민
2012-03-23
7875
6348mssql2005에서 DB생성 문제(다른사람 DB가 보임) [1]
권혁진
2012-03-21
7553
6347[MS SQL 2005] Server IP 변경 후 생성한 계정 Lock ?! [2]
초보
2012-03-20
8003
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.019초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다