Last active
June 9, 2019 04:40
-
-
Save sofakingworld/3e2be45cdd89cf07a29db35678aac4bb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
;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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment