时间戳

首页 > 解决方案 > 如何在数据集中获得每小时？

sql - 如何在数据集中获得每小时？

问题描述

我试图每小时获取数据，但是当我执行时，一天的数据显示在表中。每小时结果的 IDC_YMD 必须在表中，我不确定我是否有正确的 SUBSTR（字符串，开始，长度）。

SELECT EQP_ID, EQP_NAME, 'WorkPreparation' GUBUN,WorkPreparation 
CNT,SITE_CODE,GODS_CODE,STEP_NAME,IDC_YMD,IDC_SUMR_UNIT_CODE,MTBI,MTBA      
FROM     
(     
SELECT CASE WHEN WorkPreparation = 0 THEN '-' ELSE EQP_ID END AS EQP_ID, CASE WHEN WorkPreparation = 
0 THEN '-' ELSE SUBSTR(EQP_NAME,1,4) END AS EQP_NAME, 
WorkPreparation,SITE_CODE,GODS_CODE,STEP_NAME,IDC_YMD,IDC_SUMR_UNIT_CODE,MTBI,MTBA        
FROM(     
SELECT EQP_ID, EVENT_COMMENT EQP_NAME,  SUM(WORK_PRE_CNT) WorkPreparation,  A.SITE_CODE     
 , A.GODS_CODE     
 , A.EES_STEP_ID  AS STEP_NAME     
 , TO_CHAR(TO_NUMBER(SUBSTR(A.IDC_YMD, 12, 2))) || '/' ||   TO_CHAR(TO_NUMBER(SUBSTR(A.IDC_YMD, 12, 
2)))  AS IDC_YMD     
 , 'D'                  AS IDC_SUMR_UNIT_CODE     
 , CASE WHEN (SUM(NVL(A.INTR_CNT, 0)) + SUM(NVL(A.BRDN_CNT, 0))) = 0     
        THEN ROUND(SUM(NVL(A.OPER_MOP, 0)),1)     
        ELSE ROUND(SUM(NVL(A.OPER_MOP, 0))  / (SUM(NVL(A.INTR_CNT, 0)) + SUM(NVL(A.BRDN_CNT, 0))) / 
60,1)     
   END AS MTBI     
 , CASE WHEN (SUM(NVL(A.INTR_CNT, 0)) + SUM(NVL(A.BRDN_CNT, 0)) + SUM(NVL(A.WORK_PRE_CNT, 0)) + 
SUM(NVL(A.PROD_CHANGE_CNT, 0)) + SUM(NVL(A.QUAL_EXATN_CNT, 0)) +     
              SUM(NVL(A.MTR_EXATN_CNT, 0)) + SUM(NVL(A.STEP_CHRC_LOSS_CNT, 0)) + 
SUM(NVL(A.IDLE_3MN_UND_EXCPT_CNT, 0))) = 0     
        THEN 0     
        ELSE ROUND(SUM(NVL(A.OPER_MOP, 0))  / (SUM(NVL(A.INTR_CNT, 0)) + SUM(NVL(A.BRDN_CNT, 0)) + 
SUM(NVL(A.WORK_PRE_CNT, 0)) + SUM(NVL(A.PROD_CHANGE_CNT, 0)) + SUM(NVL(A.QUAL_EXATN_CNT, 0)) +     
                                         SUM(NVL(A.MTR_EXATN_CNT, 0)) + SUM(NVL(A.STEP_CHRC_LOSS_CNT, 
0)) + SUM(NVL(A.IDLE_3MN_UND_EXCPT_CNT, 0))) / 60,1)     
   END AS MTBA       
FROM IFR_EES_PCL A    
WHERE SITE_CODE = 'E502AA'      
AND GODS_CODE = 'N5210'     
AND MTBI_MNG_YN = 'Y'     
AND EES_STEP_ID = 'TP'          
AND A.IDC_YMD BETWEEN TO_CHAR(SYSDATE-6, 'YYYYMMDDHH') AND TO_CHAR(SYSDATE, 'YYYYMMDD')       
GROUP BY EQP_ID, EVENT_COMMENT,A.SITE_CODE     
    , A.GODS_CODE     
    ,A.EQP_TYPE      
    , A.EES_STEP_ID     
    ,  TO_CHAR(TO_NUMBER(SUBSTR(A.IDC_YMD, 12, 2))) 
, A.IDC_YMD      
ORDER BY A.IDC_YMD, MTBI DESC--TO_CHAR(TO_NUMBER(SUBSTR(A.IDC_YMD, 12, 2)))    
))     
WHERE ROWNUM <= 10     
and EQP_NAME  like 'TA%'

谢谢！

标签： sql

解决方案

推荐阅读