Skip to content

Instantly share code, notes, and snippets.

@javisantana

javisantana/res.sql

Last active Jun 20, 2019
Embed
What would you like to do?
filter ourside the join vs inside the join in clickhouse
MacBook-Pro-de-javi.local :) select cityHash64(groupArray(cityHash64(*))) from A a asof inner join (select * from B where ts<toDateTime('1970-01-01 02:00:00')) b on a.id=b.id and a.ts=b.ts where a.ts<toDateTime('1970-01-01 02:00:00');
SELECT cityHash64(groupArray(cityHash64(*)))
FROM A AS a
ASOF INNER JOIN
(
SELECT *
FROM B
WHERE ts < toDateTime('1970-01-01 02:00:00')
) AS b ON (a.id = b.id) AND (a.ts = b.ts)
WHERE a.ts < toDateTime('1970-01-01 02:00:00')
┌─cityHash64(groupArray(cityHash64(ts, id, b.ts, b.id)))─┐
11024216907930891576
└────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.009 sec. Processed 16.38 thousand rows, 292.93 KB (1.88 million rows/s., 33.62 MB/s.)
MacBook-Pro-de-javi.local :) select cityHash64(groupArray(cityHash64(*))) from A a asof inner join B b on a.id=b.id and a.ts=b.ts where a.ts<toDateTime('1970-01-01 02:00:00');
SELECT cityHash64(groupArray(cityHash64(*)))
FROM A AS a
ASOF INNER JOIN B AS b ON (a.id = b.id) AND (a.ts = b.ts)
WHERE a.ts < toDateTime('1970-01-01 02:00:00')
┌─cityHash64(groupArray(cityHash64(ts, id, b.ts, b.id)))─┐
11024216907930891576
└────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.793 sec. Processed 10.01 million rows, 179.05 MB (12.62 million rows/s., 225.78 MB/s.)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment