试试这个查询

SELECT 
    Site, 
    Machine_part, 
    SUM(OutageHours) AS Total_Outage_Hours
FROM (
    SELECT 
        Site, 
        Machine_part, 
        Journal_date, 
        LAG(Journal_date) OVER (PARTITION BY Site, Machine_part ORDER BY Journal_date) AS Prev_Journal_date, 
        New_status, 
        CASE 
            WHEN New_status = 'OOP' THEN 
                (Journal_date - LAG(Journal_date) OVER (PARTITION BY Site, Machine_part ORDER BY Journal_date)) * 24
            ELSE 0
        END AS OutageHours
    FROM machinery_table
    WHERE 
        Site = :site
        AND Machine_part = :machine_part
        AND Journal_date BETWEEN TO_DATE(:from_date, 'DD/MM/YYYY') AND TO_DATE(:to_date, 'DD/MM/YYYY')
)
GROUP BY Site, Machine_part;

字符串