Канал по Машинному обучению
@ai_machinelearning_big_data
Перейти
7 концепций SQL, которые необходимо знать для работы с данными
7 концепций SQL, которые необходимо знать для работы в области науки о данных
В этом посте объясняются все ключевые элементы SQL, которые необходимо знать специалистам в области науки о данных.
By Kanwal Mehreen, Software Engineering Student at NUST on November 18, 2022 in SQL
Введение
По мере цифровизации мира большинство компаний стали ориентироваться на данные. Огромное количество данных, которые они собирают, хранится в базе данных. Управление, анализ и обработка этих данных осуществляются с помощью СУБД (системы управления базами данных). Вследствие этого наука о данных стала одной из наиболее развивающихся областей с бесчисленными возможностями трудоустройства. Специалисту по изучению данных необходимо извлекать данные из базы данных, и именно здесь на помощь приходит SQL. Вы, должно быть, слышали о самых важных навыках работы с данными, необходимых для освоения этой области, и SQL является одним из них. Теперь возникает вопрос: Действительно ли мне нужно владеть SQL, чтобы стать хорошим специалистом по изучению данных?
Ответ – НЕТ, но знание SQL необходимо, поскольку он стал стандартом для многих систем баз данных. В этой статье мы рассмотрим все ключевые элементы SQL, которые необходимо знать и которые рекомендуются специалистами по науке о данных.
Необходимость использования SQL в Data Science
SQL расшифровывается как Structured Query Language и предназначен для управления реляционной базой данных. Давайте сначала разберемся, зачем нужен SQL в науке о данных. Что делает его уникальным и одним из самых востребованных навыков в области науки о данных? Ниже приведены некоторые моменты, которые помогут вам понять его важность:
- Широкое применение: Несмотря на то что языку SQL уже около 40 лет, он используется для формирования запросов в большинстве реляционных систем баз данных и стал стандартным инструментом для экспериментов с данными.
- Упрощает понимание данных: SQL очень удобен для навигации по содержимому базы данных. Он позволяет эффективно разобраться в особенностях.
- Легкость освоения: Это идеальная отправная точка для новичков, так как простой англоязычный синтаксис позволяет извлекать ценные сведения всего из нескольких строк кода.
- Позволяет обрабатывать большие массивы данных: SQL позволяет организованно управлять огромными массивами данных, что делает его идеальным выбором для приложений data science.
- Совместимость с другими языками программирования и приложениями: Интеграция SQL с такими языками, как Python, C++, R и т.д., очень удобна. Он также поддерживает такие инструменты бизнес-аналитики и визуализации данных, как Power BI и Tableau, что несколько упрощает процесс разработки.
Семь концепций SQL
Знание основных команд закладывает фундамент для обучения на протяжении всей жизни. В противном случае вы будете просто запоминать факты, не понимая, как они сочетаются друг с другом. Ниже приведены некоторые из наиболее часто используемых команд SQL:
- SELECT & FROM: для получения атрибутов данных из указанной таблицы.
- SELECT DISTINCT: исключает дубликаты строк и отображает только уникальные записи.
- WHERE: фильтрует записи и отображает только те, которые удовлетворяют заданному условию.
- AND, OR, NOT: не выполняет запрос, если условие не является истинным. Хотя AND и OR используются для применения нескольких условий.
- ORDER BY: сортирует данные в порядке возрастания или убывания.
- GROUP BY: группирует одинаковые данные.
- HAVING: данные, агрегированные с помощью Group By, могут быть дополнительно отфильтрованы.
- Агрегатные функции: агрегатные функции COUNT(), MAX(), MIN(), AVG() и SUM() используются для выполнения операций над заданными данными.
Рассмотрим пример их применения к таблице Employee,
Теперь мы хотим получить среднюю зарплату женщин, работающих в административном отделе.
SELECT Department,
AVG(Salary)
FROM Employees
WHERE Gender="F"
GROUP BY Department
HAVING Department = "Admin";Выход:
Admin | 17500.02) Случай, когда
Это действительно мощный и гибкий оператор в 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Он выполняет операторы по порядку и возвращает значение, как только условие становится True. Если ни одно из условий не выполняется, то выполняется блок ELSE, а если его нет, то возвращается NULL.
Предположим, что у нас есть база данных студентов, и мы хотим выставлять им оценки на основе их оценок, тогда можно использовать следующий SQL-оператор
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) Подзапросы
Для специалиста по исследованию данных знание подзапросов крайне важно, поскольку ему приходится работать с различными таблицами, а результат одного запроса может быть использован повторно для дальнейшего ограничения данных в основном запросе. Это также известно как вложенный или внутренний запрос. Подзапрос должен быть заключен в круглые скобки и выполняется перед основным запросом. Если он возвращает более одной строки, то он называется многострочным подзапросом и с ним необходимо использовать многострочные операторы.
Предположим, что страховая компания вводит новый полис и отменяет страхование людей, чей возраст превысил 80 лет. Это можно сделать с помощью следующего подзапроса:
DELETE
FROM INSURANCE_CUSTOMERS
WHERE AGE IN
(SELECT AGE
FROM INSURANCE_CUSTOMERS
WHERE AGE > 80 );Во внутреннем подзапросе выбираются все клиенты старше 80 лет, а затем для этой группы выполняется операция Delete.
4) Присоединиться к сайту
SQL-соединения используются для объединения строк из нескольких таблиц на основе логической связи между ними. Ниже перечислены 4 типа объединений SQL:
- Внутреннее соединение: внутреннее соединение показывает только те строки из обеих таблиц, которые удовлетворяют заданному условию. В терминологии множеств его можно назвать пересечением.
SELECT Student.Name
FROM Student
INNER JOIN Sports ON Student.ID = Sports.ID;Он возвращает тех студентов, которые зарегистрировались в спортивных секциях. Примечание: спортивный ID совпадает с регистрационным ID студента.
- Left Join: возвращает все записи из таблицы LEFT, а из правой таблицы выводит только совпадающие записи.
SELECT Student.Name
FROM Student
LEFT JOIN Sports ON Student.ID = Sports.ID;- Правое соединение: Это прямо противоположно тому, что делает левое соединение.
SELECT Student.Name
FROM Student
RIGHT JOIN Sports ON Student.ID = Sports.ID;- Полное объединение: содержит все строки из обеих таблиц и, если не имеет соответствующей записи, выводится значение 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 предлагает широкий спектр строковых функций для преобразования и работы со строками. Среди них наиболее часто используются следующие пять:
- CONCAT: используется для сложения двух или более строк.
SELECT CONCAT(Name, ' has a major of ', Major)
FROM Students
WHERE student_Id = 37;- SUBSTR: возвращает часть строки и принимает в качестве параметров начальную позицию и длину возвращаемой подстроки.
SELECT student_name,admission_date,
SUBSTR(admission_date, 4, 2) AS day
FROM StudentsОтдельно появится столбец day, который извлекается из admission_date.
- TRIM: основная задача trim – удаление символов из начала строк, конца строк или обоих, если они указаны. Вы должны указать лидирующий, концевой или оба символа, затем символ, который нужно удалить, и строку, из которой нужно удалить.
SELECT age,
TRIM(trailing ' years' FROM age)
FROM StudentsПри этом “26 лет” будет заменено на “26”.
- INSERT: позволяет вставить строку внутри заданной строки в указанную позицию. При этом необходимо указать позицию и длину новой подстроки, которую вы хотите записать. Обратите внимание, что новая строка будет перезаписывать предыдущий текст.
SELECT INSERT("OldWebsite.com", 1, 9, "NewWebsite"); Он будет обновлен до NewWebsite.come.
- COALESCE: может использоваться для замены нулевых значений на значения, определяемые пользователем, что часто требуется в науке о данных.
SELECT COALESCE (NULL, NULL, 10, 'John’')В результате будет возвращено 10.
7) Функции окна
Оконные функции аналогичны агрегатным функциям, но после вычисления строки не сворачиваются в одну строку. Вместо этого строки сохраняют свою индивидуальность. Они группируются в три основные категории:
- Агрегатные функции: выводят агрегированные значения из числовых столбцов, такие как AVG(), COUNT(), MAX(), MIN(), SUM() и т.д.
SELECT name,
AVG(salary) over (PARTITION BY department)
FROM Employees;Он отображает среднюю зарплату различных отделов из таблицы “Сотрудники”.
- Функции значений: каждому разделу присваиваются некоторые значения с помощью функций окна значений. Некоторые из наиболее часто используемых функций значений – 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;Сравним доходы различных филиалов банка за текущий месяц с предыдущим.
- Функции ранжирования: с их помощью можно присваивать ранги строкам на основе некоторого предопределенного упорядочения.ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), NTILE() – вот лишь некоторые из них.
SELECT
product_name, price,
RANK () OVER (
ORDER BY list DESC
) price_hightolow
FROM Products;Продукты ранжируются по их ценам с помощью функции RANK().
Заключение
Я надеюсь, что вам понравилось читать эту статью, и она дала вам полное представление о том, как много SQL необходимо знать специалисту по исследованию данных.
+1
+1
4
+1
+1
1
+1
1
