Clickhouse correlated queries/joins with multiple inequalities

Question

I have this query I am trying to join 2 table based on 4 conditions

select 
* 
from table1 t1 
asof left join table2 t2 on t1.start=t2.start and t1.end=t2.end and (t1.spending between t2.spending-10 and t2.spending+10) and (t1.timestamp_ between subtractSeconds(t2.timestamp_, 10) and addSeconds(t2.timestamp_, 10))

t2.id in ('623d9d7ce62af3465dadf31f')) 
where  t1.id in ('623d9d86e62af3465dadf327')
SETTINGS join_use_nulls = 1

My conditions are:

t1.start=t2.start
t1.end=t2.end
t1.spending between t2.spending - 10 and t2.spending + 10
t1.timestamp_ between subtractSeconds(t2.timestamp_, 10) and addSeconds(t2.timestamp_, 10)

I am trying to join them but clickhouse only supports one inequality condition. I cannot use subqueries because I have to correlate the data and clickhouse does not support correlated queries. I have tried another query using with clause and has but has is also not supported. I have seen dictionaries but according to my understanding we have to define these things in dictionaries but i cant because my last two conditions i.e iii,iv are generated dynamically

I have seen these links but i wasnt able to solve my issue

Can someone pls tell me how to run this query. Any help would be appreciated

thanks

Clickhouse correlated queries/joins with multiple inequalities

0 Answers0