Can it be done with oracle analytic functions?

Question

Hi, I need help about a query in oracle 10g,

Here is my question:

There is a table that stores signal statuses that come from different devices.

1. SS1 and SS2 signals are inserted to table in random times,

2. if one of SS1 or SS2 signal status is "up" then the result signal should be "up"

3. if both SS1 and SS2 signal statuses are "down", then result signal should be "down"

I want to prepare a query that shows the result signal status changes according to SS1 and SS2 signals.

When i see the graph it seems easy, but i can not write a query by using analytic functions.

Table creation and insert script is 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