Oracle数据库SQL复杂查询、数据处理（四、工业数据时间周期、自然周、阶段周的处理方法）

最新推荐文章于 2022-01-21 13:59:37 发布

我是个菜鸟刘

最新推荐文章于 2022-01-21 13:59:37 发布

阅读量318

点赞数

分类专栏： SQL学习笔记文章标签： SQL FINEREPORT

本文链接：https://blog.csdn.net/lq18792844344/article/details/91040616

版权

SQL学习笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

月、周、天数据处理方法汇总

例1：

SELECT '1M' FLG,
         TO_CHAR (FAB_DATE, 'YYYY.MM') FAB_DATE,
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1,
         SUM (QTY) PRD_QTY
        FROM RPT_M_GRADE A, MES_BIS_DATA B, MES_BIS_MDL_DEF C
   WHERE     A.DEF_CODE = B.DATA_EXT
         AND A.PRD_GRADE_GP IN ('S', 'K')
         AND A.DEF_CODE = B.DATA_EXT
         AND A.MDL_ID_FK = C.MDL_ID
         --AND B.OPERATION<>'DELETE'
         AND B.DATA_CATE = 'SCRS'
GROUP BY TO_CHAR (FAB_DATE, 'YYYY.MM'),
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1
         
UNION ALL
  SELECT '2W' FLG,
         TO_CHAR (FAB_DATE, 'YYYY') || 'W' || TO_CHAR (FAB_DATE, 'IW') FAB_DATE,     --'IW'是本周是第多少个自然周
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1,
         SUM (QTY) PRD_QTY
    FROM RPT_M_GRADE A, MES_BIS_DATA B, MES_BIS_MDL_DEF C
   WHERE     A.DEF_CODE = B.DATA_EXT
         AND A.PRD_GRADE_GP IN ('S', 'K')
         AND A.DEF_CODE = B.DATA_EXT
         AND A.MDL_ID_FK = C.MDL_ID
         --AND B.OPERATION<>'DELETE'
         AND B.DATA_CATE = 'SCRS'
GROUP BY TO_CHAR (FAB_DATE, 'YYYY') || 'W' || TO_CHAR (FAB_DATE, 'IW'),
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1
         
         
UNION ALL
  SELECT '3D' FLG,
         TO_CHAR (FAB_DATE, 'YYYY/MM/DD') FAB_DATE,
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1,
         SUM (QTY) PRD_QTY
    FROM RPT_M_GRADE A, MES_BIS_DATA B, MES_BIS_MDL_DEF C
   WHERE     A.DEF_CODE = B.DATA_EXT
         AND A.PRD_GRADE_GP IN ('S', 'K')
         AND A.DEF_CODE = B.DATA_EXT
         AND A.MDL_ID_FK = C.MDL_ID
         AND B.DATA_CATE = 'SCRS'
GROUP BY TO_CHAR (FAB_DATE, 'YYYY/MM/DD'),
         DEF_CODE,
         B.DATA_DESC,
         B.EXT_1
ORDER BY FLG, FAB_DATE, DEF_CODE

其中，分别选取月、周、天的统计：

 TO_CHAR (FAB_DATE, 'YYYY.MM') 
 TO_CHAR (FAB_DATE, 'YYYY') || 'W' || TO_CHAR (FAB_DATE, 'IW')
 TO_CHAR (FAB_DATE, 'YYYY/MM/DD')

缺点：
在这里插入图片描述
显示了所有的月、

例2

SELECT '1M' AS SORT_BY,
       SUBSTR(A.REGDATE, 1, 4)||'M'||SUBSTR(A.REGDATE, 5, 2)  AS CALENDAR,
       A.FACTORYNAME,
       SUM(A.SOPILLECAL) AS SOPILLECAL,
       SUM(A.SOPCHECK) AS SOPCHECK,
       SUM(A.SOPIMPROVEMENT) AS SOPIMPROVEMENT
  FROM Z_PB_SOP A 
            , STD_CALENDAR B
 WHERE 1 = 1
   AND A.FACTORYNAME = B.FACTORYNAME
   AND A.REGDATE = B.FACTORYDATE
   AND  SUBSTR(A.REGDATE, 1, 6) >= TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'YYYYMM')
   AND SUBSTR(A.REGDATE, 1, 6) <= TO_CHAR(SYSDATE, 'YYYYMM')
 GROUP BY  SUBSTR(A.REGDATE, 1, 6),A.FACTORYNAME  --B.FACTORYMONTH
 
