Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: sql

Сквозной идентификатор: решение проблемы мэтчинга персональных данных студентов Refocus

Mon, 16 Sep 2024 17:57:31 +0300

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

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

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

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

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

Кейс Refocus: данные и путь клиента

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

В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:

amoCRM, где фиксируется весь клиентский путь студента;
Discord, где проходило сопровождение студентов;
Thinkific, сама образовательная платформа с курсами.

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

В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.

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

Задача и поиск решения

Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.

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

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

имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);
телефон — с кодом страны или без, с пробелами, дефисами или слитно;
электронная почта — длинные строки сложного формата, в которых легко опечататься.

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

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

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

Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.

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

Реализация решения и рабочий процесс

Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.

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

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        IFNULL(ae.name, ap.name) as contact_name,
        ap.phone, ae.email,
        split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ai.thinkific_id, ai.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, email,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_
        FROM `Differture.amo_emails`
      ) ae using(lead_id)
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, phone,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_
        FROM `Differture.amo_phones`
      ) ap using(lead_id)
      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)
      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id пока не происходит. А происходят по email_first_part, адресу почты до символа @:

select distinct * from th_amo_ds_rf
    left join calendly ce using(email_first_part)
    left join typeform_live tfl on email_first_part=tf_email_first_part
    left join typeform tf using(email_first_part)
    left join csat using(email_first_part)

Первым шагом по практическому введению идентификатора была таблица students_main_info, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:

student_full_name	string
student_email	string
student_country_id	string
student_country_name	string
student_courses_ids	array
student_courses_names	array
student_cohort_id	string
student_cohort_name	string
cohort_community_manager_name	string
cohort_community_manager_email	string
student_onboarding_live_session_id	string
student_onboarding_live_session_time	string
student_onboarding_live_session_zoom_url	string
amo_contact_id	string
intercom_contact_id	string
thinkific_student_id	string
discord_user_id	string
discord_user_discord_id	string
discord_guild_id	string
discord_channel_id	string
discord_roles	string

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

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

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

Что получилось в итоге

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

Вот так выглядела схема соотношения этих таблиц:

А вот так выглядела основная таблица Students:

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

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

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

Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot) и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id и отбирались данные, которые понадобятся нам дальше.

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        ac.name as contact_name, ac.phone, ac.email,
        split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ac.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,
          ac.name, emails as email, phone, student_id, ic.intercom_id,
          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_
        from `Differture.AmoContacts` ac
        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id
        left join `Differture.IntercomContacts` ic using(student_id)
        , unnest(ac.emails) emails
      ) ac using(lead_id)
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:

th_amo_ds_rf as (
      select distinct * except (channel_id, channel),
        ifnull(channel_id, 'Not in discord') as channel_id,
        ifnull(channel, 'Not in discord') as channel
      from thinkific_amo_refunds
      full outer join discord using(student_id)
    )

Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:

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

Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.

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

Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.

Выводы

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

Десять советов, чтобы писать SQL-код, который приятно читать и использовать

Tue, 22 Feb 2022 16:24:18 +0300

Перевод статьи ”10 Best Practices to Write Readable and Maintainable SQL Code” автора David Martins

Как писать SQL-запросы, которые ваша команда сможет легко читать и использовать?

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

Думаю, вы осознаёте важность наличия общих правил по написанию SQL-запросов в команде. Эта статья может стать отличным руководством для формирования таких правил!

1. Используйте верхний регистр для ключевых слов

Начнем с базовых вещей: используйте заглавные буквы для ключевых слов SQL и строчные буквы для обозначения таблиц и столбцов. Также рекомендуется использовать прописные буквы для функций SQL (FIRST_VALUE(), DATE_TRUNC() и т. д.), хотя это спорно.

Избегать:

select id, name from company.customers

Предпочтительно:

SELECT id, name FROM company.customers

2. Используйте Snake Case для схем, таблиц, столбцов

В разных языках программирования есть разные оптимальные стили написания названий из нескольких слов: camelCase, PascalCase, kebab-case, and snake_case являются наиболее распространенными.

Если говорить про SQL, Snake Case (иногда называемый регистром подчеркивания) является наиболее широко используемым правилом. Чтобы писать в стиле snake_case, нужно заменить пробелы знаками подчеркивания. При этом все слова пишутся строчными буквами.

Избегать:

SELECT Customers.id, 
       Customers.name, 
       COUNT(WebVisit.id) as nbVisit
FROM COMPANY.Customers
JOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId

WHERE Customers.age <= 30
GROUP BY Customers.id, Customers.name

