데이터베이스 질문과 답변

이전 질문 올린 -> 시간대별 MIN 값 데이터 가져오기 다른 유형의 질문 올립니다! 0 11 1,543

by 홍길덩이 [SQL Query] 오라클 [2019.03.26 15:46:35]

저번에 질문 답변해 주신 마농님 감사합니다.

이전 질문에서 좀더 내용 추가하여 요청 드립니다.

제가 정렬하고 싶은방법은  작업일자가 가장 빠른순 > 작업변경시간 빠른순 > 용량 많은순 (DESC) 이며,

추가로 이전 라인에 같은 라인으로 연결하여 만들고 싶은데, 

결과화면과 같이  1라인 (1번)-> 1라인(2번) -> 2라인(1번) -> 1라인 (3번) -> 2라인(2번)

이런식으로 그전 아이템과 아이템 변경시간 빠른순으로 가져와서 정렬 하고 싶습니다.

 

FROM_ITEM TO_ITEM 아이템변경시간
A B 5
A F 5
A C 5
A D 10
A E 10
A G 5
B A 10
B F 5
B C 5
B D 10
B E 5
B G 5
F A 5
F B 5
F C 5
F D 10
F E 20
F G 10
F H 5
E A 10
E B 10
E C 5
E D 10
E F 10
E G 5
C A 5
C B 5
C E 5
C D 5
C F 10
C G 10
G A 5
G B 10
G E 10
G D 5
G F 5
G C 10
D A 10
D B 5
D E 5
D G 10
D F 10
D C 10

-- 작업관리리스트

아이템 작업날짜 라인 용량
A 2019-03-23 0:00 2 18
E 2019-03-23 0:00 2 5
C 2019-03-24 0:00 1 15
F 2019-03-24 0:00 2 11
D 2019-03-24 0:00 1 10
G 2019-03-24 0:00 1 13
B 2019-03-24 0:00 1 6
H 2019-03-26 0:00 2 2

 

-- 결과화면

ITEM_CODE 작업날짜 변경아이템 변경시간 변경아이템 용량 라인
A 2019-03-23 0:00 E 10 5 2
E 2019-03-23 0:00 F 10 11 2
C 2019-03-24 0:00 D 5 10 1
D 2019-03-24 0:00 B 5 6 1
G 2019-03-24 0:00 　 　 　 1
F 2019-03-24 0:00 H 5 2 2
H 2019-03-26 0:00 　 　 　 2

 

 

WITH W_NAME AS
(
SELECT 'A' 아이템, TO_DATE('20190323', 'yyyymmdd') 납품일, 2 라인, 18 용량 FROM DUAL
UNION ALL SELECT 'E', TO_DATE('20190323', 'yyyymmdd'), 2 라인, 5 용량 FROM DUAL
UNION ALL SELECT 'C', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 15 용량 FROM DUAL
UNION ALL SELECT 'F', TO_DATE('20190324', 'yyyymmdd'), 2 라인, 11 용량 FROM DUAL
UNION ALL SELECT 'D', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 10 용량 FROM DUAL
UNION ALL SELECT 'G', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 13 용량 FROM DUAL
UNION ALL SELECT 'B', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 6 용량 FROM DUAL
UNION ALL SELECT 'H', TO_DATE('20190326', 'yyyymmdd'), 2 라인, 2 용량 FROM DUAL
)
, W_TIME AS
(
    SELECT  'A' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'E' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'E' AS TO_ITEM, 20 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'G' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'H' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'B' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'D' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'G' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'B' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'E' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'D' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'G' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL 
    )

 

