final_query.sql

;WITH hours as (
  SELECT 0 hour_id, '00:00 - 01:00' hour_literal UNION
  SELECT 1 hour_id, '01:00 - 02:00' hour_literal UNION
  SELECT 2 hour_id, '02:00 - 03:00' hour_literal UNION
  SELECT 3 hour_id, '03:00 - 04:00' hour_literal UNION
  SELECT 4 hour_id, '04:00 - 05:00' hour_literal UNION
  SELECT 5 hour_id, '05:00 - 06:00' hour_literal UNION
  SELECT 6 hour_id, '06:00 - 07:00' hour_literal UNION
  SELECT 7 hour_id, '07:00 - 08:00' hour_literal UNION
  SELECT 8 hour_id, '08:00 - 09:00' hour_literal UNION
  SELECT 9 hour_id, '09:00 - 10:00' hour_literal UNION
  SELECT 10 hour_id, '10:00 - 11:00' hour_literal UNION
  SELECT 11 hour_id, '11:00 - 12:00' hour_literal UNION
  SELECT 12 hour_id, '12:00 - 13:00' hour_literal UNION
  SELECT 13 hour_id, '13:00 - 14:00' hour_literal UNION
  SELECT 14 hour_id, '14:00 - 15:00' hour_literal UNION
  SELECT 15 hour_id, '15:00 - 16:00' hour_literal UNION
  SELECT 16 hour_id, '16:00 - 17:00' hour_literal UNION
  SELECT 17 hour_id, '17:00 - 18:00' hour_literal UNION
  SELECT 18 hour_id, '18:00 - 19:00' hour_literal UNION
  SELECT 19 hour_id, '19:00 - 20:00' hour_literal UNION
  SELECT 20 hour_id, '20:00 - 21:00' hour_literal UNION
  SELECT 21 hour_id, '21:00 - 22:00' hour_literal UNION
  SELECT 22 hour_id, '22:00 - 23:00' hour_literal UNION
  SELECT 23 hour_id, '23:00 - 00:00' hour_literal
  ORDER BY 1
),
incomes as (
  SELECT date_part, count(*) 
  FROM
    (
      SELECT
        DATE_PART('hour', started.created_at) date_part,
        ROW_NUMBER() OVER (PARTITION BY started.id) rn
      FROM sensor_triggers started
      JOIN sensor_triggers ended 
        ON ended.created_at - started.created_at BETWEEN '00:00:00'::time AND '00:00:05'::time
        AND started.sensor_id = 1 AND ended.sensor_id = 2
        -- Параметр "дата" формирования отчета
        AND started.created_at::date = '2019.06.09'
    ) as temporally
  WHERE rn = 1 
  GROUP BY date_part 
),
outcomes as (
  SELECT date_part, count(*) 
  FROM
    (
      SELECT
        DATE_PART('hour', started.created_at) date_part, 
        ROW_NUMBER() OVER (PARTITION BY started.id) rn
      FROM sensor_triggers started
      JOIN sensor_triggers ended 
        ON ended.created_at - started.created_at BETWEEN '00:00:00'::time AND '00:00:05'::time
        AND started.sensor_id = 2 AND ended.sensor_id = 1
        -- Параметр "дата" формирования отчета
        AND started.created_at::date = '2019.06.09'
    ) as temporally
  WHERE rn = 1 
  GROUP BY date_part 
),
rows as (
  SELECT 
    hours.hour_literal,
    coalesce(incomes.count, 0) income,
    coalesce(outcomes.count, 0) outcome
  FROM hours
  LEFT JOIN outcomes on hours.hour_id = outcomes.date_part
  LEFT JOIN incomes on hours.hour_id = incomes.date_part
),
final_row as (
  SELECT 'Итог', sum(income), sum(outcome) from rows
)
SELECT * FROM rows
UNION ALL
SELECT * from final_row