oracle-databasefunctionanalytical

oracle analytical function for time period


I'm trying to find out a way with Oracle(11g) analytical function to get a time period in status data. My data are like this:

<style>
table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 100%;
}

td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}

tr:nth-child(even) {
    background-color: #dddddd;
}
</style>
<table>
  <tr>
    <th>Service</th>
    <th>Date</th> 
    <th>Status</th>
  </tr>
  <tr>
    <td>Service A</td>
    <td>01.01.2018 08:00</td>
    <td>OK</td>
  </tr>
    <tr>
    <td>Service A</td>
    <td>01.01.2018 08:05</td>
    <td>OK</td>
  </tr>
    <tr>
    <td>Service A</td>
    <td>01.01.2018 08:10</td>
    <td>WARNING</td>
  </tr>
    <tr>
    <td>Service A</td>
    <td>01.01.2018 08:15</td>
    <td>WARNING</td>
  </tr>
    <tr>
    <td>Service A</td>
    <td>01.01.2018 08:20:00</td>
    <td>WARNING</td>
  </tr>
  <tr>
    <td>Service A</td>
    <td>01.01.2018 08:25:00</td>
    <td>WARNING</td>
  </tr>
  <tr>
    <td>Service A</td>
    <td>01.01.2018 08:30:00</td>
    <td>OK</td>
  </tr>
  <tr>
    <td>Service A</td>
    <td>01.01.2018 08:35:00</td>
    <td>OK</td>
  </tr>
</table>

I habe a lot of services, more different status and finaly a lot of data. My question is how I get in a smart anlalytical way a result like this:

Service,   Start_date,       End_date,        Status
Service A, 01.01.2018 08:00, 01.01.2018 08:05,OK
Service A, 01.01.2018 08:10, 01.01.2018 08:25,WARNING
Service A, 01.01.2018 08:30, 01.01.2018 08:35,OK

and so on


Solution

  • SQL Fiddle

    Oracle 11g R2 Schema Setup:

    CREATE TABLE yourtable
        (Service varchar2(9), Date_t DATE, Status varchar2(7))
    ;
    
    INSERT ALL 
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:00:00', 'OK')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:05:00', 'OK')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:10:00', 'WARNING')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:15:00', 'WARNING')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:20:00', 'WARNING')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:25:00', 'WARNING')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:30:00', 'OK')
        INTO yourtable (Service, Date_t, Status)
             VALUES ('Service A', TIMESTAMP  '2018-01-01 08:35:00', 'OK')
    SELECT * FROM dual
    ;
    

    Query 1:

     SELECT service,
           MIN (date_t) start_date,
           MAX(date_t)  end_date,
           MAX(status) Status
    FROM   (SELECT service,
                   date_t,
                   status,
                   row_number()
                     over (
                       ORDER BY date_t ) - row_number()
                                             over (
                                               PARTITION BY service, status
                                               ORDER BY date_t ) stat_change
            FROM   yourtable)
    GROUP  BY service,
              stat_change
    ORDER  BY start_date,
              end_date
    

    Results:

    |   SERVICE |           START_DATE |             END_DATE |  STATUS |
    |-----------|----------------------|----------------------|---------|
    | Service A | 2018-01-01T08:00:00Z | 2018-01-01T08:05:00Z |      OK |
    | Service A | 2018-01-01T08:10:00Z | 2018-01-01T08:25:00Z | WARNING |
    | Service A | 2018-01-01T08:30:00Z | 2018-01-01T08:35:00Z |      OK |