Подсчёт суммы времени за промежуток времени

Question

Есть таблица с данными:

   |         time        | tag1 | tag2 |
   | ------------------- | ---- | ---- |
   | 2022-10-15 07:50:52 |   0  |   0  |
   | 2022-10-15 07:52:10 |   1  |   0  |
   | 2022-10-15 07:56:30 |   0  |   1  |
   | 2022-10-15 08:30:15 |   1  |   0  |
   | 2022-10-15 08:35:24 |   1  |   1  |
   | 2022-10-15 09:40:16 |   1  |   0  |
   | 2022-10-15 15:40:10 |   0  |   0  |

Нужно подсчитать за каждый час сколько времени tagX находился в состоянии 1.

Что должно получиться для tag1 (в секундах):

07:00-07:59	08:00-08:59	09:00-09:59	10:00-10:59	11:00-11:59	...	15:00-15:59
260	1785	3600	3600	3600	3600	2410

Что получается у меня:

07:00-07:59	08:00-08:59	09:00-09:59	10:00-10:59	11:00-11:59	...	15:00-15:59
260	4201	21594	0	0	0	0

В запросах я новичок, поэтому пришла простая идея считать разницу между текущим и следующим временем через LEAD и считать те, где tag1 = 1. Запрос, который я использую:

DECLARE @date DATETIME2 = '2022-10-15 19:00:00'

SELECT Sum(Z.Raz) / 60 + (Sum(Z.Raz) % 60.0) / 100
FROM (
    SELECT D.tag1
        ,Raz
    FROM (
        SELECT TIME
            ,tag1
            ,datediff(SECOND, TIME, LEAD(TIME) OVER (
                    ORDER BY TIME
                    )) AS Raz
        FROM [dbo].[table_1]
        WHERE TIME BETWEEN dateadd(DAY, -31, @date)
                AND dateadd(DAY, 31, @date)
        ) AS D
    WHERE TIME BETWEEN dateadd(hour, 12, @date)
        AND dateadd(hour, 13, @date)
    ) AS Z
WHERE tag1 = 1

Проблема в том, что если время переходит в следующий час, то не понимаю, как отсечь то время, чтобы оно работало в разных условиях. Подсчёт идёт не только по часам, а может и за сутки, месяц. Для этого я лишь меняю область выбора времени. Ещё есть момент, что время может быть до этого промежутка, но входить в него, поэтому приходится брать излишне больше данных (где -31..31).

Возможно это лучше и/или проще сделать через pandas, так запрос идёт именно в туда, а оттуда выгружаю в Excel.

Answer 1

Сначала формируем "сетку" с часами суток внутри диапазона, объединяем с исходным диапазоном и сортируем, чтобы восполнить "пробелы" в "целых" часах. Потом заполняем 'NaN' сверху вниз состояниями. Далее считаем разницы во времени построчно в секундах и умножаем их на состояния tag'ов (нулевые состояния обнуляют секунды). Потом через категории раскидываем по "корзинам", каждая из которых - один час. Потом группируем по "корзинам" и суммируем секунды.

df = pd.DataFrame({'time': ['2022-10-15 07:50:52', '2022-10-15 07:52:10', '2022-10-15 07:56:30', '2022-10-15 08:30:15',
                            '2022-10-15 08:35:24', '2022-10-15 09:40:16', '2022-10-15 15:40:10'],
                   'tag1': [0, 1, 0, 1, 1, 1, 0], 'tag2': [0, 0, 1, 0, 1, 0, 0], 'tag10': [1, 1, 0, 0, 0, 1, 0]})
df.time = pd.to_datetime(df.time)
bins = pd.date_range(start=df.time.min().replace(microsecond=0, second=0, minute=0),
                     end=df.time.max().replace(microsecond=0, second=0, minute=0) + pd.Timedelta(1, 'h'),
                     freq='1H')
df = pd.concat([df, bins.to_frame().rename(columns={0: 'time'})]).reset_index(drop=True).sort_values('time')
df['bins'] = pd.cut(df.time, bins, labels=[f'{dt.hour:02}:00 - {dt.hour:02}:59' for dt in bins][:-1], right=False)
df['dur'] = df.time.diff().dt.total_seconds().shift(-1, fill_value=0)
df.loc[:, cols] = df.loc[:, (cols := df.columns.str.startswith('tag'))].ffill().fillna(0).astype(int).mul(df.dur, axis=0)
df = df.set_index('bins').drop(columns=['time','dur'])
df = df.groupby(level=0).sum()
print(df)

                 tag1    tag2   tag10
bins                                 
07:00 - 07:59   260.0   210.0   338.0
08:00 - 08:59  1785.0  3291.0     0.0
09:00 - 09:59  3600.0  2416.0  1184.0
10:00 - 10:59  3600.0     0.0  3600.0
11:00 - 11:59  3600.0     0.0  3600.0
12:00 - 12:59  3600.0     0.0  3600.0
13:00 - 13:59  3600.0     0.0  3600.0
14:00 - 14:59  3600.0     0.0  3600.0
15:00 - 15:59  2410.0     0.0  2410.0

БЛОГ НА HUSL

Подсчёт суммы времени за промежуток времени

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