summary_table.sql.rb

WITH w AS (
  WITH RECURSIVE t(segment) AS (
    VALUES (date_trunc(<%= quote @range %>, (<%= quote @time_start %>)::timestamp AT TIME ZONE 'UTC' AT TIME ZONE <%= quote @time_zone %>))
    UNION ALL
    SELECT segment + <%= quote "1 #{@range}" %>::interval
    FROM t
    WHERE segment < date_trunc(<%= quote @range %>, (<%= quote @time_end %>)::timestamp AT TIME ZONE 'UTC' AT TIME ZONE <%= quote @time_zone %>)
  )
  SELECT
    (ts1.segment)::timestamp AS segment,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.duration_norm
    END AS durations_norm,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.duration_acl
    END AS durations_acl,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.duration_fail
    END AS durations_fail,
    CASE
      WHEN ts2.segment IS NULL
      THEN extract(epoch from ((ts1.segment + (<%= quote "1 #{@range}" %>)::interval) - ts1.segment))
      ELSE ts2.duration_idle
    END AS durations_idle,
    ROUND((
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.duration_norm / (ts2.duration_norm + ts2.duration_acl + ts2.duration_fail + ts2.duration_idle) * 100
      END)::NUMERIC,4
    ) AS durations_norm_percent,
    ROUND((
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.duration_acl / (ts2.duration_norm + ts2.duration_acl + ts2.duration_fail + ts2.duration_idle) * 100
      END)::NUMERIC,4
    ) AS durations_acl_percent,
    ROUND((
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.duration_fail / (ts2.duration_norm + ts2.duration_acl + ts2.duration_fail + ts2.duration_idle) * 100
      END)::NUMERIC,4
    ) AS durations_fail_percent,
    ROUND((
      CASE
        WHEN ts2.segment IS NULL
        THEN 100.0
        ELSE ts2.duration_idle / (ts2.duration_norm + ts2.duration_acl + ts2.duration_fail + ts2.duration_idle) * 100
      END)::NUMERIC,4
    ) AS durations_idle_percent,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.clamps_cnt_norm
    END AS clamps_norm,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.clamps_cnt_acl
    END AS clamps_acl,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.clamps_cnt_fail
    END AS clamps_fail,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.clamps_cnt_idle
    END AS clamps_idle,
    ROUND(
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.clamps_cnt_norm::NUMERIC / (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * 100
      END,4
    ) AS clamps_norm_percent,
    ROUND(
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.clamps_cnt_acl::NUMERIC / (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * 100
      END,4
    ) AS clamps_acl_percent,
    ROUND(
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.clamps_cnt_fail::NUMERIC / (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * 100
      END,4
    ) AS clamps_fail_percent,
    ROUND(
      CASE
        WHEN ts2.segment IS NULL
        THEN 0
        ELSE ts2.clamps_cnt_idle::NUMERIC / (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * 100
      END,4
    ) AS clamps_idle_percent,
    CASE
      WHEN ts2.segment IS NULL
      THEN extract(epoch from ((ts1.segment + (<%= quote "1 #{@range}" %>)::interval) - ts1.segment))
      ELSE (ts2.duration_norm + ts2.duration_acl + ts2.duration_fail + ts2.duration_idle)
    END AS durations,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle)
    END AS clamps,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * <%= @slot_number %>
    END AS perfomance_total_items,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle)
    END AS perfomance_total_clamps,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.duration_fail / <%= @normal_cycle %> * <%= @slot_number %>
    END AS perfomance_fail_items,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE ts2.duration_fail / <%= @normal_cycle %>
    END AS perfomance_fail_clamps,
    CASE
      WHEN ts2.segment IS NULL
      THEN (extract(epoch from ((ts1.segment + (<%= quote "1 #{@range}" %>)::interval) - ts1.segment)) / <%= @normal_cycle %> * <%= @slot_number %>)
      ELSE (ts2.duration_idle / <%= @normal_cycle %> * <%= @slot_number %>)
    END AS perfomance_idle_items,
    CASE
      WHEN ts2.segment IS NULL
      THEN (extract(epoch from ((ts1.segment + (<%= quote "1 #{@range}" %>)::interval) - ts1.segment)) / <%= @normal_cycle %>)
      ELSE ts2.duration_idle / <%= @normal_cycle %>
    END AS perfomance_idle_clamps,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE COALESCE(ROUND(ts2.duration_norm::NUMERIC / NULLIF(ts2.clamps_cnt_norm,0)::NUMERIC,4),0)
    END AS times_norm,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE COALESCE(ROUND((ts2.duration_norm + ts2.duration_acl + ts2.duration_fail)::NUMERIC / NULLIF((ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail),0)::NUMERIC,4), 0)
    END AS times_all,
    <%= @normal_cycle %> AS times_goal,
    CASE
      WHEN ts2.segment IS NULL
      THEN 0
      ELSE (ts2.clamps_cnt_norm + ts2.clamps_cnt_acl + ts2.clamps_cnt_fail + ts2.clamps_cnt_idle) * <%= @material_consumption %> / 1000.0
    END AS material_consumption
  FROM t ts1
  LEFT JOIN (
    SELECT
      date_trunc(<%= quote @range %>, tday)::timestamp AS segment,
      SUM(duration_norm) AS duration_norm,
      SUM(duration_acl) AS duration_acl,
      SUM(duration_fail) AS duration_fail,
      SUM(duration_idle) AS duration_idle,
      SUM(clamps_cnt_norm) AS clamps_cnt_norm,
      SUM(clamps_cnt_acl) AS clamps_cnt_acl,
      SUM(clamps_cnt_fail) AS clamps_cnt_fail,
      SUM(clamps_cnt_idle) AS clamps_cnt_idle
    FROM (
        SELECT
          tday,
          CASE
            WHEN (lead(diff_duration_day_norm, 1) OVER ())::NUMERIC >  0
            THEN calc_duration_day_norm + ((lead(diff_duration_day_norm, 1) OVER ())::NUMERIC % extract(epoch from ((date_trunc(<%= quote @sub_range %>, tday) + interval <%= quote "1 #{@sub_range}" %>) - date_trunc(<%= quote @sub_range %>, tday)))::NUMERIC)
            ELSE calc_duration_day_norm
          END AS duration_norm,
          CASE
            WHEN (lead(diff_duration_day_acl, 1) OVER ())::NUMERIC >  0
            THEN calc_duration_day_acl + ((lead(diff_duration_day_acl, 1) OVER ())::NUMERIC % extract(epoch from ((date_trunc(<%= quote @sub_range %>, tday) + interval <%= quote "1 #{@sub_range}" %>) - date_trunc(<%= quote @sub_range %>, tday)))::NUMERIC)
            ELSE calc_duration_day_acl
          END AS duration_acl,
          CASE
            WHEN (lead(diff_duration_day_fail, 1) OVER ())::NUMERIC >  0
            THEN calc_duration_day_fail + ((lead(diff_duration_day_fail, 1) OVER ())::NUMERIC % extract(epoch from ((date_trunc(<%= quote @sub_range %>, tday) + interval <%= quote "1 #{@sub_range}" %>) - date_trunc(<%= quote @sub_range %>, tday)))::NUMERIC)
            ELSE calc_duration_day_fail
          END AS duration_fail,
          CASE
            WHEN (lead(diff_duration_day_idle, 1) OVER ())::NUMERIC >  0
            THEN calc_duration_day_idle + ((lead(diff_duration_day_idle, 1) OVER ())::NUMERIC % extract(epoch from ((date_trunc(<%= quote @sub_range %>, tday) + interval <%= quote "1 #{@sub_range}" %>) - date_trunc(<%= quote @sub_range %>, tday)))::NUMERIC)
            ELSE calc_duration_day_idle
          END AS duration_idle,
          clamps_cnt_norm,
          clamps_cnt_acl,
          clamps_cnt_fail,
          clamps_cnt_idle
        FROM (
          SELECT
            date_trunc(<%= quote @sub_range %>, time) AS tday,
            SUM(
              CASE
                WHEN type = 'norm'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE trim_duration
                  END
                ELSE 0
              END
            ) AS calc_duration_day_norm,
            SUM(
              CASE
                WHEN type = 'norm'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN trim_duration - extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE 0
                  END
                ELSE 0
              END
            ) AS diff_duration_day_norm,
            SUM(
              CASE
                WHEN type = 'acl'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE trim_duration
                  END
                ELSE 0
              END
            ) AS calc_duration_day_acl,
            SUM(
              CASE
                WHEN type = 'acl'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN trim_duration - extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE 0
                  END
                ELSE 0
              END
            ) AS diff_duration_day_acl,
            SUM(
              CASE
                WHEN type = 'fail'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE trim_duration
                  END
                ELSE 0
              END
            ) AS calc_duration_day_fail,
            SUM(
              CASE
                WHEN type = 'fail'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN trim_duration - extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE 0
                  END
                ELSE 0
              END
            ) AS diff_duration_day_fail,
            SUM(
              CASE
                WHEN type = 'idle'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE trim_duration
                  END
                ELSE 0
              END
            ) AS calc_duration_day_idle,
            SUM(
              CASE
                WHEN type = 'idle'
                THEN
                  CASE
                    WHEN (trim_duration > (extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))) )
                    THEN trim_duration - extract(epoch from (time - date_trunc(<%= quote @sub_range %>, time)))
                    ELSE 0
                  END
                ELSE 0
              END
            ) AS diff_duration_day_idle,
            count(
              CASE
                WHEN type='norm' THEN 1 ELSE NULL
              END
            ) AS clamps_cnt_norm,
            count(
              CASE
                WHEN type='acl' THEN 1 ELSE NULL
              END
            ) AS clamps_cnt_acl,
            count(
              CASE
                WHEN type='fail' THEN 1 ELSE NULL
              END
            ) AS clamps_cnt_fail,
            count(
              CASE
                WHEN type='idle' THEN 1 ELSE NULL
              END
            ) AS clamps_cnt_idle
          FROM (
            SELECT
              time AT TIME ZONE 'UTC' AT TIME ZONE <%= quote @time_zone %> AS time,
              type,
              CASE
                WHEN (duration > (extract(epoch from (time - <%= quote @time_start %>))) )
                THEN extract(epoch from (time - <%= quote @time_start %>))
                ELSE duration
              END AS trim_duration
            FROM clamps
            WHERE device_id = <%= @device_id %> AND time BETWEEN <%= quote @time_start %> AND <%= quote @time_end %>
            ORDER BY time ASC
          ) AS t1
          GROUP BY tday
          ORDER BY tday
        ) as t2
    ) as t4
    GROUP BY segment
    ORDER BY segment
  ) ts2
  ON ts2.segment = ts1.segment
  ORDER BY ts1.segment DESC
)
SELECT
<% if @range == 'month' || @range == 'week' || @range == 'day' %>
    date_trunc(<%= quote @range %>, segment::date)::date::varchar(256) AS segment,
