데이터베이스 질문과 답변
MSSQL 서버에서 PostgreSQL 서버로 마이그레이션 관련
1
3
732
by 돌빵 [SQL Query] MSSQL PostgreSQL 마이그레이션 [2021.03.25 11:03:19]
요번에 MSSQL에서 PostgreSQL로 디비를 마이그레이션하고 있습니다.
쿼리를 손보고 있는데요..
언피벗기능을 어떻게 바꿔야할지 도통 감이 안와서요..
crosstab 함수를 사용하는 방법과 다른방법이 있긴한건같은데.. 아무리 구글링하며 씨름해도 답이 안나오네요 ㅠㅠ
혹시 고수님들 도움좀 받을수 있을까해서 질문을 올려봅니다.
아래는 원래 MSSQL로 만든 쿼리입니다.
SELECT OPERATE_DATE, OPERATE_DATENAME, [OPERATE_SCHEDULE], [OPERATE_CNT], OPERATE_TYPE
FROM (SELECT OPERATE_DATE, OPERATE_DATENAME, OPERATE_TYPE,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '04:59:00' AND '05:59:59' THEN 1 END) AS COL11,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:00:00' AND '06:29:59' THEN 1 END) AS COL12,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:30:00' AND '09:29:59' THEN 1 END) AS COL13,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '09:30:00' AND '17:29:59' THEN 1 END) AS COL14,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '17:30:00' AND '19:29:59' THEN 1 END) AS COL15,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '19:30:00' AND '19:59:59' THEN 1 END) AS COL16,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '20:00:00' AND '22:59:59' THEN 1 END) AS COL17,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '23:00:00' AND '23:59:59' THEN 1 WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 END) AS COL18,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 WHEN OPERATE_TIME BETWEEN '04:55:00' AND '23:59:59' THEN 1 END) AS SUM10,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '04:59:00' AND '05:59:59' THEN 1 END) AS COL21,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:00:00' AND '06:59:59' THEN 1 END) AS COL22,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '07:00:00' AND '20:59:59' THEN 1 END) AS COL23,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '21:00:00' AND '22:59:59' THEN 1 END) AS COL24,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '23:00:00' AND '23:59:59' THEN 1 WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 END) AS COL25,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 WHEN OPERATE_TIME BETWEEN '04:55:00' AND '23:59:59' THEN 1 END) AS SUM20
FROM (SELECT A.TREATMENT_DATE,
CONVERT(CHAR(10),A.TREATMENT_DATE,23) AS OPERATE_DATE,
CONVERT(CHAR(8),A.TREATMENT_DATE,24) AS OPERATE_TIME,
--CAST(TREATMENT_DATE AS TIME) AS OPERATE_TIME,
(CASE WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '1') THEN '일'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '2') THEN '월'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '3') THEN '화'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '4') THEN '수'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '5') THEN '목'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '6') THEN '금'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '7') THEN '토' END) AS OPERATE_DATENAME,
(CASE WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '1') THEN '0'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '2') THEN '1'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '3') THEN '1'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '4') THEN '1'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '5') THEN '1'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '6') THEN '1'
WHEN(DATEPART(WEEKDAY,A.TREATMENT_DATE) = '7') THEN '0' END) AS OPERATE_TYPE,
A.EQUIPMENT,
A.LOCALISATION,
A.OLD_STATE,
A.NEW_STATE
FROM (
SELECT * FROM table1 UNION all
SELECT * FROM table2
) A
WHERE
(A.EQUIPMENT = 'A'
AND A.LOCALISATION IN ('125')
AND A.LABEL IN ('HM')
AND A.NEW_STATE = '0'
) OR
(A.EQUIPMENT = 'B'
AND A.LOCALISATION IN ('110')
AND A.LABEL IN ('HM','HP')
AND A.NEW_STATE = '0'
)) B
GROUP BY OPERATE_DATE, OPERATE_DATENAME, OPERATE_TYPE
) C
UNPIVOT([OPERATE_CNT] FOR [OPERATE_SCHEDULE] IN (COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, SUM10, COL21, COL22, COL23, COL24, COL25, SUM20)) AS UNPVT
ORDER BY OPERATE_DATE
아래는 위 쿼리에서 언피벗만 걷어내고 함수 몇가지 반영한 쿼리입니다.
SELECT OPERATE_DATE, OPERATE_DATENAME, OPERATE_TYPE,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '04:59:00' AND '05:59:59' THEN 1 END) AS COL11,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:00:00' AND '06:29:59' THEN 1 END) AS COL12,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:30:00' AND '09:29:59' THEN 1 END) AS COL13,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '09:30:00' AND '17:29:59' THEN 1 END) AS COL14,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '17:30:00' AND '19:29:59' THEN 1 END) AS COL15,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '19:30:00' AND '19:59:59' THEN 1 END) AS COL16,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '20:00:00' AND '22:59:59' THEN 1 END) AS COL17,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '23:00:00' AND '23:59:59' THEN 1 WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 END) AS COL18,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 WHEN OPERATE_TIME BETWEEN '04:55:00' AND '23:59:59' THEN 1 END) AS SUM10,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '04:59:00' AND '05:59:59' THEN 1 END) AS COL21,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '06:00:00' AND '06:59:59' THEN 1 END) AS COL22,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '07:00:00' AND '20:59:59' THEN 1 END) AS COL23,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '21:00:00' AND '22:59:59' THEN 1 END) AS COL24,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '23:00:00' AND '23:59:59' THEN 1 WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 END) AS COL25,
COUNT(CASE WHEN OPERATE_TIME BETWEEN '00:00:00' AND '00:15:00' THEN 1 WHEN OPERATE_TIME BETWEEN '04:55:00' AND '23:59:59' THEN 1 END) AS SUM20
FROM (SELECT A.TREATMENT_DATE,
to_char(A.TREATMENT_DATE,'YYYYMMDD') AS OPERATE_DATE,
to_char(A.TREATMENT_DATE,'HH24:MI:SS') AS OPERATE_TIME,
(CASE WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '1') THEN '월'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '2') THEN '화'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '3') THEN '수'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '4') THEN '목'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '5') THEN '금'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '6') THEN '토'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '7') THEN '일' END) AS OPERATE_DATENAME,
(CASE WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '1') THEN '1'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '2') THEN '1'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '3') THEN '1'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '4') THEN '1'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '5') THEN '1'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '6') THEN '0'
WHEN(EXTRACT(ISODOW FROM A.TREATMENT_DATE) = '7') THEN '0' END) AS OPERATE_TYPE,
A.EQUIPMENT,
A.LOCALISATION,
A.OLD_STATE,
A.NEW_STATE
FROM (
SELECT * FROM table1 UNION all
SELECT * FROM table2
) A
WHERE
(A.EQUIPMENT = 'A'
AND A.LOCALISATION IN ('125')
AND A.LABEL IN ('HM')
AND A.NEW_STATE = '0'
) OR
(A.EQUIPMENT = 'B'
AND A.LOCALISATION IN ('110')
AND A.LABEL IN ('HM','HP')
AND A.NEW_STATE = '0'
)) B
GROUP BY OPERATE_DATE, OPERATE_DATENAME, OPERATE_TYPE;
어떻게 하는게 좋을까요... 도움좀 부탁드리겠습니다..
by 마농
[2021.03.25 14:37:50]
원본 쿼리부터 복잡하게 되어 있네요.
group by ~ count(case ) 부분이 PIVOT 을 구현한 부분이고
PIVOT 한걸 다시 UNPIVOT 해서 원래대로 돌아가고 있네요.
즉, PIVOT, UNPIVOT 이 다 필요 없는 상황으로 풀이가 가능합니다.
시간대별로 개별 카운트 하고 전체 카운트로 하는 듯 한데
시간대별 카운트의 시작시간과 전체 카운트의 시작시간이 일치하지 않네요?
04:59 과 04:55 으로 4분 차이가 발생합니다.
시간을 일치시켜줄 필요가 있습니다.
by 돌빵
[2021.03.25 16:33:12]
4분차이나는것도 지금 알았네요.. 그시간동안 실제 데이터가 한건도 없어서 여태껏 몰랐어요...
워낙 실력없는 제가 구글링 해가며 막 만든쿼리라.. 이해 부탁드려요~ ^^
밑에 쿼리처럼 스마트한 쿼리로 좀 바꿔보겠습니다~
감사합니다 ^^
by 마농
[2021.03.25 15:40:59]
PostgreSQL 이 없어 테스트는 못해 봤습니다.
WITH t1 AS
(
SELECT TO_CHAR(treatment_date, 'yyyymmdd') AS operate_date
, TO_CHAR(treatment_date, 'hh24:mi:ss') AS operate_time
, SUBSTR('월화수목금토일', EXTRACT(ISODOW FROM treatment_date)::numeric, 1) operate_datename
, CASE WHEN EXTRACT(ISODOW FROM treatment_date) IN ('6','7') THEN '0' ELSE '1' END operate_type
FROM (SELECT equipment, localisation, label, new_state, treatment_date FROM table1
UNION ALL
SELECT equipment, localisation, label, new_state, treatment_date FROM table2
) a
WHERE ( equipment = 'A'
AND localisation IN ('125')
AND label IN ('HM')
AND new_state = '0'
)
OR ( equipment = 'B'
AND localisation IN ('110')
AND label IN ('HM', 'HP')
AND new_state = '0'
)
)
, t2 AS
(
SELECT 11 seq, 'COL11' operate_schedule, '04:55:00' stm, '05:59:59' etm
UNION ALL SELECT 12, 'COL12', '06:00:00', '06:29:59'
UNION ALL SELECT 13, 'COL13', '06:30:00', '09:29:59'
UNION ALL SELECT 14, 'COL14', '09:30:00', '17:29:59'
UNION ALL SELECT 15, 'COL15', '17:30:00', '19:29:59'
UNION ALL SELECT 16, 'COL16', '19:30:00', '19:59:59'
UNION ALL SELECT 17, 'COL17', '20:00:00', '22:59:59'
UNION ALL SELECT 18, 'COL18', '23:00:00', '23:59:59'
UNION ALL SELECT 18, 'COL18', '00:00:00', '00:15:00'
UNION ALL SELECT 19, 'SUM10', '00:00:00', '00:15:00'
UNION ALL SELECT 19, 'SUM10', '04:55:00', '23:59:59'
UNION ALL SELECT 21, 'COL21', '04:55:00', '05:59:59'
UNION ALL SELECT 22, 'COL22', '06:00:00', '06:59:59'
UNION ALL SELECT 23, 'COL23', '07:00:00', '20:59:59'
UNION ALL SELECT 24, 'COL24', '21:00:00', '22:59:59'
UNION ALL SELECT 25, 'COL25', '23:00:00', '23:59:59'
UNION ALL SELECT 25, 'COL25', '00:00:00', '00:15:00'
UNION ALL SELECT 29, 'SUM20', '00:00:00', '00:15:00'
UNION ALL SELECT 29, 'SUM20', '04:55:00', '23:59:59'
)
SELECT a.operate_date, a.operate_datename, a.operate_type
, b.seq, b.operate_schedule
, MAX(b.stm) stm
, MIN(b.etm) etm
, COUNT(c.operate_time) operate_cnt
FROM (SELECT DISTINCT operate_date, operate_datename, operate_type FROM t1) a
CROSS JOIN t2 b
LEFT OUTER JOIN t1 c
ON c.operate_date = a.operate_date
AND c.operate_time BETWEEN b.stm AND b.etm
GROUP BY a.operate_date, a.operate_datename, a.operate_type
, b.seq, b.operate_schedule
ORDER BY a.operate_date, b.seq
;
) 버튼을 클릭하여 작성 하시면 됩니다.