Существует стереотип, что SQL используют только программисты, однако это не так. Он давно превратился в эффективный инструмент для бизнес-аналитики. Сегодня мы расскажем, что он может дать вам как предпринимателю.
Помощь при масштабировании бизнеса
Предположим, вы открываете магазин. Все данные по нему: наименования позиций, поставщики, сроки доставки и прочее собраны в одну Excel-таблицу.
Время идет, бизнес расширяется, позиций становится больше, растет круг поставщиков, появляются новые филиалы в других городах. Одной таблицы не хватит, требуется более сложная система, которая упорядочит информацию и будет выдавать ее по запросу и с учетом указанных критериев.
На помощь придет SQL. Он позволит создать упорядоченную систему, из которой легко достать нужную информацию. Принцип работы основан на реляционных базах данных (БД), то есть наборе таблиц, которые ссылаются друг на друга. Допустим, в одной таблице перечислены поставщики и числовые коды, обозначающие города, а во второй хранится расшифровка идентификаторов. Первая ссылается на вторую, позволяя узнать город.
ИНСТРУМЕНТЫ АНАЛИТИКА — что учить, чтобы стать аналитиком в IT?
Таким образом, одна огромная таблица с данными заменяется на несколько маленьких и легких, что намного проще и быстрее в использовании.
Аналитика
Главная причина, зачем нужен SQL, — это возможность собирать и анализировать важные данные. Для этого даже не нужно просматривать все таблицы, достаточно сформулировать запрос, и нужная информация будет предоставлена автоматически.
К примеру, можно узнать выручку каждой кассы, какая из них принесла максимальную прибыль, когда была последняя отгрузка и не заканчивается ли в магазине определенный товар. Такой отчет под конкретный запрос называется Ad hoc reports и активно используется аналитиками.
Работа с внутренними данными
При наличии прав можно заглянуть в любые базы данных компании и получить информацию о клиентах. Например, сотрудник техподдержки интернет-провайдера может увидеть имя позвонившего, тариф, статус услуги, дату последнего зачисления на счет. И все это – за несколько секунд, просто нажав пару кнопок. Все это благодаря SQL, без него поиск информации занимал бы много времени.
Безопасность данных
Еще одно, для чего используется SQL, это настройка индивидуального доступа к информации. К примеру, можно скрыть паспортные данные клиентов от рядовых сотрудников, ограничив им доступ к определенным таблицам.
Правда, это может замедлять работу. Например, если данные хранятся в одной БД, а ключ-дешифровщик в другой, скорость шифрования снизится. Зато такое решение повышает безопасность.
Возможен и обратный случай, когда настройка прав доступа ускоряет работу. Допустим, в компании трудятся 500 человек и все имеют доступ уровня «суперпользователь». В итоге при их запросах система ищет информацию по всем таблицам сразу, что замедляет процесс. Ограничив доступ и выдав привилегии только тем, кому это действительно необходимо, можно существенно ускорить поиск информации.
Как SQL и PYTHON используют в аналитике данных?
Все эти преимущества вы получите, внедрив SQL в работу. Если вы задумались об его использовании, обращайтесь в Garpix. Запишитесь на бесплатную консультацию, и мы поможем оптимизировать бизнес-процессы вашей компании.
Источник: garpix.com
Введение в SQL для аналитика данных
В настоящее время, если мы наблюдаем, большинство вакансий аналитика данных в компаниях, которые внедрили ИТ-системы, требуют знания SQL в качестве инструмента анализа, поэтому навыки SQL — это навыки, которыми должен овладеть тот, кто хочет работать аналитиком данных.
В этой статье я намерен представить начальный урок SQL для тех, кто ищет карьеру аналитика данных.
Оглавление
О SQL
Что такое SQL?
SQL, что означает язык структурированных запросов, является стандартным компьютерным языком, используемым для системы баз данных, или, скорее, для системы управления реляционными базами данных.
Зачем аналитикам данных нужно изучать SQL?
Наша работа как аналитика данных заключается не только в том, чтобы анализировать уже имеющиеся данные, но и в том, чтобы иметь возможность самостоятельно извлекать, изменять и получать доступ к данным из источника данных, а именно из базы данных. Аналитик данных будет использовать SQL для запроса данных и извлечения из них информации.
Кроме того, владение SQL поможет компаниям в следующих областях:
-Помогает создавать информационные отчеты, например, ежемесячные тенденции продаж, увеличение числа клиентов и т. д.
-Программисты, создающие прикладные системы.
-Повысить производительность компании.
Где используется SQL?
SQL обычно используется в компаниях, внедривших ИТ-системы, например:
Электронная коммерция
— Розничная торговля
— Logisly
— и т.д.
Типы команд SQL
Типы команд SQL, которые я часто нахожу, делятся на 4 типа, а именно язык определения данных (DDL), язык манипулирования данными (DML), язык управления данными (DCL) и язык управления транзакциями (TCL), но аналитик данных , на самом деле достаточно, чтобы освоить DDL и DML, потому что на самом деле они часто взаимодействуют с командами типа DDL и DML, возможно, если вы хотите быть администратором базы данных, то часто будете взаимодействовать с типами команд DCL и TCL. Ниже приведена картина распределения типов из команд SQL:
Язык определения данных (DDL)
DDL (язык определения данных) — это команда, которая больше предназначена для управления структурами базы данных, таких как создание, изменение и удаление. Ниже приведены команды, содержащиеся в DDL, и их функции:
- СОЗДАТЬ: создать базу данных или таблицу.
- ALTER: изменение структуры таблицы, например изменение имен таблиц, добавление столбцов, изменение столбцов, удаление столбцов и присвоение атрибутов столбцам.
- DROP: удалить базу данных или таблицу.
Язык манипулирования данными (DML)
DML (язык манипулирования данными) — это команда на языке программирования SQL, которая используется для управления данными в базе данных. Ниже приведены команды, содержащиеся в DML, и их функции:
- INSERT: вставка новых данных в таблицу.
- SELECT: выбирает и отображает столбцы из таблицы или даже несколько столбцов из таблицы.
- ОБНОВЛЕНИЕ: изменение/редактирование данных в таблице.
- УДАЛИТЬ: удалить данные из таблицы.
Язык управления данными (DCL)
Функции языка управления данными (DCL) позволяют контролировать и манипулировать разрешениями самой базы данных. Проще говоря, язык управления данными (DCL) предназначен для управления правами доступа (привилегиями) и управления базой данных USER. Ниже приведены команды, содержащиеся в DCL, и их функции:
- GRANT: эта команда используется для предоставления пользователю прав администратора.
- REVOKE: в отличие от GRANT, который предоставляет права доступа, REVOKE имеет право отозвать права доступа пользователя.
Язык управления транзакциями (TCL)
Команды TCL имеют дело с транзакциями в базе данных. Ниже приведены команды, содержащиеся в TCL, и их функции:
- COMMIT: используется для постоянного хранения транзакций в базе данных.
- ROLLBACK: команда ROLLBACK используется для возврата базы данных к последней операции COMMIT.
- SAVEPOINT: SAVEPOINT — это специальный знак внутри транзакции, который позволяет откатить все команды, выполненные после их установки, восстанавливая состояние транзакции до состояния на момент точки сохранения.
Система управления реляционными базами данных (RDBMS)
Что такое РСУБД?
Система управления реляционными базами данных, обычно сокращенно RDMBS, представляет собой программу, которая позволяет нам создавать, обновлять и управлять реляционной базой данных. Сама реляционная база данных — это тип базы данных, в которой данные обычно хранятся в структурированной форме в виде таблиц (строк и столбцов), и каждая таблица/данные, содержащиеся в базе данных, связаны (реляционно) друг с другом.
Простая иллюстрация реляционных таблиц
Вот простой пример таблицы, которая связана друг с другом через свои столбцы:
Популярные продукты РСУБД
- PostgreSQL (с открытым исходным кодом)
- MySQL, MariaDB (с открытым исходным кодом)
- SQLite (с открытым исходным кодом)
- Hadoop (с открытым исходным кодом)
- Microsoft SQL Server (не с открытым исходным кодом)
- БД Oracle (не с открытым исходным кодом)
Структура хранения базы данных
В качестве хранилища данных система реляционных баз данных имеет следующую иерархическую структуру объектов хранения:
Таблица и столбец
На следующем изображении показан пример таблицы в базе данных. Например, имя базы данных — база данных student.
Пример запроса
Здесь я покажу небольшой пример запроса на SQL, надеюсь, благодаря этому у вас есть представление о том, как выполнять запросы на SQL.
Например, в базе данных под названием student у нас есть таблица с именем Student_Identity, вот таблица:
затем мы хотим попробовать манипулировать таблицей с помощью простых манипуляций.
Получить весь столбец в таблице
Чтобы получить все столбцы таблицы Student_Identity, мы можем ввести следующую команду:
SELECT*FROM Student_Identity;
после запуска он выдаст следующий вывод:
- Начальное слово SELECT используется для информирования системы о том, что мы хотим получить данные.
- * (звездочка) означает, что все столбцы должны быть извлечены из таблицы, на которую ссылаются. Этот знак часто называют подстановочным знаком.
- FROM [TABLE_NAME] означает имя таблицы, в которой будут браться данные.
- Подписать; (точка с запятой) — знак, указывающий на окончание команды SELECT
Возьмите один столбец в таблице
Чтобы получить столбец Name в таблице Student_Identity, мы можем ввести следующую команду:
SELECT Name FROM Student_Identity;
после запуска он выдаст следующий вывод:
Возьмите более одного столбца в таблице
Чтобы получить более одного столбца, мы можем ввести следующую команду:
SELECT Name, Age FROM Student_Identity;
после запуска он выдаст следующий вывод:
Вывод
Как человеку, который хочет сделать карьеру аналитика данных, очень важно освоить SQL как инструмент анализа, чтобы освоить SQL, необходимо сначала знать основы уроков SQL, чтобы поток мышления был построен, надеюсь, объяснение выше может помочь вам!
Источник: questu.ru
Sore query language, или 5 ошибок при первом изучении SQL
Эта статья о том, как аналитик изучает основы SQL, какие допускает ошибки и как старается их исправить. В статье будет то, что помогает погрузиться в SQL впервые: схемы и примеры кода, понятия и определения, проблемы и решения. Статья рассчитана на аналитиков-новичков.
Предыстория: аналитик создает отчёты в BI для директора компании «ABC». Аналитик не умеет программировать и подключается к данным по инструкции. Директор требует срочно добавить в отчёт данные из 3 источников:
- SAP – объем выручки от продаж;
- HRLink – затраты на персонал;
- Битрикс – время обработки заявок.
Аналитик ставит задачу data инженеру на добавление новых данных в BI. Data инженер – единственный специалист по работе с данными в офисе из 400 человек. Он критически оценивает сроки выполнения задачи и объявляет: «Минимум – 2 недели». Такой срок директора не устраивает. Инженер предлагает аналитику альтернативное решение.
Что такое SQL?
SQL? DWH? Mart? Аналитик путается и пугается, но решает разобраться во всем по порядку. Задание директора выполнять придется, деваться некуда.
SQL (Structured Query Language) – язык структурированных запросов, используется для взаимодействия с базами данных и анализа данных.
Объем выручки от продаж содержится в таблице с названием order_revenue такого вида:
Важно: это учебный пример из 7 строк, которые хранятся в одной таблице. В базах данных могут быть миллионы строк в сотнях разных таблиц. В базах данных информация по клиентам обычно хранится в отдельной таблице (например, client_id, client_name, client_birth_date и пр.). Но язык SQL использует схожие команды для баз данных любых размеров.
Посчитаем сумму заказов (выручку от продаж) с помощью SQL. Сделаем это так:
/*выберем сумму (sum) всех заказов (sum_order)*/ select sum(order_sum) /*из таблицы заказов (order_revenue)*/ from order_revenue
Результат: 58 850 рублей.
Зачем SQL нужен аналитикам?
Изначально аналитик использовал только BI, не погружался в работу с данными. Чтобы создать или дополнить отчёт, нужно обратиться к data инженеру. Он подготовит данные и объяснит аналитику, как к ним подключиться. Но теперь аналитик изучает, как устроена работа с данными в компании.
В компании «ABC» процесс работы с данными состоит из 5 этапов:
- Source – источники данных, т.е. самостоятельные программы (например, SAP, HRLink, Битрикс). Обработка данных начинается с обращения к данным, которые в разных форматах содержатся в источниках.
- ETL (Extract, Transform, Load) – извлечение, преобразование и загрузка данных из источников в единое хранилище данных.
- DWH (Data Warehouse) – единое хранилище обработанных и систематизированных данных. DWH содержит актуальные и исторические данные в виде таблиц, необходимых для анализа бизнеса. Здесь находится таблица order_revenue, сюда обращаются SQL.
- OLAP (On-Line Analytical Processing) – программа для оперативного анализа данных, основанная на многомерной модели. Эта модель позволяет анализировать сотни метрик в сотнях разрезов, а элементы внутри модели связаны между собой.
- BI (Business Intelligence) – программа для анализа, визуализации и представления данных. Она помогает бизнесу принимать решения на основе данных.
Благодаря знаниям SQL аналитик не ждет, пока инженер подготовит mart в DWH и OLAP с новыми данными. Аналитик может самостоятельно обратиться в DWH за данными и сделать отчет.
Важно: разграничение прав data инженеров и аналитиков устроено в компаниях по-разному. В этом примере обращение аналитика в DWH напрямую возможно, но это не всегда так.
Как устроен DWH?
Теперь аналитик выясняет, что такое mart и зачем он нужен. Аналитик пытается разобраться в устройстве хранилища и витрин данных.
В компании «ABC» DWH состоит из трех слоев, которые связаны между собой:
- ODS (Operational Data Store) – тип базы данных, которая собирает данные из источников, обрабатывает и передает в собственно хранилище DWH. ODS хранит данные за небольшой период времени (например, час, сутки), поэтому и называется операционным или промежуточным хранилищем.
- Обработанные данные попадают в DWH, где содержатся исторические данные и оперативные данные из ODS.
- Mart (Data Mart) – витрина данных. Содержит срез данных из DWH по одной теме в виде, понятном пользователю.
Аналитику может создать витрину с нужными данными, чтобы потом использовать ее в BI. Для этого аналитику нужно написать ряд запросов SQL и сохранить их результаты в таблицу (view).
Что может пойти не так?
1. Ошибка: не учитывать специфику СУБД
Аналитик пишет свой первый код, всё чётко работает. После работы аналитик решает потренироваться в написании запросов. Приходит домой, запускает в тренажере SQL точно такой же код, но появится ошибка. Аналитик нервничает, ищет ошибку, но ничего не находит и с треском захлопывает ноутбук.
Ошибка могла произойти, потому что аналитик использует разные СУБД. Например, на работе аналитик использует СУБД PostgreSQL, а в тренажере – СУБД MS SQL Server. В СУБД PostgreSQL работает код:
/*выберем 5 первых строк из таблицы заказов (order_revenue)*/ select * from order_revenue limit 5
Но в СУБД MS SQL Server этот код не сработает. Так происходит, потому что в СУБД используются разные функции. В СУБД MS SQL Server нет функции limit, зато есть похожая функция top. Такой код в СУБД MS SQL Server отработает верно:
/*выберем 5 первых строк из таблицы заказов (order_revenue)*/ select top 5 * from order_revenue
БД (База Данных) – упорядоченный набор структурированной информации, который хранится в электронном виде в компьютерной системе. БД бывают реляционными, колончатыми, иерархическими и т.д.
СУБД (Система Управления Базами Данных) – программа, которая управляет структурой БД, контролируют доступ к данным и позволяет анализировать данные.
Чтобы обратиться к данным в базе используется СУБД, которая выступает посредником между пользователем и данными. Примеры: PostgreSQL, MS SQL Server, MySQL, MangoDB и т.д.
Ошибка
Аналитик не поинтересовался, какой СУБД пользуется для запросов. Это привело к ошибкам и неработающему коду.
Как исправить
Отличий в названиях и использовании функций в разных СУБД десятки. Поэтому перед написанием SQL запроса стоит выяснить, какая СУБД используется. А при поиске функции в интернете можно писать не просто «функции времени SQL», а «функции времени PostgreSQL». Так шансы найти нужный ответ повышаются.
2. Ошибка: неправильно ставить запятые
Далее все примеры будут написаны на PostgreSQL. В примерах от data инженера аналитик встречает код с запятой в начале строки:
select order_date , order_sum , client_name from order_revenue
Аналитик считает, что это странно и неудобно. Поэтому помещает все поля запроса в одну строку:
select order_date, order_sum, client_name from order_revenue
Код работает и визуально выглядит меньше. Но в 20% случаев при написании запроса в 20+ строк появляется ошибка:
SQL Error [42601]: ОШИБКА: ошибка синтаксиса (примерное положение: «from») Позиция: 66
Аналитик пугается, старается найти ошибку, ищет ответы в интернете. После получаса войны с кодом оказывается, что при копировании полей запроса запятая встала неверно:
select order_date, order_sum, from order_revenue where .
Ошибка
Аналитик пренебрёг опытом data инженера и не ставил запятую в начале строки.
Как исправить
Если писать код с запятой в начале строки, это поможет копировать, вставлять, удалять и добавлять поля без ошибок. Код будет визуально длиннее, но его читаемость возрастет. И не нужно будет тратить время на отслеживание запятых.
3. Ошибка: не проверять округления
Аналитик решает задачу: узнать, какой процент от общей выручки приходится на каждого клиента. Он пишет код:
select distinct client_name , sum(order_sum) as total_revenue , sum(order_sum)*100 / ( select sum(order_sum) from public.order_revenue ) as precent_of_revenue from order_revenue group by 1
Аналитик приносит результат директору. Директор замечает, что сумма долей не равняется 100. Аналитик получает замечание за некорректность данных.
Аналитик сам проверяет сумму процентов по клиентам и получает 98% вместо 100%. Он ищет ошибку на протяжении часа и приходит к выводу: SQL неправильно округляет. При появлении дробной части SQL отсекает её, вопреки математическим правилам округления. Например:
select 5/3 Результат: 1 select 1/3 Результат: 0
Так происходит, потому для столбца order_sum был задан неподходящий тип данных.
Тип данных – свойство, определяющее, какого вида данные хранятся в столбце: целые числа, даты, текст и пр.
Тип данных INT используется для целых чисел, а DECIMAL(10,2) – для чисел с двумя цифрами после запятой. В нашем случае вместо DECIMAL(10,2) мог быть использован INT, поэтому SQL посчитал дробную часть необязательной и отсек её.
Ошибка
БД использует неподходящий тип данных, это приводит к ошибкам при работе с нецелыми числами.
Как исправить
Со стороны аналитика можно решить проблему на уровне SQL-запроса. Заменим умножение на 100 умножением на 100.0, то есть переведем число в нецелочисленный вид. Добавим округление до целого, получим код:
select distinct client_name , sum(order_sum) as total_revenue , round(sum(order_sum)*100.0 / ( select sum(order_sum) from public.order_revenue ),0) as precent_of_revenue from order_revenue group by 1
Результат корректный, сумма процентов равно 100:
Data инженер со своей стороны может попробовать изменить тип данных столбца order_sum на более подходящий. Тогда в будущем похожих проблем не возникнет.
4. Ошибка: не связывать данные и реальность
Аналитик решает другую задачу: найти клиента с максимальной суммой заказа. Аналитик пишет код:
select client_name , order_sum from order_revenue order by order_sum desc limit 1 Результат: Petr 13 000
Аналитик радуется полученному результату, показывает данные директору. Директор поручает менеджеру связаться с Петром, чтобы повысить лояльность стратегического клиента и удержать его в компании «ABC». Петр получает поддержку в техническим вопросах от менеджера, ему нравится повышенное внимание компании. Поэтому клиент продолжает делать крупные заказы.
Через месяц при работе с данными аналитик замечает, что в базе было два клиента с максимальной суммой заказа. С другим клиентом никто не общался, поэтому он стал делать меньшие заказы, а потом ушел к конкурентам, так как цена у них ниже.
Ошибка
Задача предполагала, что клиент будет один, но данные этого не подтвердили. Аналитик не учел, что в реальной жизни клиентов с одной суммой заказа может быть несколько. Он не проверил данные, а сразу жестко отделил одного клиента с помощью limit.
Как исправить
Код: выбрать не одного клиента, а всех, у кого сумма заказа максимальна:
select client_name , order_sum from public.order_revenue where order_sum = ( select max(order_sum) from public.order_revenue ) Результат: Petr 13 000, Maria 13 000
Логика: стоит связать данные и реальность. Могут ли быть одинаковые суммы заказов? Да. Могут ли быть одинаковые имена клиентов? Да.
А почтовые адреса? Нет. До написания запроса SQL стоит обдумать, какими свойствами обладают клиенты, заказы, контакты в реальности. Это может помочь избежать ошибок.
5. Ошибка: не проверять результаты
В предыдущих примерах аналитик совершил ещё одну ошибку – не проверил результаты вычислений. В SQL нет возможности легко визуализировать данные встроенными инструментами, поэтому сложно проверить корректность данных визуально.
Можно выполнять математический и логические проверки данных внутри SQL. Например, результат некорректного запроса с процентами проверим так:
/*добаляем результат запроса в таблицу percent_revenue*/ select distinct client_name , sum(order_sum) as total_revenue , sum(order_sum)*100 / ( select sum(order_sum) from public.order_revenue ) as precent_of_revenue into percent_revenue from order_revenue group by 1 /*проверяем сумму процентов*/ select sum(precent_of_revenue) from percent_revenue
Код с подборными пояснениями находится тут.