<% else %>
    date_trunc(<%= quote @range %>, segment::timestamp)::varchar(256) AS segment,
<% end %>
  SUM(durations_norm) AS durations_norm,
  SUM(durations_acl) AS durations_acl,
  SUM(durations_fail) AS durations_fail,
  SUM(durations_idle) AS durations_idle,
  AVG(durations_norm_percent) AS durations_norm_percent,
  AVG(durations_acl_percent) AS durations_acl_percent,
  AVG(durations_fail_percent) AS durations_fail_percent,
  AVG(durations_idle_percent) AS durations_idle_percent,
  SUM(clamps_norm) AS clamps_norm,
  SUM(clamps_acl) AS clamps_acl,
  SUM(clamps_fail) AS clamps_fail,
  SUM(clamps_idle) AS clamps_idle,
  AVG(clamps_norm_percent) AS clamps_norm_percent,
  AVG(clamps_acl_percent) AS clamps_acl_percent,
  AVG(clamps_fail_percent) AS clamps_fail_percent,
  AVG(clamps_idle_percent) AS clamps_idle_percent,
  SUM(durations) AS durations,
  SUM(clamps) AS clamps,
  SUM(perfomance_total_items) AS perfomance_total_items,
  SUM(perfomance_total_clamps) AS perfomance_total_clamps,
  SUM(perfomance_fail_items) AS perfomance_fail_items,
  SUM(perfomance_fail_clamps) AS perfomance_fail_clamps,
  SUM(perfomance_idle_items) AS perfomance_idle_items,
  SUM(perfomance_idle_clamps) AS perfomance_idle_clamps,
  AVG(times_norm) AS times_norm,
  AVG(times_all) AS times_all,
  AVG(times_goal) AS times_goal,
  SUM(material_consumption) AS material_consumption