UNION ALL
SELECT '2W' AS SORT_BY,
       SUBSTR(B.FACTORYWEEK, 1, 4) || 'W' || SUBSTR(B.FACTORYWEEK, 5, 6)  AS  CALENDAR,
        A.FACTORYNAME,
       SUM(A.SOPILLECAL) AS SOPILLECAL,
       SUM(A.SOPCHECK) AS SOPCHECK,
       SUM(A.SOPIMPROVEMENT) AS SOPIMPROVEMENT
  FROM Z_PB_SOP A , STD_CALENDAR B
 WHERE 1 = 1
   AND A.FACTORYNAME = B.FACTORYNAME
   AND A.REGDATE = B.FACTORYDATE
   AND B.FACTORYWEEK IN
       (SELECT DISTINCT FACTORYWEEK
          FROM STD_CALENDAR
         WHERE 1 = 1
           AND FACTORYMONTH = TO_CHAR(SYSDATE, 'YYYYMM'))
 GROUP BY B.FACTORYWEEK, A.FACTORYNAME
 
UNION ALL
SELECT '3D' AS SORT_BY,
       A.REGDATE  AS  CALENDAR,
       A.FACTORYNAME,
       A.SOPILLECAL,
       A.SOPCHECK,
       A.SOPIMPROVEMENT
  FROM Z_PB_SOP A
 WHERE 1 = 1
   AND A.REGDATE IN
       (SELECT DISTINCT FACTORYDATE
          FROM STD_CALENDAR
         WHERE 1 = 1
           AND FACTORYWEEK IN
               (SELECT DISTINCT FACTORYWEEK
                  FROM STD_CALENDAR
                 WHERE 1 = 1
                   AND FACTORYDATE = TO_CHAR(SYSDATE, 'YYYYMMDD')))

此方法适用于提前建好时间对照表

例3

SELECT  FACTORYMONTH AS FACTORYDATE
       ,'MONTH' AS DATE_TYPE
       ,SUM(LOT_ALL_QTY) AS LOT_ALL_QTY
       ,SUM(LOT_GOOD_QTY) AS LOT_GOOD_QTY
       ,SUM(PANEL_ALL_QTY) AS PANEL_ALL_QTY
       ,SUM(CASE WHEN DEFECTCODE<>'GOOD' THEN PANEL_ALL_QTY ELSE 0 END) AS PANEL_DEFECT_QTY
       ,SUM(PANEL_KILL_QTY ) AS PANEL_LOSS_QTY
