EXCEPT — отсекающий UNION
Оператор EXCEPT ставится между двумя «вертикально» объединяемыми таблицами и выводит в результат только те строки первой из них, которых нет в правой.
Например, такой код
CREATE TABLE t1 (a Integer, b varchar(255));
INSERT INTO t1 (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (6, 'f');
CREATE TABLE t2 (a Integer, b varchar(255));
INSERT INTO t2 (a, b) VALUES (1, 'a'), (2, 'b'), (4, 'c'), (5, 'd');
SELECT * FROM t1
EXCEPT
SELECT * FROM t2;
выведет результат:
Я уже показывал, как можно использовать EXCEPT для сравнения двух таблиц при отладке изменений в витрине данных. Такой способ позволяет легко увидеть, какие строки пропали, появились или изменились в новой версии таблицы.
INTERSECT — пересечение таблиц
В противовес EXCEPT оператор INTERSECT выводит в результат только строки, совпадающие в обеих таблицах. Для таблиц из предыдущего примера запрос
SELECT * FROM t1
INTERSECT
SELECT * FROM t2;
выведет
Попробуйте использовать INTERSECT для выявления наличия некоторой сущности в разных таблицах. Например, так можно найти пользователей, которые совершили покупку и оставили отзыв, если данные о покупках и отзывах находятся в разных таблицах.
ARRAY JOIN — «уплощаем» массивы
ARRAY JOIN — оператор, который позволяет «развернуть» массивы в таблице, выводя каждый элемент массива в отдельную строку. Это полезно, если данные хранятся в виде массивов, а для анализа их нужно представить в виде отдельных записей. ARRAY JOIN создает новые строки, дублируя значения из других столбцов для каждого элемента массива.
Поясню на примере. Положим, есть табличка t со значениями типа массив в столбце arr:
Тогда запрос
SELECT x, arr
FROM t
ARRAY JOIN arr;
«развернет» столбец с массивом в плоскую таблицу, где на одну ячейку приходится одно значение:
Замечу, что строка с пустым массивом [] не попала в результат. Чтобы выбрать и ее, нужно использовать LEFT ARRAY JOIN.
ASOF JOIN — объединение по нечеткому условию
Во многих СУБД реализована возможность джойнить по неравным ключам, то есть
… ON t1.id = t2.id AND
t1.a > t2.a_begin AND t1.a <= t2.a_end
В Clickhouse такой штуки пока нет, но зато есть возможность выбрать одну строку с ближайшим подходящим под нечеткое условие ключом. Это может быть полезно, например, в случаях, когда данные в таблицах не полностью синхронизированы по временным меткам и точное совпадение найти невозможно или нецелесообразно.
Допустим, у вас есть данные из системы веб-аналитики:
Таблица hits с хитами и событиями:
Таблица sessions с сессиями и источниками трафика:
Вы хотите найти последний непрямой источник сессии перед событием регистрации (event = ‘registration’). Хиты и сессии связаны идентификатором клиента, но мы можем соединить их по временной метке, взяв ближайшую к этому событию сессию. (Кстати, о подобном механизме определения источника события, только для BigQuery, я, было дело, рассказывал.) Тут-то мы и применим наш ASOF LEFT JOIN:
SELECT h.*, s.utm_channel
FROM
(
SELECT *
FROM hits
WHERE event = 'registration'
) AS h
ASOF LEFT JOIN
(
SELECT *
FROM sessions
WHERE utm_channel != '(direct) / (none)' -- непрямые источники
) AS s ON s.client_id = h.client_id -- минимум одно условие равенства
AND s.session_start <= h.event_timestamp -- только одно условие неравенства
Если всё сделали правильно, на выходе получим последний непрямой источник регистрации:
