Кирилл Гуськов product практик

Практикум по SQL - агрегатные и оконные функции

Продолжение темы недвижимости и застройщиков - вид сверху

Мотивация

Открытых данных становится всё больше. Значит ли это что больше становится возможностей для принятия решений на основе этих данных? Будем надеяться!

Чтобы принимать решения на основе данных, надо данных анализировать, или хотя бы преобразовывать. Рассмотрим небольшой пример работы с данными с помощью SQL. Можно также ознакомиться с предыдущим эссе на эту тему. https://vc.ru/u/586739-kirill-guskov-product-praktik/637402-chto-takoe-sql-i-kak-rabotaet-select

Краткое описание задачи

Допустим, вы не первый, кому пришла идея поработать с общедоступными данными - “все мы стоим на плечах…” предшественников. Не всегда они являются “гигантами”. Поэтому, возьмём написанный кем-то SQL-запрос (скрипт) и попытаемся понять насколько хорошим инструментом для преобразования и анализа данных он является.

Итак, есть описание полей таблицы (см. раздел Бизнес-требования к отчёту) и скрипт (см. Приложение 1). Опишем результат работы скрипта, восстановим требования к отчёту (результату работы скрипта) и укажем на возможные ошибки в скрипте.

Результат работы скрипта

(см. также комментарии к скрипту в Приложении 1)

На выходе получаем рейтинг регионов по суммарной площади строящихся многоквартирных домов.

Каждому региону соответствует запись, содержащая следующие поля:

  • Порядковый номер (рейтинг) региона по убыванию суммарной площади
  • Если окажется, что у нескольких регионов одинакова суммарная площадь, то у них будет одинаковый рейтинг. Внутри группы регионов с одинаковым рейтингом никакого критерия для ранжирования регионов не предусмотрено.Не гарантируется сортировка записей по рейтингу (из-за использования сортировки (SORT BY) внутри window aggregate function dense_rank). Для гарантированной сортировки итоговой таблицы необходимо использовать сортировку на уровне запроса (SELECT) после FROM.
  • Название региона
  • Общая площадь по региону
  • Количество девелоперов в регионе
  • Количество девелоперов с более чем одним объектом в регионе
  • Название девелопера с максимальной площадью в регионе
  • Не обрабатывается возможность наличия в одном регионе двух и более девелоперов с одинаковыми площадями. Вероятность не велика, но всё же...В этом случае алгоритм будет работать некорректно, случайно выбирая девелопера (из тех у кого одинаково максимальная площадь в регионе) в зависимости от последовательности записей в исходной таблице и особенностей работы ORDER BY
  • Доля самого крупного (по площади) девелопера в регионе в %
  • Для данного пункта также справедливы замечания, изложенные в предыдущем

Эту таблицу можно назвать regional.rating_details.

Содержимое этого отчёта перекликается с содержимым страницы наш.дом.рф/аналитика/показатели_жилищного_строительства

Бизнес-требования к отчёту

  • Используя данные по строящимся объектам (многоквартирные дома) составить рейтинг регионов по объёму строительства.
  • Рейтинг должен быть отсортирован по убыванию объёма
  • В рейтинге должна быть отражена структура рынка (по участникам) в регионе
  • (Нефункциональные требования)
  • Минимизировать объём хранимых данныхМинимизировать время выполнения

Системные требования к отчёту

Исходные данные: список строящихся объектов с указанием площади и привязкой к девелоперу и региону

Объекты оценки (рейтинга): регионы

Критерий построения (и сортировки) рейтинга: общая площадь строящихся в регионе объектов

Требуемая информация о структуре рынка в регионе:

  • Количество девелоперов, строящих хотя бы один объект
  • Количество девелоперов, строящих более одного объекта
  • Самый крупный (по площади) девелопер
  • Доля самого крупного девелопера

Варианты развития идеи отчёта

  • Если дорабатывать рейтинг регионов, то можно добавить:
  • Лидера по количеству объектовРассчитав средние и медианные величины метража и количества объектов на девелопера по региону, можно добавить соответствующие количественные описания структуры рынкаМожно также рассчитать общенациональные рейтинги региональных лидеров для понимания рыночности конкуренции и\или перспектив развития рынка в регионе
  • Интересно посмотреть на рейтинг девелоперов с детализацией по региональному присутствию
  • Можно добавить справочник средних по каждому региону цен за квадратный метр и тогда можно будет составить рейтинг регионов по объёму строительства в денежном выражении
  • Если обогатить исходные данные параметрами объектов (тип; этажность; расположение - район, город, район;...) и информацией об этапе строительства, то можно будет построить многопараметрический рейтинг и более детально описать рынок в каждом регионе

Анализ структуры скрипта и алгоритма преобразования данных

Скрипт представляет собой одну SQL команду - SELECT запрос. До его выполнения строится (WITH) вспомогательный агрегат, который представляет собой таблицу (совокупность строк), содержащую сгруппированные по региону показатели девелоперов в данном регионе - суммарные площади и количество объектов. Записи пронумерованы (рейтинг/ранг девелопера в регионе) по убыванию площади девелопера внутри региона. Сортировка строк на выходе возможна, но не гарантирована.

Количество записей исходной таблицы соответствует общему количеству строящихся объектов. Количество записей агрегата соответствует сумме по всем регионам количества девелоперов, работающих (строящих хотя бы один объект) в регионе, что в общем случае существенно меньше общего количества объектов. Размеры таблиц могут быть равны только если каждый девелопер строит в каждом регионе не больше одного объекта. (см. замечания к разделу Результат работы скрипта)

Вспомогательный агрегат присоединяется (LEFT JOIN) к исходной таблице по соответствию (ON) идентификаторов региона и девелопера. В результате на вход основного запроса SELECT подаётся промежуточная таблица с таким же количеством строк что и исходная, при этом содержащая дополнительные столбцы с агрегатами по связке регион-девелопер, которые повторяются в каждой строке с одинаковыми значениями связки. Некоторые столбцы при этом дублируются.

Результатом выполнения основного SELECT являются записи (их количество соответствует количеству регионов), сгруппированные по имени региона и содержащие рейтинг региона и информацию о состоянии рынка в каждом регионе (см. раздел Результат работы скрипта). Сортировка строк на выходе возможна, но не гарантирована. (см. замечания к разделу Результат работы скрипта)

Замечания к скрипту

(см. также комментарии к скрипту в Приложении 1)

Структура исходных данных

Не очень рационально работать с таблицей, содержащей как идентификаторы, так и названия объектов. Для этого принято использовать справочники. В данном случае удобно было бы иметь справочники регионов и девелоперов. Это позволило бы уменьшить объём хранимых данных и скорость их обработки

Кроме того, это позволило бы избежать, например, использования aggregate function max для передачи названия девелопера в промежуточный (WITH) агрегат (см. ниже раздел Синтаксис,…).

Быстродействие

  • Про пользу от использования справочников уже упомянули в пункте Структура данных. Объем обрабатываемых данных сказывается на быстродействии и использовании вычислительных ресурсов.
  • Размер обрабатываемых таблиц (в том числе и промежуточных) негативно сказывается на быстродействии и расходе вычислительных ресурсов. Объединение (LEFT JOIN) на входе в основной SELECT исходной таблицы с агрегатом (WITH) избыточно увеличивает размер обрабатываемых данных. Это делается для расчёта региональных агрегатов, который можно было бы сделать иначе.
  • Увеличить быстродействие также можно отказавшись от избыточных повторяющихся вычислений. Например, в window function row_number не стоит каждый раз считать суммарную площадь (sum) по девелоперу. Тем более, что non-window aggregate functions выполняются до window functions и необходимое значение уже посчитано (с учётом использования sort_specification group by 1,2 которая выполняется перед выполнением select_list).

Алгоритм преобразования данных

  • Создание промежуточной (WITH) таблицы для группировки данных по регионам и рейтинга внутри каждого региона оправданно, но могло бы быть сделано с использованием других возможностей и использовано более экономично.
  • Создание входной таблицы (FROM) на основе левого объединения (LEFT JOIN) исходной таблицы с промежуточной неоправданно с точки зрения логики вычислений и сильно увеличивает объём входных данных запроса, что усложняет его логику и уменьшает скорость выполнения.
  • Условие сравнения значений столбцов с одинаковыми названиями лучше описывать с помощью USING. Это делает код более читаемым, логику более прозрачной и уменьшает объём обрабатываемых запросом данных, избегая дублирования столбцов в результате использования ON. В данном случае вместо USING можно использовать NATURAL.

Синтаксис, стиль и форматирование кода

  • Агрегатная функции max(t.dev_name) при наличии группировки по dev_id служит для обхода ограничений, накладываемых использованием GROUP BY. При использовании справочника девелоперов (см. п. Структура данных) этого можно было бы избежать.
  • Использование distinct в count(distinct t.obj_id) избыточно, поскольку obj_id - уникальный ключ исходной таблицы.
  • Вычислять агрегат sum(t.obj_square) в группировке по региону и девелоперу (то есть, суммарную площадь объектов по каждому девелоперу в каждом регионе) можно было бы один раз вместо двух.
  • В использовании выражения со всегда верным условием where 1=1 наверное есть какой-то (глубокий) смысл, но это не очень хороший стиль.
  • Указание номеров столбцов из select_list в GROUP BY не очень информативно и может привести к ошибкам при редактировании и модификации запроса. Кроме того, это особенность PostgreSQL, не входящая в стандарт SQL.
  • Значение агрегата sum(t.obj_square) в группировке по региону (то есть, суммарная площадь объектов в регионе) в основном запросе вычисляется три раза. Этого можно было бы избежать, вычислив данный агрегат, например, с помощью субзапроса в WITH или FROM если требуется сохранить столбец с рейтингом на первом месте.
  • Способ определения (выбора) девелопера с максимальной площадью max(d.dev_name) filter(where d.rn=1) будет давать непредсказуемый результат в случае когда максимальные показатели по площади в регионе имеет более одного девелопера. Не говоря уже об использовании агрегатной функции max (см. выше).
  • Вместо ON при объединении (LEFT JOIN) таблиц логичнее в данном случае было бы использование USING или NATURAL. Это бы в частности избавило бы от дублирования одинаковых столбцов, что уменьшило бы объем обрабатываемых данных, увеличив быстродействие.
  • Группировка (GROUP BY 2) по названию региона, а не по его идентификатору может привести к искажению результата при наличии ошибок в данных (дисперсия написаний названий регионов). Как отмечено выше (см. раздел Структура данных), рекомендуется использование справочников сущностей.
  • Возможно форматирование кода было бы более читаемым, если бы код (см. Приложение 1) анализировался бы в специализированном редакторе. Поэтому комментарии по этому поводу опустим.

Резюме

Данный скрипт строит требуемый отчёт (с поправкой на замечания, которые изложены в пункте Результат работы скрипта). Учитывая также замечания из раздела Замечания к скрипту, можно считать нефункциональные требования не выполненными.

Для выполнения нефункциональных требований необходимо доработать:

  • Структуру исходных данных (например, использование справочников)
  • Алгоритм и последовательность (конвейер) преобразования данных
  • Логику вычисления и использования промежуточных агрегатов (чтобы избежать повторных вычислений). Например, за счёт более широкого использования window aggregate functions и subqueries.
  • Подход к выбору используемых инструментов SQL для более рационального использования их возможностей (см. замечания выше)

Приложение 1. Скрипт с комментариями

with d as(

select

t.region_id

,t.dev_id

–(!костыль) агрегатная функция max используется для того чтобы протянуть название девелопера в выходную таблицу

,max(t.dev_name) as dev_name

--количество объектов по каждому девелоперу в каждом регионе

,count(distinct t.obj_id) as obj_cnt

--суммарная площадь объектов по каждому девелоперу в каждом регионе

,sum(t.obj_square) as obj_sq

--номер строки (рейтинг девелопера) в каждой группе (partition) по региону по убыванию площади (если найдутся девелоперы с одинаковыми площадями в данном регионе, то порядок их следования внутри группы будет произвольным)

,row_number () over(partition by t.region_id order by sum(t.obj_square) desc) as rn

from sandpit.test_table t

where 1=1

group by 1,2

)

select

–номер (рейтинг) региона по убыванию суммарной площади без пропусков (если найдутся регионы с одинаковыми площадями, они получат одинаковые номера, а порядок следования регионов внутри группы будет произвольным)

dense_rank () over(order by sum(t.obj_square) desc) as rank_num

,t.region_name

–общая площадь по региону

,sum(t.obj_square) as square

–количество девелоперов в регионе

,count(distinct t.dev_id) as dev_cnt

–количество девелоперов с более чем одним объектом (?в регионе)

,count(distinct d.dev_id) filter(where d.obj_cnt>1) as dev_m1_cnt

–название девелопера с максимальной площадью

,max(d.dev_name) filter(where d.rn=1) as dev_top_name

–доля самого крупного (по площади) девелопера в регионе в %

,max(d.obj_sq) filter(where d.rn=1) / sum(t.obj_square) * 100 as dev_top_rt

from sandpit.test_table t

left join d on d.region_id = t.region_id and d.dev_id = t.dev_id

where 1=1

group by 2

;