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 6440 게시물 읽기
No. 6440
쿼리 질문드립니다..
작성자
초보입니다.(fightinggood)
작성일
2012-11-09 13:52
조회수
8,231

 안녕하세요..

쿼리 를 수정하는데.. 도움을 받고자 합니다..

잘 사용하던 쿼리인데요.. 어느 순간부터 느려져서 시간을 너무 많이 잡아 먹네요..ㅠㅠ

사용중인 쿼리는

SELECT  SUM(STCK_INFO.AMT1) AS TOT1,

.......

SUM(STCK_INFO.AMT12) AS TOT12 

from(

CASE STCK.BASI_DT WHEN @DATE1 THEN 

(ISNULL(SUM(STCK.CYOVQ),0)+ISNULL(SUM(STCK.EWHQ),0)-ISNULL(SUM(STCK.XWHQ),0))*(OGPRC1.OG_PRC) ELSE 0 END AMT1,

....

(ISNULL(SUM(STCK.CYOVQ),0)+ISNULL(SUM(STCK.EWHQ),0)-ISNULL(SUM(STCK.XWHQ),0))*(OGPRC12.OG_PRC) ELSE 0 END AMT12

FROM TBL_DD_STCK_INFO STCK

JOIN (

 

SELECT ISNULL(OG_PRC,0) AS OG_PRC,  A.GDS_CD 

FROM TBL_GDS_OGPRC_HSTY A 

JOIN (

SELECT MAX(SEQ) AS 'SEQ', GDS_CD  

FROM TBL_GDS_OGPRC_HSTY 

WHERE CONVERT(NVARCHAR(8) , BGN_DT, 112) <= '20120131'  

GROUP BY GDS_CD

        ) B 

ON A.GDS_CD = B.GDS_CD  AND A.SEQ = B.SEQ 

       ) OGPRC1

where .......

이런 형식입니다.. 보시는 바와 같이.. 1월 부터 12월까지 금액을 구하는데요..

하단 부분에.. 각해당하는 월에 대한 값을 가져와서 계산을 합니다.. 

JOIN (

 

SELECT  ISNULL(OG_PRC,0) AS OG_PRC,  A.GDS_CD 

FROM  TBL_GDS_OGPRC_HSTY A 

JOIN (

SELECT  MAX(SEQ) AS 'SEQ', GDS_CD  

FROM  TBL_GDS_OGPRC_HSTY 

WHERE  CONVERT(NVARCHAR(8) , BGN_DT, 112) <= '20120131'   // 여기가 일자.

GROUP BY GDS_CD

         ) B 

이부분이 12개 가되죠...

1월부터 12월까지.. 좀더 효율적으로 할수는 없나요.??

이것저럭 해보는데.. 잘 안되고 있습니다..

고수님들 도움 부탁 드리겠습니다.
이 글에 대한 댓글이 총 5건 있습니다.

 WITH tt ( dt, gdcd, prc)

AS
(
SELECT '2012-10-01 01:00:00', 1, 1 UNION ALL
SELECT '2012-10-11 02:00:00', 1, 2 UNION ALL
SELECT '2012-10-21 03:00:00', 1, 3 UNION ALL
SELECT '2012-11-01 04:00:00', 1, 4 UNION ALL
SELECT '2012-11-11 05:00:00', 1, 5 UNION ALL
SELECT '2012-11-21 06:00:00', 1, 6 UNION ALL
SELECT '2012-10-01 01:01:00', 2, 11 UNION ALL
SELECT '2012-10-11 01:02:00', 2, 12 UNION ALL
SELECT '2012-10-21 01:03:00', 2, 13 UNION ALL
SELECT '2012-11-01 01:04:00', 2, 14 UNION ALL
SELECT '2012-11-16 01:05:00', 2, 15 UNION ALL
SELECT '2012-11-26 01:06:00', 2, 16 
)
SELECT *
FROM
(
SELECT tt.gdcd, CONVERT( VARCHAR(7), tt.dt, 112 ) AS yyyymm, tt.prc
FROM (
SELECT gdcd, MAX( dt ) AS maxdt
FROM tt
GROUP BY gdcd, YEAR(dt), MONTH( dt )
) AS gd__maxdt
INNER JOIN tt ON gd__maxdt.gdcd = tt.gdcd AND gd__maxdt.maxdt = tt.dt
) AS porg
PIVOT ( SUM( prc ) FOR yyyymm IN ( [2012-10], [2012-11] ) ) AS pvt
;
 
12번 월별로 찾지 않고 한번에 찾은 후에 pivoting하여 사용하시면 좋을 듯 싶습니다.
+ CONVERT(NVARCHAR(8) , BGN_DT, 112) <= '20120131'  형식으로 하시면 index 못 타는건 아시죠? ㅋ
우욱님이 2012-11-09 16:00에 작성한 댓글입니다. Edit Ｘ

 우욱님 감사합니다..

죄송한데.. 제가 이해를 잘 못하겠습니다..

DB 를 거의 안했는데.. 기본적인 것만했죠..

자세한 설명 부탁드려도 될까요.??

미리 감사드립니다..

수고하세요

초보입니다.님이 2012-11-09 16:36에 작성한 댓글입니다. Edit Ｘ

 WITH부터 SELECT 앞까지는 sample data를 위한 부분이니 pass하구요,

 
 
안쪽부터 살펴보면
 
SELECT gdcd, MAX( dt ) AS maxdt
FROM tt
GROUP BY gdcd, YEAR(dt), MONTH( dt )
상품코드별, 년별, 월별 로 그룹핑하여 상품코드와 해당 년월의 마지막 값을 구합니다.
 
다음의 코드를 실행해보시면
WITH tt ( dt, gdcd, prc)
AS
(
SELECT '2012-10-01 01:00:00', 1, 1 UNION ALL
SELECT '2012-10-11 02:00:00', 1, 2 UNION ALL
SELECT '2012-10-21 03:00:00', 1, 3 UNION ALL
SELECT '2012-11-01 04:00:00', 1, 4 UNION ALL
SELECT '2012-11-11 05:00:00', 1, 5 UNION ALL
SELECT '2012-11-21 06:00:00', 1, 6 UNION ALL
SELECT '2012-10-01 01:01:00', 2, 11 UNION ALL
SELECT '2012-10-11 01:02:00', 2, 12 UNION ALL
SELECT '2012-10-21 01:03:00', 2, 13 UNION ALL
SELECT '2012-11-01 01:04:00', 2, 14 UNION ALL
SELECT '2012-11-16 01:05:00', 2, 15 UNION ALL
SELECT '2012-11-26 01:06:00', 2, 16 
)
 
SELECT gdcd, MAX( dt ) AS maxdt
 
FROM tt
GROUP BY gdcd, YEAR(dt), MONTH( dt )
쉽게 보실 수 있겠습니다. datetime 값에 아무런 가공을 하지 않았음을 눈여겨 보세요.
 
 
 
 
그 다음은
SELECT tt.gdcd, CONVERT( VARCHAR(7), tt.dt, 112 ) AS yyyymm, tt.prc
FROM (
여기는 앞서 본 상품별 마지막가격변경일자가 있는 테이블입니다.
) AS gd__maxdt
INNER JOIN tt ON gd__maxdt.gdcd = tt.gdcd AND gd__maxdt.maxdt = tt.dt
 
그렇게 구한 테이블에서 history 테이블로 상품코드와 날짜가 일치하는 조건으로 join을 걸어서 각 상품별, 월별 마지막 변경(내지는 기록)의 가격을 구해오면서 pivoting을 위해 날짜는 yyyy-mm 형태로 변환을 미리 해둡니다.
 
역시 다음의 코드를 실행해보시면 이 단계에서 어떤 테이블을 얻었는지 아실 수 있습니다.
WITH tt ( dt, gdcd, prc)
AS
(
SELECT '2012-10-01 01:00:00', 1, 1 UNION ALL
SELECT '2012-10-11 02:00:00', 1, 2 UNION ALL
SELECT '2012-10-21 03:00:00', 1, 3 UNION ALL
SELECT '2012-11-01 04:00:00', 1, 4 UNION ALL
SELECT '2012-11-11 05:00:00', 1, 5 UNION ALL
SELECT '2012-11-21 06:00:00', 1, 6 UNION ALL
SELECT '2012-10-01 01:01:00', 2, 11 UNION ALL
SELECT '2012-10-11 01:02:00', 2, 12 UNION ALL
SELECT '2012-10-21 01:03:00', 2, 13 UNION ALL
SELECT '2012-11-01 01:04:00', 2, 14 UNION ALL
SELECT '2012-11-16 01:05:00', 2, 15 UNION ALL
SELECT '2012-11-26 01:06:00', 2, 16 
)
SELECT tt.gdcd, CONVERT( VARCHAR(7), tt.dt, 112 ) AS yyyymm, tt.prc
FROM (
SELECT gdcd, MAX( dt ) AS maxdt
FROM tt
GROUP BY gdcd, YEAR(dt), MONTH( dt )
) AS gd__maxdt
INNER JOIN tt ON gd__maxdt.gdcd = tt.gdcd AND gd__maxdt.maxdt = tt.dt
 
 
 
이제 마지막으로
SELECT *
FROM
(
앞 단계에서 구한 상품별 월별 가격이 쭉 세로로 쌓여 있는 테이블입니다.
) AS porg
PIVOT ( SUM( prc ) FOR yyyymm IN ( [2012-10], [2012-11] ) ) AS pvt
 
구하고자 하는 값은 다 구했지만 그 형태가 세로로 늘어져 있어서 바깥쪽에서 가로로 늘어트린 형태의 보고서를 출력하기에 어려운 부분이 있으니 이를 pivoting을 통해 월별로 가로로 늘어트립니다.
pivot에 대한 내용은 구글 검색 결과를 몇 개 보시면 아실 수 있으리라 생각합니다.
그래서 최종적으로 아래의 코드를 실행시켜보시면 세로는 상품별 가로는 월별로 되어 있으면서 마지막 날짜의 상품가격이 들어가 있는 테이블을 구하실 수 있습니다.
 
WITH tt ( dt, gdcd, prc)
AS
(
SELECT '2012-10-01 01:00:00', 1, 1 UNION ALL
SELECT '2012-10-11 02:00:00', 1, 2 UNION ALL
SELECT '2012-10-21 03:00:00', 1, 3 UNION ALL
SELECT '2012-11-01 04:00:00', 1, 4 UNION ALL
SELECT '2012-11-11 05:00:00', 1, 5 UNION ALL
SELECT '2012-11-21 06:00:00', 1, 6 UNION ALL
SELECT '2012-10-01 01:01:00', 2, 11 UNION ALL
SELECT '2012-10-11 01:02:00', 2, 12 UNION ALL
SELECT '2012-10-21 01:03:00', 2, 13 UNION ALL
SELECT '2012-11-01 01:04:00', 2, 14 UNION ALL
SELECT '2012-11-16 01:05:00', 2, 15 UNION ALL
SELECT '2012-11-26 01:06:00', 2, 16 
)
SELECT *
FROM
(
SELECT tt.gdcd, CONVERT( VARCHAR(7), tt.dt, 112 ) AS yyyymm, tt.prc
FROM (
SELECT gdcd, MAX( dt ) AS maxdt
FROM tt
GROUP BY gdcd, YEAR(dt), MONTH( dt )
) AS gd__maxdt
INNER JOIN tt ON gd__maxdt.gdcd = tt.gdcd AND gd__maxdt.maxdt = tt.dt
) AS porg
PIVOT ( SUM( prc ) FOR yyyymm IN ( [2012-10], [2012-11] ) ) AS pvt
;
 
 
 
그리고 테이블의 성격상 (상품코드, 기록일자)의 clustered index가 있으면 어느 정도 속도는 잡아낼 수 있지 않을까 싶습니다.
우욱님이 2012-11-09 16:58에 작성한 댓글입니다. Edit Ｘ

 상세한 설명감사드립니다..

그런데 쿼리를 수정하여 작업을 하는데요..

PIVOT 이부분에서 에러가 납니다..

2000 을 사용중입니다. 2000에서는 저 함수를 지원하지 않는것 같습니다..

수고하세요..

초보에요님이 2012-11-12 09:22에 작성한 댓글입니다. Edit Ｘ

PIVOT이 없는 2000에서는 CASE SUM 떡칠로다가.. ㅋㅋ

우욱님이 2012-11-12 16:00에 작성한 댓글입니다. Edit Ｘ
[Top]
No.
제목
작성자
작성일
조회
6446오프셋 항목 구하기? [2]
우세연
2012-11-20
7404
6443연관성이 없는 테이블 2개를 합쳐서 결과를 보여주고 싶습니다. [4]
초보
2012-11-12
7821
6441MS-SQL 쿼리 질문입니다. [1]
초보자
2012-11-09
7089
6440쿼리 질문드립니다.. [5]
초보입니다.
2012-11-09
8231
6439not in 과 not exists 질문입니다. [2]
재훈
2012-11-08
7461
6438DB반복문이 궁금합니다. [1]
정철민
2012-11-07
7591
6437오라클 프로시저의 RETURN값을 받는방법 [1]
김봉달
2012-11-06
7875
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다