Учимся создавать «окна» в SQL

В прошлом посте мы узнали, зачем нужны оконные функции. Теперь научимся их объявлять. Все начинается с инструкции OVER() - она и определяет наше «окно».

Ключевые команды внутри OVER():

▪️PARTITION BY - разделяет данные на группы (партиции). Как GROUP BY, но без «схлопывания» строк. Считает функцию внутри каждой группы отдельно.
▪️ORDER BY - сортирует строки внутри окна. Критично для функций нарастающего итога, ранжирования и смещения (LAG/LEAD).

Разберем на примере простой таблички, содержащей дату, канал с которого пришел пользователь и количество конверсий:

SELECT 
  date AS dt
, medium AS med
, conversions AS conv
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium) AS sum 
FROM orders

Что произойдет?

▪PARTITION BY Date создаст отдельное «окно» для каждой даты. Сумма будет считаться только в рамках одного дня.
▪ORDER BY medium отсортирует каналы внутри каждой даты.
▪SUM(conversions) в паре с ORDER BY рассчитает нарастающий итог конверсий внутри каждого дня. Для первой строки в окне (дне) sum будет равен ее conversions, для второй - сумме первой и второй, и так далее.

⚠️ Важно: ROWS / RANGE управляют диапазоном строк, по которым считается оконная функция. И даже если ничего не указывать, то по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

dt        med      conv  sum
10.05.20  cpa         1    1
10.05.20  cpc         2    3
10.05.20  organic     1    4
11.05.20  cpa         1    1
11.05.20  cpc         3    4
11.05.20  direct      1    5
11.05.20  organic     2    7
12.05.20  cpc         1    1
12.05.20  organic     2    3

Основы разобрали! Далее я расскажу как сужать фокус окна до «скользящего» диапазона с помощью ROWS BETWEEN.

#харды #sql

Сегодня поговорим про экосистемные метрики. Или, если слово «экосистема» кажется слишком громким - про мультипродуктовые, сквозные пользовательские метрики.

В России настоящих экосистем не так уж много: Т-Банк, Сбер, Яндекс, МТС. Но чтобы применять экосистемные метрики, не обязательно быть «экосистемой». Достаточно иметь несколько продуктов и общего пользователя.

Сначала давайте разберемся, что же такое «экосистема»?

Экосистема - это портфель продуктов, в основе которого лежит платформа единого профиля пользователя и между продуктами которого существует передаточная ценность.

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

Когда у компании один продукт, чаще всего все довольно просто и грубо. Обычно считают доходы и расходы. Юнит-экономика, Retention, LTV и CAC приходят позже, когда продуктов становится больше и бизнес усложняется.

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

А что такое «экосистемная метрика»?

Экосистемная метрика - это метрика, описывающая сразу несколько продуктов, а не один конкретный.

Это метрика, которая позволяет перейти от математики продукта к математике пользователя и экосистемы в целом.

Такие метрики нужны не «для красоты». Они используются в дереве метрик, чтобы понимать вклад конкретного продукта во всю систему, а также чтобы корректно оценивать эффекты A/B-тестов, которые почти всегда выходят за рамки одного продукта.

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

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

Какой вывод?

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

#харды #метрики

Если вы искали исчерпывающий курс по управлению данными, то он здесь, в одной книге на 800 страниц. Но приготовьтесь - это вам не «Котики».

📚 DAMA-DMBOK: Свод знаний по управлению данными. 2-е издание

The Data Management Association - это международная некоммерческая организация, которая поддерживает и развивает профессиональное сообщество в области управления данными. Она была основана в 1980-х и ранее называлась Data Administration Management Association или сокращенно DAMA. Организация знаменита публикацией ключевого справочника по управлению данными Data Management Body of Knowledge или DMBOK.

Справочник DAMA-DMBOK содержит лучшие практики, методологии и рекомендации по различным аспектам управления данными. Он охватывает 10 ключевых областей, включая архитектуру и проектирование данных, хранилища данных и бизнес-аналитику, управление качеством данных и метаданными. Авторы стремятся стандартизировать процессы работы с данными и улучшить их управление. Поэтому DAMA-DMBOK - стандарт в дата-менеджменте и основное пособие для подготовки к экзамену на Certified Data Management Professional (CDMP). Берем на заметку.

🔗 Первое издание вышло в 2009 году, второе опубликовано в 2017 году. Именно оно переведено на русский язык и продается в бумажном варианте за много денег, например, на OZON. Электронную версию в PDF - качайте по ссылке.

#книга

Как поддерживать форму, будучи руководителем?

Помнишь то чувство, когда ты впервые стал руководителем? Выходишь со встречи, на которой решаются важные вопросы, и понимаешь масштаб новой роли! А какие открываются горизонты…

Но в этой новой реальности есть и обратная сторона - постепенное отдаление от «земли». Как бывший аналитик, я ощутил это на себе: чем глубже погружаешься в менеджмент, тем выше риск потерять харды. Если расслабиться, то однажды можно обнаружить, что опыт больше не подкрепляется актуальными знаниями.

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

Я выработал для себя несколько рабочих практик:

▪️Сознательно оставлять за собой право брать сложные проекты, которые требуют глубокого погружения. Делегировать важно, но иногда нужно и самому «поработать руками», чтобы поддерживать уровень и понимать, с какими реальными вызовами сейчас сталкивается команда.

▪️Системное обучение - обязательно. Это может быть изучение перспективного языка, фреймворка или углубленный курс по новой технологии. Такой подход позволяет расширять кругозор и приносить в команду конкретные, проверенные на себе знания и идеи для внедрения.

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

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

Тренируйте свои профессиональные «мышцы» так же регулярно, как и управленческие. Именно эта дисциплина отделяет просто начальника от настоящего лидера.

#опыт

Как в SQL настроить фокус окна?

В прошлый раз мы научились делить данные на окна. Но что, если нам нужно посчитать результат не по всей группе, а сумму с соседней строкой?

Когда я осваивал оконные функции, для меня самым сложным было разобраться не с OVER, партициями или самими функциями, а именно с настройками окна.

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

RANGE, в отличие от ROWS, работает не с физическими строками, а с диапазоном значений ORDER BY. Поэтому несколько строк с одинаковым значением могут попадать в одно окно.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

Возьмем наш пример и посчитаем сумму по текущей и следующей строке внутри каждого дня:

SELECT 
  date
, medium
, conversions
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum 
FROM orders

Результат выполнения и логику запроса смотрите на картинке.

В выражении для ограничения окна также можно использовать следующие ключевые слова:

▪️UNBOUNDED PRECEDING - указывает, что окно начинается с первой строки группы;
▪️UNBOUNDED FOLLOWING - с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
▪️CURRENT ROW - инструкция указывает, что окно начинается или заканчивается на текущей строке;
▪️BETWEEN «граница окна» AND «граница окна» - указывает нижнюю и верхнюю границу;
▪️«Значение» PRECEDING - определяет число строк перед текущей строкой (не допускается в RANGE);
▪️«Значение» FOLLOWING - определяет число строк после текущей строки (не допускается в RANGE).

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

Далее разберем самые полезные оконные функции.

#харды #sql