FROM w GROUP BY segment
UNION ALL
SELECT
  <%= quote @total_sum %>,
  SUM(durations_norm),
  SUM(durations_acl),
  SUM(durations_fail),
  SUM(durations_idle),
  ROUND(AVG(durations_norm_percent::NUMERIC), 4),
  ROUND(AVG(durations_acl_percent::NUMERIC), 4),
  ROUND(AVG(durations_fail_percent::NUMERIC), 4),
  ROUND(AVG(durations_idle_percent::NUMERIC), 4),
  SUM(clamps_norm),
  SUM(clamps_acl),
  SUM(clamps_fail),
  SUM(clamps_idle),
  COALESCE(ROUND(AVG(
    CASE
      WHEN clamps_norm_percent > 0
      THEN clamps_norm_percent::NUMERIC
    END
  ),4), 0),
  COALESCE(ROUND(AVG(
    CASE
      WHEN clamps_acl_percent > 0
      THEN clamps_acl_percent::NUMERIC
    END
  ),4), 0),
  COALESCE(ROUND(AVG(
    CASE
      WHEN clamps_fail_percent > 0
      THEN clamps_fail_percent::NUMERIC
    END
  ),4), 0),
  COALESCE(ROUND(AVG(
    CASE
      WHEN clamps_idle_percent > 0
      THEN clamps_idle_percent::NUMERIC
    END
  ),4), 0),
  SUM(durations),
  SUM(clamps),
  SUM(ROUND(perfomance_total_items::NUMERIC,4)),
  SUM(ROUND(perfomance_total_clamps::NUMERIC,4)),
  SUM(ROUND(perfomance_fail_items::NUMERIC,4)),
  SUM(ROUND(perfomance_fail_clamps::NUMERIC,4)),
  SUM(ROUND(perfomance_idle_items::NUMERIC,4)),
  SUM(ROUND(perfomance_idle_clamps::NUMERIC,4)),
  COALESCE(ROUND(AVG(
    CASE
      WHEN times_norm > 0
      THEN times_norm
    END
  ), 2), 0),
  COALESCE(ROUND(AVG(
    CASE
      WHEN times_all > 0
      THEN times_all
    END
  ), 2), 0),
  ROUND(AVG(
    CASE
      WHEN times_goal > 0
      THEN times_goal
    END
  ), 2),
  SUM(material_consumption)
FROM w
ORDER BY segment DESC