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 （效率） 要做 效率 的根據所選的 年份月份 做同比 跟環比
uj5u.com熱心網友回復：
LZ 研究一下 lag 這個函式，可以達到你的目的。uj5u.com熱心網友回復：
WITH T AS(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(+)
uj5u.com熱心網友回復：
LZ的需求應該有一個前提條件，即最終結果通過頁面程式或存盤程序（或函式）等實作，用戶輸入的查詢時間是明確的，因此可以直接計算出同比、環比的具體時間，然后通過簡單的表連接即可實作，比如：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處理。
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/52512.html
標籤：開發
下一篇：PB的問題，求高手