Предпочтительно:

SELECT customers.id, 
       customers.name, 
       COUNT(web_visit.id) as nb_visit
FROM company.customers
JOIN company.web_visit ON customers.id = web_visit.customer_id

WHERE customers.age <= 30
GROUP BY customers.id, customers.name

Хотя некоторым нравится использовать разные стили составных названий (чтобы различать схемы, таблицы и столбцы), я бы рекомендовал придерживаться Snake Case.

3. Используйте псевдонимы, чтобы сделать запрос понятнее

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

Избегать:

SELECT customers.id, 
       customers.name, 
       customers.context_col1,
       nested.f0_
FROM company.customers
JOIN (
          SELECT customer_id,
                 MIN(date)
          FROM company.purchases
          GROUP BY customer_id
      ) ON customer_id = customers.id

Предпочтительно:

SELECT customers.id, 
       customers.name, 
       customers.context_col1 as ip_address,
       first_purchase.date    as first_purchase_date
FROM company.customers
JOIN (
          SELECT customer_id,
                 MIN(date) as date
          FROM company.purchases
          GROUP BY customer_id
      ) AS first_purchase 
        ON first_purchase.customer_id = customers.id

Я обычно использую для столбцов строчные буквы “as”, а для таблиц — прописные “AS”.

4. Форматирование: осторожно используйте отступы и пробелы

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

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

Избегать:

SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date
FROM company.customers
LEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase 
ON first_purchase.customer_id = customers.id 
WHERE customers.age<=30

Предпочтительно:

SELECT customers.id, 
       customers.name, 
       customers.age, 
       customers.gender, 
       customers.salary,
       first_purchase.date
FROM company.customers
LEFT JOIN (
              SELECT customer_id,
                     MIN(date) as date 
              FROM company.purchases
              GROUP BY customer_id
          ) AS first_purchase 
            ON first_purchase.customer_id = customers.id
WHERE customers.age <= 30

Обратите внимание, как использованы пробелы в условии “where”.

Избегать:

SELECT id WHERE customers.age<=30

Предпочтительно:

SELECT id WHERE customers.age <= 30

5. Избегайте Select*

Не забывайте об этом правиле: вам следует точно указывать, какие элементы таблицы вы хотите выбрать и забыть про Select*!

Select* делает ваш запрос неясным, поскольку он скрывает намерения, стоящие за запросом. Кроме того, помните, что ваши таблицы могут эволюционировать и влиять на Select*. Вот почему я не большой поклонник инструкции EXCEPT().

Избегать:

SELECT * EXCEPT(id) FROM company.customers

Предпочтительно:

SELECT name,
       age,
       salary
FROM company.customers

6. Используйте синтаксис JOIN ANSI-92

…для соединения таблиц, вместо условия WHERE. Несмотря на то, что для соединения таблиц можно использовать как условие WHERE, так и условие JOIN, лучше использовать синтаксис JOIN/ANSI-92.

Хотя с точки зрения производительности разницы нет, условие JOIN отделяет логику отношения от фильтров.

Избегать:

SELECT customers.id, 
       customers.name, 
       COUNT(transactions.id) as nb_transaction
FROM company.customers, company.transactions
WHERE customers.id = transactions.customer_id
      AND customers.age <= 30
GROUP BY customers.id, customers.name

Предпочтительно:

SELECT customers.id, 
       customers.name, 
       COUNT(transactions.id) as nb_transaction
FROM company.customers
JOIN company.transactions ON customers.id = transactions.customer_id
WHERE customers.age <= 30
GROUP BY customers.id, customers.name

Синтаксис, основанный на условии “Where”, также известный как ANSI-89, старше нового ANSI-92, поэтому он всё ещё очень распространен. Сегодня большинство разработчиков и аналитиков данных используют синтаксис JOIN.

7. Используйте обобщённое табличное выражение (Common Table Expression — CTE)

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

Он работает как производная таблица с двумя преимуществами:

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

CTE объявляется с помощью инструкции WITH … AS:

WITH my_cte AS
(
  SELECT col1, col2 FROM table
)
SELECT * FROM my_cte

Избегать:

SELECT customers.id, 
       customers.name, 
       customers.age, 
       customers.gender, 
       customers.salary,
       persona_salary.avg_salary as persona_avg_salary,
       first_purchase.date
FROM company.customers
JOIN (
          SELECT customer_id,
                 MIN(date) as date 
          FROM company.purchases
          GROUP BY customer_id
      ) AS first_purchase 
        ON first_purchase.customer_id = customers.id
