@CopsisOnFire

Как в PostgreSQL просуммировать Киловаты, группируя их по временным интервалам?

Всем доброго времени суток. Есть 2 таблицы в Postgresql. В одной id, event_start(timestamp with time zone), event_end(timestamp with time zone), в другой event_time(timestamp with time zone), kwh(int). Нужно создать select, с колонками id, event_start, event_end, SUM(kwh). Каким образом найти эту сумму, если надо просуммировать все потраченные киловатт-часы, которые были зафиксированы в промежутки времени между event_start и event_end?
Я только учусь, не кидайте пожалуйста в меня тапками.

Table periods:
id event_start event_end
500 "2020-09-02 18:22:00+07" "2020-09-02 18:22:22+07"
499 "2020-09-02 18:19:00+07" "2020-09-02 18:19:25+07"
500 "2020-09-02 18:15:00+07" "2020-09-02 18:15:07+07"
499 "2020-09-02 18:14:00+07" "2020-09-02 18:14:05+07"
500 "2020-09-02 18:05:00+07" "2020-09-02 18:05:10+07"

Table energy:
event_time kwh
"2020-09-01 04:48:00+07" 1.57037
"2020-08-01 04:00:00+07" 1.44356
"2020-08-01 04:01:00+07" 1.49
"2020-08-01 04:02:00+07" 1.52698
"2020-08-01 04:03:00+07" 1.56503
"2020-08-01 04:04:00+07" 1.71035
"2020-08-01 04:05:00+07" 1.70528
"2020-08-01 04:06:00+07" 1.51452
"2020-08-01 04:07:00+07" 1.57223
"2020-08-01 04:08:00+07" 0.91082

Вот для примера как это выглядит. Строк само собой сильно больше
  • Вопрос задан
  • 99 просмотров
Решения вопроса 1
@alexalexes
select p.endpoint_id,
       p.mode_start as event_start,
       (p.mode_start + p.mode_duration) as event_end,
       sum(e.kwh) as sum_kwh
from periods p
join energy e on e.endpoint_id = p.endpoint_id -- связываем выставленный период для прибора с показаниями прибора
             and p.mode_start <= e.event_time -- связывание начало периода с моментом замера 
             and (p.mode_start + p.mode_duration) > e.event_time -- связывание конца периода с моментом замера
group by p.endpoint_id, p.mode_start, (p.mode_start + p.mode_duration)
order by p.endpoint_id, p.mode_start

Примечание.
1. Запрос составлен без отладки, требуется отладка.
2. Если периоды накладываются у одного и того же прибора, то замер, в интервале где происходит накладка, попадет в сумму в обоих периодах.
3. Если для событий не прописан период, то они не будут учитываться. Можно написать такую связку через left join так: "... energy e left join periods p on ... ", тогда сумма неучтенной энергии упадет в отдельную строку, в которой будет отсутствовать период.
Запрос для вывода неучтенных замеров:
select e.endpoint_id,
       p.mode_start as event_start,
       (p.mode_start + p.mode_duration) as event_end,
       sum(e.kwh) as sum_kwh
from  energy e 
left join periods p on e.endpoint_id = p.endpoint_id
             and p.mode_start <= e.event_time
             and (p.mode_start + p.mode_duration) > e.event_time
group by e.endpoint_id, p.mode_start, (p.mode_start + p.mode_duration)
order by e.endpoint_id,
         p.mode_start nulls first -- ставим первой строкой сумму замеров с неучтенными значениями (в пределах одного прибора)
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы