Для чего нужен sql аналитикам

Зачем аналитику нужно программирование на SQL?

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

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

Так как работаю я в основном с Microsoft SQL Server, то в тексте речь пойдет о программных возможностях баз данных построенных именно на этой системе.
В SQL Azure, Oracle Database, PostgreSQL и прочих реляционных базах синтаксис может отличаться, но принцип остается тем же.

Программные конструкции

Программная конструкция — это часть программы, отвечающая за определенное действие и объясняющая компьютеру как решить задачу. То есть небольшой строительный блок из которых и состоит программный код.

Переменные

Изучение любого из языков программирования, практически всегда начинается со знакомства с переменными.

Переменная — это ячейка в памяти компьютера, используемая для временного хранения какой-либо информации (чисел, строк, дат и т.п.).

У переменной есть три важных атрибута:

• Имя (чтобы можно было к ней обратиться);
• Тип данных (чтобы понимать, что с этой переменной можно делать);
• Область видимости (чтобы понимать, где она будет использоваться).

Также в SQL у переменной есть три фазы:

1. Объявление переменной при помощи инструкции DECLARE ;
2. Инициализация при помощи SET или SELECT ;
3. Использование переменной при помощи подстановки ее имени в нужное место вашего скрипта.

Давайте создадим переменную:

-- Объявляем переменную с именем 'StartDate' и задаем тип данных date DECLARE @StartDate date -- Инициализируем переменную и присваиваем ей значение '2019-09-30' -- Если нужно объявить сразу несколько переменных, то можно воспользоваться инструкцией SELECT (пример будет ниже) SET @StartDate = '2019-09-30' -- Используем переменную в запросе SELECT * FROM Orders WHERE OrderDate = @StartDate

Как переменные могут помочь аналитику?

Первое что приходит на ум — это создание отчета с динамическими периодами, чтобы не редактировать каждый раз даты отчета в условии WHERE :

-- Объявляем переменные с именем 'StartDate' и 'EndDate' DECLARE @StartDate date , @EndDate date -- Инициализируем переменные и присваиваем для 'StartDate' значение '2019-09-30', а для @EndDate используем специальную функцию возвращающую текущую дату GETDATE() SELECT @StartDate = '2019-09-30', @EndDate = GETDATE() -- Используем переменную в запросе SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate

Что касается области видимости, то нужно запомнить, что переменная видима с момента ее объявления до конца скрипта, после чего она уничтожается.

Условные операторы

Практически все языки программирования предоставляют возможность использовать условные операторы и SQL не исключение.

Условный оператор приходит на помощь в тот момент, когда вам нужно выполнить либо одно, либо другое действие в зависимости от заданного условия.

• Ключевое слово IF ;
• Условие, в соответствии с которым будет происходить проверка;
• Необязательное ключевое слово ELSE , указывающее на то, что делать если условие не выполнилось.

Давайте разберем на примере:

-- Объявляем переменную с именем @var и сразу присваиваем ей значение (так тоже можно) DECLARE @var int = 4 -- Простая форма, если условие не выполняется, то ничего не происходит IF 2 + 2 = @var SELECT 'Верно' -- Сложная форма, если условие не выполняется, говорим что делать IF 2 + 2 = @var SELECT 'Верно' ELSE SELECT 'Ошибка'

Чаще всего на практике требуются более сложные составные условия, содержащие различные логические связки.

Зачем это аналитику?

Логический оператор IF , сам по себе нельзя использовать в обычном SELECT -е, однако он активно применяется при создании программных модулей, таких как функции и хранимые процедуры о которых мы поговорим ниже.

Но есть логический оператор, который можно использовать внутри запроса и речь идет о операторе CASE он же оператор множественного выбора.

Оператор множественного выбора в зависимости от указанных условий позволяет вернуть одно из множества возможных значений, то есть CASE работает как несколько IF -ов.

• Ключевое слово CASE ;
• Ключевое слово WHEN , после которого идет условие проверки;
• Ключевое слово THEN , после которого идет действие к выполнению;
• Необязательное ключевое слово ELSE , указывающее на то, что делать если ни одно из условий не выполнилось;
• Ключевое слово END .

На практике все гораздо проще:

-- CASE работает только внутри SELECT SELECT -- При помощи CASE разобьем все товары на 4 группы в зависимости от цены CASE WHEN price 500 THEN 'Дорогие товары' ELSE 'Бесплатно' END FROM Orders

Для аналитика польза очевидна, так как при помощи CASE , можно создавать новые параметры отчетах, а также в зависимости от условий рассчитывать показатели.

Для примера давайте создадим новую группу каналов, ведь часто в отчетах бизнес-пользователи хотят видеть более понятные названия вместо smm_cpc , cpc или organic .

SELECT CASE WHEN medium = 'smm_cpc' THEN 'Таргетированная реклама' WHEN medium = 'cpc' THEN 'Контекстная реклама' WHEN medium = 'organic' THEN 'Органический поиск' WHEN medium = 'referral' THEN 'Переходы по ссылкам' ELSE 'Прочее' END FROM Orders

Циклы

Ни один уважающий себя язык программирования не обходится без циклов.

Цикл — это инструкция, позволяющая выполнять один и тот же запрос несколько раз, пока условие истинно.

• Ключевое слово WHILE ;
• Условие в соответствии с которым будет выполняться цикл.

-- Объявляем переменную с именем @var DECLARE @var int = 4 -- Создаем цикл и рядом пишем условие в соответствии с которым, цикл будет выполняться до тех пор, пока @var не будет равно 0 WHILE @var > 0 -- Заключаем тело цикла в скобки, в SQL они обозначаются как BEGIN . END BEGIN -- В SELECT выводим текущее значение переменной, а в SET устанавливаем для нее новое значение минус 1 SELECT @var SET @var = @var - 1 END

В результате запрос, находящийся в операторных скобках, будет выполнен 4 раза:

В чем же польза для аналитика?

Так же как и в случае с условным оператором IF , цикл нельзя вставить внутрь запроса и он нужен для создания программных модулей о которых мы сейчас и поговорим.

Программные модули

Программный модуль — это запрос или скрипт сохраненный на сервере и оформленный в виде объектов.

Главная фишка использования программных модулей состоит в том, что они позволяют собирать сложные решения из простых составных частей. То есть позволяют декомпозировать большую задачу на несколько мелких.

Представление

При решении какой-либо задачи, наши запросы могут стать достаточно большими и их контекст сложно постоянно держать в голове. И как только такая ситуация возникнет — лучшим решением будет использование функционала представлений.

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

У представления есть несколько ограничений:

1. В качестве представления можно сохранить не любой запрос, а только SELECT ;
2. В запросе не должно быть сортировки;
3. Все столбцы должны иметь имена.

• Ключевое слово CREATE VIEW использующееся для создания представления;
• Ключевое слово AS , после которого должен идти текст запроса.

При создании представления сервер не выполняет запрос, а сохраняет его в базе для последующего использования.

Давайте разберем на примере:

-- Создаем представление с именем SalesReport CREATE VIEW SalesReport AS -- Далее указываем наш большой и сложный запрос, текст которого мы хотим сохранить на сервере SELECT [date] , cost , impressions , clicks , sessions , orders , revenue FROM Orders WHERE medium = 'cpc'

И теперь, чтобы получить результат выполнения запроса, нам достаточно выполнить следующий код:

SELECT * FROM SalesReport

Чем это полезно аналитику?

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

И теперь можно дальше усложнять запрос, используя представления как строительные блоки для конечного отчета.

Табличное выражение

Чтобы создавать представления, у вас должны быть права администратора к базе данных. Но что делать когда таких прав нет, но возможности представлений использовать хочется?

Специально для этого был придуман функционал табличного выражения.

Табличное выражение — это представление которое не сохраняется в базе, а существует только в момент выполнения SELECT -а и после самоликвидируется.

• Ключевое слово WITH использующееся для создания табличного выражения;
• Ключевое слово AS , после которого должен идти псевдоним;
• В скобочках () указывается текст запроса, который будет выполняться при обращении к псевдониму;
• После табличного выражения должен идти одиночный SELECT .

-- Создаем табличное выражение с именем Cost WITH Cost AS ( -- Далее в скобках указываем наш запрос SELECT [date] , sourceMedium , campaign , cost , impressions , clicks FROM Advertising ) -- Выводим результат выполнения запроса SELECT * FROM Cost

Пока не очень понятно в чем польза и чем это отличается от представления?

Давайте разберем пример из практики практически любого digital-аналитика:

1. Перед вами поставили задачу — создать отчет для отдела контекстной рекламы с доходом и расходом по рекламным кампаниям.
2. Данные по расходам хранятся в одной базе, а данные по заказам в другой.
3. Ключами позволяющими связать данные между собой являются UTM-метки.

Для нас теперь это раз плюнуть, вооружимся табличными выражениями и напишем запрос:

-- Запрашиваем расходы WITH Cost AS ( SELECT [date] , sourceMedium , campaign , cost , impressions , clicks FROM Advertising ) -- Запрашиваем заказы , Orders AS ( SELECT [date] , sourceMedium , campaign , orders , revenue FROM Crm ) -- Создаем итоговый отчет , Report AS ( SELECT Cost.[date] , Cost.sourceMedium , Cost.campaign , SUM(Cost.cost) AS 'cost' , SUM(Cost.impressions) AS 'impressions' , SUM(Cost.clicks) AS 'clicks' , SUM(Orders.orders) AS 'orders' , SUM(Orders.revenue) AS 'revenue' FROM Cost -- Соединяем данные между собой по дате и UTM-меткам LEFT JOIN Orders ON Cost.[date] = Orders.[date] AND Cost.sourceMedium = Orders.sourceMedium AND Cost.campaign = Orders.campaign GROUP BY Cost.[date], Cost.sourceMedium, Cost.campaign ) -- Выводим результат SELECT * FROM Report

Да, конечно, эту задачу можно было решить гораздо меньшим количеством кода.

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

Процедуры

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

Основное назначение процедур — это сохранение программных сценариев для выполнения определенных действий в базе данных ( UPDATE , DELETE , INSERT и тп.), в том числе для вызова других процедур и функций.

Но у процедуры есть ограничения:

1. Процедуру нельзя использовать в SELECT -е;
2. Процедуру нельзя усложнять «снаружи», то есть ее нельзя JOIN -ть, отфильтровать, группировать и тп.

• Ключевое слово CREATE PROCEDURE использующееся для создания процедуры;
• В процедурах можно использовать параметры, название которых указывается после знака @ ;
• Ключевое слово AS , после которого должен идти программный код;
• Для вызова процедуры используется ключевое слово EXECUTE вместе с ее названием.

-- Создаем процедуру с именем HumanResourcesProc CREATE PROCEDURE HumanResourcesProc -- Указываем параметры процедуры, которые будут выступать в качестве переменных @FirstName nvarchar(50), @LastName nvarchar(50) AS -- Далее идет текст скрипта SELECT FirstName , LastName , Department FROM HumanResources WHERE FirstName = @FirstName AND LastName = @LastName

И теперь, чтобы вызвать процедуру, достаточно выполнить следующий код:

-- Запускаем выполнение скрипта процедуры для вывода информации по определенному сотруднику EXECUTE HumanResourcesProc 'Иван', 'Иванов'

Чем процедуры могут помочь аналитику?

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

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

Функции

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

Функции — это конструкции, содержащие исполняемый код и являющиеся основными строительными блоками программы.

В некоторых языках программирования нет деления на функции и процедуры, так в чем же отличия?

1. Функция может быть усложнена как «внутри», так и «снаружи», то есть она поддается сортировке, группировке и JOIN -у.
2. Функция всегда возвращает результат определенного типа, например, встроенная функция SUM — всегда вернет число. Процедура же может выполниться и ничего не вернуть или вернуть число, строку, дату и даже таблицу в зависимости от того, как она запрограммирована.

И как тогда определиться что использовать? Все просто.

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

• Ключевое слово CREATE FUNCTION использующееся для создания функции;
• Параметры функции указываются в скобках () и после знака @ ;
• Ключевое слово RETURNS указывает на тип возвращаемых функцией данных;
• Ключевое слово AS , после которого должен идти программный код;
• Ключевое слово RETURN , сообщающее функции, что нужно вернуть значение.

Давайте переделаем процедуру из предыдущего примера в функцию:

-- Создаем функцию с именем HumanResourcesFunc CREATE FUNCTION HumanResourcesFunc ( -- В скобочках указываем параметры функции, которые будут выступать в качестве переменных @FirstName nvarchar(50), @LastName nvarchar(50) ) -- Указываем что в данном случае функция должна вернуть нам таблицу RETURNS TABLE AS RETURN -- Далее идет текст скрипта SELECT FirstName , LastName , Department FROM HumanResources WHERE FirstName = @FirstName AND LastName = @LastName

Для вызова созданной функции используем следующий код:

-- Запускаем выполнение скрипта функции для вывода информации по определенному сотруднику SELECT * FROM HumanResourcesFunc ('Иван', 'Иванов')

Как функции могут облегчить жизнь аналитику?

Помните, чуть выше в блоке про оператор множественного выбора, мы разбирали пример с новой группой каналов?

А теперь, представьте, что это действие — присваивание понятного названия канала, нам нужно сделать не один раз в одном месте, а много и в нескольких отчетах.

Чтобы не повторять один и тот же код, можно написать функцию, которая заменит CASE :

-- Создаем функцию с именем ChannelFunc CREATE FUNCTION ChannelFunc ( -- В скобочках указываем параметр функции, который будет выступать в качестве переменной @medium nvarchar(50) ) -- Указываем, что в данном случае функция должна вернуть нам одно значение в виде строки RETURNS nvarchar (100) AS -- В операторных скобкам описываем логику при помощи IF BEGIN IF @medium = 'smm_cpc' RETURN 'Таргетированная реклама' IF @medium = 'cpc' RETURN 'Контекстная реклама' IF @medium = 'organic' RETURN 'Органический поиск' IF @medium = 'referral' RETURN 'Переходы по ссылкам' RETURN 'Прочее' END

И теперь, если мы захотим в каком-нибудь отчете заменить стандартные каналы на их русские названия, то просто применим функцию:

SELECT [date] -- Вместо CASE вызываем функцию ChannelFunc с параметром medium , ChannelFunc([medium]) AS 'newChannel' , campaign , cost , impressions , clicks FROM Advertising

Что дальше?

В следующей статье я расскажу как, используя программные возможности языка SQL, можно создать автообновляемый отчет на основе стека технологий MS SQL Server и Power BI.

Документация Microsoft (Transact-SQL):

• Осваиваем SQL на примере данных интернет-магазина Google (базовые конструкции);
• Осваиваем SQL на примере данных интернет-магазина Google. Ч.2 (JOIN).

Проектирование БД и почему важен SQL для системного аналитика: гайд по улучшению качества требований

Берём в работу новую задачу или проект. Начинаем со сбора бизнес-требований. Затем переходим к проработке функциональных и нефункциональных требований. Потом архитектура системы и влияние требований на нее, БД, API, интеграции. И вот, в процессе разработки выясняется, что в требованиях опять что-то не учли. Что может быть хуже?

Может, коллеги! Когда через пол года вам же приходится возвращаться к задаче и вы понимаете, что требования к развитию системы по словам разработчиков нереализуемы. Или реализуемы, но они кривят лица: мы этого не ожидали, проще всё с нуля переделать, либо «костылями подопрем».

Меня, как начинающего системного аналитика, это выводило из себя. Как так?! Элементарная же задача! А потом мне показывают БД. И тут я всё понимаю. Да, действительно, пришло время делать выбор: дорого переделывать или «костыли» подойдут.

Один раз столкнувшись с такой ситуацией, больше не хочется оставлять без внимания базу данных. Даже если проектирование БД в компании находится в зоне ответственности разработчиков.

В этой статье:

1. Как в разработке систем возникают ситуации «костыли» или «переделываем», и почему обычно это связано с непродуманной структурой БД.
2. Как проектирование БД на ранних стадиях работы с проектом влияет на качество требований.
3. Дам пошаговый план проектирования БД.
4. SQL-запросы: почему нужно уметь читать.

«Костылями» подпираем или переделываем?

Главное отличие системного аналитика от других участников проекта заключается в его способности смотреть широко. Работая над конкретной задачей, аналитик видит не только её, но и весь проект в целом, как он функционирует сейчас и как будет развиваться в будущем.

1. Рассмотрим ситуацию с хранением ФИО в системе.

Изначально было решено хранить ФИО одной строкой.

Прошло время. Появилась необходимость интегрироваться с системами электронного документооборота (ЭДО), в которых по единому информционному протоколу это всегда три отдельных поля: имя, отчество и фамилия.

Кажется, что всё просто. Но нет.

Это простое изменение породило множество сложностей:

1. Создание алгоритма деления строки на части, который не для всех случаев с отчеством «Гаджи оглы» хорошо отработают.
2. Модификация API-методов. Минимум 1, максимум — все, если данные ФИО этой роли в системе везде.
3. Изменение пользовательского интерфейса (UI) всех мобильных и веб-приложений, где есть ФИО, и доработка взаимодействия по API с сервером.
4. Доработка существующих интеграций.

Все эти изменения были нужны лишь из-за одной «маленькой» новой функции! Моя боль об этом записана в этом видео с конференции.

2. Еще один пример — думали, что у машины может быть только один владелец в автосервисе.

Проблемы со связями в базах данных. Сначала была связь «один ко многим», где у одной машины был один владелец, и у одного владельца много машин. Но со временем эксплуатации системы в продакшн оказалось нормальной ситуацией, что иногда два человека приезжают обслуживать одну машину. Иногда даже три.

Структура БД меняется: для правильного поддержания процесса нужна связь «многие ко многим». И в одну задачу это изменение не сделать.

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

Простые изменения и требования? Да, если подумать логически, на верхнем уровне. Но из-за того, что «под капотом» системы огромный мир данных и механизмов по их хранению, получению и обработке, то иногда мы попадаем на серьезные работы по развитию.

Так и получается, что мы с разработчиками садимся обсуждать: проще сделать «костыли» или лучше переделать с нуля сейчас, чтобы через год опять не пожалеть о принятом решении.

Важно учиться искать баланс и на старте работ найти грань: не допустить овер-инжиниринга, и при этом сделать модель БД гибкой. Это нужно постигать.

База данных — фундамент системы

БД — это фундамент системы, который влияет на реализацию всех требований. Все бизнес-процессы и функции сводятся к одному – обработке данных.

Каждое действие, каждый клик — это запрос к базе данных. И в этой базе данных хранятся сведения, которые предстоит собирать и обрабатывать, чтобы обеспечиать выполнение бизнес-процессов и функций в ней.

Базовые определения и примеры, прежде чем читать далее

Сущности — реальные объекты этого мира. Например: стол, стул, машина, тарелка, цветок и другие.

Свойства (атрибуты) — характеристики сущностей, которые для каждого из объектов сущностей могут быть уникальны.

Например:- машина — сущность.- Tesla Model X 2023 г., Porsche 911 2023 г., Toyota Camry 2022 — конкретные объекты сущности «машина». — цвет, производитель, модель, год, объем двигателя — свойства (атрибуты) сущности «машина».

