Прежде всего, прежде чем приступить к изучению SQL, нам нужно знать, какие инструменты позволяют нам выполнять наши запросы. Есть много возможностей, которые можно купить или бесплатно. Различные облачные хранилища данных, такие как Databricks, Snowflake, Amazon RedShift или BigQuery, предоставляют консоли SQL для компиляции. Кроме того, платформы интеллектуального определения местоположения, такие как Epsilon Metrics, предоставляют вам консоль SQL, которая позволяет запускать пространственный SQL. Если вы просто хотите использовать бесплатные инструменты, для начала будет достаточно dBeaver или mySQL. В этом курсе мы использовали платформу Epsilon Metrics для выполнения наших запросов.

Что касается данных, которые мы будем использовать для выполнения упражнений, их можно загрузить, нажав на них:

• Розничные магазины, которые будут использоваться во всех упражнениях.
• Аэропорты Флориды, чтобы классифицировать магазины в зависимости от расстояния до аэропортов в целях логистики.

Обзор данных и мой первый запрос — SELECT

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

SELECT * 
FROM myTable

В результате мы можем видеть часть полученной таблицы ниже:

SELECT column1_name, column2_name,etc 
FROM myTable

Теперь с названиями наших столбцов

SELECT storetype, store_id, revenue, size_m2  
FROM common.retail_stores

Фильтрация моих данных — WHERE

Предположим, что наша цель — проверить магазины, выручка которых превышает 1,5 млн. Для этого мы можем добавить оператор WHERE в конце нашего запроса с желаемым условием.

SELECT storetype, store_id, revenue, size_m2  
FROM common.retail_stores
WHERE revenue > 1500000

Используя этот фильтр, мы фокусируемся на меньшем количестве строк. В нашей исходной таблице было 11 966 магазинов, а после использования WHERE у нас есть 5 991 магазин.

Организация моих данных — ORDER BY

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

SELECT storetype, store_id, revenue, size_m2
FROM common.retail_stores
WHERE revenue > 1500000
ORDER BY revenue

SELECT storetype, store_id, revenue, size_m2
FROM common.retail_stores
WHERE revenue > 1500000
ORDER BY revenue DESC

Ниже часть результата нашей таблицы:

SELECT storetype, store_id, revenue, size_m2
FROM common.retail_stores
WHERE revenue > 1500000
ORDER BY revenue ASC

Вы получите ту же таблицу, что и при использовании только ORDER BY имя_столбца. Как насчет упорядочения разных столбцов по доходу и размеру?

SELECT storetype, store_id, revenue, size_m2
FROM common.retail_stores
WHERE revenue > 1500000
ORDER BY revenue ASC, size_m2 ASC

В результате мы упорядочили наши данные следующим образом: сначала упорядочили по выручке (по возрастанию), а затем по размеру (по возрастанию). Если бы мы сначала написали размер, а доход во втором порядке, результат запроса был бы другим.

Существуют различные типы агрегатных функций SQL:

• count()
• sum()
• avg()
• min()
• max()

Вы можете использовать их, чтобы получить некоторые ключевые показатели эффективности из вашего набора данных. Сколько магазинов у меня в таблице?

SELECT COUNT(store_id)
FROM common.retail_stores

В результате запроса вы получите одно значение:

Каков самый высокий доход, который мы получаем с одним единственным магазином?

SELECT max(revenue) 
FROM common.retail_stores

Минимальный размер 100м². Мы видели, как легко использовать приведенные выше агрегатные функции. Что, если ваш вопрос заключается в том, сколько магазинов у меня есть в каждой категории магазинов? Давайте посмотрим на приведенный ниже запрос, используя оператор COUNT:

SELECT COUNT(store_id) as nstores, storetype
FROM common.retail_stores
WHERE size_m2 < 500
GROUP BY storetype
ORDER BY nstores DESC;

Результат нашего запроса:

Подытожим то, что мы наблюдаем в приведенном выше запросе:

• Мы используем COUNT() для агрегирования наших данных.
• При добавлении AS после такой операции, как count, мы даем имя новому столбцу с количеством магазинов nstores. Мы фильтруем (WHERE), собираем наши магазины по типу (GROUP BY) и упорядочиваем наш результат (ORDER BY).
• Важен порядок операторов: WHERE — GROUP BY — ORDER BY

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

SELECT SUM(revenue) as trevenue, storetype
FROM common.retail_stores
WHERE size_m2 < 500
GROUP BY storetype
ORDER BY trevenue DESC

Как результат:

Основное определение состоит в том, что он похож на обычный SQL с дополнительным типом данных (ГЕОМЕТРИЯ И ГЕОГРАФИЯ) и функциями, которые с ним работают (их имена обычно начинаются с ST, что означает пространственный тип). Однако мы можем извлечь выгоду не только из дополнительного типа данных. Есть несколько преимуществ использования пространственного SQL:

• Мы можем работать с более крупными большими данными в SQL, изначально работающем в различных облачных хранилищах данных (BigQuery, RedShift, Snowflake…)
• Обеспечьте большую эффективность рабочих процессов аналитики.
• Визуализация наших данных может очень помочь нам принять правильное решение. Некоторые расширения, такие как GeoViz от BigQuery или платформы Location Intelligence, такие как Epsilon Metrics, позволяют визуализировать наши данные благодаря типам геометрии или географии.

Прежде чем мы начнем изучать пространственный SQL, давайте определим новые типы данных, представленные выше, GEOMETRY и GEOGRAPHY:

• GEOMETRY: область или местоположение определяется с помощью системы координат или плоского представления земли.
• GEOGRAPHY: данные определяются в сферических координатах на Земле. Чтобы упростить это, мы могли бы сказать, что геометрия представляет землю плоскостным образом, тогда как география представляет землю более реалистичным образом, поскольку она принимает во внимание Землю как то, чем она является, сферой. Теперь давайте поиграем со столбцом geom (тип GEOGRAPHY) наших данных и визуализируем его на платформе Epsilon Metrics (Конструктор).

SELECT storetype, store_id, revenue, size_m2, the_geom,
    the_geom_webmercator, cartodb_id
    FROM common.retail_stores
    WHERE revenue > 1500000
    

Сравнивая наш первый результат запроса с обычным SQL, мы только что добавили столбец с именем the_geom, который содержит тип POINT и обязательные служебные столбцы the_geom_webmercator, cartodb_id. Каждый тип точки содержит координаты каждого из наших магазинов.

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

Давайте сосредоточимся на штате FLORIDA, для этого мы можем добавить еще одно условие, используя WHERE.

SELECT storetype, store_id, revenue, size_m2, the_geom,
    the_geom_webmercator, cartodb_id
    FROM common.retail_stores
    WHERE size_m2 < 500 AND area = 'Тверской район'
    

Вы можете настроить свою карту по-разному, взгляните на этот курс.

Ваш первый пространственный SQL-анализ

Как упоминалось ранее, многие облачные хранилища данных поддерживают пространственный анализ. В этом документе мы просто представим две очень распространенные пространственные функции SQL: ST_BUFFER и ST_INTERSECTION.

ST_BUFFER

Одна простая функция преобразования называется ST_BUFFER (geography,radius_m). Обычно ввод географии представляет собой тип точек, и при добавлении радиуса вокруг них география на выходе будет полигоном. Например, на изображении ниже мы наблюдаем расположение некоторых магазинов (точек), доступных в нашем наборе данных:

После использования функции ST_BUFFER с аргументом радиусом 100 метров мы получаем полигоны, как показано ниже:

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

select *, st_transform(the_geom, 3857) as the_geom_webmercator, store_id as cartodb_id
    from (
    SELECT storetype, store_id, revenue, size_m2, st_buffer(the_geom::geography,100)::geometry as the_geom 
    FROM common.retail_stores
    WHERE size_m2 < 500 AND area = 'Тверской район'
    ) a
    

Ниже представлен еще один пример, в котором мы можем использовать как ST_BUFFER, так и ST_INTERSECTS.

В настоящее время почти все покупают в Интернете различные виды товаров: электронику, книги, одежду, обувь или косметику и средства личной гигиены. Эти заказы поступают со всего мира, и правильное расположение наших магазинов важно для обеспечения конкурентоспособности. Вот почему в этом упражнении мы предполагаем, что хотели бы знать, какие магазины находятся ближе всего к аэропорту. Ранее мы классифицировали наши магазины по размеру и выручке. Однако расстояние до аэропорта является ключевым моментом. Цель состоит в том, чтобы оптимизировать логистику, чтобы продавать быстрее и дальше. Для этого анализа мы будем использовать таблицу, содержащую расположение аэропортов в Москве. Благодаря ST_BUFFER будет создано 15 км зон влияния. Для этого используем следующий запрос:

select *, st_transform(the_geom, 3857) as the_geom_webmercator
    from (
    SELECT cartodb_id, name, st_buffer(the_geom::geography,15000)::geometry as the_geom 
    FROM common.airports_moscow
    ) a
    

Таким образом, мы получаем 15-километровые буферы вокруг аэропортов (типа полигонов), которые будут использоваться в следующем разделе вместе с нашими магазинами.

ST_INTERSECTS

ST_INTERSECTS(geography_1,geography_2) возвращает логический тип true или false, если между двумя географическими регионами есть какое-либо пересечение. В случае, если наши две географии являются полигонами, мы сосредоточимся на области, общей для обеих географии. Например, если наши полигоны представляют собой два круга, полученные с помощью st_buffer, благодаря st_intersects мы получим область, показанную внутри белых линий:

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

SELECT a.* FROM common.retail_stores a,
    common.airports_moscow_15km_buffer b
    WHERE ST_INTERSECTS(a.the_geom,b.the_geom)
    

Обратите внимание, что я использую новую таблицу с 15-километровым буфером вокруг аэропортов.

Только 1703 магазина находятся рядом с аэропортом (менее 15 км) из 5817 магазинов, расположенных во Москве.