SQL задачка: напишите запрос для обработки больших данных

Артём Гогин

руководитель направления в хранилище данных в Сбербанке

Мы занимаемся обработкой больших данных — петабайтов информации, поэтому моя задачка тоже будет касаться больших данных.

Необходимо написать запрос, который считает среднюю зарплату по городам из таблицы persons (city string, salary int, person_id int).

В начале почти каждый напишет простой запрос:

			select avg(salary), city
from persons
group by city
		

Такой запрос будет прекрасно работать при малых и средних объемах данных.

Если мы работаем с большими данными, следующим следует вопрос: что может пойти не так с этим запросом если мы обрабатываем терабайты информации в распределенной системе?

Хитрость здесь кроется в том, что при работе с big data применяются распределенные вычисления — для выполнения запроса используется одновременно много серверов. Каждый сервер обрабатывает только свою часть входных данных. Для решения задачи требуется знать, как входные данные распределяются по множеству серверов для параллельной обработки. Ответ следующий: каждый сервер содержит всю информацию по одному или более ключей. Это значит, что все записи с одним и тем же ключом должны попасть на один сервер во время вычисления.

В нашем запросе ключом является поле city. Это может натолкнуть нас на мысль, что все входные данные для вычислений распределятся в зависимости от городов. Здесь нужно вспомнить, что одни города намного больше других. В этом запросе все записи по городу Москва, например, будут обрабатываться только на одном сервере и никогда не распараллелятся.

Чтобы это исправить нужно добавить синтетический ключ в группировку:

			select avg(salary), city
from persons
group by city, mod(person_id,10)
		

Теперь запрос распределится по серверам не только по ключу city, но и по значению mod(person_id, 10). Получается, что каждый город теперь распределится на 10 серверов в зависимости от person_id.

В результате этого запроса мы увидим не одну среднюю зарплату на каждый город, а 10 бессмысленных строчек. Чтобы снова вернуться к средней зарплате, нужно соединить все строчки вместе еще раз:

			select avg(avg_salary), city from (
select avg(salary) as avg_salary, city
from persons
group by city, mod(person_id,10))
group by city
		

Однако средняя от средних не даст нам настоящую среднюю. Нужно изменить формулу:

			select (sum(sum_salary)/sum(count_salary)), city from (
select sum(salary) as sum_salary, count(*) as count_salary , city
from persons
group by city, mod(person_id,10))
group by city
		

Теперь мы получили более сложный запрос, который высчитывает среднюю, но сделали это так, что наш запрос отлично параллелится в распределенных вычислениях.