pivot 관련한 쿼리 질문 0 6 956

by 잭키올 [SQL Query] [2017.06.20 23:15:09]


000.png (7,392Bytes)

안녕하세요. 

피봇과 관련되어 질문을 드립니다.

CO_SO 정보는 Order 주문입니다.
CO_LINE_HOL 정보는 휴무 정보입니다.
 - HOL_TYP 값 무시
 - START_DT_TIME, END_DT_TIME 이 휴무 시작일과 종료일
CO_LINE_CAPA 는 라인별 캐파 정보 입니다.
CO_CALD_MST 는 캘린더 정보 입니다.

아래와 같은 결과를 얻고 싶습니다.

-- 데이터
WITH CO_SO AS
(
SELECT 'ITEM_0001' AS ITEM_CD,	'24000' AS SO_QTY,	1 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0002' AS ITEM_CD,	'26000' AS SO_QTY,	2 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0003' AS ITEM_CD,	'25000' AS SO_QTY,	3 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0010' AS ITEM_CD,	'15000' AS SO_QTY,	4 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0011' AS ITEM_CD,	'40000' AS SO_QTY,	5 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0012' AS ITEM_CD,	'25000' AS SO_QTY,	6 AS PRIORITY FROM DUAL
)
, CO_LINE_HOL AS 
(
SELECT 'ALL', 'REGULAR', '2017-07-24 8:00', '2017-07-31 8:00', '휴가' FROM DUAL UNION ALL
SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM DUAL UNION ALL
SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM DUAL 
)
, CO_LINE_CAPA AS
(
SELECT 'L1', 'ITEM_0001', '3000 ' FROM DUAL UNION ALL
SELECT 'L1', 'ITEM_0002', '4000 ' FROM DUAL UNION ALL
SELECT 'L1', 'ITEM_0003', '5000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0010', '3000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0011', '4000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0012', '5000 ' FROM DUAL 
) 
, CO_CALD_MAT AS 
(
SELECT '20170717', '201729' FROM DUAL UNION ALL
SELECT '20170718', '201729' FROM DUAL UNION ALL
SELECT '20170719', '201729' FROM DUAL UNION ALL
SELECT '20170720', '201729' FROM DUAL UNION ALL
SELECT '20170721', '201729' FROM DUAL UNION ALL
SELECT '20170722', '201729' FROM DUAL UNION ALL
SELECT '20170723', '201729' FROM DUAL UNION ALL
SELECT '20170724', '201730' FROM DUAL UNION ALL
SELECT '20170725', '201730' FROM DUAL UNION ALL
SELECT '20170726', '201730' FROM DUAL UNION ALL
SELECT '20170727', '201730' FROM DUAL UNION ALL
SELECT '20170728', '201730' FROM DUAL UNION ALL
SELECT '20170729', '201730' FROM DUAL UNION ALL
SELECT '20170730', '201730' FROM DUAL UNION ALL
SELECT '20170731', '201731A' FROM DUAL UNION ALL
SELECT '20170801', '201731B' FROM DUAL UNION ALL
SELECT '20170802', '201731B' FROM DUAL UNION ALL
SELECT '20170803', '201731B' FROM DUAL UNION ALL
SELECT '20170804', '201731B' FROM DUAL UNION ALL
SELECT '20170805', '201731B' FROM DUAL UNION ALL
SELECT '20170806', '201731B' FROM DUAL UNION ALL
SELECT '20170807', '201732' FROM DUAL UNION ALL
SELECT '20170808', '201732' FROM DUAL UNION ALL
SELECT '20170809', '201732' FROM DUAL UNION ALL
SELECT '20170810', '201732' FROM DUAL UNION ALL
SELECT '20170811', '201732' FROM DUAL UNION ALL
SELECT '20170812', '201732' FROM DUAL UNION ALL
SELECT '20170813', '201732' FROM DUAL
)


주차를 PIVOT 으로 펼치는 기능이 있어야 할 것 같은데..
어렵네요.

감사합니다.

by 마농 [2017.06.21 08:50:26]

