ClickHouse - аналоги подзапросов
Каким образом я могу заменить конструкцию с подзапросом например на join (или что-то иное) в следующем примере
select x.id,
x.date_requested,
(select count(distinct x2.bo_id) from x as x2
where x2.bo_id <> x.bo_id
and x2.id < x.id
and x2.date_requested between x.date_requested - interval 1 day and x.date_requested
) as bo_1d
from x
| id | date_req | b_id | bo_1d |
|---|---|---|---|
| 1 | 01.01.2021 | 325 | 0 |
| 2 | 02.01.2021 | 112 | 1 |
| 3 | 03.01.2021 | 118 | 2 |
| 4 | 04.01.2021 | 201 | 3 |
| 5 | 05.01.2021 | 113 | 3 |
| 6 | 06.01.2021 | 100 | 3 |
| 7 | 07.01.2021 | 325 | 3 |
| 8 | 08.01.2021 | 325 | 2 |
| 9 | 09.01.2021 | 199 | 2 |
| 10 | 10.01.2021 | 190 | 2 |
Выше представлен пример исходной таблицы к которой необходимо написать запрос. Суть запроса в следующем: необходимо посчитать для каждой строки количество уникальных b.id не равных b.id в текущей строке а так же id которых меньше, за период between date_requested - interval 3 day and date_requested
Пример расчета для строки id = 5 берем все строки где date_req between date_req - interval 3 day and date_req
| id | date_req | b_id | bo_1d |
|---|---|---|---|
| 2 | 02.01.2021 | 112 | 1 |
| 3 | 03.01.2021 | 118 | 2 |
| 4 | 04.01.2021 | 201 | 3 |
| 5 | 05.01.2021 | 113 | 3 |
далее фильтруем b_id <> 113
| id | date_req | b_id | bo_1d |
|---|---|---|---|
| 2 | 02.01.2021 | 112 | 1 |
| 3 | 03.01.2021 | 118 | 2 |
| 4 | 04.01.2021 | 201 | 3 |
далее count(b_id) и как результат для строки с id=5 мы получаем результат bo_1d = 3
Представленный скрипт в шапке написан на mysql, данную логику необходимо перенести на clickhouse
Ответы (1 шт):
Если считать приведённый в тексте вопроса запрос верным, то следующий запрос является его полным эквивалентом:
SELECT x1.id,
x1.date_requested,
COALESCE(count(distinct x2.bo_id), 0) bo_1d
FROM x AS x1
LEFT JOIN x as x2 ON x2.bo_id <> x1.bo_id
AND x2.id < x1.id
AND x2.date_requested BETWEEN x1.date_requested - INTERVAL 1 DAY AND x1.date_requested
GROUP BY x1.id,
x1.date_requested