SQL для анализа данных. 7 аспектов SQL, которые следует знать специалисту по работе с данными. - UPROGER

Пост содержит в себе все ключевые элементы SQL, которые должен знать каждый специалист по Data Science. Этой статьей мы начинаем цикла статей – SQL для анализа данных.

Вступление

В следствие того, что мир постоянно прогрессирует, множество компаний начинают больше работать с данными. Огромное количество информации, которое собирают эти компании, хранится в специализированных базах данных. Управление, анализ и обработка этих данных осуществляется с помощью «Системы управления базами данных». В результате этого, сфера Data Science стала одной из наиболее развивающихся областей с многочисленными возможностями трудоустройства. Специалисту по работе с данными необходимо извлекать их из специализированной базы, и именно здесь вступает в игру такой язык программирования, как SQL. Наличие навыка SQL является одним из самых необходимых требований к любому специалисту сферы Data Science. А теперь вопрос: действительно ли мне нужно мастерски овладеть SQL, чтобы стать хорошим специалистом в Data Science?

@sqlhub – разбор реальных задач с SQL собеседований в нашем телеграм канале.

Наш ответ – «НЕТ», но требуются базовые познания SQL, ведь этот SQL стал стандартом для большинства систем баз данных. Цель этой статьи заключается в том, чтобы упомянуть все ключевые элементы SQL, которые вам необходимо знать и которые рекомендуются специалистами в области Data Science.

Зачем нужны знания SQL в сфере Data Science?

SQL расшифровывается как Язык Структурированных Запросов и предназначен для управления базами данных. Но что же делает его таким уникальным и одним из самых востребованных навыков в Data Science? Ниже приведены некоторые аспекты, которые помогут вам понять его важность:

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

Упрощает понимание данных: SQL очень удобен для навигации по содержимому базы данных. Это помогает вам более эффективно разобраться во всех особенностях.

Лёгок в освоении: Это идеальная точка старта для начинающих. SQL имеет простой синтаксис, крайне похожий на английский язык, вследствие чего полезную информацию можно получить всего лишь из нескольких строк кода.

Возможность обработки больших массивов с данными: SQL позволяет организованно управлять огромным объёмом данных, что делает его великолепным выбором для сферы Data Science.

Совместимость с другими языками программирования и приложениями: Интеграция SQL с такими языками программирования, как Python, C++, R и т. д. очень удобна. Также он поддерживает такие инструменты бизнес-аналитики и визуализации данных, как Power BL и Tableau, что заметно упрощает процесс разработки.

1) Понимание основных команд

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

– извлекать данные из базы данных;

– удалять повторяющиеся строки и показывать только уникальный контент;

– фильтровать записи и показывать только те, в которых выполняется какое-либо условие;

– не выполнять запрос, если условие не соблюдается (с помощью команд While, AND и OR);

– сортировать данные в порядке возрастания и убывания;

– группирует схожие данные;

– с помощью SQL можно создавать новые фильтры, в следствии чего фильтровать данные;

– можно использовать встроенные функции COUNT(), MAX(), MIN(), AVG() и SUM() для выполнения операция с данными.

Давайте рассмотрим пример, таблицу Employee,

SQL для анализа данных. 7 аспектов SQL, которые следует знать специалисту по работе с данными.

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

SELECT Department,
       AVG(Salary)
FROM Employees
WHERE Gender="F"
GROUP BY Department
HAVING Department = "Admin";

Вывод:

Admin | 17500.0

2) Владение условными операторами

Также очень полезными и удобными в языке SQL являются условные операторы, такие как IF, THEN или ELSE. Давайте взглянем на их синтаксис:

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

Этот запрос выполняет инструкцию по-порядку и возвращает значения, пока условие истинно. Если условие становится ложным, то срабатывает блок ELSE. При его отсутствии в результат возвращается NULL.

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

SELECT student_name,
       marks,
       CASE
           WHEN marks >= 85 THEN 'A'
           WHEN marks >= 75
                AND marks < 85 THEN 'B+'
           WHEN marks >= 65
                AND marks < 75 THEN 'B'
           WHEN marks >= 55
                AND marks < 65 THEN 'C'
           WHEN marks >= 45
                AND marks < 55 THEN 'D'
           ELSE 'F'
       END AS grading
FROM Students;

3) Подзапросы

Любому специалисту в области Data Science необходимо иметь понятие о том, что такое подзапросы, зачем они нужны и как ими правильно пользоваться. Синонимами подзапросов являются внутренние или вложенные запросы. Подзапрос всегда должен быть заключён в круглые скобки, при этом выполняться он будет перед главным (основным) запросом. Если он вернёт более одной строки (многострочный запрос), то с ним должны использоваться многострочные операторы.

Представим такую ситуацию: страховая компания вводит новый полис и отменяет возможность страхования тех людей, кому больше 80-и лет. С помощью подзапросов это можно сделать так:

DELETE

FROM INSURANCE_CUSTOMERS

WHERE AGE IN

    (SELECT AGE

     FROM INSURANCE_CUSTOMERS

     WHERE AGE >80);

Внутренний запрос группирует всех людей, чей возраст достигает более 80 лет, а затем удаляет их из базы данных.

4) Джоины

ДЖоины в SQL используется для объединения строк, которые схожи по каким-то общим признакам. Ниже будет указано 4 примера присоединения:

1. Inner Join. Оно показывает нам данные из таблицы только в том случае, если условие связывания соблюдаются.

SELECT Student.Name

FROM Student

INNER JOIN Sports ON Student.ID =Sports.ID;

Эта программа возвращает имена студентов в том случае, если их id категории «Sports» совпадает с id категории «Students».

Left Join. При использовании этого типа присоединения, мы видим все записи из левой части таблицы, тогда как правая часть будет присоединена по возможности.

SELECT Student.Name

FROM Student

LEFT JOIN Sports ON Student.ID =Sports.ID;

Right Join. Этот тип присоединения полностью противоположен предыдущему.

SELECT Student.Name

FROM Student

RIGHT JOIN Sports ON Student.ID =Sports.ID;

Full Join: Этот тип присоединения содержит все строки, и если в нём нет соответствующей записи, то он возвращает значение NULL.

SELECT Student.Name

FROM Student

FULL JOIN Sports ON Student.ID =Sports.ID;

5) Хранимые процедуры

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

CREATE PROCEDURE SelectStudents @Major nvarchar(30),
                                       @Grade char(1) AS
SELECT *
FROM Students
WHERE Major = @Major
  AND Grade = @Grade GO;

EXEC SelectStudents @Major = 'Data Science',
                    @Grade = 'A';

Этот код позволяет нам отбирать студентов разных специальностей, опираясь на их оценки. Например: нам нужно выбрать всех студентов специальности Data Science, имеющих оценку A. Стоит заметить, что CREATE PROCEDURE идентична объявлению функции и вызывается она с помощью команды EXEC.

6) Форматирование строк

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

1. CONCAT. Используется для сложения двух или более строк.

SELECT CONCAT(Name, ' has a major of  ', Major)
FROM Students
WHERE student_Id = 37

2. SUBSTR. Данная функция позволяет извлекать подстроку из строки.

SELECT student_name,admission_date,

     SUBSTR(admission_date,4,2) AS day

FROM Students

3. TRIM. Функция TRIM удаляет указанные символы (или слова) из начала или конца строки.

SELECT age,

   TRIM(trailing ' years' FROM age)

FROM Students

Если бы у нас была строка «26 years», то после использования функции сверху, программа возвращала бы только «26».

4. INSERT. Функция INSERT позволяет добавить одну или несколько строк в таблицу. Вам необходимо указать место, куда перезапишется строка, и её содержание.

SELECT INSERT("OldWebsite.com",1,9,"NewWebsite");

5. COALESCE. Эта функция используется для замены нулевых значений на те, которые укажут сами пользователи.

SELECT COALESCE (NULL, NULL,10,'John’')

7) Оконные функции

Оконная функция в SQL – функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. Они делятся на 3 типа:

1. Агрегатные функции. Они возвращают значения, которые получились при выполнении какого-либо взаимодействия между данными. Например: VG(), COUNT(), MAX(), MIN(), SUM() и другие.

SELECT name,

       AVG(salary) over (PARTITION BY department) 

FROM Employees;

Результатом этой программы становится средняя заработная плата различных отделов из таблицы сотрудников.

2. Функции значений. Эти функции используются для присвоения строкам значений из других строк. Например: LAG(),LEAD(), FIRST_VALUE(), LAST_VALUR() и NTH_VALUE().

SELECT 

         bank_branch, month, income,

        LAG(income,1) OVER (

                PARTITION BY bank_branch

                ORDER BY month

        ) income_next_month

FROM Bank;

В этой программе идёт сравнение дохода разных банков за текущий месяц с предыдущим.

3. Функции ранжирования. Они используются для присваивания классов строкам, исходя из предопределённого порядка. Например: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(),NTILE().

SELECT

        product_name, price,

        RANK () OVER ( 

                ORDER BY list DESC

        ) price_hightolow

FROM Products;

Товары классифицируются на основании их цен с помощью функции RANK().

Заключение

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