by 마농 [2019.03.26 16:24:37] 
WITH w_name AS
(
SELECT 'A' item_code, TO_DATE('20190323', 'yyyymmdd') 작업날짜, 2 라인, 18 용량 FROM dual
UNION ALL SELECT 'E', TO_DATE('20190323', 'yyyymmdd'), 2 라인,  5 용량 FROM dual
UNION ALL SELECT 'C', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 15 용량 FROM dual
UNION ALL SELECT 'F', TO_DATE('20190324', 'yyyymmdd'), 2 라인, 11 용량 FROM dual
UNION ALL SELECT 'D', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 10 용량 FROM dual
UNION ALL SELECT 'G', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 13 용량 FROM dual
UNION ALL SELECT 'B', TO_DATE('20190324', 'yyyymmdd'), 1 라인,  6 용량 FROM dual
UNION ALL SELECT 'H', TO_DATE('20190326', 'yyyymmdd'), 2 라인,  2 용량 FROM dual
)
, w_time AS
(
SELECT 'A' from_item, 'B' to_item, 5 아이템변경시간 FROM dual
UNION ALL SELECT 'A', 'F',  5 FROM dual
UNION ALL SELECT 'A', 'C', 10 FROM dual
UNION ALL SELECT 'A', 'D', 10 FROM dual
UNION ALL SELECT 'A', 'E', 10 FROM dual
UNION ALL SELECT 'A', 'G',  5 FROM dual
UNION ALL SELECT 'B', 'A', 10 FROM dual
UNION ALL SELECT 'B', 'F',  5 FROM dual
UNION ALL SELECT 'B', 'C',  5 FROM dual
UNION ALL SELECT 'B', 'D', 10 FROM dual
UNION ALL SELECT 'B', 'E',  5 FROM dual
UNION ALL SELECT 'B', 'G',  5 FROM dual
UNION ALL SELECT 'F', 'A',  5 FROM dual
UNION ALL SELECT 'F', 'B',  5 FROM dual
UNION ALL SELECT 'F', 'C',  5 FROM dual
UNION ALL SELECT 'F', 'D', 10 FROM dual
UNION ALL SELECT 'F', 'E', 20 FROM dual
UNION ALL SELECT 'F', 'G', 10 FROM dual
UNION ALL SELECT 'F', 'H',  5 FROM dual
UNION ALL SELECT 'E', 'A', 10 FROM dual
UNION ALL SELECT 'E', 'B', 10 FROM dual
UNION ALL SELECT 'E', 'C',  5 FROM dual
UNION ALL SELECT 'E', 'D', 10 FROM dual
UNION ALL SELECT 'E', 'F', 10 FROM dual
UNION ALL SELECT 'E', 'G',  5 FROM dual
UNION ALL SELECT 'C', 'A',  5 FROM dual
UNION ALL SELECT 'C', 'B',  5 FROM dual
UNION ALL SELECT 'C', 'E',  5 FROM dual
UNION ALL SELECT 'C', 'D',  5 FROM dual
UNION ALL SELECT 'C', 'F', 10 FROM dual
UNION ALL SELECT 'C', 'G', 10 FROM dual
UNION ALL SELECT 'G', 'A',  5 FROM dual
UNION ALL SELECT 'G', 'B', 10 FROM dual
UNION ALL SELECT 'G', 'E', 10 FROM dual
UNION ALL SELECT 'G', 'D',  5 FROM dual
UNION ALL SELECT 'G', 'F',  5 FROM dual
UNION ALL SELECT 'G', 'C', 10 FROM dual
UNION ALL SELECT 'D', 'A', 10 FROM dual
UNION ALL SELECT 'D', 'B',  5 FROM dual
UNION ALL SELECT 'D', 'E',  5 FROM dual
UNION ALL SELECT 'D', 'G', 10 FROM dual
UNION ALL SELECT 'D', 'F', 10 FROM dual
UNION ALL SELECT 'D', 'C', 10 FROM dual
)
, w_temp(item_path, lv, item_code, 작업날짜, 용량, 라인, to_item) AS
(
SELECT CAST('-' || item_code AS VARCHAR2(99)) item_path
     , 1 lv
     , item_code
     , 작업날짜
     , 용량
     , 라인
     , (SELECT MIN(b.to_item) KEEP(DENSE_RANK FIRST ORDER BY c.작업날짜, b.아이템변경시간, c.용량 DESC, b.to_item)
          FROM w_time b
             , w_name c
         WHERE b.from_item = a.item_code
           AND c.item_code = b.to_item
           AND c.라인      = a.라인
        ) to_item
  FROM w_name a
 WHERE item_code IN (SELECT MIN(item_code) KEEP(DENSE_RANK FIRST ORDER BY 작업날짜, 용량 DESC, item_code)
                       FROM w_name
                      GROUP BY 라인
                     )
 UNION ALL
SELECT d.item_path || '-' || a.item_code item_path
     , d.lv + 1 lv
     , a.item_code
     , a.작업날짜
     , a.용량
     , a.라인
     , (SELECT MIN(b.to_item) KEEP(DENSE_RANK FIRST ORDER BY c.작업날짜, b.아이템변경시간, c.용량 DESC, b.to_item)
          FROM w_time b
             , w_name c
         WHERE b.from_item = a.item_code
           AND c.item_code = b.to_item
           AND c.라인      = a.라인
           AND INSTR(d.item_path||'-', '-'||b.to_item||'-') = 0
        ) to_item
  FROM w_name a
     , w_temp d
 WHERE a.item_code = d.to_item
)
SELECT a.item_code
     , a.작업날짜
     , a.to_item
     , b.아이템변경시간
     , LEAD(a.용량) OVER(PARTITION BY a.라인 ORDER BY a.lv) 변경아이템용량
     , a.라인
     , a.lv
  FROM w_temp a
     , w_time b
 WHERE a.item_code = b.from_item(+)
   AND a.to_item   = b.to_item(+)
