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

am 7시 ~ am 6시까지의 정보를 30분 단위로 묶기

by JOY [SQL Query] #SQL #초보 [2023.11.01 09:10:25]

안녕하세요.

시간/ 이름 / 상태 / 수량 해당 컬럼으로

2023-10-23 18:40:00.000 / JOY / 업무중 / 150

2023-10-23 18:50:00.000 / JOY / 업무완료 / 100

2023-10-23 15:40:00.000 / 아무개 / 다른업무 / 20

쭉 10분 단위로 나눠져 나열된 표를 30분 단위로 묶어서 수량의 합계를 구하고 싶습니다.

시간은 아침 7시부터 시작해서 다음날 새벽 6시까지이며,

여러개의 이름과 상태가 있습니다... 30분 단위로 묶어서 합계를 보고 싶습니다.

추가로 몇년간의 전체 날짜/시간이 들어간 데이터 중에서 원하는 날짜를 시간 상관 없이 23/11/1일의 정보만 보고 싶어! 를 어떻게 하는지 모르겠습니다..

따로 배우지 않고 구글링 해서 처음 시작한 SQL 이라 컴퓨터 언어 자체가 너무 낯섭니다..

아무리 봐도 그렇게 구분하는 방법을 모르겠어서 글 올립니다

도움 부탁드립니다..

by 마농 [2023.11.01 09:20:03]

DB 종류와 날짜 컬럼의 데이터타입을 알려주세요.

by JOY [2023.11.01 09:29:06]

안녕하세요 마농님!

DB종류 - MySQL 인걸로 보여지나 정확하게 모르겠습니다...

날짜 - timestamp

