Диалекты SQL: что меняется в ClickHouse, BigQuery и MySQL

Ильва · геймдизайнер баланса «Эпохи Корон»

Логи матчей переехали в ClickHouse, а старые отчёты остались в PostgreSQL. Мне прислали запрос из ClickHouse с countIf и argMax. Переведи его, пожалуйста, на наш SQL, хочу сверить цифры. И объясни, что ещё там по-другому.

В уроке про инструменты мы учились узнавать диалект по куску запроса. Сегодня учимся переводить. Аналитик постоянно живёт между базами: база продукта на PostgreSQL или MySQL, хранилище на ClickHouse или BigQuery, и отчёт, написанный для одной, приходится переносить на другую. В прошлом уроке мы поняли, почему аналитические базы быстрые; теперь разберёмся, чем отличается их язык.

Один язык, разные говоры

Русский язык в Архангельске и в Краснодаре один, но одни говорят «голик», другие «веник», и приезжему приходится переспрашивать. SQL так же: SELECT, FROM, WHERE, GROUP BY, JOIN везде одинаковые, а функции, работа с датами и мелкие правила у каждой базы свои. Такой говор называют диалектом.

Хорошая новость: отличий не сотни. Аналитику хватает словаря на две страницы.

Словарь переводчика

Условные агрегаты. В PostgreSQL условие пишут в FILTER. В ClickHouse к агрегату приклеивают суффикс -If, в BigQuery есть COUNTIF, в MySQL условие складывают как число.

смысл	PostgreSQL	ClickHouse	BigQuery	MySQL
победы	count(*) FILTER (WHERE result = 'win')	countIf(result = 'win')	COUNTIF(result = 'win')	SUM(result = 'win')
сумма по условию	sum(x) FILTER (WHERE c)	sumIf(x, c)	SUM(IF(c, x, 0))	SUM(CASE WHEN c THEN x END)
среднее по условию	avg(x) FILTER (WHERE c)	avgIf(x, c)	AVG(IF(c, x, NULL))	AVG(CASE WHEN c THEN x END)
уникальные	count(DISTINCT p)	uniqExact(p)	COUNT(DISTINCT p)	COUNT(DISTINCT p)

В ClickHouse есть и uniq(p): это приближённый счётчик. На миллиардах строк он в разы быстрее, но может отличаться от точного на доли процента. Сверяешь цифры с PostgreSQL: переводи в uniqExact.

Даты. Начало месяца в PostgreSQL date_trunc('month', started_at), в ClickHouse toStartOfMonth(started_at), в BigQuery DATE_TRUNC(DATE(started_at), MONTH) с обратным порядком аргументов, в MySQL DATE_FORMAT(started_at, '%Y-%m-01'). Учти тип: toStartOfMonth возвращает дату, а date_trunc в PostgreSQL возвращает отметку времени. Чтобы колонки совпали, добавь ::date.

Значение при максимуме. В ClickHouse argMax(faction_id, started_at) значит «фракция из строки с самой поздней датой». В PostgreSQL такой функции нет, её заменяют DISTINCT ON или упорядоченным массивом:

SELECT DISTINCT ON (mp.player_id)
       mp.player_id, mp.faction_id AS last_faction
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
ORDER BY mp.player_id, m.started_at DESC;

DISTINCT ON (player_id) оставляет первую строку каждого игрока в порядке ORDER BY, то есть самую позднюю. Похожую задачу мы решали окнами в уроке про FIRST_VALUE.

Деление. Ловушка, которая молча меняет числа. В PostgreSQL 7 / 2 = 3: целые делятся нацело. В ClickHouse и BigQuery / всегда даёт дробь, 3.5. В MySQL тоже 3.5000. Запрос «доля побед» wins / games в ClickHouse вернёт 0,5, а тот же текст в PostgreSQL вернёт 0. Переводя в PostgreSQL, ставь ::numeric у делимого; переводя из PostgreSQL, помни, что intDiv в ClickHouse и DIV в BigQuery и MySQL делят нацело.

Мелочи синтаксиса. ClickHouse разрешает count() без звёздочки и LIMIT 3 BY faction_id (топ-3 в каждой группе одной строкой). BigQuery берёт имена таблиц в обратные кавычки: `project.dataset.table`. MySQL до сих пор не умеет FULL OUTER JOIN, его собирают из двух LEFT JOIN с переставленными таблицами, склеенных через UNION.