JOIN (
          SELECT age,
             gender,
             AVG(salary) as avg_salary
         FROM company.customers
         GROUP BY age, gender
      ) AS persona_salary 
        ON persona_salary.age = customers.age
           AND persona_salary.gender = customers.gender
WHERE customers.age <= 30

Предпочтительно:

WITH first_purchase AS
(
   SELECT customer_id,
          MIN(date) as date 
   FROM company.purchases
   GROUP BY customer_id
),
persona_salary AS
(
   SELECT age,
          gender,
          AVG(salary) as avg_salary
   FROM company.customers
   GROUP BY age, gender
)
SELECT customers.id, 
       customers.name, 
       customers.age, 
       customers.gender, 
       customers.salary,
       persona_salary.avg_salary as persona_avg_salary,
       first_purchase.date
FROM company.customers
JOIN first_purchase ON first_purchase.customer_id = customers.id
JOIN persona_salary ON persona_salary.age = customers.age
                       AND persona_salary.gender = customers.gender
WHERE customers.age <= 30

8. Иногда стоит разделить запрос на несколько

...но не увлекайтесь. Давайте разберем на примере.

Я часто использую AirFlow для выполнения SQL-запросов в BigQuery, преобразования данных и подготовки визуализации данных. В нем есть оркестратор рабочих процессов (Airflow), который выполняет запросы в определенном порядке. В некоторых ситуациях лучше разбивать сложные запросы на несколько более мелких.

Вместо:

CREATE TABLE customers_infos AS
SELECT customers.id,
       customers.salary,
       traffic_info.weeks_since_last_visit,
       category_info.most_visited_category_id,
       purchase_info.highest_purchase_value
FROM company.customers
LEFT JOIN ([..]) AS traffic_info
LEFT JOIN ([..]) AS category_info
LEFT JOIN ([..]) AS purchase_info

Вы могли бы использовать:

## STEP1: Create initial table
CREATE TABLE public.customers_infos AS
SELECT customers.id,
       customers.salary,
       0 as weeks_since_last_visit,
       0 as most_visited_category_id,
       0 as highest_purchase_value
FROM company.customers
## STEP2: Update traffic infos
UPDATE public.customers_infos
SET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE,
                                       last_visit.date, WEEK)
FROM (
         SELECT customer_id, max(visit_date) as date
         FROM web.traffic_info
         GROUP BY customer_id
     ) AS last_visit
WHERE last_visit.customer_id = customers_infos.id
## STEP3: Update category infos
UPDATE public.customers_infos
SET most_visited_category_id = [...]
WHERE [...]
## STEP4: Update purchase infos
UPDATE public.customers_infos
SET highest_purchase_value = [...]
WHERE [...]

ПРЕДУПРЕЖДЕНИЕ!
Несмотря на то, что этот метод отлично подходит для упрощения сложных запросов, вместе с повышением читабельности кода, вы можете здорово понизить его производительность.

Это особенно важно, если вы работаете с базой данных OLAP или любой колоночной базой данных, оптимизированной для агрегационных и аналитических запросов (SELECT, AVG, MIN, MAX, …), но менее производительной, когда речь идет о транзакциях (UPDATE).

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

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

9. Осмысленные названия, основанные на ваших внутренних правилах

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

“В компьютерных науках есть только две сложные проблемы: аннулирование кэша и придумывание названий.” — Фил Карлтон

Вот примеры правил, которые я использую:

Схемы

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

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

Любая таблица, которая будет доступна для стороннего инструмента, находится в *общедоступной* схеме. Инструменты визуализации данных, такие как DataStudio или Tableau, получают данные из неё.
Поскольку мы используем машинное обучение с BQML, ****у нас есть специальная схема *machine_learning.***

Таблицы

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

Каждая таблица в нашей общедоступной схеме имеет префикс имени дашборда. Выглядит это примерно так:

product_inbox_usage
product_addon_competitor_stats
marketing_acquisition_agencies
Executive_funnel_overview

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

Не стесняйтесь использовать эти примеры для создания своих правил.

10. Пишите полезные комментарии… но не слишком много

Примечание переводчика: Автор пишет, что “хорошо написанный код с правильными названиями не нуждается в комментариях” и иронично продемонстрировал детально расписанный код вообще без комментариев. Мы все-таки за то, чтобы необходимые пояснения в коде были, ведь это сильно ускоряет понимание сути запроса.

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

Тем не менее, комментарии могут быть полезны в некоторых ситуациях. Но перебарщивать с ними не стоит!

