Как стать автором
Обновить

Комментарии 14

SELECT T_1.a1, T_2.a1 FROM T AS T_1 INNER JOIN T AS T_2 ON T_1.a1 = T_2.a1;

Или

SELECT a1, a1 FROM T;

А вот нет, этот переход уже не работает (практически во всех SQL диалектах, с реляционной алгеброй зависит от определений). Для LEFT работал бы, а для INNER не годится без дополнительного условия, оставлю вопрос - какого?

Проверка на NULL

Да!

Согласен, забыл в том случае дополнить, что a1 в T даже должно быть уникальным, ненулевым, в общем, ключом, иначе совсем не получится JOIN по ON T_1.a1 = T_2.a1;

Ничего себе забыл, это в корне меняет все дело.

Без ограничений по a1 в T никак, без ограничений по a1 даже INNER JOIN наоборот превратится в CROSS JOIN:

SELECT T_1.a1, T_2.a1 FROM T AS T_1 INNER JOIN T AS T_2 ON T_1.a1 = T_2.a1

Ограничения по a1 в T могут быть разные (уникальность, первичный ключ, уникальность на основе данных в таблице T, но без формальных ограничений уникальности на уровне ClickHouse), и связь T_1.a1 и T_2.a1 может быть условно через третьи таблицы, каждый случай нужно рассматривать отдельно, но условие T_1.a1 = T_2.a1 по крайней мере должно работать (обеспечивать связь один к одному) на основе какого-то набора данных, чтобы дальше исследовать проблему.

Однако приведенные примеры это какая то оптимизация сферического запроса в вакууме. То есть вы консультант, смотрите чужой продукт и видите такой запрос из трёх cross join таблицы самой с собой. Но в моем опыте только один раз я видел столь безумный ляп, и он не был связан с cross join

Если же внутри одной фирмы вы видите такой запрос, то просто ищите автора, и если он не на колесах то просто говорите: дружище, без обид, но SQL - это не твое

Согласен, но и на практике при ручном написании запросов такие случаи тоже встречаются (хоть и в виде диких ляпов).

Эти случаи могут быть актуальными, когда SQL генерируется кодом (.NET, Java, Go и т.д.) для конкретного UI или отчета, т.е. не вручную. Во всех трех случаях для таблицы с 1 млн записей условно UI корректный (например, сводная таблица), или отчет условно корректный, т.е. их можно получить не за 1 млн × 1 млн операций, а за разумное число операций, условно 1 млн, но из-за CROSS JOIN получить отчет невозможно. Т.е. это больше для случаев, когда SQL генерируется не вручную и чинится тоже не вручную.

Рассмотренные случаи помогают что-то исправить в CROSS JOIN (удалить его) без значительного вмешательства в проект, такого, как:

  • ограничить число записей в запросе для одной или обеих таблиц в CROSS JOIN (например, LIMIT, LIMIT BY, topK в ClickHouse, или обычный GROUP BY - если возможно)

  • добавление паджинации для одной или обеих таблиц из запроса (если возможно)

  • условное упрощение сводной таблицы до таблицы размером 4 × 4 и выполнение 16 отдельных запросов для каждой ячейки (может быть актуально, например, для таблиц по кварталам или месяцам, при возможности можно выполнять запросы параллельно)

  • добавление или изменение фильтров для сводной таблицы

  • отказ от сводной таблицы как условно последнее средство, замена одного отчета несколькими и т.д., т.е. пересмотр бизнес-логики

Оптимизация CROSS JOIN? Никогда не думал, что увижу такое.

Лучшая оптимизация CROSS JOIN - его отсутствие.

Точно, здесь как раз простые случаи, как от него избавиться и что нужно учесть

SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2;

... если поля T_2 не используются, то можно избавиться от T_2 и CROSS JOIN соответственно

SELECT a1, a1 FROM T;

В общем случае - НЕЛЬЗЯ. Первый запрос возвращает несколько копий каждой записи. А второй - только одну копию. То есть запросы неэквивалентны. Более того, из результата первого запроса можно воспроизвести результат второго, но не наоборот. То есть предлагаемое преобразование приводит к необратимому повреждению результата и безвозвратной утрате части содержащихся в нём (мета)данных.

два перечисленные выше SQL запроса возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY).

Да нельзя их удалять, эти дубликаты! НЕЛЬЗЯ! Ничто не гарантирует, что само количество дубликатов в наборе записей на самом деле не является важной и значимой характеристикой результата. Которая будет использоваться в дальнейшем.

PS. И как по мне, вообще всё написанное - это совершенно не оптимизация. В случае алгебраических выражений это бы называли приведением, упрощением, или ещё каким термином, но уж точно не оптимизацией. Какой термин применять в случае СУБД правильно - как-то не припоминается.

Согласен, что разные результаты для SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2; и SELECT a1, a1 FROM T;, пояснение "возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)" может выглядеть неоднозначно, в playground приведён пример

SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;

SELECT a1, a1 FROM T;

Он корректен, если a1 - это ключевое уникальное поле, для произвольного a1 корректно только такое:

SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;

SELECT a1, a1 FROM T GROUP BY a1, a1;

Согласен, что с точки зрения SQL это можно назвать иначе, "оптимизация CROSS JOIN" - для упрощения, чтобы не писать условно "оптимизация производительности SQL запроса путём удаления CROSS JOIN и замены на эквивалентный SQL запрос с другими типами JOIN (или без них) и дополнительными ограничениями, который возвращает те же результаты запроса"

"возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)"

Все случаи применения CROSS JOIN, которые я встречал, именно для того и нужны, чтобы произвести дубликаты. А в статье предлагается их просто выкинуть, потеряв их основной функционал. Это не оптимизация, это ерунда какая-то.

Я бы понял, если бы предложили замену без потери функционала. LATERAL VIEW хотя бы...

Согласен, что для произвольного запроса убрать CROSS JOIN невозможно, но в статье рассматриваются три частных случая и есть условия их применимости, не предлагается выкинуть любой CROSS JOIN. Такое впечатление, что в комментарии не дубликаты имеются в виду, а декартово произведение, полный перебор. Три случая в статье относятся к особым видам запросов, где действительно можно убрать CROSS JOIN. Здесь я проиллюстрировал два запроса: первый попадает под один из описанных случаев, второй нет, и так и остаётся CROSS JOIN.

Кроме того, в реляционной алгебре нет дубликатов (т.к. идёт работа со множествами), но декартово произведение всё равно используется, т.е. декартово произведение без дубликатов (другого декартового произведения условно и нет в реляционной алгебре) и CROSS JOIN без дубликатов как минимум не лишены смысла, поэтому с учетом того, что проектов и кейсов много, и здесь наверно тоже какие-то частные случаи имеются в виду, и этот кейс не противоречит кейсам из статьи

 CROSS JOIN, которые я встречал, именно для того и нужны, чтобы произвести дубликаты

Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Публикации

Истории