Обобщение данных с помощью SQL

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

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

Джон Тьюки возродил практику исследовательского анализа данных и широко продвигал саму фразу в своей одноименной книге. Один из простейших, но наиболее полезных инструментов, предложенных Тьюки, - это пятизначное резюме. Отдавая должное своей полезности, R имеет единственную команду для получения этой сводки из любого набора данных - fivenum () - который находится в базе R.

Он очень просто состоит из минимума, максимума, медианы, первого квартиля и третьего квартиля переменной. Учитывая, что максимум и минимум всегда были стандартными агрегатами, и нет необходимости использовать сложные статистические распределения или матричную алгебру для подготовки каких-либо значений, можно предположить, что это резюме должно быть легко доступно из любой реализации SQL.

Однако совсем недавно, в начале 2000-х, это был настоящий вздор. К счастью, хотя многие вещи, такие как популярная музыка, с тех пор явно ухудшились (я достиг возраста, который давал мне право водить машину при администрации Клинтона, что сильно коррелирует с периодом, когда популярная музыка звучала для меня лучше всего), как стандартный SQL, так и с тех пор самые крупные реализации представили новые полезные функции.

Оконные функции, добавленные в ANSI SQL в версии 2003, предоставляют ряд полезных опций. Из них PERCENTILE_CONT и PERCENTILE_DISC значительно упрощают, чем раньше, получение полного набора сводных пяти чисел.

Базовый синтаксис этих функций:

PERCENTILE_DISC(int) WITHIN GROUP (ORDER BY numeric)
OVER (PARTITION BY variable)

Эти функции обычно предназначены для использования кем-либо, когда намерение состоит в том, чтобы разделить набор данных на подмножества, определяемые категориальной переменной в наборе данных. Идея сводки из пяти чисел отчасти подразумевает то, что, по крайней мере, на первом проходе мы захотим использовать ее для всего набора данных. Для этого мы просто оставляем предложение OVER пустым - например, ‘OVER (PARTITION BY variable)’ становится ‘OVER ()’.

Следовательно, полный запрос для получения 1-го квартиля, медианы и 3-го квартиля для любой отдельной числовой переменной выглядит следующим образом:

SELECT DISTINCT
  PERCENTILE_DISC(25) WITHIN GROUP (ORDER BY numeric)
  OVER () as Q1_Var
, PERCENTILE_DISC(50) WITHIN GROUP (ORDER BY numeric)
  OVER () as median_var
, PERCENTILE_DISC(75) WITHIN GROUP (ORDER BY numeric)
  OVER () as Q3_var
FROM db.tbl

Этот запрос, очевидно, возвращает три значения - максимальное и минимальное, а главное из пяти цифр, которые необходимо получить, отсутствуют. Однако, поскольку значения PERCENTILE не являются агрегатными функциями, SQL выдаст синтаксическую ошибку, если мы просто добавим min (var) и / или max (var) к нашему оператору Select.

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

WITH extreme_table (var_min,var_max)
AS
 (SELECT
    min(var)
  , max(var)
  FROM table)

SELECT DISTINCT
  var_min
, PERCENTILE_DISC(25) WITHIN GROUP (ORDER BY numeric)
  OVER () as Q1_Var
, PERCENTILE_DISC(50) WITHIN GROUP (ORDER BY numeric)
  OVER () as median_var
, PERCENTILE_DISC(75) WITHIN GROUP (ORDER BY numeric)
  OVER () as Q3_var
, var_max
FROM db.table JOIN extreme_table on 1=1

Этот запрос возвращает одну строку из пяти значений, представляющих сводку из пяти чисел. Тем не менее, мы упоминали выше, что функции SQL PERCENTILE чаще используются с переменной раздела, которая применяется к наборам данных с определенной категориальной переменной, и то же самое можно сказать и о более длительных агрегатных функциях min () и max ( ), которые обычно используются вместе с переменной Group By.

Интуитивно многие аналитики захотят применить пятизначную сводку к различным подмножествам данных. Расширяя вышеупомянутый запрос, это достигается прямым способом путем добавления категориальной переменной как в качестве предложения Group By, так и внутри предложения select в extreme_table, а также путем добавления предложения PARTITION перед присоединением к категории. Результат будет следующим.

WITH extreme_table (var_min,var_max,category)
AS
(SELECT
    min(var)
  , max(var)
  , category
  FROM table
  GROUP BY category)
SELECT DISTINCT
  var_min
, PERCENTILE_DISC(25) WITHIN GROUP (ORDER BY numeric)
  OVER (PARTITION BY category) as Q1_Var
, PERCENTILE_DISC(50) WITHIN GROUP (ORDER BY numeric)
  OVER (PARTITION BY category) as median_var
, PERCENTILE_DISC(75) WITHIN GROUP (ORDER BY numeric)
  OVER (PARTITION BY category) as Q3_var
, var_max
, table.category
FROM db.table JOIN extreme_table on extreme_table.category=table.category

Обычный анализ, полученный на основе пятизначной сводки, представляет собой ощущение перекоса - видя, где медиана расположена в пределах 1-го и 3-го квартилей, и как минимальное и максимальное значения также связаны с этими квартилями. Очевидно, что получение одних и тех же данных по категориальным подмножествам данных является мощным средством, поскольку позволяет сравнивать не только расположение данных с точки зрения медианы, но и формы распределения каждой категории, чтобы понять, как каждая категория концентрирует свои основные данные.

Роберт де Грааф - автор книги The Lazy Data Scientist, доступной через LeanPub. Следуйте за ним в Twitter: https://twitter.com/RobertdeGraaf2