Избегать:

WITH fp AS
(
   SELECT c_id,               # customer id
          MIN(date) as dt     # date of first purchase
   FROM company.purchases
   GROUP BY c_id
),
ps AS
(
   SELECT age,
          gender,
          AVG(salary) as avg
   FROM company.customers
   GROUP BY age, gender
)
SELECT customers.id, 
       ct.name, 
       ct.c_age,            # customer age
       ct.gender,
       ct.salary,
       ps.avg,              # average salary of a similar persona
       fp.dt                # date of first purchase for this client
FROM company.customers ct
# join the first purchase on client id
JOIN fp ON c_id = ct.id
# match persona based on same age and genre
JOIN ps ON ps.age = c_age
           AND ps.gender = ct.gender
WHERE c_age <= 30

Предпочтительно:

WITH first_purchase AS
(
   SELECT customer_id,
          MIN(date) as date 
   FROM company.purchases
   GROUP BY customer_id
),
persona_salary AS
(
   SELECT age,
          gender,
          AVG(salary) as avg_salary
   FROM company.customers
   GROUP BY age, gender
)
SELECT customers.id, 
       customers.name, 
       customers.age, 
       customers.gender, 
       customers.salary,
       persona_salary.avg_salary as persona_avg_salary,
       first_purchase.date
FROM company.customers
JOIN first_purchase ON first_purchase.customer_id = customers.id
JOIN persona_salary ON persona_salary.age = customers.age
                       AND persona_salary.gender = customers.gender
WHERE customers.age <= 30

Вывод

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

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

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

Я надеюсь, что эта информация поможет вам вывести качество SQL на новый уровень!

Регулярные выражения как способ решения задач в SQL

Mon, 17 Jan 2022 16:48:16 +0300

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

Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.

Что такое «регулярное выражение»?
Регулярное выражение — последовательность букв и/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.
Давайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.

Количество гласных букв в выражении

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

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество согласных букв в выражении

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

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество цифр в выражении равно 3

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

with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2')) 
as map(id, source_type))

select source_type, COUNT(*) from (
select *, regexp_matches(source_type,'\d','g') as pattern from example_table ) as t
GROUP BY source_type
HAVING COUNT(*) = 3

В номере телефона есть два дефиса

Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.
В первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().

with example_table as (
  select * from (
    values 
    (1, '8931-123-456'), 
    (2, '8931123-456'), 
    (3, '+7812123456'), 
    (4, '8-931-123-42-24')
  )
as map(id, source_type))