;

 

by 홍길덩이 [2019.03.27 10:37:55]

혹시 동일한 아이템이 있을경우 같은 정렬조건에 추가 할 수 있을까요?

W_TIME에는 동일 아이템에 대한 조건이 없어서  -> 'A'  FROM_ITEM  'A' TO_ITEM  0 아이템 변경시간

W_NAME에 동일 아이템이 있는경우엔 아이템변경시간을 0으로 주고

작업일자가 가장 빠른순 > 동일아이템 > 작업변경시간 빠른순 > 용량 많은순 (DESC)

 

이렇게 하는게 해보는데 잘 안나옵니다 ㅠㅠ

 

-- 작업관리리스트

아이템 작업날짜 라인 용량
A 2019-03-23 0:00 2 18
E 2019-03-23 0:00 2 5
C 2019-03-24 0:00 1 15
F 2019-03-24 0:00 2 11
D 2019-03-24 0:00 1 10
G 2019-03-24 0:00 1 13
B 2019-03-24 0:00 1 6
H 2019-03-26 0:00 2 2

A        2019-03-24 0:00 2    10 

 

 

WITH w_name AS
(
SELECT 'A' item_code, TO_DATE('20190323', 'yyyymmdd') 작업날짜, 2 라인, 18 용량 FROM dual
UNION ALL SELECT 'E', TO_DATE('20190323', 'yyyymmdd'), 2 라인,  5 용량 FROM dual
UNION ALL SELECT 'C', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 15 용량 FROM dual
UNION ALL SELECT 'F', TO_DATE('20190324', 'yyyymmdd'), 2 라인, 11 용량 FROM dual
UNION ALL SELECT 'D', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 10 용량 FROM dual
UNION ALL SELECT 'G', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 13 용량 FROM dual
UNION ALL SELECT 'B', TO_DATE('20190324', 'yyyymmdd'), 1 라인,  6 용량 FROM dual
UNION ALL SELECT 'H', TO_DATE('20190326', 'yyyymmdd'), 2 라인,  2 용량 FROM dual
UNION ALL SELECT 'A', TO_DATE('20190326', 'yyyymmdd'), 2 라인,  10 용량 FROM dual
)

 

by 마농 [2019.03.27 10:47:50]

설명이 어렵네요.
알이듣기 쉬운 설명과 함께 원본 대비 결과표 예시 주세요.
진행 단계별 상세 설명 있으면 좋을 듯 하구요.
지금 item_code 를 키로 생각해서 쿼리를 작성해 드렸는데.
이게 중복된다면? 좀더 상세 기준에 대한 설명이 필요합니다.
각 테이블의 실질적인 역할과 쿼리의 의미를 알았으면 좋겠네요.
데이터만 보고 질문만 보고 쿼리를 작성해 드리긴 했지만.
왜 이런 쿼리를 만드는지 이해가 안가네요.

