За восемь лет в обработке данных простые, но малоизвестные приемы работы в SQL сэкономили мне бесчисленные часы на проведение анализа и создание ETL-конвейеров.
Поделюсь с вами шестью самыми актуальными.
Поиск и удаление повторяющихся записей таблицы
Нет ничего хуже дублей. Повторяющиеся записи были для меня огромной проблемой при работе с данными. Они вносят неразбериху практически в любой анализ и дашборд — особенно ужасны те, которых не берет простой оператор 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
- MongoDB: агрегирование
- SQL или NoSQL: как правильно выбрать базу данных?
Читайте нас в Telegram, VK и Яндекс.Дзен
Перевод статьи Cameron Warren: 6 Lesser-Known SQL Techniques to Save You 100 Hours a Month