ER-диаграмма (Entity-Relationship) — графическое представление сущностей БД и связей. Нотация моделирования баз данных.

Пример про медицинскую систему

Рассмотрим медицинскую информационную систему для частной поликлиники.

1. Пациент хочет сделать запись на прием онлайн: система отображает пациенту данные о доступных услугах, врачей и время для записи.
2. Пациент записывается на прием: система обрабатывает информацию о пациенте, докторе, времени приема, кабинете.
3. Регистратура звонит пациенту и подтверждает запись на прием: система меняет в базе данных статус записи с «новая» на «подтверждена».
4. Когда пациент приходит на прием, то в регистратуре делают отметку о том, что пациент ожидает — опять смена статуса.
5. И это только начало! Процессов в поликлинике много, особенно связанных с приемом пациента 🙂

Мы, как аналитики, должны продумать все сценарии, связанные с записью пациента к врачу.

Мне в этом плане особенно нравится устаревшая нотация моделирования IDEF0, потому что она не только отражает все шаги процесса, но и показывает какие входные и выходные данные нужны для реализации каждого их них.

В результате прочтения требований к медицинской системе, получаю список сущностей и свойств — основа для логической модели БД.

Прежде чем смотреть что получилось, попробуйте сами быстро выделить список сущностей и свойств. Совпадём?

Сущности и свойства медицинской информационной системы на основе описания процесса записи на прием к врачу

1. Пациент: Человек, который обращается в поликлинику для получения медицинской услуги.
   • ФИО,
   • контактная информация,
   • история болезни,
   • и так далее.
2. Услуга: Конкретное медицинское вмешательство или процедура, доступная для предоставления пациентам.
   • название,
   • описание,
   • продолжительность,
   • стоимость.
3. Врач: Медицинский специалист — сотрудник поликлиники, предоставляющий конкретные услуги.
   • ФИО,
   • специализация,
   • график работы,
   • кабинет.
4. Запись на прием: Информация о планируемом визите пациента к врачу.
   • дата и время визита,
   • пациент,
   • врач, статус записи (новая, подтверждена, пациент ожидает и т.д.),
   • кабинет.
5. Кабинет: Помещение, где врачи принимают пациентов.
   • номер кабинета,
   • этаж,
   • врач(и), которые в нем принимают.
6. Регистратура:
   • Описание: Отдел или персонал поликлиники, ответственный за прием и регистрацию пациентов, а также подтверждение записей на прием.
   • Атрибуты: Сотрудники регистратуры, контактная информация, график работы.
7. Статус записи: Состояние, в котором находится запись на прием в данный момент.
   • новая,
   • подтверждена,
   • пациент ожидает,
   • завершена,
   • и т.д.

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

Проверка требований на полноту и непротиворечивость с помощью проектирования БД

Заметили, что в тексте я выделила жирным отдельные слова? Это я, каждый раз работая с требованиями, повторяю одну и ту же процедуру:

• Выделяю слова в тексте, обычно двумя цветами: сущности и их свойства.
• Т.к. я за ускорение процесса, то выделяю слова мысленно и сразу же начинаю строить ER-модель базы данных на логическом уровне: создаю список таблиц (сущностей в базе данных) и добавляю в них свойства.
• После того, как таблицы созданы, проверяю, всё ли логично и всего ли хватает.
  Обычно, в процессе такой проверки, я понимаю, что половина данных в описании бизнес-процесса упущена, т.к. в их описании такой уровень детализации не требовался. Глядя на список таблиц, дополняю их свойствами на основе вопросов к Google, ChatGPT с 2023 года и жизненного опыта. Всё, что вызывает вопросы, выношу на обсуждение с владельцем IT-продукта (заказчиком) о хранении информации про его бизнес сущности.
• Теперь можно строить связи и проставлять кратности (полезная картинка по кратностям). Т.к. требования я уже написала и прочитала (и выучила), то делаю это интуитивно. Если вы будете пользоваться моей стратегией, то лучше читать требования еще раз и проставлять связи.
• На ER-диаграмме ищу связи «многие-ко-многим» и убираю их. Дополняю БД внешними ключами, где их нет. Довожу до состояния полноценной логической модели данных, иногда даже сразу до физической.
• Тестирую требования: беру каждую функцию в системе и проверяю, как она будет работать с моей базой данных на запись и получение данных.
  Если я могу простыми словами объяснить разработчику в какие таблицы писать данные или из каких забирать, то всё хорошо. А если нет, то плохо.
• Проверяю масштабируемость: смотрю на список запланированных задач на следующих этап разработки или придумываю потенциально возможные сценарии к реализации. Здесь важно не фантазировать о чём-то невозможном, а продумать, что в ближайшие 3-5 лет работы системы вообще возможно по функциональности и в целом может уже работает в отрасли в других системах, у конкурентов.
  Не всегда можно всё предугадать, но как правило, эта проверка на потенциальное будущее поднимает новые вопросы на обсуждение с владельцем IT-продукта (заказчиком) о потенциальном развитии проекта.

