5 самых сложных примеров в SQL при аналитике данных.
Многие из нас сталкивались в работе себе мощь скорости и эффективности, обеспечиваемую централизацией вычислений в облачном хранилище данных. Хотя это правда, многие из нас также осознали, что, как и у всего, у этого есть свои недостатки.
Один из основных недостатков этого подхода заключается в том, что вы должны изучать и выполнять запросы на разных языках. Хотя написание SQL быстрее и проще, чем создание вторичной инфраструктуры для запуска Python (на вашем ноутбуке или офисных серверах), оно связано с множеством различных сложностей в зависимости от того, какую информацию аналитик данных хочет извлечь из облачного хранилища. Переход на облачные хранилища данных повышает сложность использования SQL по сравнению с Python. Пройдя через такой опыт, я решил записать конкретные уроки, которые наиболее полезные запросы для изучения и выполнения в SQL.
Чтобы помочь вам в рабочем процессе, я приведу примеры структуры данных до и после выполнения преобразования. Я также предоставил фактические запросы SQL, необходимый для выполнения каждого из 5 самых сложных преобразований. Мы предоставим ссылки на динамический SQL запрос для каждого преобразования, чтобы вы могли использовать SQL, необходимый для вашего анализа данных!
Начнем!
Представьте, что вы анализируете свои ежедневные данные о продажах, и это таблица выглядит так:
sales_date | product | sales |
2022-04-14 | A | 46 |
2022-04-14 | B | 409 |
2022-04-15 | A | 17 |
2022-04-15 | B | 480 |
2022-04-18 | A | 65 |
2022-04-19 | A | 45 |
2022-04-19 | B | 411 |
16 и 17 числа продаж не было, поэтому строки полностью отсутствуют. Если бы мы пытались рассчитать средние дневные продажи или построить модель прогноза временных рядов, этот формат был бы серьезной проблемой для анализа. Что нам нужно сделать, так это вставить ннули для пропущенных дней.
Вот основная концепция:
Создадим или выберем уникальные даты
Создадим или выберем уникальные продукты
Cross Join (декартово произведение) всех комбинаций 1 и 2
Outer Join
WITH GLOBAL_SPINE AS (
SELECT
ROW_NUMBER() OVER (
ORDER BY
NULL
) as INTERVAL_ID,
DATEADD(
'day',
(INTERVAL_ID - 1),
'2020-01-01T00:00' :: timestamp_ntz
) as SPINE_START,
DATEADD(
'day', INTERVAL_ID, '2020-01-01T00:00' :: timestamp_ntz
) as SPINE_END
FROM
TABLE (
GENERATOR(ROWCOUNT => 1097)
)
),
GROUPS AS (
SELECT
product,
MIN(sales_date) AS LOCAL_START,
MAX(sales_date) AS LOCAL_END
FROM
My_First_Table
GROUP BY
product
),
GROUP_SPINE AS (
SELECT
product,
SPINE_START AS GROUP_START,
SPINE_END AS GROUP_END
FROM
GROUPS G CROSS
JOIN LATERAL (
SELECT
SPINE_START,
SPINE_END
FROM
GLOBAL_SPINE S
WHERE
S.SPINE_START >= G.LOCAL_START
)
)
SELECT
G.product AS GROUP_BY_product,
GROUP_START,
GROUP_END,
T.*
FROM
GROUP_SPINE G
LEFT JOIN My_First_Table T ON sales_date >= G.GROUP_START
AND sales_date < G.GROUP_END
AND G.product = T.product;
Конечный результат будет выглядеть так:
sales_date product sales
2022-04-14 A 46
2022-04-14 B 409
2022-04-15 A 17
2022-04-15 B 480
2022-04-16 A 0
2022-04-16 B 0
2022-04-17 A 0
2022-04-17 B 0
2022-04-18 A 65
2022-04-18 B 0
2022-04-19 A 45
2022-04-19 B 411
Pivot / Unpivot
Иногда при анализе требуется реструктурировать таблицу. Например, у нас может быть список учащихся, предметов и оценок, но мы хотим разбить предметы по каждому столбцу. Мы все знаем и любим Excel из-за его сводных таблиц. Но пробовали ли вы когда-нибудь сделать это в SQL? Мало того, что каждая база данных имеет раздражающие различия в том, как поддерживается PIVOT, так еще и синтаксис неинтуитивен и легко забывается.
До:
Student | Subject | Grade |
Jared | Mathematics | 61 |
Jared | Geography | 94 |
Jared | Phys Ed | 98 |
Patrick | Mathematics | 99 |
Patrick | Geography | 93 |
Patrick | Phys Ed | 4 |
SELECT Student, MATHEMATICS, GEOGRAPHY, PHYS_ED
FROM ( SELECT Student, Grade, Subject FROM skool)
PIVOT ( AVG ( Grade ) FOR Subject IN ( 'Mathematics', 'Geography', 'Phys Ed' ) ) as p
( Student, MATHEMATICS, GEOGRAPHY, PHYS_ED );
Результат:
Student | Mathematics | Geography | Phys Ed |
Jared | 61 | 94 | 98 |
Patrick | 99 | 93 | 4 |
Быстрое кодирование (One-hot Encoding)
Это не обязательно сложно, но требует времени. Большинство специалистов по данным не рассматривают возможность Быстрое кодирование в SQL. Хотя синтаксис прост, они скорее предпочтут перенести данные из хранилища данных, чем выполнять утомительную задачу написания оператора CASE из 26 строк. Мы их не виним!
Однако мы рекомендуем воспользоваться преимуществом вашего хранилища данных и его вычислительной мощностью. Вот пример использования STATE в качестве столбца.
Таблица до преобразования
Babyname | State | Qty |
Alice | AL | 156 |
Alice | AK | 146 |
Alice | PA | 654 |
… | … | … |
Zelda | NY | 417 |
Zelda | AL | 261 |
Zelda | CO | 321 |
Настраиваемый SQL для кодинга
SELECT *,
CASE WHEN State = 'AL' THEN 1 ELSE 0 END as STATE_AL,
CASE WHEN State = 'AK' THEN 1 ELSE 0 END as STATE_AK,
CASE WHEN State = 'AZ' THEN 1 ELSE 0 END as STATE_AZ,
CASE WHEN State = 'AR' THEN 1 ELSE 0 END as STATE_AR,
CASE WHEN State = 'AS' THEN 1 ELSE 0 END as STATE_AS,
CASE WHEN State = 'CA' THEN 1 ELSE 0 END as STATE_CA,
CASE WHEN State = 'CO' THEN 1 ELSE 0 END as STATE_CO,
CASE WHEN State = 'CT' THEN 1 ELSE 0 END as STATE_CT,
CASE WHEN State = 'DC' THEN 1 ELSE 0 END as STATE_DC,
CASE WHEN State = 'FL' THEN 1 ELSE 0 END as STATE_FL,
CASE WHEN State = 'GA' THEN 1 ELSE 0 END as STATE_GA,
CASE WHEN State = 'HI' THEN 1 ELSE 0 END as STATE_HI,
CASE WHEN State = 'ID' THEN 1 ELSE 0 END as STATE_ID,
CASE WHEN State = 'IL' THEN 1 ELSE 0 END as STATE_IL,
CASE WHEN State = 'IN' THEN 1 ELSE 0 END as STATE_IN,
CASE WHEN State = 'IA' THEN 1 ELSE 0 END as STATE_IA,
CASE WHEN State = 'KS' THEN 1 ELSE 0 END as STATE_KS,
CASE WHEN State = 'KY' THEN 1 ELSE 0 END as STATE_KY,
CASE WHEN State = 'LA' THEN 1 ELSE 0 END as STATE_LA,
CASE WHEN State = 'ME' THEN 1 ELSE 0 END as STATE_ME,
CASE WHEN State = 'MD' THEN 1 ELSE 0 END as STATE_MD,
CASE WHEN State = 'MA' THEN 1 ELSE 0 END as STATE_MA,
CASE WHEN State = 'MI' THEN 1 ELSE 0 END as STATE_MI,
CASE WHEN State = 'MN' THEN 1 ELSE 0 END as STATE_MN,
CASE WHEN State = 'MS' THEN 1 ELSE 0 END as STATE_MS,
CASE WHEN State = 'MO' THEN 1 ELSE 0 END as STATE_MO,
CASE WHEN State = 'MT' THEN 1 ELSE 0 END as STATE_MT,
CASE WHEN State = 'NE' THEN 1 ELSE 0 END as STATE_NE,
CASE WHEN State = 'NV' THEN 1 ELSE 0 END as STATE_NV,
CASE WHEN State = 'NH' THEN 1 ELSE 0 END as STATE_NH,
CASE WHEN State = 'NJ' THEN 1 ELSE 0 END as STATE_NJ,
CASE WHEN State = 'NM' THEN 1 ELSE 0 END as STATE_NM,
CASE WHEN State = 'NY' THEN 1 ELSE 0 END as STATE_NY,
CASE WHEN State = 'NC' THEN 1 ELSE 0 END as STATE_NC,
CASE WHEN State = 'ND' THEN 1 ELSE 0 END as STATE_ND,
CASE WHEN State = 'OH' THEN 1 ELSE 0 END as STATE_OH,
CASE WHEN State = 'OK' THEN 1 ELSE 0 END as STATE_OK,
CASE WHEN State = 'OR' THEN 1 ELSE 0 END as STATE_OR,
CASE WHEN State = 'PA' THEN 1 ELSE 0 END as STATE_PA,
CASE WHEN State = 'RI' THEN 1 ELSE 0 END as STATE_RI,
CASE WHEN State = 'SC' THEN 1 ELSE 0 END as STATE_SC,
CASE WHEN State = 'SD' THEN 1 ELSE 0 END as STATE_SD,
CASE WHEN State = 'TN' THEN 1 ELSE 0 END as STATE_TN,
CASE WHEN State = 'TX' THEN 1 ELSE 0 END as STATE_TX,
CASE WHEN State = 'UT' THEN 1 ELSE 0 END as STATE_UT,
CASE WHEN State = 'VT' THEN 1 ELSE 0 END as STATE_VT,
CASE WHEN State = 'VA' THEN 1 ELSE 0 END as STATE_VA,
CASE WHEN State = 'WA' THEN 1 ELSE 0 END as STATE_WA,
CASE WHEN State = 'WV' THEN 1 ELSE 0 END as STATE_WV,
CASE WHEN State = 'WI' THEN 1 ELSE 0 END as STATE_WI,
CASE WHEN State = 'WY' THEN 1 ELSE 0 END as STATE_WY
FROM BABYTABLE;
Финальный результат:
Babyname | State | State_AL | State_AK | … | State_CO | Qty |
Alice | AL | 1 | 0 | … | 0 | 156 |
Alice | AK | 0 | 1 | … | 0 | 146 |
Alice | PA | 0 | 0 | … | 0 | 654 |
… | … | … | … | |||
Zelda | NY | 0 | 0 | … | 0 | 417 |
Zelda | AL | 1 | 0 | … | 0 | 261 |
Zelda | CO | 0 | 0 | … | 1 | 321 |
Анализ данных
При анализе потребительской корзины или поиске связи в данных первым шагом часто является форматирование данных для объединения каждой транзакции в одну запись. Это может быть сложно для написания , но ваше хранилище данных предназначено для эффективной обработки этих данных.
Типичные транзакции:
SALESORDERNUMBER CUSTOMERKEY ENGLISHPRODUCTNAME LISTPRICE WEIGHT ORDERDATE
SO51247 11249 Mountain-200 Black 2294.99 23.77 1/1/2013
SO51247 11249 Water Bottle - 30 oz. 4.99 1/1/2013
SO51247 11249 Mountain Bottle Cage 9.99 1/1/2013
SO51246 25625 Sport-100 Helmet 34.99 12/31/2012
SO51246 25625 Water Bottle - 30 oz. 4.99 12/31/2012
SO51246 25625 Road Bottle Cage 8.99 12/31/2012
SO51246 25625 Touring-1000 Blue 2384.07 25.42 12/31/2012
WITH order_detail as (
SELECT
SALESORDERNUMBER,
listagg(ENGLISHPRODUCTNAME, ', ') WITHIN group (
order by
ENGLISHPRODUCTNAME
) as ENGLISHPRODUCTNAME_listagg,
COUNT(ENGLISHPRODUCTNAME) as num_products
FROM
transactions
GROUP BY
SALESORDERNUMBER
)
SELECT
ENGLISHPRODUCTNAME_listagg,
count(SALESORDERNUMBER) as NumTransactions
FROM
order_detail
where
num_products > 1
GROUP BY
ENGLISHPRODUCTNAME_listagg
order by
count(SALESORDERNUMBER) desc;
NUMTRANSACTIONS | ENGLISHPRODUCTNAME_LISTAGG |
207 | Mountain Bottle Cage, Water Bottle – 30 oz. |
200 | Mountain Tire Tube, Patch Kit/8 Patches |
142 | LL Road Tire, Patch Kit/8 Patches |
137 | Patch Kit/8 Patches, Road Tire Tube |
135 | Patch Kit/8 Patches, Touring Tire Tube |
132 | HL Mountain Tire, Mountain Tire Tube, Patch Kit/8 Patches |
Работа с временными рядами
Агрегации временных рядов используются не только учеными данных, но и для аналитики. Что делает их сложными, так это то, что оконные функции требуют правильного форматирования данных.
Например, если вы хотите рассчитать среднюю сумму продаж за последние 14 дней, оконные функции требуют, чтобы все данные о продажах были разбиты на одну строку в день. К сожалению, любой, кто раньше работал с данными о продажах, знает, что обычно они хранятся на уровне транзакций. Здесь пригодится агрегация временных рядов. Вы можете создавать агрегированные исторические показатели без переформатирования всего набора данных. Это также удобно, если мы хотим добавить несколько метрик одновременно:
Средние продажи за последние 14 дней
Самая крупная покупка за последние 6 месяцев
Подсчет различных типов продуктов за последние 90 дней
Если бы вы хотели использовать оконные функции, каждую метрику нужно было бы построить независимо в несколько шагов.
Лучший способ справиться с этим — использовать общие табличные выражения (CTE) для определения каждого из предварительно агрегированных исторических окон.
Например:
Transaction ID | Customer ID | Product Type | Purchase Amt | Transaction Date |
65432 | 101 | Grocery | 101.14 | 2022-03-01 |
65493 | 101 | Grocery | 98.45 | 2022-04-30 |
65494 | 101 | Automotive | 239.98 | 2022-05-01 |
66789 | 101 | Grocery | 86.55 | 2022-05-22 |
66981 | 101 | Pharmacy | 14 | 2022-06-15 |
67145 | 101 | Grocery | 93.12 | 2022-06-22 |
WITH BASIC_OFFSET_14DAY AS (
SELECT
A.CustomerID,
A.TransactionDate,
AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST14DAY,
MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST14DAY,
COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST14DAY
FROM
My_First_Table A
INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID
AND 1 = 1
WHERE
B.TransactionDate >= DATEADD(day, -14, A.TransactionDate)
AND B.TransactionDate <= A.TransactionDate
GROUP BY
A.CustomerID,
A.TransactionDate
),
BASIC_OFFSET_90DAY AS (
SELECT
A.CustomerID,
A.TransactionDate,
AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST90DAY,
MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST90DAY,
COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST90DAY
FROM
My_First_Table A
INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID
AND 1 = 1
WHERE
B.TransactionDate >= DATEADD(day, -90, A.TransactionDate)
AND B.TransactionDate <= A.TransactionDate
GROUP BY
A.CustomerID,
A.TransactionDate
),
BASIC_OFFSET_180DAY AS (
SELECT
A.CustomerID,
A.TransactionDate,
AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST180DAY,
MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST180DAY,
COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST180DAY
FROM
My_First_Table A
INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID
AND 1 = 1
WHERE
B.TransactionDate >= DATEADD(day, -180, A.TransactionDate)
AND B.TransactionDate <= A.TransactionDate
GROUP BY
A.CustomerID,
A.TransactionDate
)
SELECT
src.*,
BASIC_OFFSET_14DAY.AVG_PURCHASEAMOUNT_PAST14DAY,
BASIC_OFFSET_14DAY.MAX_PURCHASEAMOUNT_PAST14DAY,
BASIC_OFFSET_14DAY.COUNT_DISTINCT_TRANSACTIONID_PAST14DAY,
BASIC_OFFSET_90DAY.AVG_PURCHASEAMOUNT_PAST90DAY,
BASIC_OFFSET_90DAY.MAX_PURCHASEAMOUNT_PAST90DAY,
BASIC_OFFSET_90DAY.COUNT_DISTINCT_TRANSACTIONID_PAST90DAY,
BASIC_OFFSET_180DAY.AVG_PURCHASEAMOUNT_PAST180DAY,
BASIC_OFFSET_180DAY.MAX_PURCHASEAMOUNT_PAST180DAY,
BASIC_OFFSET_180DAY.COUNT_DISTINCT_TRANSACTIONID_PAST180DAY
FROM
My_First_Table src
LEFT OUTER JOIN BASIC_OFFSET_14DAY ON BASIC_OFFSET_14DAY.TransactionDate = src.TransactionDate
AND BASIC_OFFSET_14DAY.CustomerID = src.CustomerID
LEFT OUTER JOIN BASIC_OFFSET_90DAY ON BASIC_OFFSET_90DAY.TransactionDate = src.TransactionDate
AND BASIC_OFFSET_90DAY.CustomerID = src.CustomerID
LEFT OUTER JOIN BASIC_OFFSET_180DAY ON BASIC_OFFSET_180DAY.TransactionDate = src.TransactionDate
AND BASIC_OFFSET_180DAY.CustomerID = src.CustomerID;
view raw
Так выглядит запрос. Ниже приведен результат:
Transaction ID | Customer ID | Product Type | Purchase Amt | Transaction Date | Avg Sales Past 14 Days | Max Purchase Past 6 months | Count Distinct Product Type last 90 days |
65432 | 101 | Grocery | 101.14 | 2022-03-01 | 101.14 | 101.14 | 1 |
65493 | 101 | Grocery | 98.45 | 2022-04-30 | 98.45 | 101.14 | 2 |
65494 | 101 | Automotive | 239.98 | 2022-05-01 | 169.21 | 239.98 | 2 |
66789 | 101 | Grocery | 86.55 | 2022-05-22 | 86.55 | 239.98 | 2 |
66981 | 101 | Pharmacy | 14 | 2022-06-15 | 14 | 239.98 | 3 |
67145 | 101 | Grocery | 93.12 | 2022-06-22 | 53.56 | 239.98 | 3 |
Вывод
Я надеюсь, что эта статья поможет пролить свет на различные проблемы, с которыми сталкивается специалист по работе с данными при работе с современным стеком данных. SQL — палка о двух концах, когда речь идет о запросах к облачному хранилищу. Хотя централизация вычислений в облачном хранилище данных увеличивает скорость, иногда требуются дополнительные навыки работы с SQL для получения результата. Я надеюсь, что эта часть помогла ответить на вопросы и показала синтаксис и код, необходимые для решения этих проблем.