데이터베이스 질문과 답변
start_date end date
2020/05/11 15:00:00 2020/05/11 15:20:00
2020/05/11 16:00:00 2020/05/11 17:50:00
이런 데이터가 있습니다.
이번에 mssql에서 mariadb로 옮기면서 쿼리를 전체 수정해야 하는 문제때문에 머리가 아프네요 흑 ㅠㅠ
mssql에서는 master.dbo.spt_values 로 시간 차이만큼 가상 로우 만들어서 사용했는데 mariadb는 아무리 찾아봐도 못 찾겠네요..
설명할려니 뭐라 적어야 할지도 모르겠네요;;
작업 시간인데 그래프로 통계치 보여 줄 목적이라 60이 초과하여 시간이 바뀌면 다음 시간으로 분리 되어 표시 되어야 합니다
mssql에서 사용할떄 변환 후 ....
start_date end date
2020/05/11 15:00:00 2020/05/11 15:20:00
2020/05/11 16:00:00 2020/05/11 17:00:00
2020/05/11 17:00:00 2020/05/11 17:50:00
고수님들의 도움을 부탁드리겠습니다 ... ㅠㅠ
mssql 쿼리 전문입니다.
WITH q
AS (SELECT 0 AS NOTEMP,
0 AS DUMP,
Dateadd(dd, -1, CONVERT(DATETIME, '2020-05-11')) AS D,
CONVERT(VARCHAR(20), CONVERT(VARCHAR(10), Dateadd(dd, -1,
CONVERT(
DATETIME,
'2020-05-11' )), 112)
+ Replicate(0, 2 - Len(CONVERT(VARCHAR(3),
0 ))
)
+ CONVERT(VARCHAR(3), 0 )) AS NO
UNION ALL
SELECT CASE
WHEN notemp = 23 THEN 0
ELSE notemp + 1
END
NOTEMP
,
DUMP + 1,
CASE
WHEN notemp = 23 THEN CONVERT(DATETIME, '2020-05-11')
ELSE d
END D
,
CONVERT(VARCHAR(20), CASE WHEN notemp = 23 THEN
CONVERT(VARCHAR(10),
CONVERT(
DATETIME, '2020-05-11' )
, 112) ELSE CONVERT(VARCHAR(10), d, 112)
END +
Replicate(0,
2 - Len(CONVERT(VARCHAR(3), CASE WHEN
notemp =
23
THEN 0
ELSE notemp + 1 END )) ) + CONVERT(VARCHAR(
3),
CASE
WHEN
notemp = 23 THEN 0 ELSE notemp + 1 END ))
AS NO
FROM q
WHERE DUMP < 47),
res
AS (SELECT '1115' AS NO,
'2020/05/11 15:00:00' AS f_dt,
'2020/05/11 15:20:00' AS t_dt,
'교육' AS PCODE,
'00AB86476DFA47088850646286E78D6B' AS LINE
UNION
SELECT '1116' AS NO,
'2020/05/11 16:00:00' AS f_dt,
'2020/05/11 17:50:00' AS t_dt,
'교육' AS PCODE,
'00AB86476DFA47088850646286E78D6B' AS LINE)
SELECT no,
pcode,
line,
Datediff(hh, f_dt, t_dt)
AS DIFF,
Dateadd(hh, b.number, f_dt)
AS new_time,
CONVERT(VARCHAR(10), CONVERT(DATETIME, Dateadd(hh, b.number, f_dt)), 120)
AS D,
CASE
WHEN number > 0 THEN Dateadd(hh, b.number, CONVERT(DATETIME,
CONVERT(
VARCHAR(10), CONVERT(
DATETIME,
f_dt
), 120)
+ ' '
+ CONVERT(
VARCHAR
(2), Datepart(hour,
f_dt
))
+ ':00:00'
))
ELSE Dateadd(hh, b.number, f_dt)
END
start_time,
CASE
WHEN Datediff(hh, f_dt, t_dt) > 0
AND number != Datediff(hh, f_dt, t_dt) THEN
Dateadd(hh, b.number + 1, CONVERT(DATETIME, CONVERT(VARCHAR(10),
CONVERT(
DATETIME
, f_dt ), 120)
+ ' '
+ CONVERT(VARCHAR(2),
Datepart(hour,
f_dt))
+ ':00:00'))
WHEN Datediff(hh, f_dt, t_dt) > 0
AND number = Datediff(hh, f_dt, t_dt) THEN t_dt
ELSE t_dt
END
end_time
FROM res a
CROSS apply (SELECT number
FROM master.dbo.spt_values
WHERE type = 'P'
AND number <= Datediff(hh, a.f_dt, a.t_dt)) b
원본 MSSQL 쿼리를 보면 WITH q 부분이 사용이 안되고 있습니다.
아마도 q 를 사용하려고 하다가 master.dbo.spt_values 를 사용하는 쪽으로 변경된 듯 합니다.
WITH q 부분은 필요 없으니 제거하시면 되구요.
이는 그냥 순차적인 번호가 쭈욱 나열되는 형태입니다. (0, 1, 2, ..., n)
원하는 수만큼 복제될 때 사용되는 방법이네요.
MariaDB 의 경우엔 SELECT seq FROM seq_0_to_99 형태로 사용 가능합니다.
q는 no를 가져오기 위해 만들어서 쓰고 있긴 했는데 비효율적인가 보네요 아직 미숙한 지라... 답변 감사드립니다! 참조해서 작성해보도록 하겠습니다
q.no 및 q 전체가 사용이 되지 않고 있습니다.
With q AS (...) 부분 빼고 With res 로 시작해도 오류 안나고 정상 실행됩니다.
비효율의 문제가 아니라, 아예 불필요한 부분입니다.
-- MariaDB --
WITH res AS
(
SELECT 1115 no, '2020/05/11 15:00:00' f_dt, '2020/05/11 15:20:00' t_dt, '교육' pcode, 'xxx' line
UNION ALL SELECT 1116, '2020/05/11 16:00:00', '2020/05/11 17:50:00', '교육', 'xxx'
UNION ALL SELECT 1117, '2020/05/12 16:50:00', '2020/05/14 17:10:00', '교육', 'xxx'
)
SELECT no
, f_dt, t_dt, pcode, line
, seq
, DATE_FORMAT(DATE_ADD(s, INTERVAL seq HOUR), '%Y-%m-%d') d
, GREATEST (DATE_ADD(s, INTERVAL seq HOUR), f_dt) s_dt
, LEAST (DATE_ADD(s, INTERVAL seq+1 HOUR), t_dt) e_dt
FROM (SELECT no
, f_dt, t_dt, pcode, line
, STR_TO_DATE(SUBSTR(f_dt, 1, 13), '%Y/%m/%d %H') s
, STR_TO_DATE(SUBSTR(t_dt, 1, 13), '%Y/%m/%d %H') e
FROM res
) a
INNER JOIN seq_0_to_99 b
ON b.seq <= TIMESTAMPDIFF(hour, s, e)
;
-- MSSQL --
WITH res AS
(
SELECT 1115 no, '2020/05/11 15:00:00' f_dt, '2020/05/11 15:20:00' t_dt, '교육' pcode, 'xxx' line
UNION ALL SELECT 1116, '2020/05/11 16:00:00', '2020/05/11 17:50:00', '교육', 'xxx'
UNION ALL SELECT 1117, '2020/05/12 16:50:00', '2020/05/14 17:10:00', '교육', 'xxx'
)
, copy_t AS
(
SELECT 0 lv
UNION ALL
SELECT lv + 1
FROM copy_t
WHERE lv <= 99
)
SELECT no
, f_dt, t_dt, pcode, line
, lv
, CONVERT(VARCHAR(10), DATEADD(hh, lv, f_dt), 120) d
, CASE WHEN lv = 0 THEN f_dt
ELSE DATEADD(hh, lv , CONCAT(SUBSTRING(f_dt, 1, 13), ':00:00')) END s_dt
, CASE WHEN lv = DATEDIFF(hh, f_dt, t_dt) THEN t_dt
ELSE DATEADD(hh, lv+1, CONCAT(SUBSTRING(f_dt, 1, 13), ':00:00')) END e_dt
FROM res a
INNER JOIN copy_t b
ON lv <= DATEDIFF(hh, f_dt, t_dt)
;
) 버튼을 클릭하여 작성 하시면 됩니다.