[SQL] 1ºÐ´ÜÀ§·Î Group By ÇÏ´Â °ÍÀ», 5ºÐ ´ÜÀ§·Î Group By ÇÏ°Ô ¹Ù²Ü¼ø ¾ø³ª¿ä?
1ºÐ´ÜÀ§·Î Group By ÇÏ´Â °ÍÀ», 5ºÐ ´ÜÀ§·Î Group By ÇÏ°Ô ¹Ù²Ü¼ø ¾ø³ª¿ä?
Äõ¸®»ó¿¡¼± ½Ã°£ÀÌ ¾ó¸¶ ¾È°É¸®³ª, ¿ö³« µ¥ÀÌÅÍ ·®ÀÌ ¸¹¾Æ ºê¶ó¿ìÁ®¿¡¼ Ãâ·ÂÇϴµ¥ ½Ã°£ÀÌ °É·Á¼¼¿ä
µµ¿ÍÁÖ¼¼¿ä ¤Ì¤Ì
Âü MySQL Äõ¸®ÀÌ¸ç ±âº» ¿ø¸®´Â Oracle °ú ´Ù¸£Áö ¾ÊÀ¸´Ï
°¡´ÉÇÏ´Ù¸é MySQL ÇÔ¼ö¸é ÁÁ°ÚÁö¸¸ Oracle ·Î ´äº¯ÇØ Áּŵµ ÁÁ½À´Ï´Ù.
¾Æ·¡¿¡¼ '%Y-%m-%d %H:%i' ´Â ³â-¿ù-ÀÏ ½Ã:ºÐ ÀÔ´Ï´Ù.
À妽º´Â date_time ¿¡ °É·Á ÀÖÁö¸¸, ÀÏ´Ü À妽º±îÁø ½Å°æ ¾È½áµµ ÁÁ°Åµç¿ä.
ºÎŹµå¸±²²¿ä
SELECT b.part_date_time as dateTime
,b.date AS date
,b.hour AS hour
,b.minute AS minute
,b.avg_temp_peci_agent1 AS avgTempPeciAgent1
,b.avg_temp_peci_agent2 AS avgTempPeciAgent2
,b.avg_temp_system AS avgTempSystem
,b.max_temp_peci_agent1 AS maxTempPeciAgent1
,b.max_temp_peci_agent2 AS maxTempPeciAgent2
,b.max_temp_system AS maxTempSystem
,b.min_temp_peci_agent1 AS minTempPeciAgent1
,b.min_temp_peci_agent2 AS minTempPeciAgent2
,b.min_temp_system AS minTempSystem
FROM (SELECT DATE_FORMAT( a.date_time, '%Y-%m-%d %H:%i' ) AS part_date_time
,DATE_FORMAT( a.date_time, '%Y%m%d' ) AS date
,DATE_FORMAT( a.date_time, '%H' ) AS hour
,DATE_FORMAT( a.date_time, '%i' ) AS minute
,round(avg( a.temp_peci_agent1 ),1) AS avg_temp_peci_agent1
,round(avg( a.temp_peci_agent2 ),1) AS avg_temp_peci_agent2
,round(avg( a.temp_system ),1) AS avg_temp_system
,max( a.temp_peci_agent1 ) AS max_temp_peci_agent1
,max( a.temp_peci_agent2 ) AS max_temp_peci_agent2
,max( a.temp_system ) AS max_temp_system
,min( a.temp_peci_agent1 ) AS min_temp_peci_agent1
,min( a.temp_peci_agent2 ) AS min_temp_peci_agent2
,min( a.temp_system ) AS min_temp_system
FROM temperature_log c) a
GROUP BY part_date_time
ORDER BY part_date_time DESC
LIMIT 0,240
) b
ªÀº±Û Àϼö·Ï ½ÅÁßÇÏ°Ô.