Блог

У меня есть таблица, подобная приведенной ниже, где состояние — это ограниченный набор обновлений (например, Start, End).:

 CREATE TABLE event_updates (
  event_id Int32,
  timestamp DateTime,
  state String
) ENGINE Log;
  

И я хочу иметь возможность быстро запускать запросы, такие как:

 SELECT count(*)
FROM (
 SELECT event_id,
  minOrNullIf(timestamp, state = 'Start') as start,
  minOrNullIf(timestamp, state = 'End') as end,
  end - start as duration,
  duration < 10 as is_fast,
  duration > 300 as is_slow
 FROM event_updates
 GROUP BY event_id)
WHERE start >= '2020-08-20 00:00:00'
AND start < '2020-08-20 00:00:00'
AND is_slow;
  

Но эти запросы выполняются медленно, когда данных много, я предполагаю, потому что вычисления требуются для каждой строки.

Примерные данные:

 ┌─event_id─┬───────────timestamp─┬─state─┐
│        1 │ 2020-08-21 09:58:00 │ Start │
│        1 │ 2020-08-21 10:18:00 │ End   │
│        2 │ 2020-08-21 10:23:00 │ Start │
│        2 │ 2020-08-21 10:23:05 │ End   │
│        3 │ 2020-08-21 10:23:00 │ Start │
│        3 │ 2020-08-21 10:24:00 │ End   │
│        3 │ 2020-08-21 11:24:00 │ End   │
│        4 │ 2020-08-21 10:30:00 │ Start │
└──────────┴─────────────────────┴───────┘
  

И пример запроса:

 SELECT
    event_id,
    minOrNullIf(timestamp, state = 'Start') AS start,
    minOrNullIf(timestamp, state = 'End') AS end,
    end - start AS duration,
    duration < 10 AS is_fast,
    duration > 300 AS is_slow
FROM event_updates
GROUP BY event_id
ORDER BY event_id ASC

┌─event_id─┬───────────────start─┬─────────────────end─┬─duration─┬─is_fast─┬─is_slow─┐
│        1 │ 2020-08-21 09:58:00 │ 2020-08-21 10:18:00 │     1200 │       0 │       1 │
│        2 │ 2020-08-21 10:23:00 │ 2020-08-21 10:23:05 │        5 │       1 │       0 │
│        3 │ 2020-08-21 10:23:00 │ 2020-08-21 10:24:00 │       60 │       0 │       0 │
│        4 │ 2020-08-21 10:30:00 │                ᴺᵁᴸᴸ │     ᴺᵁᴸᴸ │    ᴺᵁᴸᴸ │    ᴺᵁᴸᴸ │
└──────────┴─────────────────────┴─────────────────────┴──────────┴─────────┴─────────┘
  

То, что я хотел бы создать, — это предварительно рассчитанная таблица, подобная:

 CREATE TABLE event_stats (
  event_id Int32,
  start Nullable(DateTime),
  end Nullable(DateTime),
  duration Nullable(Int32),
  is_fast Nullable(UInt8),
  is_slow Nullable(UInt8)
);
  

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

Сначала я бы

1. используйте MergeTree-engine вместо журнала, чтобы получить преимущества от сортировки по ключу

 CREATE TABLE event_updates (
  event_id Int32,
  timestamp DateTime,
  state String
) ENGINE MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, state);
  

2. ограничьте исходный набор данных, применив WHERE-предложение к отметке времени и состоянию (в вашем запросе обрабатывается весь набор данных)

 SELECT count(*)
FROM (
 SELECT event_id,
  minOrNullIf(timestamp, state = 'Start') as start,
  minOrNullIf(timestamp, state = 'End') as end,
  end - start as duration,
  duration < 10 as is_fast,
  duration > 300 as is_slow
 FROM event_updates
 WHERE timestamp >= '2020-08-20 00:00:00' AND timestamp < '2020-09-20 00:00:00' 
    AND state IN ('Start', 'End')
 GROUP BY event_id
 HAVING start >= '2020-08-20 00:00:00' AND start < '2020-09-20 00:00:00' 
   AND is_slow);
  

Если эти методы не помогают, необходимо рассмотреть возможность использования AggregatingMergeTree для обработки предварительно вычисленных агрегатов, а не необработанных данных.