데이터베이스 질문과 답변
안녕하세요..
쿼리관련 질문 드립니다.
A 테이블
년월 품목 제품코드 판매수량
201101 전화기 C48590 15
201101 자전거 A48591 8
201102 전화기 C48590 10
201103 자전거 A48591 20
B 테이블
제품코드 입고수량
C48590 250
A48591 100
조회하고자하는 화면
(판매율/년월) 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
===============================================================================
전화기 6% 10%
자전거 8% 28%
이해가 되실지 모르겠네요..
다시 요약하자면 품목별로 현시점누적입고수량대비 월별 판매율을 조회하는 화면입니다.
월별 판매율은 해당 월의 판매율이 아니라 1월부터 누적되는 판매율을 구하는것입니다.
감사합니다..^^
by xo
[2011.04.05 19:33:20]
select 품목,
max(decode(월,'01',per)) "1월",
max(decode(월,'02',per)) "2월",
max(decode(월,'03',per)) "3월",
max(decode(월,'04',per)) "4월",
max(decode(월,'05',per)) "5월",
max(decode(월,'06',per)) "6월",
max(decode(월,'07',per)) "7월",
max(decode(월,'08',per)) "8월",
max(decode(월,'09',per)) "9월",
max(decode(월,'10',per)) "10월",
max(decode(월,'11',per)) "11월",
max(decode(월,'12',per)) "12월"
from (
select substr(A.년월,5,2) 월,
A.품목,
A.제품코드,
(sum(A.판매수량) over (partition by A.제품코드 order by A.년월) / B.입고수량) * 100 || '%' per
from A, B
where A.제품코드 = B.제품코드
and A.년월 like :year||'%'
)
group by 품목,제품코드
max(decode(월,'01',per)) "1월",
max(decode(월,'02',per)) "2월",
max(decode(월,'03',per)) "3월",
max(decode(월,'04',per)) "4월",
max(decode(월,'05',per)) "5월",
max(decode(월,'06',per)) "6월",
max(decode(월,'07',per)) "7월",
max(decode(월,'08',per)) "8월",
max(decode(월,'09',per)) "9월",
max(decode(월,'10',per)) "10월",
max(decode(월,'11',per)) "11월",
max(decode(월,'12',per)) "12월"
from (
select substr(A.년월,5,2) 월,
A.품목,
A.제품코드,
(sum(A.판매수량) over (partition by A.제품코드 order by A.년월) / B.입고수량) * 100 || '%' per
from A, B
where A.제품코드 = B.제품코드
and A.년월 like :year||'%'
)
group by 품목,제품코드
by 문두
[2011.04.05 20:32:46]
WITH A AS
(
SELECT '201101' AS YYMM, '전화기' AS ITEM, 'C48590' AS PCODE, 15 AS SQ FROM DUAL UNION ALL
SELECT '201101' AS YYMM, '자전거' AS ITEM, 'A48591' AS PCODE, 8 AS SQ FROM DUAL UNION ALL
SELECT '201102' AS YYMM, '전화기' AS ITEM, 'C48590' AS PCODE, 10 AS SQ FROM DUAL UNION ALL
SELECT '201103' AS YYMM, '자전거' AS ITEM, 'A48591' AS PCODE, 20 AS SQ FROM DUAL
)
,
B AS
(
SELECT 'C48590' AS PCODE, 250 AS QW FROM DUAL UNION ALL
SELECT 'A48591' AS PCODE, 100 AS QW FROM DUAL
)
SELECT A.ITEM
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '01' THEN A.PER END) AS M01
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '02' THEN A.PER END) AS M02
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '03' THEN A.PER END) AS M03
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '04' THEN A.PER END) AS M04
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '05' THEN A.PER END) AS M05
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '06' THEN A.PER END) AS M06
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '07' THEN A.PER END) AS M07
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '08' THEN A.PER END) AS M08
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '09' THEN A.PER END) AS M09
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '10' THEN A.PER END) AS M10
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '11' THEN A.PER END) AS M11
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '12' THEN A.PER END) AS M12
FROM (
SELECT YYMM
, ITEM
, ROUND(SUM(SQ) OVER (PARTITION BY ITEM ORDER BY YYMM RANGE UNBOUNDED PRECEDING) / B.QW * 100) AS PER
FROM A
, B
WHERE A.PCODE = B.PCODE
) A
GROUP BY ITEM
ORDER BY ITEM DESC
(
SELECT '201101' AS YYMM, '전화기' AS ITEM, 'C48590' AS PCODE, 15 AS SQ FROM DUAL UNION ALL
SELECT '201101' AS YYMM, '자전거' AS ITEM, 'A48591' AS PCODE, 8 AS SQ FROM DUAL UNION ALL
SELECT '201102' AS YYMM, '전화기' AS ITEM, 'C48590' AS PCODE, 10 AS SQ FROM DUAL UNION ALL
SELECT '201103' AS YYMM, '자전거' AS ITEM, 'A48591' AS PCODE, 20 AS SQ FROM DUAL
)
,
B AS
(
SELECT 'C48590' AS PCODE, 250 AS QW FROM DUAL UNION ALL
SELECT 'A48591' AS PCODE, 100 AS QW FROM DUAL
)
SELECT A.ITEM
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '01' THEN A.PER END) AS M01
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '02' THEN A.PER END) AS M02
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '03' THEN A.PER END) AS M03
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '04' THEN A.PER END) AS M04
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '05' THEN A.PER END) AS M05
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '06' THEN A.PER END) AS M06
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '07' THEN A.PER END) AS M07
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '08' THEN A.PER END) AS M08
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '09' THEN A.PER END) AS M09
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '10' THEN A.PER END) AS M10
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '11' THEN A.PER END) AS M11
, MAX(CASE WHEN SUBSTR(A.YYMM,5,2) = '12' THEN A.PER END) AS M12
FROM (
SELECT YYMM
, ITEM
, ROUND(SUM(SQ) OVER (PARTITION BY ITEM ORDER BY YYMM RANGE UNBOUNDED PRECEDING) / B.QW * 100) AS PER
FROM A
, B
WHERE A.PCODE = B.PCODE
) A
GROUP BY ITEM
ORDER BY ITEM DESC
by 미노~
[2011.04.06 09:33:19]
감사합니다...여러분 덕분에 고민했던부분 쉽게 처리했습니다. 좋은 하루들 되세요..^^
) 버튼을 클릭하여 작성 하시면 됩니다.