Системный аналитик должен смотреть на систему в трех измерениях:
— как работает сейчас (AS IS),
— что просят сделать (текущие требования на разработку),
— во что это может перерасти в будущем (потенциальное развитие).
Это поможет сделать разработку качественной.

Что получилось после чтения требований? Логическая модель базы данных, связанной с задачей. На её основе ставлю задачи на разработчиков Backend, а также разработчикам мобильных и десктоп приложений, когда нужно сделать локальную базу данных, например, на SQLite.

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

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

Результат:

• требования полные,
• меньше вопросов от разработчиков,
• система масштабируема и спроектирована с учетом потенциального развития.

Этап проектирования БД важен для системных и бизнес-аналитиков. Не игнорируйте его.

Пошаговый план проектирования БД

1. После завершения работы над описанием бизнес-процессов или требований, прочесть их и выделить по тексту список сущностей (таблиц БД) и свойств (полей таблиц).
2. Спроектировать логическую модель данных — ER-диаграмма:
   1. таблицы (сущности),
   2. поля (свойства),
   3. связи между таблицами,
   4. кратности связей между таблицами.

   Связи типа «многие-ко-многим» убрать.
   *Дополнительно, перед логической, можно сделать концептуальную, но она не всегда имеет практическую ценность.

   

3. Прочесть требования еще раз и проверить, что всех данных хватает.
   Недостающие данные дополнить на основе Google, ChatGPT, доступных документов и вопросов заказчику.
4. Дополнить логическую модель требованиями к типам данных, обязательности, уникальности, значениям по умолчанию и индексам свойств таблиц. Получена физическая модель БД.
5. Ставить задачи на разработчиков по их созданию, выстраивая последовательность и зависимости. Часто задачи можно делать параллельно нескольким разработчикам.

SQL для системных аналитиков

Для системного аналитика, владение SQL не просто «буквы для резюме», это ключевой навык, который:

1. Усиливает логическое мышление.
   SQL требует строгого и последовательного подхода к структурированию данных. Такой подход улучшает логические навыки, что в свою очередь помогает быстрее и точнее анализировать требования.
2. Позволяет понимать язык разработчиков и что «под капотом» системы.
   Когда аналитик знает, как устроена база данных, он может разрабатывать требования, учитывающие эту структуру, что уменьшает количество ошибок и доработок в дальнейшем. То же, что и с умением проектировать БД.
3. Помогает обращать внимание на «узкие места» в системе, где важно продумать нефункциональные требования по нагрузке, ограничения.
   Понимая, как устроен запрос и какие ресурсы он потребляет, можно заранее предусмотреть необходимые ограничения.
4. Позволяет с ходу оценивать реализуемость требований.
   Зная SQL, аналитик может сразу определить, насколько реализуемы требования, которые предлагает заказчик, и как они повлияют на производительность и структуру БД.

Знание SQL позволяет понимать, что происходит на встречах с разработчиками и какие технические детали они обсуждают. Даже знания базовых операторов как SELECT, WHERE, IF, JOIN уже многое дает. А когда речь идет о проектах с несколькими БД, то он лучше понимает последовательные цепочки запросов, обеспечивающие синхронизацию данных.

Заключение

Навык проектирования БД и понимание SQL-запросов выводит качество требований и постановок задач от аналитика на новый уровень. С его знанием аналитик точно осознает к каким техническим задачам ведет очередное бизнесовое требование. Кстати, для менеджеров проектов это тоже полезно.

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

Из рекомендаций, что почитать и посмотреть по проектированию БД:

1. Книга: Технологии проектирования баз данных. Д. Л. Осипов.
2. Практический вебинар (видео): Проектирование БД: с чего начать.
3. Практический урок (видео): на проекте для автосервиса показывала, как сделать БД на логическом уровне.
4. Гайд по проектированию БД и SQL с помощью ChatGPT. Полезно, когда хочешь сделать свой демо-проект для портфолио или устал писать SQL-запросы сам.
5. Проект по проектированию БД Зоомагазина в моем канале — разбор с нуля, первое сообщение.

1. DBeaver. Особенно, если надо подключиться к БД и посмотреть ER-диаграмму на существующей базе.

БД — не сложно. Особенно, если на практике освоить все правила!

• системный анализ
• проектирование баз данных
• бизнес-анализ
• sql
• системный аналитик
• sql-запросы
• требования к системе
• базы данных
• реляционные бд
• er-диаграмма

Зачем аналитикам данных знать SQL

Эксперт «Нетологии» рассказывает, что такое SQL, как работать с SQL командами и, главное, зачем это всё нужно аналитикам данных.

Рассказывает Ирина Хомутова, эксперт и методолог курса SQL «Нетологии»

Что такое SQL?

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

