꿈꾸는 개발자, DBA 커뮤니티 구루비

LAG함수를 사용한 분당생산량 출력 질문

by 치비 [SQL Query] [2021.11.05 17:33:29]

반갑습니다. 사내 ERP를 개발하는 도중 궁금한게 있어서 질문드립니다.

시간대별 생산 시작과 마감을 저장하는 A테이블과

30분당 실적이 업데이트 되게끔 해서

30분 단위로 몇개가 생산됬는지 저장되는 B테이블이 있습니다.

두개의 테이블을 조인하여서

실적업데이트 시간을 lag 함수를 사용하여서

실적업데이트(전) / 실적업데이트시간(후)를 구한뒤에

시간차이를 분으로 표시하는건 성공했는데

1/2라인 전체가 밑으로 lag함수로 내려가서 계산이 되다 보니까

시간차가 재대로 출력이 안되네요....

조인해서 라인별/품목을 그룹화 하여

처음 시작되는 품목의 첫행 A테이블의 생산시작이 출력되게끔 할려면

쿼리식을 어떡해 짜야할지를 모르겠네요....

간단한 팁이라도 주시면 정말 감사드리겠습니다!!

A테이블

라인	날짜	품목	시작시간	마감시간
1	20211104	A품목	8:30	10:13
1	20211104	B품목	10:16	11:44
2	20211104	C품목	8:32	10:13

B테이블

품목	날짜	실적업데이트 시간	생산량
A품목	20211104	9:13:55	416
A품목	20211104	9:38:57	980
A품목	20211104	9:43:55	198
A품목	20211104	10:13:56	1168
B품목	20211104	10:43:57	1187
B품목	20211104	11:13:59	1170
C품목	20211104	8:43:54	234
C품목	20211104	9:01:21	763
C품목	20211104	9:13:55	517
C품목	20211104	9:38:57	1035
C품목	20211104	9:43:56	218
C품목	20211104	10:13:56	1270

결과물 (이렇게는 어떡해 짜야 할까요?)

라인	품목	날짜	전	후	시간차	생산량
1	A품목	20211104	8:30	9:13:00	0:43:00	416
1	A품목	20211104	9:13:00	9:38	0:25:00	980
1	A품목	20211104	9:38	9:43:00	0:05:00	198
1	A품목	20211104	9:43:00	9:13:00	0:30:00	1168
1	B품목	20211104	10:16	10:43:00	0:27:00	1187
1	B품목	20211104	10:43	11:13	0:30:00	1170
2	C품목	20211104	8:32	8:43	0:11:00	234
2	C품목	20211104	8:43	9:01	0:18:00	763
2	C품목	20211104	9:01	9:13	0:12:00	517
2	C품목	20211104	9:13	9:38	0:25:00	1035
2	C품목	20211104	9:38	9:43	0:05:00	218
2	C품목	20211104	9:43	10:13	0:30:00	1270

by 마농 [2021.11.08 08:25:41]

1. 시간이 문자로 들어가 있나요?
- 고정 자리수로 앞에 0 을 채워서 들어가면 좋을 텐데. 09:38:57, 08:30:00
- 가변 자리수로 들어가 있네요? 9:38:57, 8:30
2. 시간 계산은
- 초단위 버리고 분단위만 계산하나요?
3. 두 테이블의 조인키는 뭘까요?
- (품목) ?
- (품목, 날짜) ?
- (품목, 날짜, 시간) ?

by 치비 [2021.11.08 08:29:58]

댓글달아 주셔서 감사합니다.

1. 들어가는 입력란에는 고정자리수 앞에 0이 있습니다.

엑셀로 옮기다 보니까 형식상 0이 제거되어서 저렇게 표시된겁니다^^

2. 시간계산은 분단위로만 합니다.

by 마농 [2021.11.08 08:53:35]

