Группировать и агрегировать, когда столбцы меняются с течением времени в Clickhouse

avatar
Arash Mousavi
9 августа 2021 в 05:36
158
3
0

Предположим, у меня есть следующая таблица в Clickhouse:

f_datetime, f_user, f_tile
2021-07-08 07:00:00, x, a
2021-07-08 08:30:00, x, a
2021-07-08 08:45:00, x, a

2021-07-08 09:00:00, x, b
2021-07-08 11:00:00, x, b

2021-07-08 12:00:00, x, a
2021-07-08 15:00:00, x, a

2021-07-08 16:00:00, x, b
2021-07-08 20:00:00, x, b

Я хочу, чтобы агрегированный запрос выдавал следующие результаты:

f_user, f_tile, f_duration
x, a, 105
x, b, 120
x, a, 180
x, b, 240

Я хочу сгруппировать и рассчитать продолжительность до тех пор, пока f_tile, f_user не изменит упорядоченную таблицу на f_datetime.

Есть ли решение?

Источник
Denny Crane
9 августа 2021 в 13:20
0

coderhelper.com/questions/61163259/…

Ответы (3)

avatar
Gordon Linoff
9 августа 2021 в 11:45
2

Это пример задачи о пробелах и островах. Для этой версии самым простым решением, вероятно, является разница номеров строк: 

select f_user, t_tile,
       min(f_datetime), max(f_datetime),
       date_diff('minute', min(f_datetime), max(f_datetime)) as f_duration
from (select t.*,
             row_number() over (partition by f_user order by f_datetime) as seqnum,
             row_number() over (partition by f_user, f_tile order by f_datetime) as seqnum_2
      from t
     ) t
group by f_user, f_tile, (seqnum - seqnum_2)
avatar
Jon Armstrong
9 августа 2021 в 11:04
1

Поскольку вам нужны вычисления для каждого непрерывного сегмента f_tile (на f_user, я предполагаю), вот один из подходов с использованием оконных функций:

  • данные: Исходная таблица.
  • cte2: найти край каждого непрерывного f_tile запуска на f_user
  • cte3: вычислить индикатор группы (grp) для каждого прогона для агрегирования
  • cte4: расчет продолжительности для каждого запуска f_tile
WITH cte2 AS (  -- Find edges of each f_tile run for each f_user by datetime
        SELECT t.*
             , CASE WHEN LAG(f_tile) OVER (PARTITION BY f_user ORDER BY f_datetime) = f_tile THEN 0 ELSE 1 END AS edge
          FROM data AS t
     )
   , cte3 AS (  -- Assign a group (grp) indicator for each run for aggregation
        SELECT t.*, SUM(edge) OVER (PARTITION BY f_user ORDER BY f_datetime) AS grp
          FROM cte2 AS t
     )
   , cte4 AS (
        SELECT f_user, f_tile, grp
             , MIN(f_datetime) AS start
             , DATE_DIFF('minute', MAX(f_datetime), MIN(f_datetime)) AS duration
          FROM cte3 AS t
         GROUP BY f_user, f_tile, grp
     )
SELECT f_user, f_tile, duration
  FROM cte4
 ORDER BY start
;

Результат:

+--------+--------+----------+
| f_user | f_tile | duration |
+--------+--------+----------+
| x      | a      |      105 |
| x      | b      |      120 |
| x      | a      |      180 |
| x      | b      |      240 |
+--------+--------+----------+

Примечание. У меня нет экземпляра clickhouse для тестирования. Отрегулируйте по мере необходимости. Я протестировал аналог с другим движком.

avatar
Michael
9 августа 2021 в 06:37
1

Следующее значение даты и времени в вашей таблице является наименьшим значением для этого пользователя, которое равно >= дате и времени текущей строки.

SELECT t.f_user, t.f_title,
(SELECT MIN(t1.f_datetime) 
    FROM Yourtable t1 
    WHERE t1.f_datetime >= t.f_datetime AND t1.f_user = t.f_user AND t1.f_tile = t.f_tile) - t.f_datetime
FROM Yourtable t

Вместо вычитания этих значений вы можете применить функцию DIFF.