5 самых сложных примеров в SQL при аналитике данных.

https://t.me/data_analysis_ml

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

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

Чтобы помочь вам в рабочем процессе, я приведу примеры структуры данных до и после выполнения преобразования. Я также предоставил фактические запросы SQL, необходимый для выполнения каждого из 5 самых сложных преобразований. Мы предоставим ссылки на динамический SQL запрос для каждого преобразования, чтобы вы могли использовать SQL, необходимый для вашего анализа данных!

Начнем!

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

sales_dateproductsales
2022-04-14A46
2022-04-14B409
2022-04-15A17
2022-04-15B480
2022-04-18A65
2022-04-19A45
2022-04-19B411

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, так еще и синтаксис неинтуитивен и легко забывается.

До:

StudentSubjectGrade
JaredMathematics61
JaredGeography94
JaredPhys Ed98
PatrickMathematics99
PatrickGeography93
PatrickPhys Ed4
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 );

Результат:

StudentMathematicsGeographyPhys Ed
Jared619498
Patrick99934

Быстрое кодирование (One-hot Encoding)

Это не обязательно сложно, но требует времени. Большинство специалистов по данным не рассматривают возможность Быстрое кодирование в SQL. Хотя синтаксис прост, они скорее предпочтут перенести данные из хранилища данных, чем выполнять утомительную задачу написания оператора CASE из 26 строк. Мы их не виним!

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

Таблица до преобразования

BabynameStateQty
AliceAL156
AliceAK146
AlicePA654
ZeldaNY417
ZeldaAL261
ZeldaCO321

Настраиваемый 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;

Финальный результат:

BabynameStateState_ALState_AKState_COQty
AliceAL100156
AliceAK010146
AlicePA000654
ZeldaNY000417
ZeldaAL100261
ZeldaCO001321

Анализ данных

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

Типичные транзакции:

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;
NUMTRANSACTIONSENGLISHPRODUCTNAME_LISTAGG
207Mountain Bottle Cage, Water Bottle – 30 oz.
200Mountain Tire Tube, Patch Kit/8 Patches
142LL Road Tire, Patch Kit/8 Patches
137Patch Kit/8 Patches, Road Tire Tube
135Patch Kit/8 Patches, Touring Tire Tube
132HL Mountain Tire, Mountain Tire Tube, Patch Kit/8 Patches

Работа с временными рядами

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

Например, если вы хотите рассчитать среднюю сумму продаж за последние 14 дней, оконные функции требуют, чтобы все данные о продажах были разбиты на одну строку в день. К сожалению, любой, кто раньше работал с данными о продажах, знает, что обычно они хранятся на уровне транзакций. Здесь пригодится агрегация временных рядов. Вы можете создавать агрегированные исторические показатели без переформатирования всего набора данных. Это также удобно, если мы хотим добавить несколько метрик одновременно:

Средние продажи за последние 14 дней
Самая крупная покупка за последние 6 месяцев
Подсчет различных типов продуктов за последние 90 дней
Если бы вы хотели использовать оконные функции, каждую метрику нужно было бы построить независимо в несколько шагов.

Лучший способ справиться с этим — использовать общие табличные выражения (CTE) для определения каждого из предварительно агрегированных исторических окон.

Например:

Transaction IDCustomer IDProduct TypePurchase AmtTransaction Date
65432101Grocery101.142022-03-01
65493101Grocery98.452022-04-30
65494101Automotive239.982022-05-01
66789101Grocery86.552022-05-22
66981101Pharmacy142022-06-15
67145101Grocery93.122022-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 IDCustomer IDProduct TypePurchase AmtTransaction DateAvg Sales Past 14 DaysMax Purchase Past 6 monthsCount Distinct Product Type last 90 days
65432101Grocery101.142022-03-01101.14101.141
65493101Grocery98.452022-04-3098.45101.142
65494101Automotive239.982022-05-01169.21239.982
66789101Grocery86.552022-05-2286.55239.982
66981101Pharmacy142022-06-1514239.983
67145101Grocery93.122022-06-2253.56239.983

Вывод

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

Ответить