피벗하기 전 일자별 결과물을 보여주세요.
어떤 형태로 수량 배분이 되는지?
특히 ITEM_0002 의 경우 4000 씩 쪼개다 보면 CAPA 4000 중에 절반인 2000 이 남게 되는데.
다음 ITEM_0003 처리시 동일일자로 나머지 절반을 사용해야 하는지? 5000 의 절반 2500.
아니면 남은 capa 무시하고 일자를 바꾸어 새로 시작해야 하는지? 5000.
아이템 10,11,12 는 보여주신 결과가 맞는지? 순서가 이상한 듯 하구요.
지난번 질문에서는 라인도 2개 아이템도 2개 있었고 2*2 조합의 line-kapa 을 모두 가지고 있었는데.
이번 질문에는 아이템 하나당 하나의 라인만 지정되어 있네요? 이게 맞는건가요? 다른 조합도 가능한지?


by 잭키올 [2017.06.21 09:32:28]

마농님 안녕하세요.

저번 질문에 오류가 있어 다시 정리하여 질문 드립니다.

* 수정사항
 - SO 의 수량과 우선순위에 오차가 있었습니다.
 - PIVOT 을 돌리기 전 데이터를 첨부하였습니다.
 
* 물량 배분 룰
1. CAPA 가 남아도 다음 ITEM 처리 시 동일일자로 나머지 일자를 사용하지 않는다.
   남은 capa 무시하고 일자 바꾸어 새로 시작한다.
2. 하나의 item 은 하나의 line 에서만 생산 가능하다. (다른 조합 불가능)

 
-- 데이터
WITH CO_SO AS
(
SELECT 'ITEM_0001' AS ITEM_CD,  '24000' AS SO_QTY,  1 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0002' AS ITEM_CD,  '26000' AS SO_QTY,  2 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0003' AS ITEM_CD,  '25000' AS SO_QTY,  3 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0010' AS ITEM_CD,  '15000' AS SO_QTY,  6 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0011' AS ITEM_CD,  '40000' AS SO_QTY,  4 AS PRIORITY FROM DUAL UNION ALL
SELECT 'ITEM_0012' AS ITEM_CD,  '20000' AS SO_QTY,  5 AS PRIORITY FROM DUAL
)
, CO_LINE_HOL AS
(
SELECT 'ALL', 'REGULAR', '2017-07-24 8:00', '2017-07-31 8:00', '휴가' FROM DUAL UNION ALL
SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM DUAL UNION ALL
SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM DUAL 
)
, CO_LINE_CAPA AS
(
SELECT 'L1', 'ITEM_0001', '3000 ' FROM DUAL UNION ALL
SELECT 'L1', 'ITEM_0002', '4000 ' FROM DUAL UNION ALL
SELECT 'L1', 'ITEM_0003', '5000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0010', '3000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0011', '4000 ' FROM DUAL UNION ALL
SELECT 'L2', 'ITEM_0012', '5000 ' FROM DUAL 
) 
, CO_CALD_MAT AS
(
SELECT '20170717', '201729' FROM DUAL UNION ALL
SELECT '20170718', '201729' FROM DUAL UNION ALL
SELECT '20170719', '201729' FROM DUAL UNION ALL
SELECT '20170720', '201729' FROM DUAL UNION ALL
SELECT '20170721', '201729' FROM DUAL UNION ALL
SELECT '20170722', '201729' FROM DUAL UNION ALL
SELECT '20170723', '201729' FROM DUAL UNION ALL
SELECT '20170724', '201730' FROM DUAL UNION ALL
SELECT '20170725', '201730' FROM DUAL UNION ALL
SELECT '20170726', '201730' FROM DUAL UNION ALL
SELECT '20170727', '201730' FROM DUAL UNION ALL
SELECT '20170728', '201730' FROM DUAL UNION ALL
SELECT '20170729', '201730' FROM DUAL UNION ALL
SELECT '20170730', '201730' FROM DUAL UNION ALL
SELECT '20170731', '201731A' FROM DUAL UNION ALL
SELECT '20170801', '201731B' FROM DUAL UNION ALL
SELECT '20170802', '201731B' FROM DUAL UNION ALL
SELECT '20170803', '201731B' FROM DUAL UNION ALL
SELECT '20170804', '201731B' FROM DUAL UNION ALL
SELECT '20170805', '201731B' FROM DUAL UNION ALL
SELECT '20170806', '201731B' FROM DUAL UNION ALL
SELECT '20170807', '201732' FROM DUAL UNION ALL
SELECT '20170808', '201732' FROM DUAL UNION ALL
SELECT '20170809', '201732' FROM DUAL UNION ALL
SELECT '20170810', '201732' FROM DUAL UNION ALL
SELECT '20170811', '201732' FROM DUAL UNION ALL
SELECT '20170812', '201732' FROM DUAL UNION ALL
SELECT '20170813', '201732' FROM DUAL
)
 
