Crystal Reports группировать данные по "плавающим" часам

Question

стоит такой вопрос. Существует таблица SQL с данными по расписанию поездов. В ней две колонки - rasp_date, которая отвечает за выезд поезда, и race_hours, которое указывает на время поездки этого поезда до конечного пункта. Приведу пример данной таблицы:

RASP_DATE                  RACE_HOURS
01.10.2020 15:05:00        4
01.10.2020 15:35:00        3
01.10.2020 16:10:00        8
01.10.2020 16:25:00        6

Нужно разработать отчет на основе этих данных, посчитать, какое суммарное время поездки каждый час. Например, по примеру выше отчет выдаст, что с 15:00 до 16:00 суммарное время поездки - 7 часов, с 16:00 до 17:00 - 14 часов. Данная задача легко выполняется при помощи группировки. Однако, стоит задача немного другая - рассчитать количество часов поездки в "плавающем" часе. Пользователь вводит параметр "время минут" (например, 15), который кратен 5, и теперь "окна" должны рассчитываться не с 15:00 до 16:00, а с 15:15 до 16:15, с 17:15 до 18:15, и т.д. Скорее всего, для этого нужно менять группировку, но точно не знаю, как это сделать. Прошу, пожалуйста, подсказать.

Answer 1

Пример получения данных по указанному размеру окна. СУБД и версия не указана, использован MySQL версии 8. Суммарная длительность посчитана в минутах.

WITH RECURSIVE
cte1 AS ( SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(MIN(start)) DIV 3600) * 3600) start,
                 TIMESTAMP(FROM_UNIXTIME((UNIX_TIMESTAMP(MIN(start)) DIV 3600) * 3600), @chunk) finish
          FROM test
        UNION ALL
          SELECT TIMESTAMP(start, @chunk), TIMESTAMP(TIMESTAMP(start, @chunk), @chunk)
          FROM cte1
          WHERE start < ( SELECT MAX(start + INTERVAL length HOUR)
                          FROM test ) )
,
cte2 AS ( SELECT start, start + INTERVAL length HOUR finish
          FROM test )
SELECT cte1.start, 
       cte1.finish,
       SUM(TIMESTAMPDIFF(MINUTE, GREATEST(cte1.start, cte2.start), LEAST(cte1.finish, cte2.finish))) total,
       GROUP_CONCAT(cte2.start) starts
FROM cte1
JOIN cte2 ON cte1.start <= cte2.finish AND cte2.start <= cte1.finish
GROUP BY cte1.start, 
         cte1.finish;

test - таблица исходных данных

@chunk - (введённый юзером) размер окна

fiddle

Запрос дополнительно, для контроля, выводит список start тех записей, которые учтены в суммарном времени для текущего окна.

Если необходимо вывести данные не за весь период, а только за определённый отрезок, измените соотв. образом cte1 и генерируйте список окон на основании требуемых границ.

Answer 2

В итоге, пришел к решению вопроса. Делается группировка по плавающему часу вот так:

Создать параметр "minutes".

Создать новую формулу "floating_hour":

if minute({TableName.RASP_DATE}) < {?minutes} then
time(hour({TableName.RASP_DATE})-1,{?minutes},0) else
if minute({TableName.RASP_DATE}) >= {?minutes} then
time(hour({TableName.RASP_DATE}),{?minutes},0)

Создана группа и группировка по "RASP_DATE" (По неделям)
Создана группа и группировка по формуле "floating_hour".
При помощи инструментов CR просуммировано RACE_HOURS по группе "floating_hours".

БЛОГ НА HUSL

Crystal Reports группировать данные по "плавающим" часам

Ответы (2 шт):