Блог

Я хочу проводить аналитику хранения/событий на основе данных о скрытых точках, которые хранятся в ClickHouse. Допустим, у меня есть два типа событий: app_launch (buried_point_id=1) и user_register (buried_point_id=2). Я хочу понять: в течение 1-дневного временного окна, сколько пользователей регистрируется после запуска приложения. См. Примеры данных о погребенных точках ниже:

Вот запрос, который я хочу выполнить:

 SELECT COUNT (DISTINCT t1.user_id), COUNT (DISTINCT t2.user_id)
FROM
  (SELECT user_id,
          happened_at
   FROM buried_points
   WHERE buried_point_id = 1
     AND happened_at >= 1613923200
     AND happened_at <= 1614182400
     AND ) AS t1
ASOF LEFT JOIN
  (SELECT user_id,
          happened_at
   FROM buried_points
   WHERE buried_point_id = 2
     AND happened_at >= 1613923200
     AND happened_at <= 1614182400) AS t2
ON t1.user_id = t2.user_id
AND t1.happened_at < t2.happened_at
AND t2.happened_at - t1.happened_at < 86400;
 

Это ожидаемый результат запроса:

 2 (123,345), 1 (123)
 

Однако, согласно документам ClickHouse, может поддерживаться только 1 неравенство:

Вы можете использовать любое количество условий равенства и ровно одно условие ближайшего совпадения. Например, ВЫБЕРИТЕ count() ИЗ table_1 В КАЧЕСТВЕ ЛЕВОГО СОЕДИНЕНИЯ table_2 В table_1.a == table_2.b И table_2.t <= table_1.t. Условия, поддерживаемые для ближайшего соответствия:>, < = table_1.t. Условия, поддерживаемые для ближайшего соответствия:>>=, < = table_1.t. Условия, поддерживаемые для ближайшего соответствия:>><,

Однако мне нужно 2 неравенства для выполнения моей работы — Есть ли решение этой проблемы?

Рассмотрите возможность использования специальной последовательности агрегатных функций:

 SELECT 
  user_id, 
  sequenceMatch('(?1)(?2)')(happened_at, buried_point_id = 1, buried_point_id = 2) retention
FROM (
  /* emulate test dataset */
  SELECT data.1 buried_point_id, data.2  happened_at, data.3 user_id
  FROM (
    SELECT arrayJoin(
      [(1, 1613966400, 123), 
      (1, 1613966411, 123), 
      (1, 1613966422, 123), 
      (1, 1614009600, 345), 
      (1, 1614009611, 345), 
      (2, 1613923200, 123), 
      (2, 1614009600, 234), 
      (2, 1614182400, 345)]) data)
  )
WHERE happened_at >= 1613923200 AND happened_at <= 1614182400 
GROUP BY user_id  

/*
┌─user_id─┬─retention─┐
│     123 │         0 │
│     234 │         0 │
│     345 │         1 │
└─────────┴───────────┘
*/
 

Расчет на основе minIf:

 SELECT 
  user_id, 
  minIf(happened_at, buried_point_id = 1) first_launch,
  minIf(happened_at, buried_point_id = 2) first_registration,
  first_launch != 0 and first_registration > first_launch ? 1 : 0 AS is_user_registered_after_launch
FROM (
  /* emulate test dataset */
  SELECT data.1 buried_point_id, data.2  happened_at, data.3 user_id
  FROM (
    SELECT arrayJoin(
      [(1, 1613966400, 123), 
      (1, 1614009600, 345), 
      (2, 1613923200, 123), 
      (2, 1614009600, 234), 
      (2, 1614182400, 345)]) data)
  )
WHERE happened_at >= 1613923200 AND happened_at <= 1614182400 
GROUP BY user_id 

/*
┌─user_id─┬─first_launch─┬─first_registration─┬─is_user_registered_after_launch─┐
│     123 │   1613966400 │         1613923200 │                               0 │
│     234 │            0 │         1614009600 │                               0 │
│     345 │   1614009600 │         1614182400 │                               1 │
└─────────┴──────────────┴────────────────────┴─────────────────────────────────┘
*/