by 홍길덩이 [2019.03.27 11:06:15]

 

결과화면에 동일한 날짜의 동일한 아이템일경우 우선적으로 순서를 더 높게 주고

W_NAME에 동일아이템은 W_TIME에 데이터가 없기 때문에 아이템변경시간은 0으로 하고

정렬 순서는 

작업일자 가장 빠른순 > 아이템변경시간 빠른순(동일아이템은 아이템 변경시간 0) > 용량 많은순 (DESC)

으로 하고 싶습니다.

 

--W_TIME

FROM_ITEM TO_ITEM 아이템변경시간
A B 5
A F 5
A C 5
A D 10
A E 10
A G 5
B A 10
B F 5
B C 5
B D 10
B E 5
B G 5
F A 5
F B 5
F C 5
F D 10
F E 20
F G 10
F H 5
E A 10
E B 10
E C 5
E D 10
E F 10
E G 5
C A 5
C B 5
C E 5
C D 5
C F 10
C G 10
G A 5
G B 10
G E 10
G D 5
G F 5
G C 10
D A 10
D B 5
D E 5
D G 10
D F 10
D C 10

--W_NAME

아이템 작업날짜 라인 용량
A 2019-03-23 0:00 2 18
E 2019-03-23 0:00 2 10
A 2019-03-23 0:00 2 5
C 2019-03-24 0:00 1 15
F 2019-03-24 0:00 2 11
D 2019-03-24 0:00 1 10
G 2019-03-24 0:00 1 13
B 2019-03-24 0:00 1 6
H 2019-03-26 0:00 2 2

 

--결과화면

ITEM_CODE 작업날짜 변경아이템 변경시간 변경아이템 용량 라인
A 2019-03-23 0:00 A 0 5 2
A 2019-03-23 0:00 E 10 10 2
E 2019-03-23 0:00 F 10 11 2
C 2019-03-24 0:00 D 5 10 1
D 2019-03-24 0:00 B 5 6 1
G 2019-03-24 0:00 　 　 　 1
F 2019-03-24 0:00 H 5 2 2
H 2019-03-26 0:00 　 　 　 2
by 마농 [2019.03.27 11:21:37]

1. 결과에 B -> G 는 왜 없죠?
2. 같은 아이템은 항상 직접 연결되나요? A->A->E
  - A->E->A 로 되는 경우는 없겠죠?

by 홍길덩이 [2019.03.27 12:05:28]

1. 결과에 B->G 는 빼먹었습니다.

ITEM_CODE

 작업날짜 변경아이템 변경시간 변경아이템 용량 라인
A 2019-03-23 0:00 A 0 5 2
A 2019-03-23 0:00 E 10 10 2
E 2019-03-23 0:00 F 10 11 2
C 2019-03-24 0:00 D 5 10 1
D 2019-03-24 0:00 B 5 6 1
B 2019-03-24 0:00 G 5 13 1
G 2019-03-24 0:00 　 　 　 1
F 2019-03-24 0:00 H 5 2 2
H 2019-03-26 0:00 　 　 　

2

 

2. 같은 아이템인경우 작업날짜가 같지 않은 경우 A-E-A 갈 수 있지만

   A 3/23

   E 3/23

   A 3/24

   같은 아이템의 같은 작업날짜인경우 A->A->E 로 이어집니다.

   A 3/23

   A 3/23

   E 3/24

by 마농 [2019.03.27 14:08:25]

w_time 엔 A -> A 자료가 없다는 건가요?
뭔가 이런 예외가 있으면 힘든데요?
데이터가 정규화가 되어 있어야 하지 않을까요?

by 홍길덩이 [2019.03.27 14:22:18]

저도 그렇게 생각하지만, 저희쪽 데이터가 아니라 마음대로 손대기가 어렵습니다....

by 마농 [2019.03.27 14:32:48]

