day, month select union으로 뽑기

[Day : 00~24시]

#day

SELECT '00' AS BASE_HOUR

UNION ALL

SELECT '01' AS BASE_HOUR

UNION ALL

SELECT '02' AS BASE_HOUR

UNION ALL

SELECT '03' AS BASE_HOUR

UNION ALL

SELECT '04' AS BASE_HOUR

UNION ALL

SELECT '05' AS BASE_HOUR

UNION ALL

SELECT '06' AS BASE_HOUR

UNION ALL

SELECT '07' AS BASE_HOUR

UNION ALL

SELECT '08' AS BASE_HOUR

UNION ALL

SELECT '09' AS BASE_HOUR

UNION ALL

SELECT '10' AS BASE_HOUR

UNION ALL

SELECT '11' AS BASE_HOUR

UNION ALL

SELECT '12' AS BASE_HOUR

UNION ALL

SELECT '13' AS BASE_HOUR

UNION ALL

SELECT '14' AS BASE_HOUR

UNION ALL

SELECT '15' AS BASE_HOUR

UNION ALL

SELECT '16' AS BASE_HOUR

UNION ALL

SELECT '17' AS BASE_HOUR

UNION ALL

SELECT '18' AS BASE_HOUR

UNION ALL

SELECT '19' AS BASE_HOUR

UNION ALL

SELECT '20' AS BASE_HOUR

UNION ALL

SELECT '21' AS BASE_HOUR

UNION ALL

SELECT '22' AS BASE_HOUR

UNION ALL

SELECT '23' AS BASE_HOUR

[Month: 해당 달의 모든 일자]

#month ym

SELECT 

  A.BASE_DATE

FROM (

SELECT 

(SELECT LAST_DAY(now())) - INTERVAL (A.A + (10 * B.A) + (100 * C.A)) DAY AS BASE_DATE

FROM (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS A

CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS B

CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS C

) A

WHERE DATE_FORMAT(A.BASE_DATE, '%Y%m') = '201808'

Day 예제>

: 시간을 구했으면 RIGHT OUTER JOIN으로 모 데이터에 시간을 붙임

SELECT B.LOG_H_CNT AS LOG_DATE,

A.BASE_HOUR

FROM (

SELECT COUNT(*) LOG_H_CNT,

RESOURCE_ID,

LOG_H

FROM MONITORING_LOG

WHERE RESOURCE_ID = '140C5BFFFF000FC8'

AND LOG_YMD = '20180831'

GROUP BY LOG_H

)B

RIGHT OUTER JOIN (

SELECT '00' AS BASE_HOUR

UNION ALL

SELECT '01' AS BASE_HOUR

UNION ALL

SELECT '02' AS BASE_HOUR

UNION ALL

SELECT '03' AS BASE_HOUR

UNION ALL

SELECT '04' AS BASE_HOUR

UNION ALL

SELECT '05' AS BASE_HOUR

UNION ALL

SELECT '06' AS BASE_HOUR

UNION ALL

SELECT '07' AS BASE_HOUR

UNION ALL

SELECT '08' AS BASE_HOUR

UNION ALL

SELECT '09' AS BASE_HOUR

UNION ALL

SELECT '10' AS BASE_HOUR

UNION ALL

SELECT '11' AS BASE_HOUR

UNION ALL

SELECT '12' AS BASE_HOUR

UNION ALL

SELECT '13' AS BASE_HOUR

UNION ALL

SELECT '14' AS BASE_HOUR

UNION ALL

SELECT '15' AS BASE_HOUR

UNION ALL

SELECT '16' AS BASE_HOUR

UNION ALL

SELECT '17' AS BASE_HOUR

UNION ALL

SELECT '18' AS BASE_HOUR

UNION ALL

SELECT '19' AS BASE_HOUR

UNION ALL

SELECT '20' AS BASE_HOUR

UNION ALL

SELECT '21' AS BASE_HOUR

UNION ALL

SELECT '22' AS BASE_HOUR

UNION ALL

SELECT '23' AS BASE_HOUR

) A

ON B.LOG_H = A.BASE_HOUR