<피봇을 돌리기 전 데이터>    
LINE_CD ITEM_CD CALD_DT CAPA_QTY
L1 ITEM_0001 20170717 3000
L1 ITEM_0001 20170718 0
L1 ITEM_0001 20170719 3000
L1 ITEM_0001 20170720 3000
L1 ITEM_0001 20170721 3000
L1 ITEM_0001 20170722 3000
L1 ITEM_0001 20170723 3000
L1 ITEM_0001 20170731 3000
L1 ITEM_0001 20170801 3000
L1 ITEM_0002 20170802 4000
L1 ITEM_0002 20170803 4000
L1 ITEM_0002 20170804 4000
L1 ITEM_0002 20170805 4000
L1 ITEM_0002 20170806 4000
L1 ITEM_0002 20170807 4000
L1 ITEM_0002 20170808 2000
L1 ITEM_0003 20170809 5000
L1 ITEM_0003 20170810 5000
L1 ITEM_0003 20170811 5000
L1 ITEM_0003 20170812 5000
L1 ITEM_0003 20170813 5000
L2 ITEM_0011 20170717 4000
L2 ITEM_0011 20170718 4000
L2 ITEM_0011 20170719 4000
L2 ITEM_0011 20170720 4000
L2 ITEM_0011 20170721 4000
L2 ITEM_0011 20170722 4000
L2 ITEM_0011 20170723 4000
L2 ITEM_0011 20170731 4000
L2 ITEM_0011 20170801 4000
L2 ITEM_0011 20170802 0
L2 ITEM_0011 20170803 0
L2 ITEM_0011 20170804 4000
L2 ITEM_0012 20170805 5000
L2 ITEM_0012 20170806 5000
L2 ITEM_0012 20170807 5000
L2 ITEM_0012 20170808 5000
L2 ITEM_0010 20170809 3000
L2 ITEM_0010 20170810 3000
L2 ITEM_0010 20170811 3000
L2 ITEM_0010 20170812 3000
L2 ITEM_0010 20170813 3000