select source_type, CASE WHEN COUNT(1) >= 2 THEN 'True' ELSE 'False' END from (
select *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t
GROUP BY 1

Все имена, которые написаны с большой буквы

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

with example_table as (
  select * from (
    values 
    (1, 'alex'), 
    (2, 'Alex'), 
    (3, 'Vasya'), 
    (4, 'petya')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t
GROUP BY 1

Вывести номера телефонов, которые попадают под паттерн +71234564578

Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.

with example_table as (
  select * from (
    values 
    (1, '+7(931)1234546'), 
    (2, '+79312991809'), 
    (3, '89311234565'), 
    (4, '244-02-38')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^\+7[0-9]{10}','g') as pattern from example_table ) as t
GROUP BY 1

Вывести все настоящие email-адреса

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

with example_table as (
    select * from (
    values
        (1, 'email.asd@ya.ru'),
        (2, 'something@new.ru'),
        (3, '@ya.ru'),
        (4, 'asdasd'),
        (5, '_asdasdasd@mail.ru'),
        (6, 'asd_asdas@mail.ru'),
        (7, '.asdasd@mail.ru'),
        (8, '007asd@email.com')
        ) as map(id, source_type)
)

select source_type 
from (
    select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\.[a-z]+$')
    from example_table ) as t

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

Три способа рассчитать накопленную сумму в SQL

Fri, 10 Dec 2021 19:10:34 +0300

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

Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.

Что у нас есть?

Date	Revenue
10.11.2021	1200
11.11.2021	1600
12.11.2021	800
13.11.2021	3000

Что мы хотим найти?

Date	Revenue	Cumulative Revenue
10.11.2021	1200	1200 ↓
11.11.2021	1600	2800↓
12.11.2021	800	3600 ↓
13.11.2021	3000	6600

На графике две этих переменных выглядят следующим образом:

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

Способ 1 — Идеальный — Используем оконные функции

Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.

SELECT
	date,
	revenue,
	SUM(revenue) OVER (ORDER BY date asc) as total
FROM leftjoin.daily_sales_sample 
ORDER BY date;

Способ 2 — Хитрый — Решение без оконных функций

Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:

SELECT * 
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
ORDER BY ds1.date, ds2.date;

А его результат:

Date 1	Revenue 1	Date 2	Revenue 2
10.11.2021	1200	10.11.2021	1200
11.11.2021	1600	10.11.2021	1200
11.11.2021	1600	11.11.2021	1600
12.11.2021	800	10.11.2021	1200
12.11.2021	800	11.11.2021	1600
12.11.2021	800	12.11.2021	800
13.11.2021	300	10.11.2021	1200
13.11.2021	300	11.11.2021	1600
13.11.2021	300	12.11.2021	800
13.11.2021	300	13.11.2021	300

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

SELECT
	ds1.date,
	ds1.revenue,
	SUM(ds2.revenue) as total
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
GROUP BY ds1.date, ds1.revenue
ORDER BY ds1.date;

Способ 3 — Специфический — Решение с помощью массивов в ClickHouse

Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.

SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)

Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе

Так, мы получим три массива значений:

dates	revs	total
[’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’]	[1200, 1600, 800, 300]	[1200, 2800, 3600, 3900]

Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.

SELECT dates, revs, total FROM
(SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)) as t
ARRAY JOIN dates, revs, total;

Бонус — Оконные функции в Clickhouse

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

SELECT date, runningAccumulate(revenue)
  FROM 
  (
    SELECT date, sumState(revenue) AS revenue
    FROM leftjoin.daily_sales_sample
    GROUP BY date 
    ORDER BY date ASC
  )
ORDER BY date

Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа :)

Моделирование LTV в SQL

Mon, 16 Aug 2021 09:32:37 +0300

У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, что такое Retention и как его посчитать. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.

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

В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.

Как посчитать LTV?

В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.

Посмотрим на классический график Retention:

Lifetime — это площадь фигуры под Retention:

Откуда взялись интегралы и площади можно подробнее узнать в этом материале

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

Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:

При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:

ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.

Строим LTV

Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:

--общее количество юзеров в когорте
with cohort as (
    select count(distinct id) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),

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

--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),

Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:

По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными агрегатными функциями в PostgreSQL и для известных X и Y по методу наименьших квадратов.

Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:

Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в этом мануале

Из свойства натурального логарифма следует, что:

Наклон считаем аналогичным образом:

Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:

--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),

И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:

lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),

Сравним прогноз на 180 дней с Retention:

Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:

select cumulative_lt as LT,
           cumulative_lt * 83.7 as LTV
from lt

Наконец, построим график LTV на 180 дней:

Весь запрос:

--общее количество юзеров в когорте
with cohort as (
    select count(*) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),
--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),
--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),
lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),
select cumulative_lt as LT,
            cumulative_lt * 83.7 as LTV
from lt

Тренинг по Clickhouse от Altinity

Mon, 09 Aug 2021 08:41:19 +0300

Буквально на днях закончил обучение Clickhouse от Altinity (101 Series Training). Для тех, кто только знакомится с Clickhouse Altinity предлагает базовый бесплатный тренинг: Data Warehouse Basics. Рекомендую начать с него, если планируете погружаться в обучение.

Сертификация от Altinity

Хочу поделиться своими впечатлениями об обучении и поделиться своим конспектом с тренинга.
Обучение стоит $500 и длится четыре дня по два часа, проводится в наше вечернее время (начиная с 19:00 GMT+3).

Сессия №1

Первый день в бОльшей степени повторяет пройденное в Data Warehouse Basics, однако в нем есть несколько новых идей, например о том, как можно получить полезную информацию о запросах из системных таблиц.

Например, такой query выдаст какие команды запущены и в каком они статусе:

SELECT command, is_done
FROM system.mutations
WHERE table = 'ontime'

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

ALTER TABLE ontime
 MODIFY COLUMN TailNum LowCardinality(String) CODEC(ZSTD(1))

Для тех, кто начинает погружение в Clickhouse первый день будет супер-полезным в том, чтобы разобраться с движками таблиц и синатксисом их создания, партициями, вставкой данных (к примеру, напрямую из S3).

