데이터베이스 질문과 답변
안녕하세요. 쿼리 작성하는데 어려움이 있어서 도움을 요청합니다.
*계획테이블(tab1)
| 제품명(nm) | 계획량(pqty) |
| 가 | 100 |
| 나 | 100 |
| 다 | 100 |
| 라 | 100 |
* 판매테이블(tab2)
| 제품명(nm) | 판매일자(sdt) | 판매량(sqty) |
| 가 | 01월 01일 | 50 |
| 01월 10일 | 30 | |
| 01월 11일 | 10 | |
| 나 | 01월 05일 | 30 |
* 얻고 싶은 결과값
| 제품명 | 판매일 | 총판매량 | 계획수량 |
| 가 | 01월 10일 | 90 | 100 |
| 나 | 30 | 100 | |
| 다 | 0 | 100 | |
| 라 | 0 | 100 |
* 계획테이블 기준으로 모든 제품리스트가 조회가 되어야 하고요,
* 판매일자는 제품별 판매된 수량이 계획대비 70%이상이 되는 최초판매일자를 가져와야 합니다.
* 총판매량은 해당 제품의 현재까지 총 판매량을 가져오면 되고요..
sum(tab2.sqty) over(partition by tab2.nm order by tab2.sdt asc row between unbounded preceding and current row)
를 이용하여 일자별 판매수량을 구한 후
계획수량과 비율을 계산해서 70% 이상인 데이타를 where조건으로 거니 70%미만 판매된
제품데이타가 리스트에서 제외됩니다.
간단하게 할 수 있는 방법이 없을까요?? 도와주세요~~~~
by 아발란체
[2016.01.25 17:14:13]
WITH TAB1(제품명, 계획량) AS (
SELECT '가', 100 FROM DUAL
UNION ALL SELECT '나', 100 FROM DUAL
UNION ALL SELECT '다', 100 FROM DUAL
UNION ALL SELECT '라', 100 FROM DUAL
), TAB2(제품명, 판매일자, 판매량) AS (
SELECT '가', '20160101', 50 FROM DUAL
UNION ALL SELECT '가', '20160110', 30 FROM DUAL
UNION ALL SELECT '가', '20160111', 10 FROM DUAL
UNION ALL SELECT '나', '20160105', 30 FROM DUAL
)
SELECT
제품명,
MIN(CASE WHEN(판매량1 / 계획량 >= 0.7) THEN 판매일자 ELSE NULL END) AS 판매일자,
SUM(판매량2) AS 판매량,
MAX(계획량) AS 계획량
FROM (
SELECT
TAB1.제품명,
판매일자,
SUM(판매량) OVER(PARTITION BY TAB1.제품명 ORDER BY 판매일자) AS 판매량1,
판매량 AS 판매량2,
계획량
FROM
TAB1, TAB2
WHERE
TAB1.제품명 = TAB2.제품명(+)
)
GROUP BY
제품명
;
by 우리집아찌
[2016.01.25 17:17:02]
/* 답에만 맞추었습니다.*/
WITH TAB1 AS (
SELECT '가' nm , 100 pqty FROM DUAL UNION ALL
SELECT '나' , 100 FROM DUAL UNION ALL
SELECT '다' , 100 FROM DUAL UNION ALL
SELECT '라' , 100 FROM DUAL
) , TAB2 AS (
SELECT '가' nm, '0101' sdt ,50 sqty FROM DUAL UNION ALL
SELECT '가' , '0110' ,30 FROM DUAL UNION ALL
SELECT '가' , '0111' ,10 FROM DUAL UNION ALL
SELECT '나' , '0105' ,30 FROM DUAL
)
SELECT A.NM
, MIN(CASE WHEN ( A.PQTY * 0.7) <= (B.SUM_QTY) THEN (B.SDT)END) SDT
, NVL(MAX(SUM_QTY),0)
, A.PQTY
FROM TAB1 A
LEFT OUTER JOIN
( SELECT NM , SDT,SUM(SQTY) OVER(PARTITION BY NM ORDER BY SDT) SUM_QTY FROM TAB2 ) B
ON A.NM = B.NM
GROUP BY A.NM , A.PQTY
ORDER BY NM
by 잼보야
[2016.01.25 17:39:09]
아... 이렇게 하면 되는구나~~ 다들 머리가 너무 좋으시네요~
고민고민하다가 글을 올렸는데 이렇게 금방 해결을 하시다니 너무 감사하고 대단하십니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.