by 마농 [2017.06.21 10:39:27]
WITH co_so AS
(
SELECT 'ITEM_0001' item_cd, 24000 so_qty, 1 priority FROM dual
UNION ALL SELECT 'ITEM_0002', 26000, 2 FROM dual
UNION ALL SELECT 'ITEM_0003', 25000, 3 FROM dual
UNION ALL SELECT 'ITEM_0010', 15000, 6 FROM dual
UNION ALL SELECT 'ITEM_0011', 40000, 4 FROM dual
UNION ALL SELECT 'ITEM_0012', 20000, 5 FROM dual
)
, co_line_hol AS
(
SELECT 'ALL' line_cd, 'REGULAR' hol_typ, '2017-07-24 8:00' start_dt_time, '2017-07-31 8:00' end_dt_time, '휴가' hol_txt FROM dual
UNION ALL SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM dual
UNION ALL SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM dual
)
, co_line_capa AS
(
SELECT 'L1' line_cd, 'ITEM_0001' item_cd, 3000 capa_qty FROM dual
UNION ALL SELECT 'L1', 'ITEM_0002', 4000 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003', 5000 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0010', 3000 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0011', 4000 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0012', 5000 FROM dual
)
, co_cald_mat AS
(
SELECT '20170717' dt, '201729' yw  FROM dual
UNION ALL SELECT '20170718', '201729'  FROM dual
UNION ALL SELECT '20170719', '201729'  FROM dual
UNION ALL SELECT '20170720', '201729'  FROM dual
UNION ALL SELECT '20170721', '201729'  FROM dual
UNION ALL SELECT '20170722', '201729'  FROM dual
UNION ALL SELECT '20170723', '201729'  FROM dual
UNION ALL SELECT '20170724', '201730'  FROM dual
UNION ALL SELECT '20170725', '201730'  FROM dual
UNION ALL SELECT '20170726', '201730'  FROM dual
UNION ALL SELECT '20170727', '201730'  FROM dual
UNION ALL SELECT '20170728', '201730'  FROM dual
UNION ALL SELECT '20170729', '201730'  FROM dual
UNION ALL SELECT '20170730', '201730'  FROM dual
UNION ALL SELECT '20170731', '201731A' FROM dual
UNION ALL SELECT '20170801', '201731B' FROM dual
UNION ALL SELECT '20170802', '201731B' FROM dual
UNION ALL SELECT '20170803', '201731B' FROM dual
UNION ALL SELECT '20170804', '201731B' FROM dual
UNION ALL SELECT '20170805', '201731B' FROM dual
UNION ALL SELECT '20170806', '201731B' FROM dual
UNION ALL SELECT '20170807', '201732'  FROM dual
UNION ALL SELECT '20170808', '201732'  FROM dual
UNION ALL SELECT '20170809', '201732'  FROM dual
UNION ALL SELECT '20170810', '201732'  FROM dual
UNION ALL SELECT '20170811', '201732'  FROM dual
UNION ALL SELECT '20170812', '201732'  FROM dual
UNION ALL SELECT '20170813', '201732'  FROM dual
)
SELECT line_cd
     , item_cd
     , SUM(DECODE(yw, '201729' , qty)) "201729"
     , SUM(DECODE(yw, '201730' , qty)) "201730"
     , SUM(DECODE(yw, '201731A', qty)) "201731A"
     , SUM(DECODE(yw, '201731B', qty)) "201731B"
     , SUM(DECODE(yw, '201732' , qty)) "201732"
     , SUM(DECODE(yw, '201733' , qty)) "201733"
  FROM (SELECT a.line_cd
             , a.item_cd
             , b.dt
             , b.yw
             , LEAST(a.capa_qty, a.so_qty - (b.lv - a.x) * a.capa_qty) qty
          FROM (SELECT b.line_cd
                     , a.priority
                     , a.item_cd
                     , a.so_qty
                     , b.capa_qty
                     , SUM(CEIL(a.so_qty / b.capa_qty)) OVER(PARTITION BY b.line_cd ORDER BY a.priority)
                         - CEIL(a.so_qty / b.capa_qty) + 1 x
                     , SUM(CEIL(a.so_qty / b.capa_qty)) OVER(PARTITION BY b.line_cd ORDER BY a.priority) y
                  FROM co_so a
                     , co_line_capa b
                 WHERE a.item_cd = b.item_cd
                ) a
             , (SELECT a.line_cd, b.dt, b.yw
                     , COUNT(*) OVER(PARTITION BY a.line_cd ORDER BY b.dt) lv
                  FROM (SELECT DISTINCT line_cd FROM co_line_capa) a
                 CROSS JOIN co_cald_mat b
                  LEFT OUTER JOIN co_line_hol c
                    ON b.dt >= REPLACE(SUBSTR(c.start_dt_time, 1, 10), '-')
                   AND b.dt <  REPLACE(SUBSTR(c.end_dt_time  , 1, 10), '-')
                   AND a.line_cd = DECODE(c.line_cd, 'ALL', a.line_cd, c.line_cd)
                 WHERE b.dt >= '20170717'
                   AND c.line_cd IS NULL
                ) b
         WHERE a.line_cd = b.line_cd
           AND b.lv BETWEEN a.x AND a.y
        )
 GROUP BY line_cd, item_cd
 ORDER BY line_cd, item_cd
;

 


by 잭키올 [2017.06.21 12:42:53]

마농님 쿼리 확인했습니다.
x 와 y 의 필드가 하는 역할이 무엇인지 알 수 있을까요?

그리고 SUM OVER PARTITION BY 를 사용하여 구하는 이유가 무엇인지 궁금해요..

어떤식으로 진행하는지는 대충 이해를 했는데요.

어떤식으로 로직을 전개하여 'LINE 별로 SO_QTY / CAPA_QTY 하여 SUM 해야겠다'

이런 로직을 세울 수 있는지 궁금합니다.


by 마농 [2017.06.21 13:09:28]

lv 는 첫날부터 휴일을 제외한 날짜에 번호를 붙인거구요.
lv BETWEEN x AND y 는 몇일부터 몇일까지 인지를 나타냅니다.
item 1번은 1~8, 2번은 9~15, 3번은 16~20 을 나타냅니다.
CEIL(a.so_qty / b.capa_qty) 로 아이템별 소요일자를 구하고
분석함수를 통해 소요일자를 누적합산하면서 x 와 y 를 구합니다.
잘 모르실 때는 부분부분 나누어 실행하면서 결과 확인하세요.


by 잭키올 [2017.06.21 14:34:06]

마농님 정말 감사드립니다.
보내주신 쿼리 이해하였습니다.
응용하여 사용하겠습니다.
감사합니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입