INSERT INTO sdata
SELECT * FROM s3(
 'https://s3.us-east-1.amazonaws.com/d1-altinity/data/sdata*.csv.gz',
 'aws_access_key_id',
 'aws_secret_access_key',
 'Parquet',
 'DevId Int32, Type String, MDate Date, MDatetime
DateTime, Value Float64')

Сессия №2

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

Отдельное внимание устройству джойнов в Clickhouse

Мне было супер-полезно узнать про типы индексов в CH

Сессия №3

В рамках третьего дня коллеги делятся знаниями о том как работать с Kafka, JSON-объектами, которые хранятся в таблицах.
Интересно было узнать, что работа с типами данных массив в Clickhouse очень похоже на работу с массивами в Python:

WITH [1, 2, 4] AS array
SELECT
 array[1] AS First,
 array[2] AS Second,
 array[3] AS Third,
 array[-1] AS Last,
 length(array) AS Length

И при работе с массивами крутая фича это ARRAY JOIN, который «разворачивает» массив в плоскую реляционную таблицу:

Clickhouse позволяет эффективно взаимодействовать с JSON-объектами, которые хранятся в таблице:

-- Get a JSON string value
SELECT JSONExtractString(row, 'request') AS request
FROM log_row LIMIT 3
-- Get a JSON numeric value
SELECT JSONExtractInt(row, 'status') AS status
FROM log_row LIMIT 3

На примере этого кусочка кода отдельно извлекаются элементы JSON-массива ’request’ и ’status’.

Их можно сложить в ту же таблицу:

ALTER TABLE log_row
 ADD COLUMN
status Int16 DEFAULT
 JSONExtractInt(row, 'status')
ALTER TABLE log_row
UPDATE status = status WHERE 1 = 1

Сессия №4

А на заключительный четвертый день оставлена самая трудная тема с моей точки зрения: построение шардированных и реплицированных кластеров, построение запросов на распределенных серверах Clickhouse.

Отдельный респект Altinity за отличную подборку лабораторных заданий в ходе обучения.

Ссылки:

Нормализация данных через запрос в SQL

Thu, 13 May 2021 11:15:58 +0300

Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены данные, зависят результаты всей вашей работы.

Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.

В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().

Для работы возьмем таблицу students с данными о росте учащихся.

name	height
Иван	174
Петр	181
Денис	199
Ксения	158
Сергей	179
Ольга	165
Юлия	152
Кирилл	188
Антон	177
Софья	165

Min-Max масштабирование

Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.

Выполним масштабирование по формуле:

Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.

SQL-запрос с подзапросом:

SELECT height, 
       1.0 * (height-t1.min_height)/(t1.max_height - t1.min_height) AS scaled_minmax
  FROM students, 
      (SELECT min(height) as min_height, 
              max(height) as max_height 
         FROM students
      ) as t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - MIN(height) OVER ()) * 1.0 / (MAX(height) OVER () - MIN(height) OVER ()) AS scaled_minmax
  FROM students;

