Can this be done with the analytic functions of the oracle?

Question

I need help requesting in oracle 10g,

Here is my question:

There is a table that stores the status of signals coming from different devices.

• Signals SS1 and SS2 are inserted into the table at random times,

• if one of the signal states SS1 or SS2 is up, then the resulting signal must be up

• if the state of signals SS1 and SS2 is “down”, then the result signal should be “down”

I want to prepare a query that displays the state changes of the result signal in accordance with signals SS1 and SS2.

When I see a graph, it seems simple, but I cannot write a query using analytic functions.

Create a table and insert a script here.

Thanks in advance,

Answer 1

SELECT  s2.*,
        CASE WHEN ss1 = 'down' AND ss2 = 'down' THEN 'down' ELSE 'up' END AS result
FROM    (
        SELECT  s.*,
                LAST_VALUE(DECODE(signal_id, 'SS1', signal_status, NULL) IGNORE NULLS) OVER (ORDER BY signal_date) AS ss1,
                LAST_VALUE(DECODE(signal_id, 'SS2', signal_status, NULL) IGNORE NULLS) OVER (ORDER BY signal_date) AS ss2
        FROM    t_signal s
        ) s2
ORDER BY
        signal_date