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