select begin_date,end_date, department_id,machine_name,sum(case when status='运行' then machine_time else 0 end) run,sum(case when status='待机' then machine_time else 0 end) as stop,sum(case when status='关机' then machine_time else 0 end) as off,sum(case when status='故障' then machine_time else 0 end) as fault
from
( select begin_date,end_date, department_id,machine_name,STATUS,SUM(ROUND(TO_NUMBER(end_date-begin_date)*24*60*60)) as MACHINE_TIME
from(
select dept.id as department_id,dnchis.machine_name,
case when begin_date <= to_date('2015-08-1 00:00:00','yyyy-MM-dd HH24:mi:ss') then to_date('2015-08-1 00:00:00','yyyy-MM-dd HH24:mi:ss') else begin_date end as begin_date,
case when (end_date<=to_date('2015-08-30 23:59:59','yyyy-MM-dd HH24:mi:ss') or end_date is null) then to_date('2015-08-30 23:59:59','yyyy-MM-dd HH24:mi:ss') else end_date end as end_date,
STATUS
from DNC_MACHINE_STATUS_HIS dnchis inner join DNC_NC_DEFIN1 dnc
on dnchis.MACHINE_NAME=dnc.MACHINE_NAME
left join pm_department dept
on dept.id = dnc.department_id
)t group by begin_date,end_date, department_id,machine_name,STATUS
) tb group by tb.begin_date,tb.end_date,tb.department_id,tb.machine_name
这是 求 OEE (效率) 要做 效率 的根据所选的 年份月份 做同比 跟环比
from
( select begin_date,end_date, department_id,machine_name,STATUS,SUM(ROUND(TO_NUMBER(end_date-begin_date)*24*60*60)) as MACHINE_TIME
from(
select dept.id as department_id,dnchis.machine_name,
case when begin_date <= to_date('2015-08-1 00:00:00','yyyy-MM-dd HH24:mi:ss') then to_date('2015-08-1 00:00:00','yyyy-MM-dd HH24:mi:ss') else begin_date end as begin_date,
case when (end_date<=to_date('2015-08-30 23:59:59','yyyy-MM-dd HH24:mi:ss') or end_date is null) then to_date('2015-08-30 23:59:59','yyyy-MM-dd HH24:mi:ss') else end_date end as end_date,
STATUS
from DNC_MACHINE_STATUS_HIS dnchis inner join DNC_NC_DEFIN1 dnc
on dnchis.MACHINE_NAME=dnc.MACHINE_NAME
left join pm_department dept
on dept.id = dnc.department_id
)t group by begin_date,end_date, department_id,machine_name,STATUS
) tb group by tb.begin_date,tb.end_date,tb.department_id,tb.machine_name
这是 求 OEE (效率) 要做 效率 的根据所选的 年份月份 做同比 跟环比
(SELECT DATE '2015-7-4' AS C1, 60 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-7-5' AS C1, 70 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-7-6' AS C1, 58 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-7-7' AS C1, 73 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-8-4' AS C1, 89 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-8-5' AS C1, 82 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-8-6' AS C1, 89 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-8-7' AS C1, 72 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-9-6' AS C1, 56 AS SEQ
FROM DUAL
UNION ALL
SELECT DATE '2015-9-7' AS C1, 84 AS SEQ FROM DUAL)
SELECT T.C1, T.SEQ, HB_T.SEQ, TB_T.SEQ
FROM T, T HB_T, T TB_T
WHERE T.C1 - 1 = HB_T.C1(+) AND ADD_MONTHS(T.C1, -1) = TB_T.C1(+)
SELECT KEY_COL,
VAL / DECODE(T2.YOY_VAL, 0, T1.VAL, T2.YOY_VAL) AS YOY_RATE,
VAL / DECODE(T2.MOM_VAL, 0, T1.VAL, T2.MOM_VAL) AS MOM_RATE,
FROM T1,
(SELECT KEY_COL,
SUM(DECODE(DATE_COL, :YOY_DATE, VAL, 0)) AS YOY_VAL,
SUM(DECODE(DATE_COL, :MOM_DATE, VAL, 0)) AS MOM_VAL
FROM T1
WHERE DATE_COL IN (:YOY_DATE, :MOM_DATE)
GROUP BY KEY_COL) T2
WHERE T1.KEY_COL = T2.KEY_COL
AND T1.DATE_COL = :INPUT_DATE如果T1是一个实时查询的结果集,可用WITH处理。
VAL / DECODE(T2.YOY_VAL, 0, T1.VAL, T2.YOY_VAL) AS YOY_RATE,
VAL / DECODE(T2.MOM_VAL, 0, T1.VAL, T2.MOM_VAL) AS MOM_RATE,
FROM T1,
(SELECT KEY_COL,
SUM(DECODE(DATE_COL, :YOY_DATE, VAL, 0)) AS YOY_VAL,
SUM(DECODE(DATE_COL, :MOM_DATE, VAL, 0)) AS MOM_VAL
FROM T1
WHERE DATE_COL IN (:YOY_DATE, :MOM_DATE)
GROUP BY KEY_COL) T2
WHERE T1.KEY_COL = T2.KEY_COL
AND T1.DATE_COL = :INPUT_DATE