¿À¶óŬ Äõ¸®¿¡¼­ DATE ŸÀÔÀÌ CHAR ŸÀÔº¸´Ù ºü¸¥°¡¿ä?

¹ÎºÀ±â   
   Á¶È¸ 4882   Ãßõ 9    

´ÙÀ½°ú °°Àº µÎ°³ÀÇ Äõ¸®´Â °á°ú´Â ¶È°°À¸¸ç
date ŸÀÔÀ» ½è´À³Ä char ŸÀÔÀ» ½è´À³Ä¸é ´Ù¸¨´Ï´Ù.

(1)
        SELECT u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME
                   ,u.TOTAL_VALUE
                   ,u.TOTAL_NUM
                   ,u.MIN_VALUE
                   ,INST1.fnpempa9001(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MIN_VALUE) MIN_TIME
                   ,u.MAX_VALUE
                   ,INST1.fnpempa9002(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MAX_VALUE) MAX_TIME
                   ,INST1.fnpempa9003(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_VALUE
                   ,INST1.fnpempa9004(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_TIME
        FROM (SELECT OBJECT_ID, DATE_DAY, DATE_TIME
                                   ,SUM(VALUE) TOTAL_VALUE
                                   ,COUNT(VALUE) TOTAL_NUM
                                   ,MIN(VALUE) MIN_VALUE
                                   ,MAX(VALUE) MAX_VALUE
                        FROM (
                                 SELECT OBJECT_ID,VALUE_HISTORY_ID, VALUE, DATE_DAY, DATE_TIME
                                 FROM (SELECT a.OBJECT_ID, a.VALUE_HISTORY_ID, a.VALUE
                                                                ,TO_CHAR(a.TIMESTAMP,'YYYYMMDD') DATE_DAY
                                                                ,TO_CHAR(a.TIMESTAMP,'HH24') DATE_TIME
                                           FROM INST1.TSPEMPA60 a, INST1.TSPEMPA50 b
                                           WHERE a.OBJECT_ID = b.OBJECT_ID
                                                             --AND b.NODE_NAME = 'atmsdb1' /*Input - ¼­¹ö¸í*/
                                          )
                                 WHERE 1=1
                                            AND DATE_DAY = '20070227' /*Input - ³¯Â¥*/                                
                                )
                        GROUP BY OBJECT_ID, DATE_DAY, DATE_TIME
                ) u

(2)
        SELECT u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME
                   ,u.TOTAL_VALUE
                   ,u.TOTAL_NUM
                   ,u.MIN_VALUE
                   ,INST1.fnpempa9001(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MIN_VALUE) MIN_TIME
                   ,u.MAX_VALUE
                   ,INST1.fnpempa9002(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MAX_VALUE) MAX_TIME
                   ,INST1.fnpempa9003(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_VALUE
                   ,INST1.fnpempa9004(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_TIME
        FROM (SELECT a.OBJECT_ID, DATE_DAY, DATE_TIME
                                   ,SUM(VALUE) TOTAL_VALUE
                                   ,COUNT(VALUE) TOTAL_NUM
                                   ,MIN(VALUE) MIN_VALUE
                                   ,MAX(VALUE) MAX_VALUE
                        FROM INST1.TSPEMPA60_T a, INST1.TSPEMPA50 b
                        WHERE a.OBJECT_ID = b.OBJECT_ID
                                             AND b.NODE_NAME = 'atmsdb1' /*Input - ¼­¹ö¸í*/
                                         AND DATE_DAY = '20070227' /*Input - ³¯Â¥*/
                        GROUP BY a.OBJECT_ID, DATE_DAY, DATE_TIME
                ) u


µ¥ÀÌÅÍ´Â ¿ø½Ã Å×À̺íÀÇ µ¥ÀÌÅÍ °Ç¼ö°¡ õ¸¸°Ç À̳»À̸ç
¼Óµµ´Â (1)¹ø Äõ¸®°¡ (2)¹ø º¸´Ù 30¹è Á¤µµ ºü¸¨´Ï´Ù.
À¯ÀÏÇÑ Â÷ÀÌ´Â TIMESTAMP¶ó°í µÇ¾î ÀÖ´Â Ä÷³ÀÌ DATE ŸÀÔÀ¸·Î Á¤ÀǵǾî Àִµ¥
ÀÌ ºÎºÐÀÌ DATE_DAY¿Í µ¿ÀÏÇÏ°Ô Å¸ÀÔ¸¸ ´Ù¸¥°ÍÀ̰ŵç¿ä.

¿ø·¡ DATE ŸÀÔÀÌ ºü¸¥°ÇÁö... ÀÌÁ¤µµ·Î ºü¸¥°ÇÁö ¾Ë°í ½Í½À´Ï´Ù.
¾Æ´Ô ¼Óµµ Â÷ÀÌ°¡ ³ª´Âµ¥ ´Ù¸¥ ¿äÀÎÀÌ ÀÖ¾ú´ø °ÇÁö...

À妽º´Â (1)°æ¿ì¿¡ OBJECT_ID ¿¡ °É·Á ÀÖÀ¸¸ç
(2)°æ¿ì¿¡ OBJECT_ID ±×¸®°í NODE_NAME, DATE_DAY¿¡ °É·Á ÀÖ´Â »óÅÂÀÔ´Ï´Ù.
ªÀº±Û Àϼö·Ï ½ÅÁßÇÏ°Ô.


QnA
Á¦¸ñPage 2681/5685
2014-05   4963623   Á¤ÀºÁØ1
2015-12   1500073   ¹é¸Þ°¡
2016-11   4881   ½Öcpu
2009-02   4881   ¹ÎÁöÈÆ
2007-01   4881   ¿À¼º±â
2006-09   4881   ÀÌÀç¼±
2016-09   4881   ¸®¿À
2008-08   4881   À̼±±Ô
2007-08   4881   ¹èÁ¤ÇÑ
2016-01   4881   º°¹Ù¶ó±â
2014-02   4881   ¹è»ó0¿ø
2018-04   4881   inquisitive
2015-10   4881   ¹ÙºÎÆÃÀÌ
2006-11   4882   °­°æ¸ð
2007-10   4882   Á¶ÀçÇö
2014-05   4882   akfalles
2014-12   4882   ´ÏÆ÷
2012-11   4882   À嵿°Ç2014
2011-10   4882   ºí·ç¿µ»ó
2016-09   4882   º°À̹ÀÜ
2007-10   4882   À̽ÂÁØ
2006-12   4882   Á¤¸íÇö