데이터베이스 질문과 답변
아직 model에 대해서 잘 이해가 아되서 한번 변경시켜 볼까 햇는데 잘 안되네요 ;;;
WITH T AS (
SELECT '2007' AS YEAR, 1000 AS AMT FROM DUAL UNION ALL
SELECT '2007' AS YEAR, 1100 AS AMT FROM DUAL UNION ALL
SELECT '2007' AS YEAR, 1200 AS AMT FROM DUAL UNION ALL
SELECT '2008' AS YEAR, 1000 AS AMT FROM DUAL UNION ALL
SELECT '2008' AS YEAR, 1200 AS AMT FROM DUAL UNION ALL
SELECT '2008' AS YEAR, 1400 AS AMT FROM DUAL UNION ALL
SELECT '2008' AS YEAR, 1600 AS AMT FROM DUAL UNION ALL
SELECT '2009' AS YEAR, 2000 AS AMT FROM DUAL UNION ALL
SELECT '2009' AS YEAR, 2200 AS AMT FROM DUAL UNION ALL
SELECT '2009' AS YEAR, 2400 AS AMT FROM DUAL UNION ALL
SELECT '2009' AS YEAR, 2600 AS AMT FROM DUAL UNION ALL
SELECT '2009' AS YEAR, 2500 AS AMT FROM DUAL
)
SELECT YEAR, tt, a, b, no
FROM ( select year, amt
, row_number() over( partition by year order by rownum ) as rn
from t
)
MODEL PARTITION BY ( year )
DIMENSION BY ( RN tt )
MEASURES( amt a, rn b, 0 no )
RULES(
no[any] = CASE WHEN max(a)[cv(tt)] = b[cv()] then sum(a)[cv()] else 0 end
)
order by 1,2
가장 큰값을 만나면 sum() 하면 될 느낌인데;;; 잘 안되네요 ㅠㅠ
조언좀 부탁드리겠습니다.
YEAR AMT RESULT
2007 1000
2007 1100
2007 1200 3300
2008 1000
2008 1200
2008 1400
2008 1600 5200
2009 2000
2009 2200
2009 2400
2009 2600
2009 2600 11800
(
SELECT '2007' year, 1000 amt FROM dual
UNION ALL SELECT '2007', 1100 FROM dual
UNION ALL SELECT '2007', 1200 FROM dual
UNION ALL SELECT '2008', 1000 FROM dual
UNION ALL SELECT '2008', 1200 FROM dual
UNION ALL SELECT '2008', 1400 FROM dual
UNION ALL SELECT '2008', 1600 FROM dual
UNION ALL SELECT '2009', 2000 FROM dual
UNION ALL SELECT '2009', 2200 FROM dual
UNION ALL SELECT '2009', 2400 FROM dual
UNION ALL SELECT '2009', 2600 FROM dual
UNION ALL SELECT '2009', 2500 FROM dual
)
, t1 AS
(
SELECT year, amt
, ROW_NUMBER() OVER(PARTITION BY year ORDER BY ROWNUM DESC) rn
FROM t
ORDER BY year, rn DESC
)
SELECT year, amt, result
FROM t1
MODEL PARTITION BY (year)
DIMENSION BY (rn)
MEASURES (amt, CAST(null AS NUMBER) result)
RULES(result[1] = SUM(amt)[ANY])
;
(
SELECT '2007' year, 1000 amt FROM dual
UNION ALL SELECT '2007', 1100 FROM dual
UNION ALL SELECT '2007', 1200 FROM dual
UNION ALL SELECT '2008', 1000 FROM dual
UNION ALL SELECT '2008', 1200 FROM dual
UNION ALL SELECT '2008', 1400 FROM dual
UNION ALL SELECT '2008', 1600 FROM dual
UNION ALL SELECT '2009', 2000 FROM dual
UNION ALL SELECT '2009', 2200 FROM dual
UNION ALL SELECT '2009', 2400 FROM dual
UNION ALL SELECT '2009', 2600 FROM dual
UNION ALL SELECT '2009', 2500 FROM dual
)
SELECT year, amt, result
FROM t
MODEL PARTITION BY (year)
DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY year ORDER BY ROWNUM DESC) rn)
MEASURES (amt, CAST(null AS NUMBER) result)
RULES(result[1] = SUM(amt)[ANY])
ORDER BY year, rn DESC
;
그런데 여기서 보시면 result[1]은
파티션 바이에 rn에 값을 말하는 것입니까???
그쪽 위치에 값으로 들어가던데 ㅋㅋ
그냥 0으로 하니까 값에 0이 들어가던데 이 부분을 넣은 의미가??
솔직히 결과물은 그다지 중요한것이 아니라서 결과물을 잘 못 봤습니다.;;;
어떻게 해야 나오는지에 정신이 팔려서;;;;
그 옵션이 무슨 의미가 있는지만 생각하다 보니까 물어본것입니다.
cast( null as number ) 이건 cast 라는 함수 안에 number이지만 초기값은 null이다
라는 의미인건가요???
null을 number형으로 바꾸라는 의미입니다.
to_number(null) 과 같습니다.
잘 배웠습니다.^^
수고하십시오.
) 버튼을 클릭하여 작성 하시면 됩니다.