SQL - фундаментальный навык для аналитиков данных, но его применение иногда ограничивается базовыми запросами. Однако SQL можно успешно использовать для многих задач предварительной обработки данных, таких как очистка данных и обработка данных, как показано ниже в статье .
Знание SQL считается одним из основных навыков специалиста по данным, потому что большой объем данных встречается в каждой задаче работы в реляционных базах данных. Типичный подход к анализу данных - извлечь данные из баз и провести анализ на R или Python с использованием мощных пакетов / библиотек, доступных в обеих средах. При таком подходе SQL используется только для извлечения данных; Для этого достаточно базовых запросов.
Однако практики знают, что существует длинный путь от обработки исходных данных до комплексного анализа: данные должны быть тщательно подготовлены, а это сложная задача, включающая несколько процессов: очистку помеченных данных, обработку данных или предварительную подготовку и чистку данных. Возникает вопрос о том, в какой среде выполнять этот процесс. Некоторые аналитики предпочитают выполнять весь процесс на R или Python, поскольку оба языка обеспечивают обширную поддержку работы с бд (особенно R), оба языка очень интерактивны, в то время как SQL не хватает классных пакетов / библиотек, которые помогают делать многие операции с данными и выполняются просто однострочно, как в R или Python. С другой стороны, у этого подхода есть несколько недостатков: при работе с очень большими наборами данных и R, и Python могут работать медленно. или сталкиваются с проблемами, а множество пакетов / библиотек (часто с перекрывающейся функциональностью) создает дополнительную сложность в этих средах. Поэтому, по крайней мере, в некоторых случаях необходимо выполнить некоторую очистку данных и звонки в SQL могут быть полезны. Как мы показываем здесь, многие общие задачи могут быть выполнены без излишней нагрузки на SQL. Чтобы проиллюстрировать это, мы приводим пример, в котором основное внимание уделяется идентификации и замене пропущенных значений.
Отсутствующие значения представлены в SQL маркером NULL, но данные не всегда могут быть четко обозначены. Представьте себе набор данных, содержащий таблицу «Пациенты» с информацией о пациентах в медицинском исследовании. Один из атрибутов - это id, идентификатор, а два других - это рост и вес, представляющие, соответственно, рост и вес каждого пациента в начале исследования. Отметим, что отсутствуют некоторые значения веса, обозначенные -1. Затем мы «очищаем» таблицу следующим образом:
Как только это будет сделано, мы можем использовать предикат IS NULL для единообразной обработки всех пропущенных значений: запрос
сообщит нам, сколько именно значений отсутствует. Если пропущено только несколько значений, мы можем удалить неполные данные. Команда SQL
исключит строки (наблюдения), в которых отсутствует вес. Если атрибут не важен для последующего анализа, команда
исключит атрибут из всей таблицы, то есть из всех строк (наблюдений). Если мы предпочитаем вменять недостающие значения, команда:
заменит все пропущенные значения средним из существующих значений. Более сложный подход заключался бы в вменении значения недостающего атрибута из связанного атрибута. Мы можем задаться вопросом, связан ли рост с весом; запрос:
вычислит коэффициент корреляции между двумя атрибутами в системе, в которой существует встроенная функция корреляции. Даже в тех системах, где это не так, SQL позволяет нам просто писать собственные:
потому что функции среднего (avg) и стандартного отклонения (std) присутствуют повсеместно. Если мы считаем корреляцию достаточно высокой, мы можем использовать алгоритм kNN для вывода соответствующих значений.В общем случае SQL позволяет нам вычислять расстояния между элементами набора данных, используя обычные функции расстояния (например, евклидову или другую норму), и сортировать результат по вычисленным расстояниям, чтобы можно было использовать k ближайших соседей для экстраполяции желаемого значение. В этом конкретном примере вычисление очень простое: мы используем абсолютное значение разницы высот в качестве нашего расстояния и средний вес ближайших 5 соседей для нашего нового условного веса:
В этом запросе подзапрос в предложении FROM вычисляет расстояния между двумя отдельными точками данных (пациентами), причем у одной из них отсутствует вес; упорядочивает результат по этому расстоянию (ORDER BY) и сохраняет только 5 ближайших результатов (LIMIT); среднее значение весов этих 5 соседей затем используется для вмененного значения (примечание: связи разрываются произвольно). Мы возвращаем результат для каждой точки данных с пропущенным весом, поэтому мы предоставляем идентификатор такой точки. Этот результат можно использовать во втором запросе, аналогичном второму UPDATE выше, чтобы изменить значения данных в Patient.
Дополнительным преимуществом SQL является то, что он является устоявшимся стандартом для довольно эффективной работы с очень большими наборами данных (напомним, что отдельный оптимизатор запросов анализирует все приведенные выше операторы и определяет наиболее эффективный способ выполнения системой команды SQL с учетом текущих данных. и ресурсы). Возможность выполнить некоторую очистку, обработку и фильтрацию данных перед извлечением данных из базы данных может сделать конвейер данных более простым и эффективным.
Биография: Антонио Бадиа - доцент кафедры компьютерных наук и инженерии Университета Луисвилля. Его исследования баз данных были поддержаны NSF (включая премию CAREER) и привели к более чем 50 публикациям. Он преподает курсы по базам данных и вводные курсы по управлению и анализу данных для специальностей, не связанных с CS. Он является автором книги «SQL for Data Science: Data Cleaning, Wrangling and Analytics with Relational Databases», Springer, из которой извлечен этот пост.