FROM FPS_FQCSAMPLE_DAY
WHERE FACTORYDATE BETWEEN REPLACE('${dtFromDate}','-','') AND REPLACE('${dtEndDate}','-','') 
 ${IF(LEN(cmbType) == 0,"","AND PROCESSOPERATIONTYPE IN ('" + cmbType + "')")} 
 ${IF(LEN(cmbOper) == 0,"","AND PROCESSOPERATIONNAME IN ('" + cmbOper + "')")} 
 ${IF(LEN(cmbGC_FC) == 0,"","AND SUBSTR(PRODUCTSPECNAME,11,2) IN ('" + cmbGC_FC + "')")}
 ${IF(LEN(cmbProSpec) == 0,"","AND PRODUCTSPECNAME IN ('" + cmbProSpec + "')")} 
 ${IF(LEN(cmbRootMachine) == 0,"","AND ROOTPRODUCTSPEC IN ('" + cmbRootMachine + "')")} 
 ${IF(LEN(cmbDateShift) == 0," ","AND FACTORYDATESHIFT IN ('" + cmbDateShift + "')")} 
 ${IF(LEN(cmbWorkOrder) == 0,"","AND CASE WHEN SUBSTR(PRODUCTREQUESTNAME,1,2) IN ('10','20','55','65') 
                                          THEN 'P' ELSE 'E' END IN ('" + cmbWorkOrder + "')") } 

GROUP BY FACTORYMONTH

UNION ALL

SELECT  'WK'||TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')+4,'IW') AS FACTORYDATE
       ,'WEEK' AS DATE_TYPE
       ,SUM(LOT_ALL_QTY)  AS LOT_ALL_QTY
       ,SUM(LOT_GOOD_QTY) AS LOT_GOOD_QTY
       ,SUM(PANEL_ALL_QTY) AS PANEL_ALL_QTY
       ,SUM(CASE WHEN DEFECTCODE<>'GOOD' THEN PANEL_ALL_QTY ELSE 0 END) AS PANEL_DEFECT_QTY
       ,SUM(PANEL_KILL_QTY ) AS PANEL_LOSS_QTY
FROM FPS_FQCSAMPLE_DAY
WHERE FACTORYDATE BETWEEN   TO_CHAR(TRUNC(TO_DATE(TO_CHAR(DATE'${dtEndDate}','YYYYMM')||'01','YYYYMMDD'), 'd') + 1-4,'YYYYMMDD') AND  REPLACE('${dtEndDate}','-','')
 ${IF(LEN(cmbType) == 0,"","AND PROCESSOPERATIONTYPE IN ('" + cmbType + "')")}
  ${IF(LEN(cmbOper) == 0,"","AND PROCESSOPERATIONNAME IN ('" + cmbOper + "')")}

${IF(LEN(cmbGC_FC) == 0,"","AND SUBSTR(PRODUCTSPECNAME,11,2) IN ('" + cmbGC_FC + "')")}
${IF(LEN(cmbProSpec) == 0,"","AND PRODUCTSPECNAME IN ('" + cmbProSpec + "')")} 
${IF(LEN(cmbRootMachine) == 0,"","AND ROOTPRODUCTSPEC IN ('" + cmbRootMachine + "')")} 
${IF(LEN(cmbDateShift) == 0,"","AND FACTORYDATESHIFT IN ('" + cmbDateShift + "')")} 
${IF(LEN(cmbWorkOrder) == 0,"","AND CASE WHEN SUBSTR(PRODUCTREQUESTNAME,1,2) IN ('10','20','55','65') 
                        THEN 'P' ELSE 'E' END IN ('" + cmbWorkOrder + "')")} 


GROUP BY 'WK'||TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')+4,'IW')

UNION ALL

SELECT  'WK'||TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')+4,'IW')||'.'||(CASE WHEN TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')>=4 THEN TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')-3
ELSE TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')+4 END )  AS FACTORYDATE
       ,'ZDAY' AS DATE_TYPE
       ,SUM(LOT_ALL_QTY)  AS LOT_ALL_QTY
       ,SUM(LOT_GOOD_QTY) AS LOT_GOOD_QTY
       ,SUM(PANEL_ALL_QTY) AS PANEL_ALL_QTY
       ,SUM(CASE WHEN DEFECTCODE<>'GOOD' THEN PANEL_ALL_QTY ELSE 0 END) AS PANEL_DEFECT_QTY
       ,SUM(PANEL_KILL_QTY ) AS PANEL_LOSS_QTY
FROM FPS_FQCSAMPLE_DAY
WHERE FACTORYDATE BETWEEN TO_CHAR(CASE WHEN TO_CHAR(TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-1,'D') < '4' 
THEN  TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-7+4-TO_CHAR(TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-1,'D')
WHEN TO_CHAR(TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-1,'D') >= '4' 
THEN TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-TO_CHAR(TO_DATE(REPLACE('${dtEndDate}','-',''),'yyyymmdd')-1,'D')+4 END,'YYYYMMDD')
AND  REPLACE('${dtEndDate}','-','')
 ${IF(LEN(cmbType) == 0,"","AND PROCESSOPERATIONTYPE IN ('" + cmbType + "')")}
  ${IF(LEN(cmbOper) == 0,"","AND PROCESSOPERATIONNAME IN ('" + cmbOper + "')")}

 ${IF(LEN(cmbGC_FC) == 0,"","AND SUBSTR(PRODUCTSPECNAME,11,2) IN ('" + cmbGC_FC + "')")}
 ${IF(LEN(cmbProSpec) == 0,"","AND PRODUCTSPECNAME IN ('" + cmbProSpec + "')")} 
 ${IF(LEN(cmbRootMachine) == 0,"","AND ROOTPRODUCTSPEC IN ('" + cmbRootMachine + "')")}
 ${IF(LEN(cmbDateShift) == 0,"","AND FACTORYDATESHIFT IN ('" + cmbDateShift + "')")} 
 ${IF(LEN(cmbWorkOrder) == 0,"","AND CASE WHEN SUBSTR(PRODUCTREQUESTNAME,1,2) IN ('10','20','55','65') 
                        THEN 'P' ELSE 'E' END IN ('" + cmbWorkOrder + "')")} 

  
GROUP BY 'WK'||TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')+4,'IW')||'.'||(CASE WHEN TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')>=4 THEN TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')-3
ELSE TO_CHAR(TO_DATE(FACTORYDATE,'YYYYMMDD')-1,'D')+4 END ) 
ORDER BY DATE_TYPE,FACTORYDATE

标准处理方式，定义时间周期。