{
    "version": "https:\/\/jsonfeed.org\/version\/1",
    "title": "Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: sql",
    "home_page_url": "http:\/\/test.leftjoin.ru\/tags\/sql\/",
    "feed_url": "http:\/\/test.leftjoin.ru\/tags\/sql\/json\/",
    "icon": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg",
    "author": {
        "name": "Николай Валиотти",
        "url": "http:\/\/test.leftjoin.ru\/",
        "avatar": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg"
    },
    "items": [
        {
            "id": "148",
            "url": "http:\/\/test.leftjoin.ru\/all\/skvoznoy-identifikator-reshenie-problemy-metchinga-personalnyh-d\/",
            "title": "Сквозной идентификатор: решение проблемы мэтчинга персональных данных студентов Refocus",
            "content_html": "<p><img src=http:\/\/test.leftjoin.ru\/pictures\/cover.png  border=“0” width=100% height=100%><\/p>\n<p>В системах сквозной аналитики ключевую роль играет правильная модель атрибуции. Без нее данные невозможно интерпретировать, и их ценность для бизнеса невелика. При этом важно понимать, что любая модель напрямую зависит от качества данных.<\/p>\n<p>Частая проблема с сырыми данными в том, что информация об одном клиенте дублируется или, напротив, противоречит друг другу в разных источниках.<\/p>\n<p>Кроме того, что предобработка данных — база для аналитика, без правильного объединения персональных данных в принципе сложно отследить клиентский путь. Значит, нужно настраивать процессы объединения неоднородных персональных данных.<\/p>\n<p>Сегодня в любом клиентском бизнесе воронки регистрации устроены таким образом, что клиенты попадают в базу множеством способов — часто через маркетинговые каналы, которых всегда много (рассылки, реклама, соцсети). В каждом таком канале может быть ссылка на форму подписки, регистрацию на платформе или чат, и один клиент часто проходит все эти этапы. Сразу же образуется путаница в идентификации, которая сильно влияет на качество данных и результаты аналитики, если ее не лечить.<\/p>\n<p>Мы столкнулись с этой проблемой, работая с одним из наших клиентов, и решили ее, создав сквозной идентификатор. Это уникальный номер, который присваивается реальному клиенту и дублируется во все источники, где есть данные об этом клиенте, тем самым избавляя от путаницы.<\/p>\n<h2>Кейс Refocus: данные и путь клиента<\/h2>\n<p>Мы разрабатывали кастомную систему сквозной аналитики для эдтех-стартапа Refocus. Данные каждого студента в системы Refocus попадали из нескольких источников и были записаны несколько раз — как минимум при регистрации на курс, при первом входе на образовательную платформу и при входе в чат сопровождения.<\/p>\n<p>В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:<\/p>\n<ul>\n<li><b>amoCRM,<\/b> где фиксируется весь клиентский путь студента;<\/li>\n<li><b>Discord,<\/b> где проходило сопровождение студентов;<\/li>\n<li><b>Thinkific,<\/b> сама образовательная платформа с курсами.<\/li>\n<\/ul>\n<p>Остальные источники, с которыми мы работали, либо не содержали данных студентов (например, цифры эффективности работы sales-менеджеров были завязаны на данных сотрудников и трекались через другие системы), либо дублировали информацию из указанных трех.<\/p>\n<p>В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.<\/p>\n<p>Часть этих факторов может решаться корректировкой самой клиентской воронки. Правда, не все платформы позволяют одинаково настроить вводные поля, а просьбы вводить данные в конкретном формате не всегда работают и не страхуют от ошибок. Плюс, задача аналитиков — получить чистые данные в любом случае.<\/p>\n<h2>Задача и поиск решения<\/h2>\n<p>Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.<\/p>\n<p>Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.<\/p>\n<p>Поля, в которых возникали ошибки и для которых нам важен был мэтчинг, чтобы правильно отследить клиентский путь:<\/p>\n<ul>\n<li>имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);<\/li>\n<li>телефон — с кодом страны или без, с пробелами, дефисами или слитно;<\/li>\n<li>электронная почта — длинные строки сложного формата, в которых легко опечататься.<\/li>\n<\/ul>\n<p>Поначалу, пока количество студентов Refocus было относительно небольшим, достаточно было скриптов, которые объединяли данные по одному из этих полей. В полученных таблицах в Tableau проводился поиск строк с пустым значением в соответствующем поле — и вот видно всех студентов, чьи данные не сошлись.<\/p>\n<p>Количество таких строк было в пределах пары десятков, и трекать и объединять их было несложно вручную. Это делалось прямо в первоисточниках сотрудниками Refocus, которые могли поправить опечатки и ошибки у себя в системах. После этого наш код выгрузки в хранилище перезапускался и тянул уже чистые данные. Если после этого что-то не сходилось, то наши аналитики правили информацию на уровне базы данных.<\/p>\n<p>Но при росте компании в какой-то момент число студентов, потерянных при мэтчинге, могло достигать сотни за месяц. Пока ошибка обнаружится, данные поправят в источниках, а мы перезапустим код выгрузки, могло пройти несколько часов — а это критичный интервал. Да и перезапускать выгрузку каждый день ради нескольких несовпадений — неэффективно. Стало понятно, что масштаб проблемы требует более точного и универсального решения.<\/p>\n<p>Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае  — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.<\/p>\n<p>Руководствуясь соображениями эффективности, мы предложили ввести сквозной идентификатор — одно значение ID, присваиваемое одному клиенту после автоматической интеграции его данных из разных источников.<\/p>\n<h2>Реализация решения и рабочий процесс<\/h2>\n<p>Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.<\/p>\n<p>Например, нам нужно было мэтчить данные для создания дашборда по возвратам, о которых информация объединялась как раз из наших трех основных источников. В ранней версии скрипта данные отбирались таким образом:<\/p>\n<pre class=\"e2-text-code\"><code>WITH snapshot_ AS (\r\n      SELECT DISTINCT s.*,\r\n        IFNULL(ae.name, ap.name) as contact_name,\r\n        ap.phone, ae.email,\r\n        split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,\r\n        ai.thinkific_id, ai.intercom_id, ac.student_id\r\n      FROM (\r\n        SELECT *,\r\n          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,\r\n        FROM `Differture.amocrm_leads_snapshot`\r\n      ) s\r\n      LEFT JOIN (\r\n        SELECT DISTINCT lead_id, contact_id, name, email,\r\n          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_\r\n        FROM `Differture.amo_emails`\r\n      ) ae using(lead_id)\r\n      LEFT JOIN (\r\n        SELECT DISTINCT lead_id, contact_id, name, phone,\r\n          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_\r\n        FROM `Differture.amo_phones`\r\n      ) ap using(lead_id)\r\n      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)\r\n      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id\r\n      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)\r\n        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'\r\n    )<\/code><\/pre><p>Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует <tt>student_id<\/tt>. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по <tt>student_id<\/tt> пока не происходит. А происходят по <tt>email_first_part<\/tt>, адресу почты до символа @:<\/p>\n<pre class=\"e2-text-code\"><code>select distinct * from th_amo_ds_rf\r\n    left join calendly ce using(email_first_part)\r\n    left join typeform_live tfl on email_first_part=tf_email_first_part\r\n    left join typeform tf using(email_first_part)\r\n    left join csat using(email_first_part)<\/code><\/pre><p>Первым шагом по практическому введению идентификатора была таблица <b>students_main_info<\/b>, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td style=\"text-align: left\">student_full_name<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_email<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_country_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_country_name<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_courses_ids<\/td>\n<td style=\"text-align: center\">array<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_courses_names<\/td>\n<td style=\"text-align: center\">array<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_cohort_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_cohort_name<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">cohort_community_manager_name<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">cohort_community_manager_email<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_onboarding_live_session_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_onboarding_live_session_time<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">student_onboarding_live_session_zoom_url<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">amo_contact_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">intercom_contact_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">thinkific_student_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">discord_user_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">discord_user_discord_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">discord_guild_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">discord_channel_id<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">discord_roles<\/td>\n<td style=\"text-align: center\">string<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>В students_main_info хранились данные из нужных источников с общим идентификатором в первой строке, и объединение проходило через сравнение этого поля.<\/p>\n<p>При этом поле <tt>student_id<\/tt> использовалось пока не везде; также использовались другие поля этой таблицы — например, <tt>thinkific_student_id<\/tt> или <tt>discord_user_id<\/tt>.<\/p>\n<p>После выгрузки и мэтчинга данных с помощью students_main_info студентов, которые потерялись при объединении, стало меньше, чем при первой схеме мэтчинга. Так мы убедились, что движемся в верном направлении. Тем не менее, использование одной таблицы, которая содержит больше десятка полей обо всех имеющихся персональных данных, не очень эффективно. Данные в ней уже обработаны скриптом специалиста Refocus, и если надо сверить их с сырыми источниками или ввести новый критерий отслеживания, все придется менять на бэкенде.<\/p>\n<h2>Что получилось в итоге<\/h2>\n<p>После теста сквозного идентификатора через одну большую таблицу мы продолжили улучшать структуру данных на бэке. Вместо students_main_info усилиями специалиста Refocus появилась подробная сеть более мелких таблиц, которые могут обращаться друг к другу и лежат в одном хранилище с нашими таблицами сырых данных.<\/p>\n<p>Вот так выглядела схема соотношения этих таблиц:<\/p>\n<p><img src=http:\/\/test.leftjoin.ru\/pictures\/image-2.png  border=“0” width=100% height=100%><\/p>\n<p>А вот так выглядела основная таблица Students:<\/p>\n<p><img src=http:\/\/test.leftjoin.ru\/pictures\/students.png border=“0” width=40% height=40%><\/p>\n<p>В ней-то и находились основные персональные данные студентов с присвоенным идентификатором, и к ней можно было обращаться для мэтчинга из остальных источников.<\/p>\n<p>Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.<\/p>\n<p>Финальный код, написанный нашими аналитиками,  объединял данные при выгрузке из хранилища, и больше не опирался на ненадежный мэтчинг через имейл.<\/p>\n<p>Сначала он отбирал собранные нами данные из amoCRM <tt>(amocrm_leads_snapshot)<\/tt> и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле <tt>student_id<\/tt> и отбирались данные, которые понадобятся нам дальше.<\/p>\n<pre class=\"e2-text-code\"><code>WITH snapshot_ AS (\r\n      SELECT DISTINCT s.*,\r\n        ac.name as contact_name, ac.phone, ac.email,\r\n        split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,\r\n        ac.intercom_id, ac.student_id\r\n      FROM (\r\n        SELECT *,\r\n          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,\r\n        FROM `Differture.amocrm_leads_snapshot`\r\n      ) s\r\n      LEFT JOIN (\r\n        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,\r\n          ac.name, emails as email, phone, student_id, ic.intercom_id,\r\n          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_\r\n        from `Differture.AmoContacts` ac\r\n        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id\r\n        left join `Differture.IntercomContacts` ic using(student_id)\r\n        , unnest(ac.emails) emails\r\n      ) ac using(lead_id)\r\n      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)\r\n        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'\r\n    )<\/code><\/pre><p>Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:<\/p>\n<pre class=\"e2-text-code\"><code>th_amo_ds_rf as (\r\n      select distinct * except (channel_id, channel),\r\n        ifnull(channel_id, 'Not in discord') as channel_id,\r\n        ifnull(channel, 'Not in discord') as channel\r\n      from thinkific_amo_refunds\r\n      full outer join discord using(student_id)\r\n    )<\/code><\/pre><p>Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:<\/p>\n<p><img src=http:\/\/test.leftjoin.ru\/pictures\/filter.png border=“0” width=50% height=50%><\/p>\n<p>Пустое значение поля student_id означает, что мэтча не случилось — где-то информация расходилась слишком сильно и не подтянулась в таблицы с идентификатором. Раньше, до введения идентификатора, поиск был таким же, но обращался к полям почты, телефона или имени-фамилии.<\/p>\n<p>Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.<\/p>\n<p><img src=http:\/\/test.leftjoin.ru\/pictures\/unnamed.png  border=“0” width=100% height=100%><\/p>\n<p>А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.<\/p>\n<p><img src=http:\/\/test.leftjoin.ru\/pictures\/discord.png  border=“0” width=100% height=100%><\/p>\n<p>Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.<\/p>\n<h2>Выводы<\/h2>\n<p>Сквозной идентификатор — эффективное решение проблемы мэтчинга персональных данных. Он позволяет максимально автоматизировать процесс отслеживания и устранения несовпадений или дубликатов клиентских сущностей при выгрузке данных для анализа. В случаях, когда объем данных в системе невелик, а у компании нет возможности выделить ресурсы на реализацию такого решения, можно воспользоваться и другими вариантами. Например, алерты в оркестраторе процессов хорошо справятся в ситуации, когда объединить данные — вопрос ручного запуска одного скрипта раз в неделю. Но сквозной идентификатор — наверное, самое универсальное из доступных решений, которое покроет большинство ошибок и заметно уменьшит погрешность в качестве данных.<\/p>\n",
            "date_published": "2024-09-16T17:57:31+03:00",
            "date_modified": "2024-09-16T17:57:05+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/students.png",
            "_date_published_rfc2822": "Mon, 16 Sep 2024 17:57:31 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "148",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/students.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/image-2.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/filter.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/unnamed.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/discord.png"
                ]
            }
        },
        {
            "id": "130",
            "url": "http:\/\/test.leftjoin.ru\/",
            "title": "Десять советов, чтобы писать SQL-код, который приятно читать и использовать",
            "content_html": "<p><img src=\"http:\/\/test.leftjoin.ru\/pictures\/1*NGniUKoit2YaJY08brT01g.png\" border=\"0\" width=\"100%\" height=\"100%\"><\/a><\/p>\n<p class=\"note\">Перевод статьи <a href=\"https:\/\/towardsdatascience.com\/10-best-practices-to-write-readable-and-maintainable-sql-code-427f6bb98208\">”10 Best Practices to Write Readable and Maintainable SQL Code”<\/a> автора <a href=\"https:\/\/davidjmartins.medium.com\/?source=post_page-----427f6bb98208-----------------------------------\">David Martins<\/a><\/p>\n<h2>Как писать SQL-запросы, которые ваша команда сможет легко читать и использовать?<\/h2>\n<p>Без хорошей культуры написания SQL-запросы очень легко становятся запутанными. У каждого члена команды могут быть свои привычки написания запросов на SQL и вы очень быстро можете получить запутанный код, который будет понятен лишь одному человеку, а все остальные не смогут его использовать.<\/p>\n<p>Думаю, вы осознаёте важность наличия общих правил по написанию SQL-запросов в команде. Эта статья может стать отличным руководством для формирования таких правил!<\/p>\n<h3><b>1. Используйте верхний регистр для ключевых слов<\/b><\/h3>\n<p>Начнем с базовых вещей: используйте заглавные буквы для ключевых слов SQL и строчные буквы для обозначения таблиц и столбцов. Также рекомендуется использовать прописные буквы для функций SQL (FIRST_VALUE(), DATE_TRUNC() и т. д.), хотя это спорно.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>select id, name from company.customers<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT id, name FROM company.customers<\/code><\/pre><h3><b>2. Используйте Snake Case для схем, таблиц, столбцов<\/b><\/h3>\n<p>В разных языках программирования есть разные оптимальные стили написания названий из нескольких слов: camelCase, PascalCase, kebab-case, and snake_case являются наиболее распространенными.<\/p>\n<p>Если говорить про SQL, Snake Case (иногда называемый регистром подчеркивания) является наиболее широко используемым правилом. Чтобы писать в стиле snake_case, нужно заменить пробелы знаками подчеркивания. При этом все слова пишутся строчными буквами.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT Customers.id, \r\n       Customers.name, \r\n       COUNT(WebVisit.id) as nbVisit\r\nFROM COMPANY.Customers\r\nJOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId\r\n\r\nWHERE Customers.age &lt;= 30\r\nGROUP BY Customers.id, Customers.name<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       COUNT(web_visit.id) as nb_visit\r\nFROM company.customers\r\nJOIN company.web_visit ON customers.id = web_visit.customer_id\r\n\r\nWHERE customers.age &lt;= 30\r\nGROUP BY customers.id, customers.name<\/code><\/pre><p>Хотя некоторым нравится использовать разные стили составных названий (чтобы различать схемы, таблицы и столбцы), я бы рекомендовал придерживаться Snake Case.<\/p>\n<h3><b>3. Используйте псевдонимы, чтобы сделать запрос понятнее<\/b><\/h3>\n<p>Хорошо известно, что псевдонимы — это удобный способ для переименования таблиц или столбцов, чтобы добавить точность смысловой нагрузкой. Не стесняйтесь давать псевдонимы таблицам и столбцам, чтобы название лучше описывало процесс в а функциях.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       customers.context_col1,\r\n       nested.f0_\r\nFROM company.customers\r\nJOIN (\r\n          SELECT customer_id,\r\n                 MIN(date)\r\n          FROM company.purchases\r\n          GROUP BY customer_id\r\n      ) ON customer_id = customers.id<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       customers.context_col1 as ip_address,\r\n       first_purchase.date    as first_purchase_date\r\nFROM company.customers\r\nJOIN (\r\n          SELECT customer_id,\r\n                 MIN(date) as date\r\n          FROM company.purchases\r\n          GROUP BY customer_id\r\n      ) AS first_purchase \r\n        ON first_purchase.customer_id = customers.id<\/code><\/pre><p>Я обычно использую для столбцов строчные буквы “as”, а для таблиц — прописные “AS”.<\/p>\n<h3><b>4. Форматирование: осторожно используйте отступы и пробелы<\/b><\/h3>\n<p>Это базовый принцип. Один из ключевых элементов, чтобы все функции в вашем запросе были четко видны. Если вы знаете Python (в нем без грамотных отступов код не будет работать), то примените этот навык здесь.<\/p>\n<p>Используйте пробелы после ключевого слова и при обозначении подзапроса или производной таблицы.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date\r\nFROM company.customers\r\nLEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase \r\nON first_purchase.customer_id = customers.id \r\nWHERE customers.age&lt;=30<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       customers.age, \r\n       customers.gender, \r\n       customers.salary,\r\n       first_purchase.date\r\nFROM company.customers\r\nLEFT JOIN (\r\n              SELECT customer_id,\r\n                     MIN(date) as date \r\n              FROM company.purchases\r\n              GROUP BY customer_id\r\n          ) AS first_purchase \r\n            ON first_purchase.customer_id = customers.id\r\nWHERE customers.age &lt;= 30<\/code><\/pre><p>Обратите внимание, как использованы пробелы в условии “where”.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT id WHERE customers.age&lt;=30<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT id WHERE customers.age &lt;= 30<\/code><\/pre><h3><b>5. Избегайте Select<\/b>*<\/h3>\n<p>Не забывайте об этом правиле: вам следует точно указывать, какие элементы таблицы вы хотите выбрать и забыть про Select*!<\/p>\n<p>Select* делает ваш запрос неясным, поскольку он скрывает намерения, стоящие за запросом. Кроме того, помните, что ваши таблицы могут эволюционировать и влиять на Select*. Вот почему я не большой поклонник инструкции EXCEPT().<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT * EXCEPT(id) FROM company.customers<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT name,\r\n       age,\r\n       salary\r\nFROM company.customers<\/code><\/pre><h3><b>6. Используйте синтаксис JOIN ANSI-92<\/b><\/h3>\n<p>…для соединения таблиц, вместо условия WHERE. Несмотря на то, что для соединения таблиц можно использовать как условие WHERE, так и условие JOIN, лучше использовать синтаксис JOIN\/ANSI-92.<\/p>\n<p>Хотя с точки зрения производительности разницы нет, условие JOIN отделяет логику отношения от фильтров.<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       COUNT(transactions.id) as nb_transaction\r\nFROM company.customers, company.transactions\r\nWHERE customers.id = transactions.customer_id\r\n      AND customers.age &lt;= 30\r\nGROUP BY customers.id, customers.name<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       COUNT(transactions.id) as nb_transaction\r\nFROM company.customers\r\nJOIN company.transactions ON customers.id = transactions.customer_id\r\nWHERE customers.age &lt;= 30\r\nGROUP BY customers.id, customers.name<\/code><\/pre><p>Синтаксис, основанный на условии “Where”, также известный как ANSI-89, старше нового ANSI-92, поэтому он всё ещё очень распространен. Сегодня большинство разработчиков и аналитиков данных используют синтаксис JOIN.<\/p>\n<h3><b>7. Используйте обобщённое табличное выражение (Common Table Expression — CTE)<\/b><\/h3>\n<p>CTE позволяет создать запрос, результат которого существует временно и может использоваться в более крупном запросе. CTE доступны в большинстве современных баз данных.<\/p>\n<p>Он работает как производная таблица с двумя преимуществами:<\/p>\n<ul>\n<li>Использование CTE улучшает читабельность вашего запроса.<\/li>\n<li>CTE определяется один раз, после чего на него можно ссылаться многократно.<\/li>\n<\/ul>\n<p>CTE объявляется с помощью инструкции <b>WITH … AS<\/b>:<\/p>\n<pre class=\"e2-text-code\"><code>WITH my_cte AS\r\n(\r\n  SELECT col1, col2 FROM table\r\n)\r\nSELECT * FROM my_cte<\/code><\/pre><p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>SELECT customers.id, \r\n       customers.name, \r\n       customers.age, \r\n       customers.gender, \r\n       customers.salary,\r\n       persona_salary.avg_salary as persona_avg_salary,\r\n       first_purchase.date\r\nFROM company.customers\r\nJOIN (\r\n          SELECT customer_id,\r\n                 MIN(date) as date \r\n          FROM company.purchases\r\n          GROUP BY customer_id\r\n      ) AS first_purchase \r\n        ON first_purchase.customer_id = customers.id\r\nJOIN (\r\n          SELECT age,\r\n             gender,\r\n             AVG(salary) as avg_salary\r\n         FROM company.customers\r\n         GROUP BY age, gender\r\n      ) AS persona_salary \r\n        ON persona_salary.age = customers.age\r\n           AND persona_salary.gender = customers.gender\r\nWHERE customers.age &lt;= 30<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>WITH first_purchase AS\r\n(\r\n   SELECT customer_id,\r\n          MIN(date) as date \r\n   FROM company.purchases\r\n   GROUP BY customer_id\r\n),\r\npersona_salary AS\r\n(\r\n   SELECT age,\r\n          gender,\r\n          AVG(salary) as avg_salary\r\n   FROM company.customers\r\n   GROUP BY age, gender\r\n)\r\nSELECT customers.id, \r\n       customers.name, \r\n       customers.age, \r\n       customers.gender, \r\n       customers.salary,\r\n       persona_salary.avg_salary as persona_avg_salary,\r\n       first_purchase.date\r\nFROM company.customers\r\nJOIN first_purchase ON first_purchase.customer_id = customers.id\r\nJOIN persona_salary ON persona_salary.age = customers.age\r\n                       AND persona_salary.gender = customers.gender\r\nWHERE customers.age &lt;= 30<\/code><\/pre><h3><b>8. Иногда стоит разделить запрос на несколько<\/b><\/h3>\n<p>...но не увлекайтесь. Давайте разберем на примере.<\/p>\n<p>Я часто использую AirFlow для выполнения SQL-запросов в BigQuery, преобразования данных и подготовки визуализации данных. В нем есть оркестратор рабочих процессов (Airflow), который выполняет запросы в определенном порядке. В некоторых ситуациях лучше разбивать сложные запросы на несколько более мелких.<\/p>\n<p><b>Вместо:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>CREATE TABLE customers_infos AS\r\nSELECT customers.id,\r\n       customers.salary,\r\n       traffic_info.weeks_since_last_visit,\r\n       category_info.most_visited_category_id,\r\n       purchase_info.highest_purchase_value\r\nFROM company.customers\r\nLEFT JOIN ([..]) AS traffic_info\r\nLEFT JOIN ([..]) AS category_info\r\nLEFT JOIN ([..]) AS purchase_info<\/code><\/pre><p><b>Вы могли бы использовать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>## STEP1: Create initial table\r\nCREATE TABLE public.customers_infos AS\r\nSELECT customers.id,\r\n       customers.salary,\r\n       0 as weeks_since_last_visit,\r\n       0 as most_visited_category_id,\r\n       0 as highest_purchase_value\r\nFROM company.customers\r\n## STEP2: Update traffic infos\r\nUPDATE public.customers_infos\r\nSET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE,\r\n                                       last_visit.date, WEEK)\r\nFROM (\r\n         SELECT customer_id, max(visit_date) as date\r\n         FROM web.traffic_info\r\n         GROUP BY customer_id\r\n     ) AS last_visit\r\nWHERE last_visit.customer_id = customers_infos.id\r\n## STEP3: Update category infos\r\nUPDATE public.customers_infos\r\nSET most_visited_category_id = [...]\r\nWHERE [...]\r\n## STEP4: Update purchase infos\r\nUPDATE public.customers_infos\r\nSET highest_purchase_value = [...]\r\nWHERE [...]<\/code><\/pre><p><b>ПРЕДУПРЕЖДЕНИЕ!<\/b> <br \/>\nНесмотря на то, что этот метод отлично подходит для упрощения сложных запросов, вместе с повышением читабельности кода, вы можете здорово понизить его производительность.<\/p>\n<p>Это особенно важно, если вы работаете с базой данных OLAP или любой колоночной базой данных, оптимизированной для агрегационных и аналитических запросов (SELECT, AVG, MIN, MAX, …), но менее производительной, когда речь идет о транзакциях (UPDATE).<\/p>\n<p>Несмотря на это в некоторых случаях это может улучшить производительность работы с базой данных. Даже в современной базе данных, ориентированной на колонки, слишком большое количество JOIN’ов приведет к проблемам с памятью или производительностью. В таких ситуациях разделение вашего запроса обычно помогает улучшить производительность и оптимизировать используемую память.<\/p>\n<p>Кроме того, не стоит забывать, что вам нужен инструмент или оркестратор для выполнения ваших запросов в определенном порядке.<\/p>\n<h3><b>9. Осмысленные названия, основанные на ваших внутренних правилах<\/b><\/h3>\n<p>Правильно называть схемы и таблицы сложно. Какие варианты возможных имён использовать — вопрос дискуссионный, но задача выбора единых правил присвоения имён — это не сложно. Вы должны определить <b>свои<\/b> правила и использовать их всей командой.<\/p>\n<p>“В компьютерных науках есть только две сложные проблемы: аннулирование кэша и придумывание названий.” — Фил Карлтон<\/p>\n<p>Вот примеры правил, которые я использую:<\/p>\n<h4><b>Схемы<\/b><\/h4>\n<p>Если вы работаете с аналитической базой данных, которая служит нескольким целям, хорошей практикой является организация таблиц в выразительные схемы.<\/p>\n<p>В нашей базе данных BigQuery у нас есть одна схема для каждого источника данных. Что еще более важно, мы выводим результаты в разных схемах в зависимости от их назначения.<\/p>\n<ul>\n<li>Любая таблица, которая будет доступна для стороннего инструмента, находится в *<b>общедоступной<\/b>* схеме. Инструменты визуализации данных, такие как DataStudio или Tableau, получают данные из неё.<\/li>\n<li>Поскольку мы используем машинное обучение с BQML, ****<b>у нас есть специальная схема <\/b>*machine_learning.***<\/li>\n<\/ul>\n<h4>Таблицы<\/h4>\n<p>Сами таблицы должны называться в соответствии с правилами. В Agorapulse у нас есть несколько дашбордов для визуализации данных, каждый из которых имеет свое назначение: дашборд управления маркетингом, дашборд управления продуктом, дашборд управления для руководителей и многие другие.<\/p>\n<p>Каждая таблица в нашей общедоступной схеме имеет префикс имени дашборда. Выглядит это примерно так:<\/p>\n<pre class=\"e2-text-code\"><code>product_inbox_usage\r\nproduct_addon_competitor_stats\r\nmarketing_acquisition_agencies\r\nExecutive_funnel_overview<\/code><\/pre><p>При командной работе стоит уделить время определению общих правил. Когда вы придумываете название новой таблицы, то не используйте быстрое и заезженное имя, которое вы «измените позже» — вы наверняка этого не сделаете.<\/p>\n<p>Не стесняйтесь использовать эти примеры для создания своих правил.<\/p>\n<h3><b>10. Пишите полезные комментарии… но не слишком много<\/b><\/h3>\n<p class=\"note\"><i>Примечание переводчика:<\/i> Автор пишет, что “хорошо написанный код с правильными названиями не нуждается в комментариях” и иронично продемонстрировал детально расписанный код вообще без комментариев. Мы все-таки за то, чтобы необходимые пояснения в коде были, ведь это сильно ускоряет понимание сути запроса.<\/p>\n<p>Я согласен с тезисом, что хорошо написанный код с правильными названиями не нуждается в комментариях. Тот, кто читает ваш код, должен понимать логику и замысел еще до того, как появится результат работы кода.<\/p>\n<p>Тем не менее, комментарии могут быть полезны в некоторых ситуациях. Но перебарщивать с ними не стоит!<\/p>\n<p><b>Избегать:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>WITH fp AS\r\n(\r\n   SELECT c_id,               # customer id\r\n          MIN(date) as dt     # date of first purchase\r\n   FROM company.purchases\r\n   GROUP BY c_id\r\n),\r\nps AS\r\n(\r\n   SELECT age,\r\n          gender,\r\n          AVG(salary) as avg\r\n   FROM company.customers\r\n   GROUP BY age, gender\r\n)\r\nSELECT customers.id, \r\n       ct.name, \r\n       ct.c_age,            # customer age\r\n       ct.gender,\r\n       ct.salary,\r\n       ps.avg,              # average salary of a similar persona\r\n       fp.dt                # date of first purchase for this client\r\nFROM company.customers ct\r\n# join the first purchase on client id\r\nJOIN fp ON c_id = ct.id\r\n# match persona based on same age and genre\r\nJOIN ps ON ps.age = c_age\r\n           AND ps.gender = ct.gender\r\nWHERE c_age &lt;= 30<\/code><\/pre><p><b>Предпочтительно:<\/b><\/p>\n<pre class=\"e2-text-code\"><code>WITH first_purchase AS\r\n(\r\n   SELECT customer_id,\r\n          MIN(date) as date \r\n   FROM company.purchases\r\n   GROUP BY customer_id\r\n),\r\npersona_salary AS\r\n(\r\n   SELECT age,\r\n          gender,\r\n          AVG(salary) as avg_salary\r\n   FROM company.customers\r\n   GROUP BY age, gender\r\n)\r\nSELECT customers.id, \r\n       customers.name, \r\n       customers.age, \r\n       customers.gender, \r\n       customers.salary,\r\n       persona_salary.avg_salary as persona_avg_salary,\r\n       first_purchase.date\r\nFROM company.customers\r\nJOIN first_purchase ON first_purchase.customer_id = customers.id\r\nJOIN persona_salary ON persona_salary.age = customers.age\r\n                       AND persona_salary.gender = customers.gender\r\nWHERE customers.age &lt;= 30<\/code><\/pre><h2><b>Вывод<\/b><\/h2>\n<p>SQL великолепен. Это одна из основ анализа данных, науки о данных, инжиниринга данных и даже разработки программного обеспечения: неправильный код не простит ошибки. Его гибкость является силой, но может быть ловушкой.<\/p>\n<p>Сначала вы можете этого не осознавать, особенно если с кодом работаете только вы. Но когда вы работаете в команде или если кто-то должен будет продолжать вашу работу, SQL-код написанный без соблюдения этих правил будет ночным кошмаром аналитика.<\/p>\n<p>В этой статье я обобщил наиболее распространенные рекомендации по написанию SQL-запросов. Конечно, некоторые из них дискуссионные или основаны на личном мнении — вы можете черпать отсюда вдохновение и создавать собственные правила со своей командой.<\/p>\n<p>Я надеюсь, что эта информация поможет вам вывести качество SQL на новый уровень!<\/p>\n",
            "date_published": "2022-02-22T16:24:18+03:00",
            "date_modified": "2022-02-22T17:11:30+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/1*NGniUKoit2YaJY08brT01g.png",
            "_date_published_rfc2822": "Tue, 22 Feb 2022 16:24:18 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "130",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/1*NGniUKoit2YaJY08brT01g.png"
                ]
            }
        },
        {
            "id": "127",
            "url": "http:\/\/test.leftjoin.ru\/all\/sql-regular-expressions\/",
            "title": "Регулярные выражения как способ решения задач в SQL",
            "content_html": "<p>Использование регулярных выражений для выбора определенных ячеек таблицы используется в SQL не так часто, как могло бы. И очень зря — этот инструмент легко позволяет найти в таблице нужные значения, так как использует шаблон для поиска последовательности метасимволов в тексте. Такие задачи встречаются как в различных тренажерах или на собеседованиях на позиции аналитика, так и в реальной практике аналитиков, которые работают в базах SQL. Подобные шаблоны для поиска определенных элементов и последовательностей в тексте используются в самых разных областях. Например, на многих сайтах существует проверка email-адреса, который вы вводите при регистрации, на соответствие стандартному  шаблону.<\/p>\n<p><img src=\"http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg\"  border=\"0\" width=\"100%\" height=\"100%\"><\/p>\n<p>Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.<\/p>\n<p><i>Что такое «регулярное выражение»?<\/i><br \/>\n<b>Регулярное выражение —<\/b> последовательность букв и\/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.<br \/>\nДавайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.<\/p>\n<h2>Количество гласных букв в выражении<\/h2>\n<p>Итак, предположим, вам нужно посчитать количество гласных букв в каждой ячейке определенного столбца таблицы. Именно для такой задачи и нужны регулярные выражения. Код, который приведен ниже (вы можете легко его прогнать в своем SQL), на простом примере показывает, как легко решить эту задачу. В результате, мы получаем еще одну колонку Count, в которой хранится искомая информация.<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) \r\nas map(id, source_type))\r\n\r\nselect source_type, count(1) from (\r\nselect *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t\r\ngroup by source_type<\/code><\/pre><h2>Количество согласных букв в выражении<\/h2>\n<p>Если мы хотим решить обратную задачу, то можно подойти к решению двумя способами. Первый способ — аналогично предыдущему можно перечислить все согласные буквы английского алфавита в квадратных скобках. Но почему бы не решить задачу элегантнее? Для этого есть второй способ — использовать отрицание, то есть посчитать количество всех букв, которые не являются гласными. Для этого используется оператор ^.<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) \r\nas map(id, source_type))\r\n\r\nselect source_type, count(1) from (\r\nselect *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t\r\ngroup by source_type<\/code><\/pre><h2>Количество цифр в выражении равно 3<\/h2>\n<p>Если нужно найти конкретное число определенных символов в выражении, то в конце запроса нужно указать оператор HAVING.<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2')) \r\nas map(id, source_type))\r\n\r\nselect source_type, COUNT(*) from (\r\nselect *, regexp_matches(source_type,'\\d','g') as pattern from example_table ) as t\r\nGROUP BY source_type\r\nHAVING COUNT(*) = 3<\/code><\/pre><h2>В номере телефона есть два дефиса<\/h2>\n<p>Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.<br \/>\nВ первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True\/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (\r\n  select * from (\r\n    values \r\n    (1, '8931-123-456'), \r\n    (2, '8931123-456'), \r\n    (3, '+7812123456'), \r\n    (4, '8-931-123-42-24')\r\n  )\r\nas map(id, source_type))\r\n\r\nselect source_type, CASE WHEN COUNT(1) &gt;= 2 THEN 'True' ELSE 'False' END from (\r\nselect *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre><h2>Все имена, которые написаны с большой буквы<\/h2>\n<p>Тут мы уже приступаем к задаче посложнее: нужно найти имена людей, которые написаны с заглавной буквы со всем датасете. Для этого нам нужно найти все значения, подходящие под заданный шаблон: первая буква слова — заглавная.<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (\r\n  select * from (\r\n    values \r\n    (1, 'alex'), \r\n    (2, 'Alex'), \r\n    (3, 'Vasya'), \r\n    (4, 'petya')\r\n  )\r\nas map(id, source_type))\r\n\r\nselect source_type from (\r\nselect *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre><h2>Вывести номера телефонов, которые попадают под паттерн +71234564578<\/h2>\n<p>Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (\r\n  select * from (\r\n    values \r\n    (1, '+7(931)1234546'), \r\n    (2, '+79312991809'), \r\n    (3, '89311234565'), \r\n    (4, '244-02-38')\r\n  )\r\nas map(id, source_type))\r\n\r\nselect source_type from (\r\nselect *, regexp_matches(source_type,'^\\+7[0-9]{10}','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre><h2>Вывести все настоящие email-адреса<\/h2>\n<p>Как мы говорили в начале, регулярные выражения могут использоваться для таких задач как поиск сложных выражений по определённому шаблону. На самом деле, ничего особенного в такой задаче нет — главное, грамотно сформировать шаблон выражения и дело в шляпе!<\/p>\n<pre class=\"e2-text-code\"><code>with example_table as (\r\n    select * from (\r\n    values\r\n        (1, 'email.asd@ya.ru'),\r\n        (2, 'something@new.ru'),\r\n        (3, '@ya.ru'),\r\n        (4, 'asdasd'),\r\n        (5, '_asdasdasd@mail.ru'),\r\n        (6, 'asd_asdas@mail.ru'),\r\n        (7, '.asdasd@mail.ru'),\r\n        (8, '007asd@email.com')\r\n        ) as map(id, source_type)\r\n)\r\n​\r\nselect source_type \r\nfrom (\r\n    select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\\.[a-z]+$')\r\n    from example_table ) as t<\/code><\/pre><p>Использование регулярных выражений может помочь легко и просто решить достаточно трудные задачи. Пишите в комментариях, если у вас есть какая-то задача по поиску определенных шаблонов в тексте, которая вам никак не дается. Попробуем решить её вместе!<\/p>\n",
            "date_published": "2022-01-17T16:48:16+03:00",
            "date_modified": "2022-01-13T14:24:01+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg",
            "_date_published_rfc2822": "Mon, 17 Jan 2022 16:48:16 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "127",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg"
                ]
            }
        },
        {
            "id": "125",
            "url": "http:\/\/test.leftjoin.ru\/all\/sql-running-total\/",
            "title": "Три способа рассчитать накопленную сумму в SQL",
            "content_html": "<p>Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.<\/p>\n<p>Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.<\/p>\n<h3>Что у нас есть?<\/h3>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>Date<\/b><\/td>\n<td><b>Revenue<\/b><\/td>\n<\/tr>\n<tr>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<\/tr>\n<tr>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<\/tr>\n<tr>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<\/tr>\n<tr>\n<td>13.11.2021<\/td>\n<td>3000<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<h3>Что мы хотим найти?<\/h3>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td style=\"text-align: left\">Date<\/td>\n<td style=\"text-align: center\">Revenue<\/td>\n<td style=\"text-align: right\">Cumulative Revenue<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">10.11.2021<\/td>\n<td>1200<\/td>\n<td>1200 ↓<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">11.11.2021<\/td>\n<td>1600<\/td>\n<td>2800↓<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">12.11.2021<\/td>\n<td style=\"text-align: left\">800<\/td>\n<td>3600 ↓<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left\">13.11.2021<\/td>\n<td style=\"text-align: left\">3000<\/td>\n<td>6600<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>На графике две этих переменных выглядят следующим образом:<br \/>\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/sql_graph.png\"  border=\"0\" width=\"100%\" height=\"100%\"><\/p>\n<p>Итак, без лишних слов, давайте приступать к решению задачи.<\/p>\n<h2>Способ 1 — Идеальный —  Используем оконные функции<\/h2>\n<p>Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.<\/p>\n<pre class=\"e2-text-code\"><code>SELECT\r\n\tdate,\r\n\trevenue,\r\n\tSUM(revenue) OVER (ORDER BY date asc) as total\r\nFROM leftjoin.daily_sales_sample \r\nORDER BY date;<\/code><\/pre><h2>Способ 2 — Хитрый — Решение без оконных функций<\/h2>\n<p>Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?<br \/>\nЧтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT * \r\nFROM leftjoin.daily_sales_sample ds1 \r\nINNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date&gt;=ds2.date\r\nORDER BY ds1.date, ds2.date;<\/code><\/pre><p>А его результат:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>Date 1<\/b><\/td>\n<td><b>Revenue 1<\/b><\/td>\n<td><b>Date 2<\/b><\/td>\n<td><b>Revenue 2<\/b><\/td>\n<\/tr>\n<tr>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<\/tr>\n<tr>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<\/tr>\n<tr>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<\/tr>\n<tr>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<\/tr>\n<tr>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<\/tr>\n<tr>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<\/tr>\n<tr>\n<td>13.11.2021<\/td>\n<td>300<\/td>\n<td>10.11.2021<\/td>\n<td>1200<\/td>\n<\/tr>\n<tr>\n<td>13.11.2021<\/td>\n<td>300<\/td>\n<td>11.11.2021<\/td>\n<td>1600<\/td>\n<\/tr>\n<tr>\n<td>13.11.2021<\/td>\n<td>300<\/td>\n<td>12.11.2021<\/td>\n<td>800<\/td>\n<\/tr>\n<tr>\n<td>13.11.2021<\/td>\n<td>300<\/td>\n<td>13.11.2021<\/td>\n<td>300<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT\r\n\tds1.date,\r\n\tds1.revenue,\r\n\tSUM(ds2.revenue) as total\r\nFROM leftjoin.daily_sales_sample ds1 \r\nINNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date&gt;=ds2.date\r\nGROUP BY ds1.date, ds1.revenue\r\nORDER BY ds1.date;<\/code><\/pre><h2>Способ 3 — Специфический — Решение с помощью массивов в ClickHouse<\/h2>\n<p>Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.<\/p>\n<pre class=\"e2-text-code\"><code>SELECT groupArray(date) dates, groupArray(revenue) as revs, \r\ngroupArrayMovingSum(revenue) AS total\r\nFROM (SELECT date, revenue FROM leftjoin.daily_sales_sample\r\n\t  ORDER BY date)<\/code><\/pre><p><i>Спасибо <a href=\"https:\/\/t.me\/unamedrus\">Дмитрию Титову<\/a> из Altinity за комментарий про сортировку в подзапросе<\/i><\/p>\n<p>Так, мы получим три массива значений:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td style=\"text-align: left\"><b>dates<\/b><\/td>\n<td style=\"text-align: center\"><b>revs<\/b><\/td>\n<td style=\"text-align: right\"><b>total<\/b><\/td>\n<\/tr>\n<tr>\n<td>[’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’]<\/td>\n<td style=\"text-align: center\">[1200, 1600, 800, 300]<\/td>\n<td style=\"text-align: right\">[1200, 2800, 3600, 3900]<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.<\/p>\n<pre class=\"e2-text-code\"><code>SELECT dates, revs, total FROM\r\n(SELECT groupArray(date) dates, groupArray(revenue) as revs, \r\ngroupArrayMovingSum(revenue) AS total\r\nFROM (SELECT date, revenue FROM leftjoin.daily_sales_sample\r\n\t  ORDER BY date)) as t\r\nARRAY JOIN dates, revs, total;<\/code><\/pre><h2>Бонус — Оконные функции в Clickhouse<\/h2>\n<p>Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи.  Можно использовать оконные функции, например функцию <a href=\"https:\/\/clickhouse.com\/docs\/en\/sql-reference\/functions\/other-functions\/\">runningAccumulate(<\/a>), которая суммирует  значения всех ячеек с первой до текущей.<\/p>\n<pre class=\"e2-text-code\"><code>SELECT date, runningAccumulate(revenue)\r\n  FROM \r\n  (\r\n    SELECT date, sumState(revenue) AS revenue\r\n    FROM leftjoin.daily_sales_sample\r\n    GROUP BY date \r\n    ORDER BY date ASC\r\n  )\r\nORDER BY date<\/code><\/pre><p>Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа :)<\/p>\n",
            "date_published": "2021-12-10T19:10:34+03:00",
            "date_modified": "2022-01-14T12:54:12+03:00",
            "_date_published_rfc2822": "Fri, 10 Dec 2021 19:10:34 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "125",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "115",
            "url": "http:\/\/test.leftjoin.ru\/all\/modeling-ltv-with-sql\/",
            "title": "Моделирование LTV в SQL",
            "content_html": "<p>У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, <a href=\"http:\/\/test.leftjoin.ru\/all\/retention-rate\/\">что такое Retention и как его посчитать<\/a>. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.<\/p>\n<p class=\"note\">Для материала были взяты данные из одного реального игрового проекта. Нулевой день не отображен для того, чтобы видеть динамику в деталях<\/p>\n<p>В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.<\/p>\n<h2>Как посчитать LTV?<\/h2>\n<p>В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart.png\" width=\"223\" height=\"19\" alt=\"\" \/>\n<\/div>\n<p>Посмотрим на классический график Retention:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.54.53.png\" width=\"942\" height=\"413\" alt=\"\" \/>\n<\/div>\n<p>Lifetime — это площадь фигуры под Retention:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.55.13.png\" width=\"923\" height=\"410\" alt=\"\" \/>\n<\/div>\n<p class=\"note\">Откуда взялись интегралы и площади можно подробнее узнать в <a href=\"https:\/\/gdcuffs.com\/ltv-integrals-and-areas\/\">этом материале<\/a><\/p>\n<p>Значит, чтобы посчитать Lifetime, нужно взять интеграл от функции удержания по времени. Формула приобретает следующий вид:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-2.png\" width=\"198\" height=\"28\" alt=\"\" \/>\n<\/div>\n<p>Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.58.19.png\" width=\"802\" height=\"403\" alt=\"\" \/>\n<\/div>\n<p>При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-4.png\" width=\"64\" height=\"18\" alt=\"\" \/>\n<\/div>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-5.png\" width=\"154\" height=\"20\" alt=\"\" \/>\n<\/div>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-6.png\" width=\"156\" height=\"19\" alt=\"\" \/>\n<\/div>\n<p>ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.<\/p>\n<h2>Строим LTV<\/h2>\n<p>Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:<\/p>\n<pre class=\"e2-text-code\"><code>--общее количество юзеров в когорте\r\nwith cohort as (\r\n    select count(distinct id) as total_users_of_cohort\r\n    from users\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30'\r\n),<\/code><\/pre><p>Теперь посмотрим, как ведут себя эти пользователи в последующие 90 дней:<\/p>\n<pre class=\"e2-text-code\"><code>--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты\r\nactive_users as (\r\n    select date_part('day', activity.date - users.registration) as activity_day, \r\n               count(distinct users.id) as active_users_of_day\r\n    from activity\r\n    join users on activity.user_id = users.id\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30' \r\n    group by 1\r\n    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.\r\n),<\/code><\/pre><p>Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.54.53.png\" width=\"942\" height=\"413\" alt=\"\" \/>\n<\/div>\n<p>По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными <a href=\"https:\/\/www.postgresql.org\/docs\/9.4\/functions-aggregate.html\">агрегатными функциями в PostgreSQL<\/a> и для известных X и Y по методу наименьших квадратов.<\/p>\n<p>Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:<\/p>\n<p class=\"note\">Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в <a href=\"https:\/\/www.mathsisfun.com\/data\/least-squares-regression.html\">этом мануале<\/a><\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-6.png\" width=\"156\" height=\"19\" alt=\"\" \/>\n<\/div>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-10.png\" width=\"217\" height=\"21\" alt=\"\" \/>\n<\/div>\n<p>Из свойства натурального логарифма следует, что:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-11.png\" width=\"165\" height=\"25\" alt=\"\" \/>\n<\/div>\n<p>Наклон считаем аналогичным образом:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/chart-12.png\" width=\"157\" height=\"21\" alt=\"\" \/>\n<\/div>\n<p>Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:<\/p>\n<pre class=\"e2-text-code\"><code>--рассчитываем коэффициенты регрессии\r\ncoef as (\r\n    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, \r\n                regr_slope(ln(activity), ln(activity_day)) as b\r\n    from(\r\n                select activity_day,\r\n                            active_users_of_day::real \/ total_users_of_cohort as activity\r\n                from active_users \r\n                cross join cohort order by activity_day \r\n            )\r\n),<\/code><\/pre><p>И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:<\/p>\n<pre class=\"e2-text-code\"><code>lt as(\r\n    select generate_series as activity_day,\r\n               active_users_of_day::real\/total_users_of_cohort as real_data,\r\n               a*power(generate_series,b) as pred_data, \t \r\n               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt\r\n    from generate_series(1,180,1)\r\n    cross join coef\r\n    join active_users on generate_series = activity_day::int\r\n),<\/code><\/pre><p>Сравним прогноз на 180 дней с Retention:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.30.59.png\" width=\"936\" height=\"425\" alt=\"\" \/>\n<\/div>\n<p>Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:<\/p>\n<pre class=\"e2-text-code\"><code>select cumulative_lt as LT,\r\n           cumulative_lt * 83.7 as LTV\r\nfrom lt<\/code><\/pre><p>Наконец, построим график LTV на 180 дней:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.51.05.png\" width=\"852\" height=\"416\" alt=\"\" \/>\n<\/div>\n<p>Весь запрос:<\/p>\n<pre class=\"e2-text-code\"><code>--общее количество юзеров в когорте\r\nwith cohort as (\r\n    select count(*) as total_users_of_cohort\r\n    from users\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30'\r\n),\r\n--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты\r\nactive_users as (\r\n    select date_part('day', activity.date - users.registration) as activity_day, \r\n               count(distinct users.id) as active_users_of_day\r\n    from activity\r\n    join users on activity.user_id = users.id\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30' \r\n    group by 1\r\n    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.\r\n),\r\n--рассчитываем коэффициенты регрессии\r\ncoef as (\r\n    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, \r\n                regr_slope(ln(activity), ln(activity_day)) as b\r\n    from(\r\n                select activity_day,\r\n                            active_users_of_day::real \/ total_users_of_cohort as activity\r\n                from active_users \r\n                cross join cohort order by activity_day \r\n            )\r\n),\r\nlt as(\r\n    select generate_series as activity_day,\r\n               active_users_of_day::real\/total_users_of_cohort as real_data,\r\n               a*power(generate_series,b) as pred_data, \t \r\n               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt\r\n    from generate_series(1,180,1)\r\n    cross join coef\r\n    join active_users on generate_series = activity_day::int\r\n),\r\nselect cumulative_lt as LT,\r\n            cumulative_lt * 83.7 as LTV\r\nfrom lt<\/code><\/pre>",
            "date_published": "2021-08-16T09:32:37+03:00",
            "date_modified": "2021-08-16T11:03:19+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/chart.png",
            "_date_published_rfc2822": "Mon, 16 Aug 2021 09:32:37 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "115",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/chart.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.55.13.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-2.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.58.19.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-4.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-5.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.54.53.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-6.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-10.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-11.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-12.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.30.59.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.51.05.png"
                ]
            }
        },
        {
            "id": "113",
            "url": "http:\/\/test.leftjoin.ru\/all\/altinity-clickhouse-training-101\/",
            "title": "Тренинг по Clickhouse от Altinity",
            "content_html": "<p>Буквально на днях закончил обучение <a href=\"https:\/\/altinity.com\/clickhouse-training\/?utm_source=leftjoin\">Clickhouse от Altinity (101 Series Training).<\/a> Для тех, кто только знакомится с Clickhouse Altinity предлагает базовый бесплатный тренинг: <a href=\"https:\/\/altinity.com\/data-warehouse-basics\/?utm_source=leftjoin\">Data Warehouse Basics<\/a>. Рекомендую начать с него, если планируете погружаться в обучение.<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/altinity-clickhouse-developer-300px.png\" width=\"300\" height=\"300\" alt=\"\" \/>\n<div class=\"e2-text-caption\">Сертификация от Altinity<\/div>\n<\/div>\n<p>Хочу поделиться своими впечатлениями об обучении и поделиться своим <a href=\"https:\/\/valiotti-analytics.notion.site\/Clickhouse-Training-101-by-Altinity-notes-120f1b6467f44a30956d6d7ffeff7b08\">конспектом с тренинга<\/a>.<br \/>\nОбучение стоит $500 и длится четыре дня по два часа, проводится в наше вечернее время (начиная с 19:00 GMT+3).<\/p>\n<h2>Сессия №1<\/h2>\n<p>Первый день в бОльшей степени повторяет пройденное в Data Warehouse Basics, однако в нем есть несколько новых идей, например о том, как можно получить полезную информацию о запросах из системных таблиц.<\/p>\n<p>Например, такой query выдаст какие команды запущены и в каком они статусе:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT command, is_done\r\nFROM system.mutations\r\nWHERE table = 'ontime'<\/code><\/pre><p>Помимо этого, для меня было очень полезно узнать про компрессию колонок с использованием кодеков:<\/p>\n<pre class=\"e2-text-code\"><code>ALTER TABLE ontime\r\n MODIFY COLUMN TailNum LowCardinality(String) CODEC(ZSTD(1))<\/code><\/pre><div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-01--11.53.59.png\" width=\"1732\" height=\"1048\" alt=\"\" \/>\n<\/div>\n<p>Для тех, кто начинает погружение в Clickhouse первый день будет супер-полезным в том, чтобы разобраться с движками таблиц и синатксисом их создания, партициями, вставкой данных (к примеру, напрямую из S3).<\/p>\n<pre class=\"e2-text-code\"><code>INSERT INTO sdata\r\nSELECT * FROM s3(\r\n 'https:\/\/s3.us-east-1.amazonaws.com\/d1-altinity\/data\/sdata*.csv.gz',\r\n 'aws_access_key_id',\r\n 'aws_secret_access_key',\r\n 'Parquet',\r\n 'DevId Int32, Type String, MDate Date, MDatetime\r\nDateTime, Value Float64')<\/code><\/pre><h2>Сессия №2<\/h2>\n<p>Второй день мне представляется максимально насыщенным и полезным, потому что в рамках него Robert из Altinity подробно рассказывает про агрегирующие функции в Clickhouse и про создание материализованных представлений (подробно по шагам разбирается <a href=\"https:\/\/www.notion.so\/Session-2-35af1ed8d2c54c6fa7fcbea3c9385810#f36adc3df7d74deebedcb3c04e019661\">схема создания материализованного представления<\/a>).<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--18.11.45.png\" width=\"877\" height=\"495\" alt=\"\" \/>\n<div class=\"e2-text-caption\">Отдельное внимание устройству джойнов в Clickhouse<\/div>\n<\/div>\n<p>Мне было супер-полезно узнать про типы индексов в CH<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--18.20.35.png\" width=\"874\" height=\"472\" alt=\"\" \/>\n<\/div>\n<h2>Сессия №3<\/h2>\n<p>В рамках третьего дня коллеги делятся знаниями о том как работать с Kafka, JSON-объектами, которые хранятся в таблицах.<br \/>\nИнтересно было узнать, что работа с типами данных массив в Clickhouse очень похоже на работу с массивами в Python:<\/p>\n<pre class=\"e2-text-code\"><code>WITH [1, 2, 4] AS array\r\nSELECT\r\n array[1] AS First,\r\n array[2] AS Second,\r\n array[3] AS Third,\r\n array[-1] AS Last,\r\n length(array) AS Length<\/code><\/pre><p>И при работе с массивами крутая фича это ARRAY JOIN, который «разворачивает» массив в плоскую реляционную таблицу:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.14.28.png\" width=\"812\" height=\"518\" alt=\"\" \/>\n<\/div>\n<p>Clickhouse позволяет эффективно взаимодействовать с JSON-объектами, которые хранятся в таблице:<\/p>\n<pre class=\"e2-text-code\"><code>-- Get a JSON string value\r\nSELECT JSONExtractString(row, 'request') AS request\r\nFROM log_row LIMIT 3\r\n-- Get a JSON numeric value\r\nSELECT JSONExtractInt(row, 'status') AS status\r\nFROM log_row LIMIT 3<\/code><\/pre><p>На примере этого кусочка кода отдельно извлекаются элементы JSON-массива ’request’ и ’status’.<\/p>\n<p>Их можно сложить в ту же таблицу:<\/p>\n<pre class=\"e2-text-code\"><code>ALTER TABLE log_row\r\n ADD COLUMN\r\nstatus Int16 DEFAULT\r\n JSONExtractInt(row, 'status')\r\nALTER TABLE log_row\r\nUPDATE status = status WHERE 1 = 1<\/code><\/pre><h2>Сессия №4<\/h2>\n<p>А на заключительный четвертый день оставлена самая трудная тема с моей точки зрения: <a href=\"https:\/\/www.notion.so\/Session-4-f2aa33b6fe434a4e8542f0f64f9439bc#3a3038e94dbf4b47a10284dc1dc226ec\">построение шардированных и реплицированных кластеров<\/a>, построение запросов на распределенных серверах Clickhouse.<\/p>\n<div class=\"e2-text-picture\">\n<div class=\"fotorama\" data-width=\"938\" data-ratio=\"1.8073217726397\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.43.22.png\" width=\"938\" height=\"519\" alt=\"\" \/>\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.47.52.png\" width=\"934\" height=\"520\" alt=\"\" \/>\n<\/div>\n<\/div>\n<p>Отдельный респект Altinity за отличную подборку лабораторных заданий в ходе обучения.<\/p>\n<p><b>Ссылки<\/b>:<\/p>\n<ul>\n<li><a href=\"https:\/\/capable-stream-f18.notion.site\/Clickhouse-Training-101-by-Altinity-notes-120f1b6467f44a30956d6d7ffeff7b08\">Конспект в Notion<\/a><\/li>\n<li><a href=\"https:\/\/altinity.com\/clickhouse-training\/?utm_source=leftjoin\">ClickHouse 101 Training от Altinity<\/a><\/li>\n<\/ul>\n",
            "date_published": "2021-08-09T08:41:19+03:00",
            "date_modified": "2023-04-24T12:20:42+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/altinity-clickhouse-developer-300px.png",
            "_date_published_rfc2822": "Mon, 09 Aug 2021 08:41:19 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "113",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/fotorama\/fotorama.css",
                    "system\/library\/fotorama\/fotorama.js"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/altinity-clickhouse-developer-300px.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-01--11.53.59.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--18.11.45.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--18.20.35.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.14.28.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.43.22.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-02--19.47.52.png"
                ]
            }
        },
        {
            "id": "109",
            "url": "http:\/\/test.leftjoin.ru\/all\/data-scaling-with-sql\/",
            "title": "Нормализация данных через запрос в SQL",
            "content_html": "<p>Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены  данные, зависят результаты всей вашей работы.<\/p>\n<p>Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.<\/p>\n<p>В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().<\/p>\n<p>Для работы возьмем таблицу <b>students<\/b> с данными о росте учащихся.<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td>name<\/td>\n<td>height<\/td>\n<\/tr>\n<tr>\n<td>Иван<\/td>\n<td style=\"text-align: right\">174<\/td>\n<\/tr>\n<tr>\n<td>Петр<\/td>\n<td style=\"text-align: right\">181<\/td>\n<\/tr>\n<tr>\n<td>Денис<\/td>\n<td style=\"text-align: right\">199<\/td>\n<\/tr>\n<tr>\n<td>Ксения<\/td>\n<td style=\"text-align: right\">158<\/td>\n<\/tr>\n<tr>\n<td>Сергей<\/td>\n<td style=\"text-align: right\">179<\/td>\n<\/tr>\n<tr>\n<td>Ольга<\/td>\n<td style=\"text-align: right\">165<\/td>\n<\/tr>\n<tr>\n<td>Юлия<\/td>\n<td style=\"text-align: right\">152<\/td>\n<\/tr>\n<tr>\n<td>Кирилл<\/td>\n<td style=\"text-align: right\">188<\/td>\n<\/tr>\n<tr>\n<td>Антон<\/td>\n<td style=\"text-align: right\">177<\/td>\n<\/tr>\n<tr>\n<td>Софья<\/td>\n<td style=\"text-align: right\">165<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<h2>Min-Max масштабирование<\/h2>\n<p>Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.<\/p>\n<p>Выполним масштабирование по формуле:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.42.52.png\" width=\"460\" height=\"104\" alt=\"\" \/>\n<\/div>\n<p>Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.<\/p>\n<p>SQL-запрос с подзапросом:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       1.0 * (height-t1.min_height)\/(t1.max_height - t1.min_height) AS scaled_minmax\r\n  FROM students, \r\n      (SELECT min(height) as min_height, \r\n              max(height) as max_height \r\n         FROM students\r\n      ) as t1;<\/code><\/pre><p>SQL-запрос с оконной функцией:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       (height - MIN(height) OVER ()) * 1.0 \/ (MAX(height) OVER () - MIN(height) OVER ()) AS scaled_minmax\r\n  FROM students;<\/code><\/pre><p>В результате мы получим переменные в диапазоне [0...1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td>name<\/td>\n<td>height<\/td>\n<td>scaled_minmax<\/td>\n<\/tr>\n<tr>\n<td>Иван<\/td>\n<td style=\"text-align: right\">174<\/td>\n<td>0.46809<\/td>\n<\/tr>\n<tr>\n<td>Петр<\/td>\n<td style=\"text-align: right\">181<\/td>\n<td>0.61702<\/td>\n<\/tr>\n<tr>\n<td>Денис<\/td>\n<td style=\"text-align: right\">199<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>Ксения<\/td>\n<td style=\"text-align: right\">158<\/td>\n<td>0.12766<\/td>\n<\/tr>\n<tr>\n<td>Сергей<\/td>\n<td style=\"text-align: right\">179<\/td>\n<td>0.57447<\/td>\n<\/tr>\n<tr>\n<td>Ольга<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>0.2766<\/td>\n<\/tr>\n<tr>\n<td>Юлия<\/td>\n<td style=\"text-align: right\">152<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>Кирилл<\/td>\n<td style=\"text-align: right\">188<\/td>\n<td>0.76596<\/td>\n<\/tr>\n<tr>\n<td>Антон<\/td>\n<td style=\"text-align: right\">177<\/td>\n<td>0.53191<\/td>\n<\/tr>\n<tr>\n<td>Софья<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>0.2766<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<h2>Масштабирование для заданного диапазона<\/h2>\n<p>Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.<br \/>\nФормула для вычисления в этом случае такая:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.43.04.png\" width=\"530\" height=\"104\" alt=\"\" \/>\n<\/div>\n<p>Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.<\/p>\n<p>SQL-запрос с подзапросом:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       ((height - min_height) * (20.0 - 10.0) \/ (max_height - min_height)) + 10 AS scaled_ab\r\n  FROM students,\r\n      (SELECT MAX(height) as max_height, \r\n              MIN(height) as min_height\r\n         FROM students  \r\n      ) t1;<\/code><\/pre><p>SQL-запрос с оконной функцией:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       ((height - MIN(height) OVER() ) * (20.0 - 10.0) \/ (MAX(height) OVER() - MIN(height) OVER())) + 10.0 AS scaled_ab\r\n  FROM students;<\/code><\/pre><p>Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td>name<\/td>\n<td>height<\/td>\n<td>scaled_ab<\/td>\n<\/tr>\n<tr>\n<td>Иван<\/td>\n<td style=\"text-align: right\">174<\/td>\n<td>14.68085<\/td>\n<\/tr>\n<tr>\n<td>Петр<\/td>\n<td style=\"text-align: right\">181<\/td>\n<td>16.17021<\/td>\n<\/tr>\n<tr>\n<td>Денис<\/td>\n<td style=\"text-align: right\">199<\/td>\n<td>20<\/td>\n<\/tr>\n<tr>\n<td>Ксения<\/td>\n<td style=\"text-align: right\">158<\/td>\n<td>11.2766<\/td>\n<\/tr>\n<tr>\n<td>Сергей<\/td>\n<td style=\"text-align: right\">179<\/td>\n<td>15.74468<\/td>\n<\/tr>\n<tr>\n<td>Ольга<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>12.76596<\/td>\n<\/tr>\n<tr>\n<td>Юлия<\/td>\n<td style=\"text-align: right\">152<\/td>\n<td>10<\/td>\n<\/tr>\n<tr>\n<td>Кирилл<\/td>\n<td style=\"text-align: right\">188<\/td>\n<td>17.65957<\/td>\n<\/tr>\n<tr>\n<td>Антон<\/td>\n<td style=\"text-align: right\">177<\/td>\n<td>15.31915<\/td>\n<\/tr>\n<tr>\n<td>Софья<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>12.76596<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<h2>Нормализация с помощью z-score<\/h2>\n<p>В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.<\/p>\n<p>Вычисляется z-score по формуле:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.43.19.png\" width=\"368\" height=\"101\" alt=\"\" \/>\n<\/div>\n<p>SQL-запрос с подзапросом:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       (height - t1.mean) * 1.0 \/ t1.sigma AS zscore\r\n  FROM students,\r\n      (SELECT AVG(height) AS mean, \r\n              STDDEV(height) AS sigma\r\n         FROM students\r\n        ) t1;<\/code><\/pre><p>SQL-запрос с оконной функцией:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT height, \r\n       (height - AVG(height) OVER()) * 1.0 \/ STDDEV(height) OVER() AS z-score\r\n  FROM students;<\/code><\/pre><p>В результате мы сразу заметим выбросы, которые выходят за пределы стандартного отклонения.<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td>name<\/td>\n<td>height<\/td>\n<td>zscore<\/td>\n<\/tr>\n<tr>\n<td>Иван<\/td>\n<td style=\"text-align: right\">174<\/td>\n<td>0.01488<\/td>\n<\/tr>\n<tr>\n<td>Петр<\/td>\n<td style=\"text-align: right\">181<\/td>\n<td>0.53582<\/td>\n<\/tr>\n<tr>\n<td>Денис<\/td>\n<td style=\"text-align: right\">199<\/td>\n<td>1.87538<\/td>\n<\/tr>\n<tr>\n<td>Ксения<\/td>\n<td style=\"text-align: right\">158<\/td>\n<td>-1.17583<\/td>\n<\/tr>\n<tr>\n<td>Сергей<\/td>\n<td style=\"text-align: right\">179<\/td>\n<td>0.38698<\/td>\n<\/tr>\n<tr>\n<td>Ольга<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>-0.65489<\/td>\n<\/tr>\n<tr>\n<td>Юлия<\/td>\n<td style=\"text-align: right\">152<\/td>\n<td>-1.62235<\/td>\n<\/tr>\n<tr>\n<td>Кирилл<\/td>\n<td style=\"text-align: right\">188<\/td>\n<td>1.05676<\/td>\n<\/tr>\n<tr>\n<td>Антон<\/td>\n<td style=\"text-align: right\">177<\/td>\n<td>0.23814<\/td>\n<\/tr>\n<tr>\n<td>Софья<\/td>\n<td style=\"text-align: right\">165<\/td>\n<td>-0.65489<\/td>\n<\/tr>\n<\/table>\n<\/div>\n",
            "date_published": "2021-05-13T11:15:58+03:00",
            "date_modified": "2021-05-14T08:36:57+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.42.52.png",
            "_date_published_rfc2822": "Thu, 13 May 2021 11:15:58 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "109",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.42.52.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.43.04.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-05-12--16.43.19.png"
                ]
            }
        },
        {
            "id": "94",
            "url": "http:\/\/test.leftjoin.ru\/all\/tranzakcii-v-sqlalchemy\/",
            "title": "Транзакции в SQLAlchemy",
            "content_html": "<p>Транзакция — последовательность действий, связанных с базой данных. Их основная польза заключается в том, что при возникновении какой-то ошибки или достижении других нужных условий всю транзакцию можно отменить, и все изменения, примененные к базе данных, будут отменены. Сегодня мы напишем небольшой скрипт, который при помощи транзакций SQLAlchemy пишет информацию о подписчиках сообщества в базу данных MySQL, а при возникновении ошибки отменяет текущую транзакцию.<\/p>\n<h2>Сбор информации об участниках через VK API<\/h2>\n<p>Для начала напишем пару маленьких функций — первая будет возвращать число подписчиков сообщества, а вторая — отправлять запрос и формировать датафрейм с информацией о подписчиках сообщества.<\/p>\n<p class=\"note\">Подробнее о том, как получить токен, можно прочитать в материале <a href=\"http:\/\/test.leftjoin.ru\/all\/get-data-from-vk\/\" class=\"nu\">«<u>Собираем данные по рекламным кампаниям ВКонтакте<\/u>»<\/a><\/p>\n<pre class=\"e2-text-code\"><code>from sqlalchemy import create_engine\r\nimport pandas as pd\r\nimport requests\r\nimport time\r\n\r\ntoken = '42hj2ehd3djdournf48fjurhf9r9o2eurnf48fjurhf9r9734'\r\ngroup_id = 'leftjoin'<\/code><\/pre><p>Чтобы узнать число подписчиков достаточно отправить метод groups.getMembers с любыми параметрами — в ответе всегда возвращается количество в поле count.<\/p>\n<pre class=\"e2-text-code\"><code>def get_subs_count(group_id):\r\n    count = requests.get('https:\/\/api.vk.com\/method\/groups.getMembers', params={\r\n        'access_token':token,\r\n        'v':5.103,\r\n        'group_id':group_id\r\n    }).json()['response']['count']\r\n    return count<\/code><\/pre><p>Для примера будем брать имена, id, фамилии подписчиков, некоторую расширенную информацию и получать только по 10 подписчиков за раз, чтобы рассмотреть работу транзакций детально — каждые 10 подписчиков будут вставляться одной транзакцией. Введём дополнительное поле offset, чтобы знать, в какой итерации добавлены строки.<\/p>\n<pre class=\"e2-text-code\"><code>def get_subs_info(group_id, offset):\r\n    response = requests.get('https:\/\/api.vk.com\/method\/groups.getMembers', params={\r\n        'access_token':token,\r\n        'v':5.103,\r\n        'group_id':group_id,\r\n        'offset':offset,\r\n        'count':10,\r\n        'fields':'sex, has_mobile, relation, can_post'\r\n    }).json()['response']['items']\r\n    df = pd.DataFrame(response)\r\n    df['offset'] = offset\r\n    return df<\/code><\/pre><h2>Транзакции<\/h2>\n<p>Наконец, можем подсоединиться к базе данных при помощи SQLAlchemy:<\/p>\n<pre class=\"e2-text-code\"><code>engine = create_engine('mysql+mysqlconnector:\/\/' +\r\n                           'root' + ':' + '' + '@' +\r\n                           'localhost' + '\/' +\r\n                           'transaction', echo=False)<\/code><\/pre><p>У транзакций всегда должно быть начало — begin, и конец — commit. В случае, если произошла какая-то ошибка, можно сделать откат — rollback. Сперва получаем число подписчиков сообщество, и в каждой итерации цикла при помощи контекстного менеджера with ... as создаём новое подключение. Сразу после объявляем начало транзакции по этому подключению и с обработчиком исключений пробуем получить информацию о десяти подписчиках через функцию get_subs_info. Вставляем полученный датафрейм в таблицу методом to_sql и завершаем транзакцию при помощи метода commit(). В случае, если возникла какая-то ошибка — печатаем её на экран и отменяем транзакцию.<\/p>\n<pre class=\"e2-text-code\"><code>offset = 0\r\nsubs_count = get_subs_count(group_id)\r\nwhile offset &lt; subs_count:\r\n    with engine.connect() as conn:\r\n        transaction = conn.begin()\r\n        try:\r\n            df = get_subs_info(group_id, offset)\r\n            df.to_sql('subscribers', con=conn, if_exists='append', index=False)\r\n            transaction.commit()\r\n        except Exception as E:\r\n            print(E)\r\n            transaction.rollback()\r\n    time.sleep(1)\r\n    offset += 10<\/code><\/pre><p>Чтобы протестировать работу транзакций слегка обновим последний блок кода — добавим вызов ошибки ValueError после вставки данных в базу, если текущий offset равен 10.<\/p>\n<pre class=\"e2-text-code\"><code>offset = 0\r\nsubs_count = get_subs_count(group_id)\r\nwhile offset &lt; subs_count:\r\n    with engine.connect() as conn:\r\n        transaction = conn.begin()\r\n        try:\r\n            df = get_subs_info(group_id, offset)\r\n            df.to_sql('subscribers', con=conn, if_exists='append', index=False)\r\n            if offset == 10:\r\n                raise(ValueError)\r\n            transaction.commit()\r\n        except Exception as E:\r\n            print(E)\r\n            transaction.rollback()\r\n    time.sleep(1)\r\n    offset += 10<\/code><\/pre><p>Как и планировалось, данные за итерацию с offset = 10 не занесены в таблицу. Несмотря на то, что ошибка возникла уже после добавления новых данных, транзакция была прервана методом rollback() и завершение транзакции было отменено.<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/1-23.png\" width=\"759\" height=\"562\" alt=\"\" \/>\n<\/div>\n",
            "date_published": "2021-02-12T11:10:22+03:00",
            "date_modified": "2021-02-08T13:11:02+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/1-23.png",
            "_date_published_rfc2822": "Fri, 12 Feb 2021 11:10:22 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "94",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/1-23.png"
                ]
            }
        },
        {
            "id": "89",
            "url": "http:\/\/test.leftjoin.ru\/all\/unpivot-with-cross-join\/",
            "title": "UNPIVOT данных с использованием CROSS JOIN",
            "content_html": "<p>Зачастую мы получаем данные в предагрегированном виде, когда каждая отдельная колонка является посчитанной метрикой. По аналогии мы получаем подобный результат, когда строим сводную таблицу в Excel и используем некоторое количество фактов для агрегации. Но что делать, если нам нужно произвести обратную операцию — Unpivot?<\/p>\n<p>Как поступить, если в датасете понадобилось трансформировать данные в реляционный вид? В Tableau есть фича <a href=\"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/pivot.htm\">Unpivot<\/a>, которая сделает всё сама: если датасет построен из файла, достаточно выделить нужные колонки и нажать на кнопку «Pivot». А в некоторых диалектах SQL, например, в Transact, уже есть <a href=\"https:\/\/docs.microsoft.com\/ru-ru\/sql\/t-sql\/queries\/from-using-pivot-and-unpivot\">встроенные функции<\/a>, которые тоже делают это сами.<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/qs_pivot_example.png\" width=\"650\" height=\"263\" alt=\"\" \/>\n<\/div>\n<p>Но в случае, если датасет построен на Custom SQL Query из базы данных, у которой в арсенале отсутствуют встроенные функции для трансформации в сводную и обратно, необходим какой-то другой подход, и Tableau порекомендует для такой таблицы:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>ID<\/b><\/td>\n<td><b>a<\/b><\/td>\n<td><b>b<\/b><\/td>\n<td><b>c<\/b><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>a1<\/td>\n<td>b1<\/td>\n<td>c1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>a2<\/td>\n<td>b2<\/td>\n<td>c2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>Воспользоваться таким стандартным универсальным, но не очень эффективным решением:<\/p>\n<pre class=\"e2-text-code\"><code>select id, ‘a’ AS col, a AS value\r\nfrom yourtable\r\nunion all\r\nselect id, ‘b’ AS col, b AS value\r\nfrom yourtable\r\nunion all\r\nselect id, ‘c’ AS col, c AS value\r\nfrom yourtable<\/code><\/pre><p>И в результате получить таблицу вида:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>id<\/b><\/td>\n<td><b>col<\/b><\/td>\n<td><b>value<\/b><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>a<\/td>\n<td>a1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>a<\/td>\n<td>a2<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>b<\/td>\n<td>b1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>b<\/td>\n<td>b2<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>c<\/td>\n<td>c1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>c<\/td>\n<td>c2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>Порой, когда мы работаем с физической таблицей и нам надо быстро получить результаты для двух-трех колонок, действительно, подобное решение можно быстро применить, не задумываясь. Однако в случае, когда вместо таблицы содержится, например, сложный подзапрос с несколькими джойнами и нужно сделать Pivot для 5+ колонок, подзапрос вызовется целых 5+ раз, согласитесь, не очень действенно считать одно и тоже неоднократно. Вместо этого можно воспользоваться рецептом с CROSS JOIN, найденным на просторах Stack Overflow:<\/p>\n<pre class=\"e2-text-code\"><code>select t.id,\r\nc.col,\r\n    case c.col\r\n        when 'a' then a\r\n        when 'b' then b\r\n        when 'c' then c\r\n    end as data\r\nfrom yourtable t\r\ncross join\r\n(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre><p>Разберём запрос подробнее. CROSS JOIN — перекрёстное соединение, декартово произведение, или, проще говоря, произведение всех строк со всеми. За ненадобностью в синтаксисе CROSS JOIN отсутствует ON — мы объединяем не по какому-то конкретному полю две таблицы, а сразу по всем существующим строкам.<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/Background_2.png\" width=\"730\" height=\"388\" alt=\"\" \/>\n<\/div>\n<p>Сначала мы формируем таблицу со всеми колонками, предназначенными для преобразования в строки. В нашем случае это колонки a, b и c: поэтому мы сделали таблицу c, в которой будет колонка col со значениями a, b и c:<\/p>\n<pre class=\"e2-text-code\"><code>(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre><p>Выглядит она так:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>col<\/b><\/td>\n<\/tr>\n<tr>\n<td>a<\/td>\n<\/tr>\n<tr>\n<td>b<\/td>\n<\/tr>\n<tr>\n<td>c<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<p>Затем таблицы yourtable и c объединятся перекрестным соединением, а после мы возьмём поля id, col и в зависимости от того, как называется ячейка в col, подставим соответствующие данные в поле data.<\/p>\n<pre class=\"e2-text-code\"><code>select t.id,\r\nc.col,\r\n    case c.col\r\n        when 'a' then a\r\n        when 'b' then b\r\n        when 'c' then c\r\n    end as value\r\nfrom yourtable t\r\ncross join\r\n(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre><p>В итоге получим ту же самую искомую таблицу, с которой уже можно удобно работать любым аналитическим инструментом:<\/p>\n<div class=\"e2-text-table\">\n<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n<tr>\n<td><b>id<\/b><\/td>\n<td><b>col<\/b><\/td>\n<td><b>value<\/b><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>a<\/td>\n<td>a1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>a<\/td>\n<td>a2<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>b<\/td>\n<td>b1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>b<\/td>\n<td>b2<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>c<\/td>\n<td>c1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>c<\/td>\n<td>c2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n",
            "date_published": "2021-01-08T16:30:14+03:00",
            "date_modified": "2021-01-08T16:31:33+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/qs_pivot_example.png",
            "_date_published_rfc2822": "Fri, 08 Jan 2021 16:30:14 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "89",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/qs_pivot_example.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/Background_2.png"
                ]
            }
        },
        {
            "id": "83",
            "url": "http:\/\/test.leftjoin.ru\/all\/coalesce2020-dbt\/",
            "title": "Конференция Coalesce от dbt: что посмотреть?",
            "content_html": "<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/Eg2IiVMX0AI1eLj.jpg-large.jpeg\" width=\"1516\" height=\"760\" alt=\"\" \/>\n<\/div>\n<p>С 7 по 11 декабря проходила <a href=\"https:\/\/www.getdbt.com\/coalesce\">конференция Coalesce<\/a>, о которой я рассказывал ранее. В этом году все организаторы решили проводить конференции по 5 дней с кучей докладов.<\/p>\n<p>С одной стороны это плюс — ощущение, что информации много и можно выбрать, что интересно. С другой стороны такое количество информации несколько изматывает, потому что часто по названию доклада не очень понятно насколько он окажется полезным и интересным. Мне все же кажется, что более трех дней для конференции это много, т. к. интерес аудитории теряется, да и необходимость заниматься своими личными и профессиональными делами не может испариться из-за события, которое хоть и в онлайне, но занимает твое внимание.<\/p>\n<p>Однако мне удалось посмотреть большую часть докладов, кое-что пролистывая. Для начала коротко в целом о впечатлениях: очень круто изучать доклады с подобной конференции как Coalesce, потому что речь идет в основном о современных инструментах и облачных решениях. Почти в каждом докладе можно услышать про Redshift \/ BigQuery \/ Snowflake, а с точки зрения BI: Mode \/ Tableau \/ Looker \/ Metabase. В центре всего, разумеется, dbt.<\/p>\n<p>Мой шорт-лист докладов, которые рекомендую изучить:<\/p>\n<ol start=\"1\">\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/dbt-101-eu-and-us-friendly\">dbt 101<\/a> — вводный доклад и интро в то, что такое dbt и как его используют<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/kimball-in-the-context-of-the-modern-data-warehouse-whats-worth-keeping-and-whats-not\">Kimball in the context of the modern data warehouse: what’s worth keeping, and what’s not<\/a> — интересный и очень-очень спорный доклад, который вызвал массу вопросов в slack dbt. Вкратце, автор предлагает перейти на «широкие» аналитические таблицы и отказаться от нормальных форм всюду.<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/building-a-robust-data-pipeline-with-dbt-airflow-and-great-expectations\">Building a robust data pipeline with dbt, Airflow, and Great Expectations<\/a> — в докладе про небезынтересный инструмент <a href=\"https:\/\/greatexpectations.io\">greatexpectations<\/a>, суть которого в валидации данных<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/orchestrating-dbt-with-dagster\">Orchestrating dbt with Dagster<\/a> — мне было несколько скучновато слушать, но если хочется познакомиться с Dagster — самое то<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/supercharging-your-data-team\">Supercharging your data team<\/a> — ребята сделали обертку к dbt, назвали dbt executor 9000 и рассказывают о нем<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/presenting-sqlfluff\">Presenting: SQLFluff<\/a> — про очень классную штуку <a href=\"https:\/\/www.sqlfluff.com\">SQLFluff<\/a>, которая автоматически редактирует SQL-код согласно канонам<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/quickstart-your-analytics-with-fivetran-dbt-packages\">Quickstart your analytics with Fivetran dbt packages<\/a> — из доклада можно узнать, что такое Fivetran и как его используют совместно с dbt<\/li>\n<li><a href=\"https:\/\/www.getdbt.com\/coalesce\/agenda\/perfect-complements-using-dbt-with-looker-for-effective-data-governance\">Perfect complements: Using dbt with Looker for effective data governance<\/a> — про взаимодействие dbt и looker, про различия и схожие части инструментов<\/li>\n<\/ol>\n",
            "date_published": "2020-12-11T13:27:14+03:00",
            "date_modified": "2020-12-11T13:27:05+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/poster.png",
            "_date_published_rfc2822": "Fri, 11 Dec 2020 13:27:14 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "83",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/poster.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/Eg2IiVMX0AI1eLj.jpg-large.jpeg"
                ]
            }
        }
    ],
    "_e2_version": 3365,
    "_e2_ua_string": "E2 (v3365; Aegea)"
}