Как SQL используется для эффективной фильтрации и преобразования данных
SQL - это язык, используемый для управления данными в реляционных базах данных. Основным компонентом реляционной базы данных является таблица, в которой хранятся данные в табличной форме с помеченными строками и столбцами.
Мы запрашиваем данные из реляционной базы данных с помощью оператора select SQL. Оператор select очень универсален и гибок с точки зрения операций преобразования и фильтрации данных.
В этом смысле SQL можно рассматривать как инструмент анализа данных. Преимущество использования SQL для преобразования и фильтрации данных заключается в том, что мы получаем только те данные, которые нам нужны. Это более практично и эффективно, чем получение всех данных с последующим применением этих операций.
В этой статье мы рассмотрим 7 примеров, чтобы продемонстрировать, как можно использовать SQL в качестве инструмента анализа данных. Сложность примеров неуклонно увеличивается, поэтому лучше, если вы дойдете до конца.
Ранее я сохранял набор данных медицинских расходов, доступный на Kaggle, в таблицу SQL, называемую страховкой. Давайте сначала посмотрим на таблицу, отобразив первые 5 строк.
mysql> select * from insurance
-> limit 5;
«*» Указывает, что мы хотим отобразить весь столбец, а ключевое слово limit определяет количество отображаемых строк.
Набор данных содержит некоторую личную информацию и сумму, взимаемую за страхование.
Пример 1
Мы можем посчитать среднюю сумму оплаты для курящих и некурящих.
mysql> select smoker, avg(charges)
-> from insurance
-> group by smoker;
+--------+-----------------+
| smoker | avg(charges) |
+--------+-----------------+
| no | 8434.268297857 |
| yes | 32050.231831532 |
+--------+-----------------+
Это похоже на группу по функциям панд. Мы выбираем столбцы «Курильщик» и «Плата» и применяем функцию усреднения к начислениям. Оператор group by позволяет разделять строки на основе различных категорий в столбце курильщика. Таким образом, в результате мы получаем два средних значения.
Если мы хотим увидеть общее среднее значение, нам нужно только выбрать столбец сборов.
mysql> select avg(charges) from insurance; +-----------------+ | avg(charges) | +-----------------+ | 13270.422265142 | +-----------------+
Пример 2
Мы также можем захотеть увидеть количество курильщиков и некурящих вместе со средним значением оплаты.
mysql> select smoker, avg(charges), count(charges)
-> from insurance
-> group by smoker;
+--------+-----------------+----------------+
| smoker | avg(charges) | count(charges) |
+--------+-----------------+----------------+
| no | 8434.268297857 | 1064 |
| yes | 32050.231831532 | 274 |
+--------+-----------------+----------------+
В дополнение к предыдущему примеру мы выбираем счетчик в столбце начислений.
Пример 3
Рассмотрим случай, когда нам необходимо дополнительно разделить курильщиков и некурящих по признаку пола. Для выполнения этой задачи нам нужно разделить столбец пола на группу по утверждениям.
mysql> select smoker, sex, avg(charges), count(charges)
-> from insurance
-> group by smoker, sex;
+--------+--------+-----------------+----------------+
| smoker | sex | avg(charges) | count(charges) |
+--------+--------+-----------------+----------------+
| no | female | 8762.297299542 | 547 |
| no | male | 8087.204731276 | 517 |
| yes | female | 30678.996276260 | 115 |
| yes | male | 33042.005975283 | 159 |
+--------+--------+-----------------+----------------+
Пример 4
Оператор select также позволяет фильтровать. Например, мы можем выполнить запрос из предыдущего оператора для людей, которые живут в юго-восточном регионе.
mysql> select smoker, sex, avg(charges), count(charges)
-> from insurance
-> where region = 'southeast'
-> group by smoker, sex;
+--------+--------+-----------------+----------------+
| smoker | sex | avg(charges) | count(charges) |
+--------+--------+-----------------+----------------+
| no | female | 8440.205551942 | 139 |
| no | male | 7609.003586716 | 134 |
| yes | female | 33034.820716388 | 36 |
| yes | male | 36029.839366545 | 55 |
+--------+--------+-----------------+----------------+
Мы используем оператор where, чтобы указать условие для фильтрации. Важно отметить, что оператор where должен быть написан перед оператором group by в запросе select.
Пример 5
Мы хотим найти среднее значение BMI для различных категорий на основе столбцов для курильщиков и детей, но отображаем только 3 верхних категории с точки зрения среднего значения BMI.
Этот пример включает сортировку агрегированных значений, которая может быть выполнена с помощью оператора order by.
mysql> select smoker, children, avg(bmi)
-> from insurance
-> group by smoker, children
-> order by avg(bmi) desc
-> limit 3;
+--------+----------+------------+
| smoker | children | avg(bmi) |
+--------+----------+------------+
| no | 4 | 31.6754545 |
| yes | 2 | 31.3041818 |
| no | 2 | 30.8811622 |
+--------+----------+------------+
Оператор order by сортирует строки по заданному столбцу в порядке возрастания. Мы можем изменить его в порядке убывания, используя ключевое слово desc после имени столбца.
Пример 6
Давайте подробнее остановимся на предыдущем примере. Рассмотрим случай, когда нам нужны группы со средними значениями bmi выше общего среднего.
Один из способов - отдельно рассчитать общее среднее значение и использовать его в качестве условия для фильтрации.
mysql> select avg(bmi) from insurance;
+------------+
| avg(bmi) |
+------------+
| 30.6633969 |
+------------+
mysql> select smoker, children, avg(bmi)
-> from insurance
-> group by smoker, children
-> having avg(bmi) > 30.6633969;
+--------+----------+------------+
| smoker | children | avg(bmi) |
+--------+----------+------------+
| no | 2 | 30.8811622 |
| no | 3 | 30.7384322 |
| no | 4 | 31.6754545 |
| yes | 1 | 30.8743443 |
| yes | 2 | 31.3041818 |
+--------+----------+------------+
Важно отметить, что при фильтрации на основе агрегированного значения мы используем оператор Have вместо оператора where.
Второй вариант - объединить эти два запроса во вложенный запрос.
mysql> select smoker, children, avg(bmi)
-> from insurance
-> group by smoker, children
-> having avg(bmi) > (
-> select avg(bmi) from insurance
-> );
+--------+----------+------------+
| smoker | children | avg(bmi) |
+--------+----------+------------+
| no | 2 | 30.8811622 |
| no | 3 | 30.7384322 |
| no | 4 | 31.6754545 |
| yes | 1 | 30.8743443 |
| yes | 2 | 31.3041818 |
+--------+----------+------------+
Я предпочитаю второй вариант, потому что первый более подвержен ошибкам.
Пример 7
Этот пример немного сложнее предыдущего. Вместо того, чтобы отображать только те строки, которые имеют значение bmi выше среднего, мы хотим создать новый столбец, который указывает, является ли строка выше среднего значения или нет.
Мы будем использовать оператор case-when внутри нашего вложенного запроса.
mysql> select smoke, children, avg(bmi),
-> (case when avg(bmi) > (
-> select avg(bmi) from insurance) then "more than avg"
-> else "less than avg" end) as compared_to_avg
-> from insurance
-> group by smoker, children;
Прежде чем отображать результат, давайте попробуем понять, что делает каждый шаг запроса.
Выбираем три столбца аналогично предыдущему примеру. Четвертый столбец создается с помощью оператора case-when. Новый столбец принимает значения «больше среднего» или «меньше среднего» на основе сравнения среднего значения bmi для строки и общего среднего значения bmi.
Общее среднее значение bmi вычисляется с помощью вложенного оператора select внутри оператора case-when. Вот результат этого запроса.
+--------+----------+------------+-----------------+ | smoker | children | avg(bmi) | compared_to_avg | +--------+----------+------------+-----------------+ | no | 0 | 30.5519499 | less than avg | | no | 1 | 30.5648859 | less than avg | | no | 2 | 30.8811622 | more than avg | | no | 3 | 30.7384322 | more than avg | | no | 4 | 31.6754545 | more than avg | | no | 5 | 30.2700000 | less than avg | | yes | 0 | 30.5436957 | less than avg | | yes | 1 | 30.8743443 | more than avg | | yes | 2 | 31.3041818 | more than avg | | yes | 3 | 30.5206410 | less than avg | | yes | 4 | 29.3066667 | less than avg | | yes | 5 | 18.3000000 | less than avg | +--------+----------+------------+-----------------+
Вывод
Мы рассмотрели несколько примеров запросов, чтобы продемонстрировать возможности SQL для анализа данных.
Я считаю, что SQL - необходимый навык для специалистов по обработке данных или аналитиков. По крайней мере, мы должны знать, как запрашивать реляционную базу данных. Сложные запросы, которые выполняют операции преобразования и манипулирования данными при извлечении данных, могут сэкономить память. Они также облегчают выполнение задач, которые необходимо выполнить впоследствии.
Спасибо за чтение. Пожалуйста, дайте мне знать, если у вас есть какие-либо отзывы.