В результате мы получим переменные в диапазоне [0...1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.

name	height	scaled_minmax
Иван	174	0.46809
Петр	181	0.61702
Денис	199	1
Ксения	158	0.12766
Сергей	179	0.57447
Ольга	165	0.2766
Юлия	152	0
Кирилл	188	0.76596
Антон	177	0.53191
Софья	165	0.2766

Масштабирование для заданного диапазона

Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.
Формула для вычисления в этом случае такая:

Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.

SQL-запрос с подзапросом:

SELECT height, 
       ((height - min_height) * (20.0 - 10.0) / (max_height - min_height)) + 10 AS scaled_ab
  FROM students,
      (SELECT MAX(height) as max_height, 
              MIN(height) as min_height
         FROM students  
      ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       ((height - MIN(height) OVER() ) * (20.0 - 10.0) / (MAX(height) OVER() - MIN(height) OVER())) + 10.0 AS scaled_ab
  FROM students;

Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.

name	height	scaled_ab
Иван	174	14.68085
Петр	181	16.17021
Денис	199	20
Ксения	158	11.2766
Сергей	179	15.74468
Ольга	165	12.76596
Юлия	152	10
Кирилл	188	17.65957
Антон	177	15.31915
Софья	165	12.76596

Нормализация с помощью z-score

В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.

Вычисляется z-score по формуле:

SQL-запрос с подзапросом:

SELECT height, 
       (height - t1.mean) * 1.0 / t1.sigma AS zscore
  FROM students,
      (SELECT AVG(height) AS mean, 
              STDDEV(height) AS sigma
         FROM students
        ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - AVG(height) OVER()) * 1.0 / STDDEV(height) OVER() AS z-score
  FROM students;

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

name	height	zscore
Иван	174	0.01488
Петр	181	0.53582
Денис	199	1.87538
Ксения	158	-1.17583
Сергей	179	0.38698
Ольга	165	-0.65489
Юлия	152	-1.62235
Кирилл	188	1.05676
Антон	177	0.23814
Софья	165	-0.65489

Транзакции в SQLAlchemy

Fri, 12 Feb 2021 11:10:22 +0300

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

Сбор информации об участниках через VK API

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

Подробнее о том, как получить токен, можно прочитать в материале «Собираем данные по рекламным кампаниям ВКонтакте»

from sqlalchemy import create_engine
import pandas as pd
import requests
import time

token = '42hj2ehd3djdournf48fjurhf9r9o2eurnf48fjurhf9r9734'
group_id = 'leftjoin'

Чтобы узнать число подписчиков достаточно отправить метод groups.getMembers с любыми параметрами — в ответе всегда возвращается количество в поле count.

def get_subs_count(group_id):
    count = requests.get('https://api.vk.com/method/groups.getMembers', params={
        'access_token':token,
        'v':5.103,
        'group_id':group_id
    }).json()['response']['count']
    return count

Для примера будем брать имена, id, фамилии подписчиков, некоторую расширенную информацию и получать только по 10 подписчиков за раз, чтобы рассмотреть работу транзакций детально — каждые 10 подписчиков будут вставляться одной транзакцией. Введём дополнительное поле offset, чтобы знать, в какой итерации добавлены строки.

def get_subs_info(group_id, offset):
    response = requests.get('https://api.vk.com/method/groups.getMembers', params={
        'access_token':token,
        'v':5.103,
        'group_id':group_id,
        'offset':offset,
        'count':10,
        'fields':'sex, has_mobile, relation, can_post'
    }).json()['response']['items']
    df = pd.DataFrame(response)
    df['offset'] = offset
    return df

Транзакции

Наконец, можем подсоединиться к базе данных при помощи SQLAlchemy:

engine = create_engine('mysql+mysqlconnector://' +
                           'root' + ':' + '' + '@' +
                           'localhost' + '/' +
                           'transaction', echo=False)

У транзакций всегда должно быть начало — begin, и конец — commit. В случае, если произошла какая-то ошибка, можно сделать откат — rollback. Сперва получаем число подписчиков сообщество, и в каждой итерации цикла при помощи контекстного менеджера with ... as создаём новое подключение. Сразу после объявляем начало транзакции по этому подключению и с обработчиком исключений пробуем получить информацию о десяти подписчиках через функцию get_subs_info. Вставляем полученный датафрейм в таблицу методом to_sql и завершаем транзакцию при помощи метода commit(). В случае, если возникла какая-то ошибка — печатаем её на экран и отменяем транзакцию.

offset = 0
subs_count = get_subs_count(group_id)
while offset < subs_count:
    with engine.connect() as conn:
        transaction = conn.begin()
        try:
            df = get_subs_info(group_id, offset)
            df.to_sql('subscribers', con=conn, if_exists='append', index=False)
            transaction.commit()
        except Exception as E:
            print(E)
            transaction.rollback()
    time.sleep(1)
    offset += 10

Чтобы протестировать работу транзакций слегка обновим последний блок кода — добавим вызов ошибки ValueError после вставки данных в базу, если текущий offset равен 10.

offset = 0
subs_count = get_subs_count(group_id)
while offset < subs_count:
    with engine.connect() as conn:
        transaction = conn.begin()
        try:
            df = get_subs_info(group_id, offset)
            df.to_sql('subscribers', con=conn, if_exists='append', index=False)
            if offset == 10:
                raise(ValueError)
            transaction.commit()
        except Exception as E:
            print(E)
            transaction.rollback()
    time.sleep(1)
    offset += 10

Как и планировалось, данные за итерацию с offset = 10 не занесены в таблицу. Несмотря на то, что ошибка возникла уже после добавления новых данных, транзакция была прервана методом rollback() и завершение транзакции было отменено.

UNPIVOT данных с использованием CROSS JOIN

Fri, 08 Jan 2021 16:30:14 +0300

Зачастую мы получаем данные в предагрегированном виде, когда каждая отдельная колонка является посчитанной метрикой. По аналогии мы получаем подобный результат, когда строим сводную таблицу в Excel и используем некоторое количество фактов для агрегации. Но что делать, если нам нужно произвести обратную операцию — Unpivot?

Как поступить, если в датасете понадобилось трансформировать данные в реляционный вид? В Tableau есть фича Unpivot, которая сделает всё сама: если датасет построен из файла, достаточно выделить нужные колонки и нажать на кнопку «Pivot». А в некоторых диалектах SQL, например, в Transact, уже есть встроенные функции, которые тоже делают это сами.

Но в случае, если датасет построен на Custom SQL Query из базы данных, у которой в арсенале отсутствуют встроенные функции для трансформации в сводную и обратно, необходим какой-то другой подход, и Tableau порекомендует для такой таблицы:

ID	a	b	c
1	a1	b1	c1
2	a2	b2	c2

Воспользоваться таким стандартным универсальным, но не очень эффективным решением:

select id, ‘a’ AS col, a AS value
from yourtable
union all
select id, ‘b’ AS col, b AS value
from yourtable
union all
select id, ‘c’ AS col, c AS value
from yourtable

И в результате получить таблицу вида:

id	col	value
1	a	a1
2	a	a2
1	b	b1
2	b	b2
1	c	c1
2	c	c2

Порой, когда мы работаем с физической таблицей и нам надо быстро получить результаты для двух-трех колонок, действительно, подобное решение можно быстро применить, не задумываясь. Однако в случае, когда вместо таблицы содержится, например, сложный подзапрос с несколькими джойнами и нужно сделать Pivot для 5+ колонок, подзапрос вызовется целых 5+ раз, согласитесь, не очень действенно считать одно и тоже неоднократно. Вместо этого можно воспользоваться рецептом с CROSS JOIN, найденным на просторах Stack Overflow:

select t.id,
c.col,
    case c.col
        when 'a' then a
        when 'b' then b
        when 'c' then c
    end as data
from yourtable t
cross join
(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

Разберём запрос подробнее. CROSS JOIN — перекрёстное соединение, декартово произведение, или, проще говоря, произведение всех строк со всеми. За ненадобностью в синтаксисе CROSS JOIN отсутствует ON — мы объединяем не по какому-то конкретному полю две таблицы, а сразу по всем существующим строкам.

Сначала мы формируем таблицу со всеми колонками, предназначенными для преобразования в строки. В нашем случае это колонки a, b и c: поэтому мы сделали таблицу c, в которой будет колонка col со значениями a, b и c:

(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

Выглядит она так:

col

Затем таблицы yourtable и c объединятся перекрестным соединением, а после мы возьмём поля id, col и в зависимости от того, как называется ячейка в col, подставим соответствующие данные в поле data.

select t.id,
c.col,
    case c.col
        when 'a' then a
        when 'b' then b
        when 'c' then c
    end as value
from yourtable t
cross join
(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

В итоге получим ту же самую искомую таблицу, с которой уже можно удобно работать любым аналитическим инструментом:

id	col	value
1	a	a1
2	a	a2
1	b	b1
2	b	b2
1	c	c1
2	c	c2

Конференция Coalesce от dbt: что посмотреть?

Fri, 11 Dec 2020 13:27:14 +0300

С 7 по 11 декабря проходила конференция Coalesce, о которой я рассказывал ранее. В этом году все организаторы решили проводить конференции по 5 дней с кучей докладов.

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

Однако мне удалось посмотреть большую часть докладов, кое-что пролистывая. Для начала коротко в целом о впечатлениях: очень круто изучать доклады с подобной конференции как Coalesce, потому что речь идет в основном о современных инструментах и облачных решениях. Почти в каждом докладе можно услышать про Redshift / BigQuery / Snowflake, а с точки зрения BI: Mode / Tableau / Looker / Metabase. В центре всего, разумеется, dbt.

Мой шорт-лист докладов, которые рекомендую изучить:

dbt 101 — вводный доклад и интро в то, что такое dbt и как его используют
Kimball in the context of the modern data warehouse: what’s worth keeping, and what’s not — интересный и очень-очень спорный доклад, который вызвал массу вопросов в slack dbt. Вкратце, автор предлагает перейти на «широкие» аналитические таблицы и отказаться от нормальных форм всюду.
Building a robust data pipeline with dbt, Airflow, and Great Expectations — в докладе про небезынтересный инструмент greatexpectations, суть которого в валидации данных
Orchestrating dbt with Dagster — мне было несколько скучновато слушать, но если хочется познакомиться с Dagster — самое то
Supercharging your data team — ребята сделали обертку к dbt, назвали dbt executor 9000 и рассказывают о нем
Presenting: SQLFluff — про очень классную штуку SQLFluff, которая автоматически редактирует SQL-код согласно канонам
Quickstart your analytics with Fivetran dbt packages — из доклада можно узнать, что такое Fivetran и как его используют совместно с dbt
Perfect complements: Using dbt with Looker for effective data governance — про взаимодействие dbt и looker, про различия и схожие части инструментов