mv_controller_dir_oracle.sql

CREATE MATERIALIZED VIEW mv_controller_dir_oracle AS
  SELECT
    con.gis_id                                                                         AS PP_ID,
    cmr3.NAME_DIRECTION,
    cmr3.day_timestamp,
    cmr3.plan_time_on,
    cmr3.plan_time_off,
    CASE WHEN length(string_agg(cmr3.periods, ', ' :: TEXT ORDER BY cmr3.start_timestamp)) > 3999
      THEN left(string_agg(cmr3.periods, ', ' :: TEXT ORDER BY cmr3.start_timestamp), 3995) || '...'
    ELSE string_agg(cmr3.periods, ', ' :: TEXT ORDER BY cmr3.start_timestamp) END      AS periods,
    max(is_fact_datetime_on)                                                           AS is_fact_datetime_on,
    max(is_fact_datetime_off)                                                          AS is_fact_datetime_off,
    EXTRACT(EPOCH FROM cmr3.plan_period_timestamp) :: NUMERIC                          AS plan_period_timestamp,
    EXTRACT(EPOCH FROM sum(cmr3.period_timestamp)) :: NUMERIC                          AS period_timestamp,
    EXTRACT(EPOCH FROM sum(cmr3.period_timestamp) - cmr3.plan_period_timestamp) :: INT AS diff,
    CASE WHEN NOT EXTRACT(EPOCH FROM sum(cmr3.period_timestamp)) :: NUMERIC = 0
      THEN
        cast(EXTRACT(EPOCH FROM cmr3.plan_period_timestamp) :: NUMERIC /
             EXTRACT(EPOCH FROM sum(cmr3.period_timestamp)) :: NUMERIC * 100 AS INTEGER)
    ELSE 0 END
                                                                                       AS diff_proc
  FROM
    (
      SELECT DISTINCT
        cmr2.controller_id,
        cmr2.NAME_DIRECTION,
        cmr2.day_timestamp                                    AS day_timestamp,
        cmr2.START_timestamp                                  AS START_timestamp,
        cmr2.END_timestamp                                    AS END_timestamp,
        se.datetime_on                                        AS datetime_on,
        se.datetime_off                                       AS datetime_off,
        to_char(datetime_on + INTERVAL '3 hour', 'HH24:MI')   AS plan_time_on,
        to_char(datetime_off + INTERVAL '3 hour', 'HH24:MI')  AS plan_time_off,
        to_char(START_timestamp + INTERVAL '3 hour', 'HH24:MI') || '-' ||
        to_char(END_timestamp + INTERVAL '3 hour', 'HH24:MI') AS periods,

        CASE WHEN abs(extract(EPOCH FROM age(datetime_on, START_timestamp))) < 900
          THEN 1
        ELSE 0 END                                               is_fact_datetime_on,
        CASE WHEN abs(extract(EPOCH FROM age(datetime_off, END_timestamp))) < 900
          THEN 1
        ELSE 0 END                                               is_fact_datetime_off,
        (datetime_off -
         datetime_on)                                         AS plan_period_timestamp,
        (END_timestamp - START_timestamp)                     AS period_timestamp
      FROM
        (
          SELECT
            cmr.controller_id,
            cmr.monitor_id,
            cmr.timestamp,
            cmr.value,
            substring(cmr.tag FROM position('PHASE' IN cmr.tag))                 AS NAME_DIRECTION,
            CASE WHEN cmr.value = 0
              THEN date_trunc('day', cmr.timestamp) END                        AS day_timestamp,
            CASE WHEN cmr.value = 0
              THEN cmr.raw_timestamp END                                           AS START_timestamp,

            CASE WHEN cmr.value = 0 AND (lead(cmr.value)
            OVER (
              ORDER BY  cmr.controller_id, cmr.monitor_id, cmr.raw_timestamp)) = 1
                THEN lead(cmr.raw_timestamp)
              OVER (ORDER BY cmr.controller_id, cmr.monitor_id, cmr.raw_timestamp)
            else (lag(cmr.raw_timestamp) OVER (ORDER BY cmr.controller_id, cmr.monitor_id, cmr.raw_timestamp)) END AS END_timestamp
          FROM (
                 SELECT
                   cm.controller_id,
                   cmr.monitor_id,
                   cm.tag,
                   date_trunc('minute', cmr.timestamp) AS timestamp,
                   cmr.timestamp  AS raw_timestamp,
                   cmr.value,
                   CASE WHEN (lag(cmr.value, 1, -1)
                   OVER (
                     ORDER BY cm.controller_id, cmr.monitor_id, timestamp)) <> cmr.value
                     THEN 1
                   ELSE 0 END                          AS rank_true
                 FROM controller_monitor_reading cmr
                   JOIN controller_monitor cm ON cm.id = cmr.monitor_id
                 WHERE cmr.state_id IN (62, 63)
--                   and  date_trunc('day', cmr.timestamp) = '2016-11-04'
--       AND  cm.controller_id in (340, 1394)
               ) AS cmr
          WHERE cmr.rank_true = 1
        ) AS cmr2
        LEFT JOIN schedule_entry se ON se.date = cmr2.day_timestamp
      WHERE cmr2.value = 0
    ) AS cmr3
    JOIN controller con ON con.id = cmr3.controller_id
  WHERE con.gis_id IS NOT NULL
  GROUP BY con.gis_id,
    cmr3.NAME_DIRECTION,
    cmr3.plan_time_on,
    cmr3.plan_time_off,
    cmr3.day_timestamp,
    cmr3.plan_period_timestamp;