꿈꾸는 개발자, DBA 커뮤니티 구루비

(수정) 지금에 결과를 모델로 변경하면 ;;; 감이 잘 안오네요 ㅠㅠ( 모델모 오타입니다.)

by 러드 [2009.09.24 16:58:37]

WITH A AS
(
SELECT ’20090901’ DT , 10 CNT, ’1’ CD FROM DUAL UNION ALL
SELECT ’20090901’ DT , 30 CNT, ’2’ CD FROM DUAL UNION ALL
SELECT ’20090901’ DT , 10 CNT, ’1’ CD FROM DUAL UNION ALL
SELECT ’20090902’ DT , 10 CNT, ’1’ CD FROM DUAL
)
SELECT DT
, TT
FROM ( SELECT DT
, SUM( CASE WHEN CD = 2 THEN -CNT ELSE CNT END ) OVER( ORDER BY ROWNUM ) TT
, ROW_NUMBER() OVER( PARTITION BY DT ORDER BY ROWNUM DESC ) RN
FROM A
)
WHERE RN = 1

by 임형섭 [2009.09.24 19:04:25]

모델모가 먼가요?;;

by 러드 [2009.09.25 10:05:01]

ㅋㅋㅋ 오타네요;;; 모델인데;;;;

with t_table as (
select '20090831' plan_date, 'AAA' cust, '1111' item, 1 seq_no, 4875 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 2 seq_no, 4875 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 3 seq_no, 3250 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 4 seq_no, 3250 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 5 seq_no, 1800 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 6 seq_no, 9000 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 7 seq_no, 3600 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 8 seq_no, 1800 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 9 seq_no, 1800 qty from dual union
select '20090831' plan_date, 'AAA' cust, '1111' item, 10 seq_no, 4000 qty from dual
)
SELECT *
FROM
(
SELECT plan_date, cust, item, seq_no
, seq_no + no result_no, qty
FROM t_table
MODEL PARTITION BY (plan_date, cust, item)
DIMENSION BY (seq_no, 0 no)
MEASURES (qty)
RULES
( qty[FOR seq_no FROM 1 TO 5 INCREMENT 1, 1] = qty[cv()+1, 0]
, qty[FOR seq_no FROM 1 TO 5 INCREMENT 1, 2] = qty[cv()+2, 0] )
)
WHERE seq_no <= 5
ORDER BY plan_date, cust, item, seq_no, result_no

이런것 입니다.!!!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변