И если мы хотим анализировать данные — нам нужно их откуда-то получать, а получаем мы их, как правило, из баз данных. Не всегда это видно невооружённым взглядом, но читаем ли мы новости, переводим деньги со счёта на счёт, запрашиваем выписки по счёту, делаем покупки в интернет-магазине или просто знакомимся с ценами конкурентов — всё это мы делаем, обращаясь к различным базам данным.

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

Кто такие аналитики данных и с чем они работают?

Аналитики данных — своего рода экспериментаторы, которые владеют инструментарием для соединения потоков данных из различных источников, а также выдвигают гипотезы и проверяют их. Вот тут-то им и нужны базы данных и язык, позволяющий точно формулировать к ним запрос. Правильное получение исходных данных — это искусство, гарантирующее высокую вероятность того, что в поведении исследуемых объектов установятся причинно-следственные связи. Именно качество данных позволяет выбирать наиболее подходящие гипотезы.

Помимо SQL, аналитику данных необходимо знать инструменты статистического анализа данных: это и узкоспециализированные пакеты — SPSS, Statistika, и различные языки программирования — SAS, R, Python, обладающие функциональностью для анализа и визуализации данных, и совсем легковесные решения типа Gretl. А самый популярный набор инструментов у аналитиков, это, пожалуй, Python + SQL.

Часто нужно представить данные наглядно и понятно для бизнеса, а иногда и самому оценить, какие факторы, влияющие на данные, являются значимыми, а какие нет, какие причины отклонений созависимы. Когда необходимо строить прогнозы, аналитики вместо того чтобы работать напрямую с базами, работают с датасетами (таблицами данных) с помощью различных пакетных решений.

При этом в каждой индустрии свои стандарты анализа, зависящие от чувствительности данных: для госструктуры набор инструментов будет одним (исходя из жёстких ограничений доступов), для НКО — другим, для диджитал-стартапа — третьим.

Откуда брать данные и что делать с SQL командами?

В организациях есть свои хранилища данных и при необходимости к ним можно получить доступ. Это происходит и при разграничении прав пользователей, и при работах по разработке баз данных. С помощью Python, зная адрес сервера и данные для подключения к нему, можно импортировать нужные библиотеки и писать запросы уже внутри используемой программы.
Примеры библиотек: для ODBC — pyodbc, PostgreSQL — psycopg2, MySQL — mysql.connector и т. д.

Ваша программа-«получатель данных», в свою очередь, может находиться внутри облачного решения на основе jupiter notebook. А к полученным данным вы примените дальнейшие выкладки и/или построите графики (как минимум, библиотеки: pandas, numpy, matplotlib и т.д.).

Аналитические функции

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

• вычислить частоту общения друзей друг с другом (например, необходимо определить максимально близкие контакты внутри выделенных кругов): тогда мы будем искать процентные соотношения длительности переговоров, частоты переписок и телефонных разговоров, количество географических пересечений, посещений общих ресурсов и т.д.,
• сравнить зарплаты сотрудников внутри отделов, вычислить рейтинги,
• узнать топ-N заказчиков в сечении по услугам или продуктам,
• узнать загруженность соседних точек продаж и т. д.

На помощь приходят аналитические функции SQL — мощный инструмент, который помогает разгрузить клиента от большого объёма процедурного кода, СУБД — от сложных и порой малоэффективных запросов, уменьшить время на разработку и получить при этом желаемый результат.

Функции для статистических расчётов включают в себя как стандартные агрегирующие функции (используются для получения обобщающих значений), так и расширения, характерные для конкретных СУБД.

Рассмотрим примеры, основанные на классическом тренировочном датасете Postgres.

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

with flycnt as ( select book_ref, book_date, total_amount/max(total_amount) over (partition by date_trunc('day', book_date)) as pcnt from bookings.bookings ) select * from ( select book_ref, book_date, pcnt, row_number() over(partition by date_trunc('day', book_date) order by pcnt) as top from flycnt ) as topflyers where top 

На неделю получили примерно такой список:

SQL-аналитик: кто это, сколько зарабатывает и как им стать

Skillfactory 24 Августа 2023, в 14:22

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

Что такое SQL-аналитика

Structured Query Language переводится как «язык структурированных запросов». Он необходим, чтобы управлять базами данных. SQL помогает искать нужные сведения в большом потоке информации, исследовать и обрабатывать их. Например, можно составить список покупателей, которые в прошлом месяце обращались больше одного раза.

Данные выглядят как таблицы – похожие на Excel, но более объемные. Вручную проанализировать их не получится, поэтому используют поисковые запросы. SQL-аналитик вводит их в командную строку и оперативно получает результат.

Чем занимается SQL-аналитик

Основная задача – понять, как получить, преобразовать и визуализировать информацию. Перечислим, что именно делает специалист:

1. Ставит цель анализа.
2. Определяет перечень необходимых действий.
3. Подбирает источники получения сведений.
4. Выгружает информацию, изучает ее параметры с помощью инструментов аналитики и алгоритмов SQL.
5. Управляет данными – например, группирует их, объединяет сведения из разных таблиц.
6. Находит закономерности, взаимосвязанные и повторяющиеся события.
7. Составляет отчеты с выводами.

Как работает специалист

Рассмотрим, какие этапы нужно пройти SQL-аналитику.

Выявить проблему и потребности

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

Оценить качество информации

Чтобы сделать точные выводы, определить причину проблем и точки роста бизнеса, нужны достоверные сведения. Если выбрать их неправильно, можно получить неверный результат. SQL-аналитик изучает информацию, при необходимости чистит данные, приводит их в порядок.

Провести анализ

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

• Автоматически. С помощью алгоритмов, которые позволяют обнаружить повторяющиеся цепочки событий.
• Полуавтоматически. Начало и конец операции нужно задать вручную, а закономерности и связи найдет алгоритм.
• Вручную. Метод применяют, чтобы исследовать длинные или сложные процессы, когда необходимо использовать много разных источников.

Подготовить результаты

SQL-аналитик должен простым и понятным языком донести до заказчика итоги проделанной работы. Обычно для этого используют графики, схемы и диаграммы.

Что должен знать и уметь SQL-аналитик

Чтобы освоить профессию, важно обладать определенными техническими навыками (hard skills) и так называемыми мягкими умениями (soft skills). Рассмотрим основные из них.

Hard skills

1. Понимать, как устроены СУБД – системы управления базами данных.
2. Решать аналитические задачи с помощью языка SQL.
3. Знать архитектуру и структуру востребованных СУБД – например, MySQL, PostgreSQL, MS SQL.
4. Разбираться в основах администрирования баз данных, операторах для составления запросов, загрузки, обновления, удаления информации в любом формате.
5. Уметь пользоваться командами для работы с таблицами, чтобы фильтровать, группировать, объединять сведения.
6. Базово знать математику, статистику, теорию вероятности.
7. Разбираться в программах для представления данных в виде схем, диаграмм, графиков.

Soft skills

• Иметь аналитический склад ума.
• Интересоваться математикой, статистикой.
• Внимательно относиться к деталям.
• Работать с большими потоками информации.
• Иметь хорошую память, развитую логику.
• Доносить сведения просто, понятно, лаконично.
• Видеть закономерности и связи.
• Уметь находить общий язык с заказчиком, коллегами.
• Работать в команде.
• Интересоваться сферой, в которой проводится аналитика.

Профессия рутинная, но важно уметь нестандартно мыслить, находить креативные решения.

Где работают и сколько получают специалисты

SQL-аналитики востребованы в IT, интернет-коммерции, ритейле, банках и финансовых компаниях. Также они нужны в организациях, связанных с транспортными услугами, телекоммуникацией, мобильной связью.

В августе 2023 года на сайте hh.ru работодатели разместили почти 8000 вакансий для SQL-аналитиков. Специалистов ищут не только в Москве и Санкт-Петербурге, но и почти во всех крупных городах России. Они нужны и небольшим фирмам, и огромным корпорациям, таким как «Сбер», Askona, «Деловые Линии», «Билайн», «Контур», МТС, банк «Точка».

Компании принимают на работу и опытных специалистов, и новичков. Зарплата зависит от стажа, квалификации и навыков.

Новичкам работодатели предлагают от 45 000 рублей в месяц

Специалистам с опытом от одного до трех лет платят в два раза больше, чем новичкам. В среднем – 100 000–150 000 рублей в месяц.

Вакансии с высокой зарплатой встречаются и в Москве, и в регионах. Работать можно из любой точки мира

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

Зарплата опытного специалиста достигает 400 000 рублей в месяц

Как стать SQL-аналитиком с нуля

Освоить профессию способен человек даже без навыков в IT. Можно смотреть видео, читать книги по теме, но самый удобный способ – пройти специальное обучение, а после начать поиск работы.

Например, в онлайн-школе Skillfactory есть «Курс по SQL для анализа данных». На нем вы освоите язык SQL, чтобы работать со сложными запросами и функциями. Научитесь взаимодействовать с разными продуктовыми и маркетинговыми метриками, собирать и обрабатывать данные, в простой и понятной форме визуализировать их.

Курс состоит из двух уровней – базового и продвинутого. Первый длится семь недель. Он подходит новичкам и тем, кто хочет систематизировать знания и навыки. Продвинутый рассчитан на восемь недель и поможет изучить более сложные инструменты SQL для работы с базами данных.

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

Друзья, теперь вы можете поддержать SEOnews https://pay.cloudtips.ru/p/8828f772 Ваши донаты помогут нам развивать издание и дальше радовать вас полезным контентом.

Похожие публикации:

1. Как в фотошопе убрать одежду
2. Как войти в группу в вайбере
3. как сохранить контакты в память телефона xiaomi
4. Что такое буфер журнала на xiaomi