🚨 Справочник по каналу 🚨
Немного очевидного: меня зовут Николай Валиотти, LEFT JOIN – телеграм-канал, который я веду вместе со своей командой Valiotti Analytics. Меня увлекает тема анализа, инжиниринга и визуализации данных, а также машинного обучения. Этот канал появился в ноябре 2018 года и довольно стремительно развивается все это время от заметок автора по теме интересных инструментов и приемов в Python/SQL к полноценному медиа со своим подкастом, дайджестами и примечательными проектами.
Мне бы хотелось, чтобы у всех специалистов была возможность разобраться в сложной информации об анализе данных, поэтому мы рассказываем об этом простым языком с яркими примерами. Чаще всего в канале можно встретить именно такой контент. Однако иногда я просто публикую интересные новости IT-сферы, поэтому не удивляйтесь. Например, у нас есть рубрика Новостной Дайджест, где информация далеко не только о данных.

🙋 Немного обо мне:
▫️С 2009 года работаю в области анализа данных и даже защитил кандидатскую диссертацию в СПбГУ по теме использования нейронных сетей
▫️Сейчас я учусь на программе Master of Analytics в американском ВУЗе Georgia Tech
▫️Работал в ряде крупных компаний: Лента, Yota, Балтика, Юлмарт, Tapcore, Airpush
▫️Увлекался программированием на разных языках: PHP, JS, C++ (в универе), Java, R, Python, а потом переключился на создание бизнеса, и так в 2019 году появилась компания Valiotti Analytics
▫️Развиваю c партнером стартап Mprove – self-service BI-платформа.
▫️Занимаюсь ангельским инвестированием в технологичные проекты преимущественно в области данных. Если вы – такой проект, то скорее пишите мне.
▫️Интересуюсь криптовалютой и проектами в этой области (особенно здорово, когда они на стыке с данными/аналитикой).
▫️Живу на Кипре с женой и дочкой, увлекаюсь теннисом 🎾, периодически рублю в плойку 🎮 😇

🛠 Услуги
🔹В рамках Valiotti Analytics мы помогаем компаниям строить аналитический стек: процессы инжиниринга, хранилища данных, отчетность, внедрение BI-инструментов на всем, что называется избитым "modern data stack"
🔹Для нас это означает, что мы работаем с современными цифровыми заказчиками, использующими облачные технологии, и совсем не работаем с несколько устаревшим стеком, где, например, присутствует 1С
🔹Также иногда я помогаю в персональном менторинге и консультирую владельцев компаний/топ-менеджеров


❕ Важные материалы
🖼 Цикл постов про использование оконных функций
По хештегу: #leftjoin_sql

📝 Полезные посты из блога LeftJoin
Аналитические метрики здорового маркетолога
Лонгрид про выбор цветов для визуализации данных
Мануал по Yandex.Datasphere
Поиск интересных новых мест по геотегам в Instagram
Граф телеграм-каналов по теме аналитики
Дашборд первых 8 месяцев жизни малыша

👫 Совместные проекты
Дашборд с анализом рынка вакансий аналитиков c hh.ru с @revealthedata

📚 Статьи на Хабре
Используем serverless для построения аналитики на данных из AmoCRM в Yandex.Cloud
Подробный анализ телеграм-канала Артемия Лебедева и кое-что еще

🔬 Исследования
Исследование рынка онлайн-образования по анализу данных в России

📰 Интересные новости
Партнерство Tableau и Looker
Десять open-source аналогов Google Analytics
Snowflake купил Streamlit: почему это важно?
Список альтернатив Slack

🎞 Канал на YouTube
Все, что нужно знать про поступление в магистратуру Georgia Tech
Гайд по современным BI-инструментам

🎤 Интервью и выступления
Выступление на SmartData: Self-Service BI
Выступление на Матемаркетинге
Интервью для @start_ds
Интервью для @revealthedata
Презентация с выступления на конференции Linq

🎧 Подкаст Data Heroes: 2 сезон 👾
Эпизод 1: Рынок аналитики сейчас: какие произошли изменения после старта февральского безумия?
Эпизод 2: Data образование за рубежом: опыт тех, кто уже прошел этот путь

📧 Контакты и ссылки
С удовольствием знакомлюсь и общаюсь с новым и интересными людьми! Если у вас что-то есть по делу, пишите.
Телеграм — @valiotti
Почта — nikolay@valiotti.com
Valiotti Analytics | Блог Left Join | Tableau Public | YouTube
Подкаст Data Heroes | Твиттер | LinkedIn

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

Разберемся подробнее
Когда мы пишем запрос для получения агрегатов, то строки обрабатываются «единым куском», для которого вычисляется агрегат.
А при использовании оконных функций, запрос делится на “окна” и уже для каждой из отдельных частей считаются нужные агрегаты. ​​Важно помнить: движок посчитает в рамках окна только те агрегаты, которые используют оконное выражение, а не все подряд в запросе.
Окно определяется с помощью обязательной инструкции OVER():