WITH tab_a AS
(
SELECT 1 line, '20211104' dt, 'A품목' item, '08:30' stm, '10:13' etm FROM dual
UNION ALL SELECT 1, '20211104', 'B품목', '10:16', '11:44' FROM dual
UNION ALL SELECT 2, '20211104', 'C품목', '08:32', '10:13' FROM dual
)
, tab_b AS
(
SELECT 'A품목' item, '20211104' dt, '09:13:55' tm, 416 qty FROM dual
UNION ALL SELECT 'A품목', '20211104', '09:38:57',  980 FROM dual
UNION ALL SELECT 'A품목', '20211104', '09:43:55',  198 FROM dual
UNION ALL SELECT 'A품목', '20211104', '10:13:56', 1168 FROM dual
UNION ALL SELECT 'B품목', '20211104', '10:43:57', 1187 FROM dual
UNION ALL SELECT 'B품목', '20211104', '11:13:59', 1170 FROM dual
UNION ALL SELECT 'C품목', '20211104', '08:43:54',  234 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:01:21',  763 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:13:55',  517 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:38:57', 1035 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:43:56',  218 FROM dual
UNION ALL SELECT 'C품목', '20211104', '10:13:56', 1270 FROM dual
)
SELECT a.line
     , a.item
     , a.dt
     , LAG(SUBSTR(b.tm, 1, 5), 1, a.stm)
       OVER(PARTITION BY a.line, a.item ORDER BY b.tm) stm
     , SUBSTR(b.tm, 1, 5) etm
     , TO_CHAR(TO_DATE(ROUND(
       ( TO_DATE(SUBSTR(b.tm, 1, 5), 'hh24:mi')
       - TO_DATE( LAG(SUBSTR(b.tm, 1, 5), 1, a.stm)
                  OVER(PARTITION BY a.line, a.item ORDER BY b.tm)
                , 'hh24:mi')
       ) *24*60*60
       ), 'sssss'), 'hh24:mi') tm
     , b.qty
  FROM tab_a a
     , tab_b b
 WHERE a.item = b.item
   AND a.dt   = b.dt
   AND SUBSTR(b.tm, 1, 5) BETWEEN a.stm AND a.etm
;

by 치비 [2021.11.08 13:29:11]

감사합니다. partition 절에서 라인번호와 아이템을 구분짓는게 핵심이었네요!!

갓마농 사마!!

by 김성진 [2021.11.15 16:03:31]

마농님 답변을 CUBRID에서 해보니 에러나서 살짝 손댄 쿼리입니다.

WITH tab_a
AS (
SELECT 1 line, '20211104' dt, 'A품목' item, '08:30' stm, '10:13' etm FROM dual
UNION ALL SELECT 1, '20211104', 'B품목', '10:16', '11:44' FROM dual
UNION ALL SELECT 2, '20211104', 'C품목', '08:32', '10:13' FROM dual
	),
tab_b
AS (
SELECT 'A품목' item, '20211104' dt, '09:13:55' tm, 416 qty FROM dual
UNION ALL SELECT 'A품목', '20211104', '09:38:57',  980 FROM dual
UNION ALL SELECT 'A품목', '20211104', '09:43:55',  198 FROM dual
UNION ALL SELECT 'A품목', '20211104', '10:13:56', 1168 FROM dual
UNION ALL SELECT 'B품목', '20211104', '10:43:57', 1187 FROM dual
UNION ALL SELECT 'B품목', '20211104', '11:13:59', 1170 FROM dual
UNION ALL SELECT 'C품목', '20211104', '08:43:54',  234 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:01:21',  763 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:13:55',  517 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:38:57', 1035 FROM dual
UNION ALL SELECT 'C품목', '20211104', '09:43:56',  218 FROM dual
UNION ALL SELECT 'C품목', '20211104', '10:13:56', 1270 FROM dual
	)
SELECT a.line,
	a.item,
	a.dt,
	LAG(SUBSTR(b.tm, 1, 5), 1, a.stm) OVER (
		PARTITION BY a.line,
		a.item ORDER BY b.tm
		) stm,
	SUBSTR(b.tm, 1, 5) etm,
	TO_CHAR(timediff(TO_time(SUBSTR(b.tm, 1, 5), 'hh24:mi'),TO_time(LAG(SUBSTR(b.tm, 1, 5), 1, a.stm) OVER (
						PARTITION BY a.line,
						a.item ORDER BY b.tm
						), 'hh24:mi')), 'hh24:mi') tm,
	b.qty
FROM tab_a a,
	tab_b b
WHERE a.item = b.item
	AND a.dt = b.dt
	AND SUBSTR(b.tm, 1, 5) BETWEEN a.stm
		AND a.etm;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변