GROUP BY HOUR(datetime),FLOOR(MINUTE(datetime)/30*10 - line 13:69: mismatched input '<EOF>'. Expecting: ')', ',', 'ORDER' 이런 에러가 나옵니다ㅠㅠ

by 마농 [2023.11.01 10:13:39]

괄호 하나 누락되었네요. 곱하기 숫자도 틀렸네요.
- 오류 : GROUP BY HOUR(datetime), FLOOR(MINUTE(datetime)/30*10
- 수정 : GROUP BY HOUR(datetime), FLOOR(MINUTE(datetime)/30)*30

by 마농 [2023.11.01 10:23:23]

WITH t AS
(
SELECT '2023-10-23 06:50:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 07:00:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 18:40:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 18:50:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 19:00:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 19:10:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 19:20:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 19:30:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-23 19:40:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-24 06:40:00.000' dt, 'a' nm, 1 cnt
UNION ALL SELECT '2023-10-24 07:40:00.000' dt, 'a' nm, 1 cnt
)
SELECT nm
     , DATE_FORMAT(dt, '%Y-%m-%d') ymd
     , LPAD(HOUR(dt), 2, '0') hh24
     , LPAD(FLOOR(MINUTE(dt) / 30) * 30, 2, '0') mi
     , SUM(cnt) cnt
  FROM t
 WHERE dt >= '2023-10-23' + INTERVAL 7 HOUR
   AND dt <  '2023-10-23' + INTERVAL 7 HOUR + INTERVAL 1 DAY
 GROUP BY nm
     , DATE_FORMAT(dt, '%Y-%m-%d')
     , LPAD(HOUR(dt), 2, '0')
     , LPAD(FLOOR(MINUTE(dt) / 30) * 30, 2, '0')
;

by JOY [2023.11.01 10:23:59]

그럼 select DATETIME, code, workstatus, sum(qty) 이렇게 select 을 지정했는데 기존 그룹은 [group by 1, 2, 3] 이였습니다.

그럼 [GROUP BY HOUR(wip.snapshotdatetime), FLOOR(MINUTE(wip.snapshotdatetime)/30)*10, 2, 3] 으로 써야 되는걸까요? 이렇게 쓰면

=> line 1:8: 'datetime' must be an aggregate expression or appear in GROUP BY clause 이런식으로 에러가 납니다ㅠㅠ

by JOY [2023.11.01 14:33:42]

WITH WIP AS datetime
(
SELECT '2023-10-23 06:50:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 07:00:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 18:40:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 18:50:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 19:00:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 19:10:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 19:20:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 19:30:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-23 19:40:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-24 06:40:00.000' datetime, 'a' id, 1 qty
UNION ALL SELECT '2023-10-24 07:40:00.000' datetime, 'a' id, 1 qty
)
SELECT id
     , DATE_FORMAT(datetime, '%Y-%m-%d') ymd
     , HOUR(datetime) hh24
     , FLOOR(MINUTE(datetime) / 30) * 30 mi
     , SUM(qty) qty
     
  FROM bb.WIP
  
 WHERE datetime >= '2023-10-23' + INTERVAL 7 HOUR
   AND datetime <  '2023-10-23' + INTERVAL 7 HOUR + INTERVAL 1 DAY
   
 GROUP BY id
     , DATE_FORMAT(datetime, '%Y-%m-%d')
     , HOUR(datetime)
     , FLOOR(MINUTE(datetime) / 30) * 30

이런식으로 변경해서 입력하는게 맞을까요..? line 1:13: mismatched input 'datetime'. Expecting: '(' 이렇게 에러가 납니다...

제가 기존에 썼던 쿼리문은 아래 내용입니다.

select wip.datetime AS DATETIME
, ct.code
, wip.status
, sum(wip.qty)

from dd.WIP
join (select id, code, isfresh from os.id) ct ON ct.id = wip.id

where 1=1
AND ct.code in ('1센터', '2센터', '3센터', '4센터', '5센터', '6센터')
AND wip.datetime BETWEEN TIMESTAMP '2023-10-23 07:00:00' AND TIMESTAMP '2023-10-24 06:59:59'

GROUP BY HOUR(wip.datetime), FLOOR(MINUTE(wip.datetime )/30)*10

line 1:8: 'wip.datetime' must be an aggregate expression or appear in GROUP BY clause

이렇게 에러가 납니다...

by 마농 [2023.11.01 15:24:43]

1. WITH 구문 첫출 오타 : AS 뒤에 datetime 불필요
2. WITH 구문은 제가 테스트 용도로 사용한 것입니다.
- with 구문 불필요. WITH 빼고 실테이블에 쿼리 적용하세요.
3. 집계 그룹핑 기준이 명확해야 합니다.
- ID 기준인지? 코드 기준인지? 상태 기준인지? 복합 기준인지?
- 위와 같이 시간대로 group by 하면서 code 나, datetime, status 등 집계기준이 아닌 항목을 select 하면 에러납니다.

by 마농 [2023.11.01 15:36:11]

-- 코드별 시간대별 집계쿼리 예시
SELECT ct.code
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d') ymd
     , LPAD(HOUR(wip.datetime)                   , 2, '0') hh24
     , LPAD(FLOOR(MINUTE(wip.datetime) / 30) * 30, 2, '0') mi
     , SUM(wip.qty) qty
  FROM dd.wip
  JOIN os.id ct
    ON ct.id = wip.id
 WHERE 1=1
   AND ct.code IN ('1센터', '2센터', '3센터', '4센터', '5센터', '6센터')
   AND wip.datetime >= TIMESTAMP '2023-10-23 07:00:00'
   AND wip.datetime <  TIMESTAMP '2023-10-24 07:00:00'
 GROUP BY ct.code
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d')
     , LPAD(HOUR(wip.datetime)                   , 2, '0')
     , LPAD(FLOOR(MINUTE(wip.datetime) / 30) * 30, 2, '0')
;

by JOY [2023.11.02 08:48:22]

안녕하세요 마농님
초보자에게 도움을 주셔서 정말 너무 감사드립니다!

오늘도 행복한 하루 시작하시길 바랄게요! xD

해당 쿼리로 진행을 해봤는데

line 3:3: Unexpected parameters (bigint, integer, varchar(1)) for function lpad. Expected: lpad(varchar(x), bigint, varchar(y)), lpad(varbinary, bigint, varbinary)

3:3 -, LPAD(HOUR(wip.datetime), 2, '0') hh24

입니다...

구하고 싶은 정보는 30분 마다 동일한 이름, 상태에 해당 되는 수량의 합계를 원합니다.

by 마농 [2023.11.02 09:05:47]

저는 잘되는데요. 일단 LPAD 부분 빼보세요.
오류메시지 보시면 어느정도 원인과 해결책이 보입니다.
에러메시지 보시고 이것 저것 시도해 보세요.
이름별, 상태별 시간대별 이네요?
GROUP BY 이름, 상태, 시간대 <-- 이렇게 하시면 됩니다.
이름이 코드 인가요?

-- 코드(이름?), 상태, 시간대(30분단위) 별 집계 --
SELECT ct.code
     , wip.status
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d') ymd
     , DATE_FORMAT(wip.datetime, '%H')       hh24
     , FLOOR(MINUTE(wip.datetime) / 30) * 30 mi
     , SUM(wip.qty) qty
  FROM dd.wip
  JOIN os.id ct
    ON ct.id = wip.id
 WHERE 1=1
   AND ct.code IN ('1센터', '2센터', '3센터', '4센터', '5센터', '6센터')
   AND wip.datetime >= TIMESTAMP '2023-10-23 07:00:00'
   AND wip.datetime <  TIMESTAMP '2023-10-24 07:00:00'
 GROUP BY ct.code
     , wip.status
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d')
     , DATE_FORMAT(wip.datetime, '%H')
     , FLOOR(MINUTE(wip.datetime) / 30) * 30
;

by 마농 [2023.11.02 09:30:03]

오류메시지를 보면 답이 보입니다.
1. line 13:69: mismatched input '<EOF>'. Expecting: ')', ',',
- 괄호 등이 안맞는다는 에러
2. line 1:8: 'datetime' must be an aggregate expression or appear in GROUP BY clause
- 그룹바이가 틀렸다는 에러, 그룹바이 없는 항목은 집계함수랑 사용해야 함
3. line 1:13: mismatched input 'datetime'. Expecting:
- 첫째 줄에 'datetime' 이 이상하다는 거
4. line 1:8: 'wip.datetime' must be an aggregate expression or appear in GROUP BY clause
- 그룹바이가 틀렸다는 에러, 2번 에러와 동일
5. line 3:3: Unexpected parameters (bigint, integer, varchar(1)) for function lpad. Expected: lpad(varchar(x), bigint, varchar(y)), lpad(varbinary, bigint, varbinary)
- LPAD 인자의 자료형이 틀렸다는 거, 문자가 필요한 곳에 숫자가 옴.
- 이게 MySQL 이라면 묵시적 형변환으로 에러 안날텐데...

by JOY [2023.11.02 10:32:12]

SELECT ct.code
     , wip.status
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d') ymd
     , DATE_FORMAT(wip.datetime, '%H')       hh24
     , FLOOR(MINUTE(wip.datetime) / 30) * 30 mi
     , SUM(wip.qty) qty
  FROM dd.wip
  JOIN os.id ct
    ON ct.id = wip.id
 WHERE 1=1
   AND ct.code IN ('1센터', '2센터', '3센터', '4센터', '5센터', '6센터')
   AND wip.datetime >= TIMESTAMP '2023-10-23 07:00:00'
   AND wip.datetime <  TIMESTAMP '2023-10-24 07:00:00'
 GROUP BY ct.code
     , wip.status
     , DATE_FORMAT(wip.datetime, '%Y-%m-%d')
     , DATE_FORMAT(wip.datetime, '%H')
     , FLOOR(MINUTE(wip.datetime) / 30) * 30

와... 대박입니다.... 드디어 원하던 결과가 나왔습니다ㅠ
저에겐 아직 많은 미션이 남아있지만 구루비를 알게 되어서 정말 행복합니다ㅠㅠㅠㅠㅠㅠㅠ

얼른 기초부터 공부 제대로 시작해야 될 것같습니다.

도움 주셔서 너무 감사합니다!!!

댓글등록

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

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

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

데이터베이스 질문과 답변