SELECT функция (столбец для вычислений)
OVER (
   [PARTITION BY столбец для формирования окна]
   [ORDER BY столбец для сортировки]
   [ROWS или RANGE выражение для ограничения строк в пределах группы]
   )

Для лучшего понимания работы оконных функций стоит вспомнить о порядке выполнения запроса: так как оконные функции выполняются в блоке SELECT, то все вычисления в них происходят после возможной фильтрации и/или группировки. То есть, если в таблице с домашними животными указать WHERE animal = ‘cat’, то оконные функции будут оперировать только котиками, а попугаи и пёсики в их расчёты не попадут.

Интересная особенность
Можно не использовать опциональные конструкции и получить аналогичный обыкновенной агрегации результат.
Сравните:

SELECT SUM(amount) OVER() AS total_amount
FROM A

VS

SELECT SUM(amount) AS total_amount
FROM A

Оба запроса выдадут один и тот же результат. Но в первом случае мы можем дополнительно сразу выбрать другие поля, не участвующие в агрегации, в то время как второй вариант (классическая агрегация) такого не допускает.

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

Поговорим про них отдельно?
#leftjoin_sql

Параметры оконных функций: PARTITION BY и ORDER BY
Этот пост – продолжение серии постов про оконные функции (предыдущий пост был введением). Сегодня мы обсудим два необязательных параметра оконки: PARTITION BY и ORDER BY.

Разбираемся подробнее
Оба параметра помогают нам точнее определить порядок и алгоритм работы запроса: один отвечает за группировку данных, а другой – за сортировку.

Предложение PARTITION BY определяет столбец, по которому будет производиться разбиение на окна. Суть в том, чтобы разбить столбец на разные категории и посчитать некоторые метрики в рамках полученных групп (например, если вам нужно сравнить вес котика с остальными котиками, но не с попугаями или песиками).

Вместе с PARTITION BY может применяться предложение ORDER BY, которое определяет порядок сортировки внутри окна. Порядок сортировки очень важен, ведь оконная функция будет обрабатывать данные согласно этому порядку (например, чтобы сравнить, насколько котик тяжелее предыдущего по весу, нужно их всех отсортировать внутри окна). Если вы не используете предложение PARTITION BY, а только ORDER BY, то окном будет весь набор данных.

Давайте вместе потренируемся в написании запросов с использованием ORDER BY и PARTITION BY. Итак, возьмем таблицу animals (на картинке), которая содержит вид животного, его имя, вес и возраст, и попробуем решить следующую задачу: определить насколько процентов каждое животное старше среднего по группе.

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

Оконные функции LAG и LEAD
В предыдущем посте мы разобрались с параметрами оконных функций, а теперь перейдем к конкретным примерам аналитических функций.

Функции смещения
Эти функции позволяют обращаться к предыдущим или следующим значениям окна.

Функция LAG обращается к данным из предыдущей строки окна, а LEAD – к данным из следующей строки. Эти функции нужны для того, чтобы, например, сравнить текущее значение строки с предыдущим или следующим. Каждая функция имеет три параметра:
- столбец, значение которого необходимо вернуть,
- количество строк для смещения (по умолчанию 1),
- значение, которое необходимо вернуть, если после смещения возвращается значение NULL (к примеру, когда мы ищем предыдущее значение для первой строки окна).

Давайте посмотрим, на примере таблички orders, как работает функция LEAD. Например, если мы хотим увидеть текущую и следующую дату покупки, то запрос будет выглядеть так:

SELECT user_id,
 order_date,
 LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) as next_order_date,
 receipt_no,
 amount
FROM orders

Результат запроса в первом комментарии под постом 👇

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

#leftjoin_sql

Ограничение оконных функций ROWS: Часть 1
Сегодня обсудим еще один важный параметр оконной функции: ROWS.

Разбираемся подробнее
Параметр ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.
В выражении для ограничения строк ROWS также можно использовать следующие ключевые слова:
▪️ UNBOUNDED PRECEDING — указывает, что окно начинается с первой строки группы.
▪️ UNBOUNDED FOLLOWING – с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы.
▪️ CURRENT ROW – инструкция указывает, что окно начинается или заканчивается на текущей строке.
▪️ BETWEEN «граница окна» AND «граница окна» — указывает нижнюю и верхнюю границу окна.
▪️ «Значение» PRECEDING – определяет число строк перед текущей строкой.
▪️ «Значение» FOLLOWING — определяет число строк после текущей строки.

Давайте разберем на примерах, как можно использовать перечисленные ключевые слова и как именно работают оконные функции. Вернемся к ранее использованной таблице animals.

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
Для начала попробуем определить средний возраст двух питомцев (текущего и следующего) одного вида, отсортированных по возрастанию веса. Для этого, как вы, наверное, уже догадались, мы будем использовать ключевые слова BETWEEN, CURRENT ROW и FOLLOWING.

SELECT kind,
       name,
       weight,
       age,
       AVG(age) OVER (PARTITION BY kind ORDER BY weight
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM animals

#leftjoin_sql