테이블에 대한 설명 부탁드려요.
업무 로직에 대한 설명도 부탁드려요.
데이터만 보고는 이해가 안가고 이해가 안가는 상황에서의 쿼리 작성이 어렵네요.

by 마농 [2019.03.27 17:19:35]

중복 아이템은 2개가 최대인가요? 3개 이상도 가능한가요?

by 마농 [2019.03.28 10:07:29] 
WITH w_name AS
(
SELECT 'A' item_code, TO_DATE('20190323', 'yyyymmdd') 작업날짜, 2 라인, 18 용량 FROM dual
UNION ALL SELECT 'E', TO_DATE('20190323', 'yyyymmdd'), 2 라인, 10 용량 FROM dual
UNION ALL SELECT 'C', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 15 용량 FROM dual
UNION ALL SELECT 'F', TO_DATE('20190324', 'yyyymmdd'), 2 라인, 11 용량 FROM dual
UNION ALL SELECT 'D', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 10 용량 FROM dual
UNION ALL SELECT 'G', TO_DATE('20190324', 'yyyymmdd'), 1 라인, 13 용량 FROM dual
UNION ALL SELECT 'B', TO_DATE('20190324', 'yyyymmdd'), 1 라인,  6 용량 FROM dual
UNION ALL SELECT 'H', TO_DATE('20190326', 'yyyymmdd'), 2 라인,  2 용량 FROM dual
UNION ALL SELECT 'A', TO_DATE('20190323', 'yyyymmdd'), 2 라인,  5 용량 FROM dual
)
, w_time AS
(
SELECT 'A' from_item, 'B' to_item, 5 아이템변경시간 FROM dual
UNION ALL SELECT 'A', 'F',  5 FROM dual
UNION ALL SELECT 'A', 'C', 10 FROM dual
UNION ALL SELECT 'A', 'D', 10 FROM dual
UNION ALL SELECT 'A', 'E', 10 FROM dual
UNION ALL SELECT 'A', 'G',  5 FROM dual
UNION ALL SELECT 'B', 'A', 10 FROM dual
UNION ALL SELECT 'B', 'F',  5 FROM dual
UNION ALL SELECT 'B', 'C',  5 FROM dual
UNION ALL SELECT 'B', 'D', 10 FROM dual
UNION ALL SELECT 'B', 'E',  5 FROM dual
UNION ALL SELECT 'B', 'G',  5 FROM dual
UNION ALL SELECT 'F', 'A',  5 FROM dual
UNION ALL SELECT 'F', 'B',  5 FROM dual
UNION ALL SELECT 'F', 'C',  5 FROM dual
UNION ALL SELECT 'F', 'D', 10 FROM dual
UNION ALL SELECT 'F', 'E', 20 FROM dual
UNION ALL SELECT 'F', 'G', 10 FROM dual
UNION ALL SELECT 'F', 'H',  5 FROM dual
UNION ALL SELECT 'E', 'A', 10 FROM dual
UNION ALL SELECT 'E', 'B', 10 FROM dual
UNION ALL SELECT 'E', 'C',  5 FROM dual
UNION ALL SELECT 'E', 'D', 10 FROM dual
UNION ALL SELECT 'E', 'F', 10 FROM dual
UNION ALL SELECT 'E', 'G',  5 FROM dual
UNION ALL SELECT 'C', 'A',  5 FROM dual
UNION ALL SELECT 'C', 'B',  5 FROM dual
UNION ALL SELECT 'C', 'E',  5 FROM dual
UNION ALL SELECT 'C', 'D',  5 FROM dual
UNION ALL SELECT 'C', 'F', 10 FROM dual
UNION ALL SELECT 'C', 'G', 10 FROM dual
UNION ALL SELECT 'G', 'A',  5 FROM dual
UNION ALL SELECT 'G', 'B', 10 FROM dual
UNION ALL SELECT 'G', 'E', 10 FROM dual
UNION ALL SELECT 'G', 'D',  5 FROM dual
UNION ALL SELECT 'G', 'F',  5 FROM dual
UNION ALL SELECT 'G', 'C', 10 FROM dual
UNION ALL SELECT 'D', 'A', 10 FROM dual
UNION ALL SELECT 'D', 'B',  5 FROM dual
UNION ALL SELECT 'D', 'E',  5 FROM dual
UNION ALL SELECT 'D', 'G', 10 FROM dual
UNION ALL SELECT 'D', 'F', 10 FROM dual
UNION ALL SELECT 'D', 'C', 10 FROM dual
)
, w_name1 AS
(
SELECT item_code
     , ROW_NUMBER() OVER(PARTITION BY 라인 ORDER BY 작업날짜, 용량 DESC, item_code) rn1
     , ROW_NUMBER() OVER(PARTITION BY item_code ORDER BY 작업날짜, 용량 DESC) rn
     , 작업날짜, 라인, 용량
  FROM w_name
)
, w_time1 AS
(
SELECT b.item_code from_item
     , b.rn from_rn
     , c.라인
     , c.item_code to_item
     , c.rn to_rn
     , c.작업날짜
     , 0 아이템변경시간
     , c.용량
  FROM w_name1 b
     , w_name1 c
 WHERE b.item_code = c.item_code
   AND b.rn + 1 = c.rn
 UNION ALL
SELECT a.from_item
     , b.rn from_rn
     , c.라인
     , a.to_item
     , c.rn to_rn
     , c.작업날짜
     , a.아이템변경시간
     , c.용량
  FROM w_time a
     , w_name1 b
     , w_name1 c
 WHERE a.from_item = b.item_code
   AND a.to_item   = c.item_code
)
, w_temp1(item_path, lv, item_code, rn, 작업날짜, 용량, 라인, to_item_rn) AS
(
SELECT CAST('-'|| item_code ||'_'|| rn AS VARCHAR2(99)) item_path
     , 1 lv
     , item_code
     , rn
     , 작업날짜
     , 용량
     , 라인
     , (SELECT MIN(b.to_item ||'_'|| b.to_rn)
               KEEP(DENSE_RANK FIRST ORDER BY c.작업날짜, b.아이템변경시간, c.용량 DESC, b.to_item)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.item_code
           AND b.from_rn   = a.rn
           AND b.to_item   = c.item_code
           AND b.to_rn     = c.rn
           AND c.라인      = a.라인
        ) to_item_rn
  FROM w_name1 a
 WHERE rn1 = 1
 UNION ALL
SELECT d.item_path ||'-'|| a.item_code ||'_'|| a.rn item_path
     , d.lv + 1 lv
     , a.item_code
     , a.rn
     , a.작업날짜
     , a.용량
     , a.라인
     , (SELECT MIN(b.to_item ||'_'|| b.to_rn)
               KEEP(DENSE_RANK FIRST ORDER BY c.작업날짜, b.아이템변경시간, c.용량 DESC, b.to_item)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.item_code
           AND b.from_rn   = a.rn
           AND b.to_item   = c.item_code
           AND b.to_rn     = c.rn
           AND c.라인      = a.라인
           AND INSTR(d.item_path||'-', '-'||b.to_item||'_'||c.rn||'-') = 0
        ) to_item_rn
  FROM w_name1 a
     , w_temp1 d
 WHERE a.라인 = d.라인
   AND a.item_code||'_'||a.rn = d.to_item_rn
   AND INSTR(d.item_path||'-', '-'||a.item_code||'_'||a.rn||'-') = 0
)
SELECT a.item_code
     , a.rn
     , a.작업날짜
     , REGEXP_SUBSTR(a.to_item_rn, '[^_]+', 1, 1) to_item
     , REGEXP_SUBSTR(a.to_item_rn, '[^_]+', 1, 2) to_rn
     , b.아이템변경시간
     , a.용량
     , LEAD(a.용량) OVER(PARTITION BY a.라인 ORDER BY a.lv) 변경아이템용량
     , a.라인
     , a.lv
  FROM w_temp1 a
     , w_time1 b
 WHERE a.item_code  = b.from_item(+)
   AND a.rn         = b.from_rn(+)
   AND a.to_item_rn = b.to_item(+)||'_'||b.to_rn(+)
 ORDER BY a.item_path
;

 

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