От 0 до 300 SQL-запросов в месяц: практические советы для аналитика данных.
https://t.me/machinelearning_interviewДо текущей работы бизнес-аналитиком, на которую я устроилась в августе 2021 года, я написала всего несколько SQL-запросов. Меня наняли не из-за знания SQL (Structured Query Language, язык структурированных запросов). Однако в ноябре в нашей компании внедрялась новая база данных. Я, как единственный человек в команде, который когда-либо писал на языке программирования, стала самым подходящим кандидатом в специалисты по извлечению данных из новой БД.
Сегодня я делаю запросы к базе данных по 7-10 раз в день. В следующем месяце получу доступ ко второй БД, а позже еще к нескольким за счет привлечения в компанию других бизнес-направлений.
Помимо базовых правил использования операторов, я полагаюсь на несколько лайфхаков, помогающих упростить запросы и загрузить данные в дашборд более эффективно.
1. Поиск по схеме определенных имен столбцов
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE %search term%
Первые запросы к базе данных в SQL Server Management Studio я выполняла щелчком правой кнопкой мыши на имени таблицы и перебором 1000 верхних строк для каждой таблицы. Я просматривала строку за строкой, чтобы найти имя столбца, которое соответствовало тому, что мне было нужно.
При поиске по схеме данных вышеописанным способом вы получите не только список потенциальных столбцов, представляющих интерес, и соответствующих им таблиц, но и порядковый номер, что полезно при наличии таблицы со 100 и более строками.
2. Создание столбца на основе условий с помощью CASE WHEN
"New Variable"=
CASE WHEN condition THEN 'output if true' ELSE 'output if false'
END
Предложение CASE WHEN вставляется под оператором SELECT, как и любой другой столбец, который нужно вернуть. Если оно следует за другим столбцом в операторе SELECT, просто поставьте запятую перед именем новой переменной (New Variable).
CASE WHEN выручает, когда требуется вернуть двоичную переменную, если строка данных удовлетворяет одному или нескольким условиям. Вставить второе условие просто: нужно поместить оператор AND между двумя условиями в оператор CASE WHEN.
Использование этого оператора экономит много времени. Альтернативой является создание новой переменной в Power Query Editor при импорте данных в Power BI, что приходится повторять каждый раз при необходимости отредактировать запрос.
3. Комментарии и псевдонимы
--I am commenting out a statement in my SQL Query OriginalColumnTitle AS "Original Column Title"
Комментарии и псевдонимы относительно просты в использовании, но применять их следует после некоторой подготовки. Поначалу я не вставляла никаких комментариев в SQL-запросы. Однако столкнулась с ситуацией, когда затруднилась разъяснить собственный SQL-запрос: просто не могла вспомнить, что именно хотела выполнить. Благодаря комментариям, коллеги помогали мне редактировать запросы.
На противоположном конце маршрута SQL-запросов псевдонимы служили простыми, но важными элементами для изучения более продвинутых практик работы. Конечные пользователи дашбордов, создаваемых мной с помощью данных, разбросаны по всему миру. Например, у конечного пользователя в Канаде было другое имя значения столбца — не такое, как в моей базе данных. Создание псевдонима, соответствовавшего его ожиданиям, позволило избежать путаницы с самого начала.
Чтобы вставить комментарий, который будет проигнорирован программой, начните с двух или трех штрихов. Это окрасит ваш комментарий в зеленый цвет в Management Studio, и он будет проигнорирован в запросе. Чтобы присвоить псевдоним, за именем столбца должен следовать оператор AS, при этом псевдоним пишется в одинарных или двойных кавычках.
Работа с данными для меня началась с изучения кода на языке SAS, затем R и, наконец, Python. Всего несколько раз мне пришлось извлекать небольшой набор данных с использованием навыков SAS. Однако ни один из этих языков не позволил создавать сквозные процессы обработки данных в аналитическом инструменте. Такую возможность предоставил только SQL.
Поиск и удаление повторяющихся записей таблицы
Нет ничего хуже дублей. Повторяющиеся записи были для меня огромной проблемой при работе с данными. Они вносят неразбериху практически в любой анализ и дашборд — особенно ужасны те, которых не берет простой оператор DISTINCT.
Существуют разные способы выявления дублей. Вот самый простой пример:
with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
select * from x where rowRank > 1;
Просто оберните основной запрос в обобщенное табличное выражение и после всех переменных, которые нужно проверить, добавьте функцию row_number. Для всех ключей таблицы будет выделен раздел, чтобы обеспечить корректную работу. Иначе вы можете отнести в неправильную категорию значение, не являющееся дублем.
С помощью функции row_number все указываемые экземпляры ключей упорядочиваются. После обобщенного табличного выражения запустите простой оператор select, а также оператор WHERE — будут отфильтрованы значения поля новой функции row_number, которые превышают 1. В результате вернутся все повторяющиеся записи, ведь у любой записи с rowRank > 1 в таблице есть повторяющийся ключ.
А еще можно увидеть, сколько всего содержится повторяющихся записей. Вот так:
with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
select [keys], max(rowRank) - 1 num_duplicates from x group by [keys];
Чтобы избавиться от всех дублей, используйте оператор delete с обобщенным табличным выражением:
with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
delete * from x where rowRank > 1;
Внимание: применение delete приводит к безвозвратному удалению записей из таблицы, поэтому используйте его с осторожностью. Как протестировать этот метод? Создайте временную копию таблицы и попробуйте выполнить удаление сначала в ней. Затем проведите проверку качества и выполните удаление в основной таблице.
Запрос самого последнего набора записей из таблицы
select a.*, a.[date] from {schema}.{table} a
join (select max(date) maxDate from schema.table) b
on a.date = b.maxDate
Большинство data-специалистов работают с большим количеством данных из временных рядов. Однако временные ряды — это нечто большее, чем просто значения с метками даты. Это могут быть и версии набора данных с метками даты.
Например, на работе мы обычно «делаем снимок» копии сегодняшней версии набора данных, чтобы отслеживать его изменение во времени. Затем важно получить из таблицы последний набор записей (т. е. последнюю «версию»).
В приведенном выше запросе это делается посредством объединения таблицы с самой собой в поле MAX date. При внутреннем объединении все записи с датами, не соответствующими этой max date (максимальной дате), отсеиваются.
В качестве альтернативы можно использовать левое объединение, а затем применить фильтрацию с помощью оператора where:
select a.*, a.[date], b.maxDate from {schema}.{table} a
left join (select max(date) maxDate from schema.table) b
on a.date = b.maxDate
where date = maxDate
Агрегирование повседневных данных помесячно и на уровне начала/конца недели
Помесячно:
select [key], sum([field]), DATEADD(month, DATEDIFF(month, 0, [date field]), 0) as month from {schema}.{table} group by [key]
Начало недели:
select [key], sum([field]), DATEADD(wk, DATEDIFF(wk, 6, [date]), 6) as weekBeginning from {schema}.{table} group by [key]
Две приведенные выше техники позволяют быстро и легко группировать повседневные данные временных рядов — помесячно и на уровне начала недели. В SQL есть и другие функции даты, которые справляются с той же задачей, но эти намного проще.
Кроме того, эта техника упрощает отображение временных рядов в дэшборд-инструментах и Excel. Например, я обычно применяю визуальные элементы, последовательно отображающие месяцы во временном ряду в формате ГГГГ-ММ. Соответствующая настройка запросов делает это намного легче.
Агрегирование данных в пользовательских (CASE WHEN) категориях
select [key], sum([field]),
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end as Seasons from {schema}.{table} group by
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end
С этой техникой можно агрегировать данные в пользовательских категориях, применяя операторы CASE и GROUP BY. Это можно сделать в одном операторе (подобном приведенному выше) или — во избежание использования длинного group by — применить обобщенное табличное выражение.
Примечание: в GROUP BY оператор case заканчивается на end, а не на end as, как в операторе SELECT.
WITH X as (select [key], [field]),
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end as Seasons from {schema}.{table})
select [key], sum([field]), Seasons from X group by Seasons
В этом примере, используя параметры даты, я создаю поле Seasons. Вы же можете с их помощью решать практически любые задачи.
Нахождение разницы между сегодняшними и вчерашними данными (или данными любых двух дат) в одной таблице
-- MS SQL SERVER 2016 or later
with x as (
select *, row_number() over(partition by [keys] order by [date_field] desc) as dateOrder
from {schema}.{table}
where [date_field] >= dateadd(day,-2,getdate()))
,
x1 as (
select * from x where dateOrder = 1),
x2 as (select * from x where dateOrder = 2)
select [fields] from x1
left join x2 on x1.key = x2.key (and x1.key = x2.key and x1.key = x2.key)
where x2.[key] is null
-- POSTGRES SQL
with x as (
select *, row_number() over(partition by [keys] order by [date_field] desc) as dateOrder
from {schema}.{table}
where [date_field] >= CURRENT_TIMESTAMP - interval '2 day'
,
x1 as (
select * from x where dateOrder = 1),
x2 as (select * from x where dateOrder = 2)
select [fields] from x1
left join x2 on x1.key = x2.key (and x1.key = x2.key and x1.key = x2.key)
where x2.[key] is null
Такое применение кажется очень узкоспециальным, но встречается довольно часто при:
- отслеживании числа новых записей, добавляемых в таблицу каждый день;
- выявлении новых записей, добавленных между двумя датами в таблицах со «снимками» (то есть это те самые таблицы, которые содержат копии с отметками времени одного и того же набора или источника данных).
Объединение данных из одной таблицы с данными другой (простой способ)
delete from {schema}.{target_table} where exists (select 1 from {schema}.{source_table} where {schema}.{source_table}.[key] = {schema}.{target_table}.[key])
Есть несколько способов объединения данных из двух таблиц. В MS SQL для этого существует оператор MERGE. Но вот самый простой способ — настроить объединение данных в скриптовом ETL-конвейере.
Я пишу много кода, чтобы на автоматической основе получать данные из API, а затем регулярно сбрасывать новые данные в таблицу базы данных.
Обычно это делается с помощью скрипта Python — в нем возвращается определенный временной интервал новых данных (от двух дней до недели или больше в зависимости от источника), после чего все эти данные помещаются во временную таблицу.
А дальше запускается оператор delete — для поиска в рабочей таблице записей, уже имеющихся в новой таблице. Наконец, как только все эти записи из целевой таблицы удаляются, в нее выполняется простая вставка из временной таблицы.
Самым большим вознаграждением за свой труд я считаю овладение искусством извлечения данных при работе над клиентской частью. Благодаря навыкам, приобретенным с помощью SQL, я могу самостоятельно справляться со всем объемом работы.
https://t.me/ai_machinelearning_big_data