LEFT JOIN

Ограничение оконных функций ROWS: Часть 2
Теперь посмотрим, как работают UNBOUNDED PRECEDING и UNBOUNDED FOLLOWING.

Перейдем сразу к практике: UNBOUNDED PRECEDING
Давайте посчитаем средний возраст питомцев внутри одного вида, взяв окно с первого питомца до текущего по списку, отсортированному по возрастанию весов животных. В этом запросе нам помогут ключевые слова BETWEEN, CURRENT ROW, а также UNBOUNDED PRECEDING.

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

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

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

Пишите в комментариях, какую оконную функцию разобрать следующей, и оставляйте реакцию, если вам нравится цикл постов про SQL!
#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

💁‍♂️ Ассистент по созданию кода
Я уже рассказывал вам про инструмент, который объясняет код и может быть полезен тем, кто вынужден разбираться в тысячах строк чужого кода. Сегодня я наткнулся на сервис CodeSquire, который может еще сильнее помочь тем, кто пишет много кода на постояннной основе, или тем, кто лишь нарабатывает этот навык.

Что может CodeSqire?
◾️ Превращать комментарии в строки кода, выполняющие нужные действия, например:

# create a bar chart that shows number of survived and not survived broken down by Pclass using plotly
⬇️
fig = px.bar(a, x='Pclass', y='PassengerId', color='Survived', text='PassengerId', color_discrete_map={0: '#FF0000', 1: '#0000FF'}) fig.show()

◾️ Создавать SQL-запросы по кратким текстовым вводным, например:

-- get top 10 most popular male names
⬇️
SELECT state, gender, year, name, number FROM bigquery-public-data.usa_names.usa_1910_2013 WHERE state = 'FL' AND gender = ‘M' ORDER BY number DESC LIMIT 10;

◾️ Объяснять код, который вы (или кто-то другой) написали
◾️ Разделять код на шаги и создавать комментарии, поясняющие логику алгоритма
◾️ Оптимизировать уже написанный код

Сервис доступен по подписке на месяц и на год, а также существует опция бесплатного 30-дневного пробного периода.

Оставляйте 🔥, если думаете, что CodeSquire – полезный сервис, и 🤨, если думаете, что проще будет работать без него.

На связи DataHeroes и сегодня мы поступаем в зарубежные ВУЗы! 👩‍🎓

Наши спикеры - те, кто уже прошли путь от подготовки документов до защиты диплома. Они поделятся своим опытом, дадут рекомендации по поступлению и ответят на важные вопросы:
- Можно ли поступить на магистра, если только заканчиваешь бакалавриат в России?
- Как проходят экзамены в зарубежном ВУЗе?
- Что даст диплом в дальнейшей карьере?
- В чем отличия обучения в иностранном ВУЗе и ВУЗе в РФ?

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

СПИКЕРЫ: Юрий Душин (The University of Edinburgh ), Антон Охотников (University of Southampton), Илья Шилов (Paris Saclay, establishment Paris Sud)

Слушайте подкаст на платформах: Spotify, Anchor, Apple Podcasts, Google, Yandex, Mave, Castbox, Overcast, Telegram (↓)

#DataHeroes #подкаст

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

Решение:

SELECT user_id,
  order_date,
  receipt_no,
  amount,
   amount - (LAG(amount, 1, amount) OVER (PARTITION BY user_id ORDER BY order_date)) as diff
FROM orders

Оконные функции 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

Clickhouse Cloud — в публичной бете! 🎉
Дают триал на 14 дней.

📊 Вакансии аналитиков за полгода
Обновили карточки по зарплатам и вакансиям аналитиков на HH за последние полгода и добавили отдельные карточки «Москва + Питер». В среднем по сравнению с предыдущим годом количество вакансий снизилось на 12%, а зарплата выросла на 13%. Больше всего по количеству вакансий просели области BI и Data Science, а меньше всего изменилась область системного/бизнес анализа и инжиниринга данных.
@revealthedata | @leftjoin

Фоллоу-ап к нашему последнему выпуску подкаста DataHeroes: супер-информативные карточки от Ромы по вакансиям и зарплатам для аналитиков с HH.ru. Идеальное дополнение к расуждениям о том, что вообще творится на рынке вакансий (рекомендую листать параллельно с выпуском!).

🙋 IT-конференция Linq 🎯
Самая классная возможность в любом комьюнити – это делиться собственными знаниями и опытом, дискутировать, искать открытые вопросы и новые возможности. Поэтому я стараюсь как можно чаще участвовать в конверенциях: как минимум – слушателем, как максимум – спикером.

Через несколько недель я буду выступать с темой «Text Generation Tasks and Modern Data Stack» в блоке ML/AI на одной из масштабных IT-конференций на Кипре – Linq. Конференция проводится на анлийском языке и среди спикеров (помимо меня) будут Валерий Бабушкин (blockchain.com), Elena Ehrlich (Amazon Web), Mariusz Gasiewski (Google)и многие другие специалисты.

В целом, помимо блока ML/AI, будут выспупления других актуальных IT-сфер: Metaverse, Relocate IT, PR & Comms in Tech, Gamedev, Invest in Tech.

Если вам это интересно, то расписание, билеты и список спикеров есть на сайте конференции.

А еще, специально для подписчиков LEFTJOIN организаторы сделали промокод 10% на билеты ValiottixLinq.

Привет! Снова на связи DataHeroes и мы (наконец-то) начинаем второй сезон! 👾 Первый выпуск будет о как-никогда-актуальной теме — об изменениях на рынке аналитики после старта этого безумия в феврале.

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

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

СПИКЕРЫ: Кира Кузьменко и Оксана Прутьянова (NEWHR), Наталья Данина (hh.ru), Денис Кравченко (Exness)

Слушайте подкаст на платформах: Spotify, Anchor, Apple Podcasts, Google, Yandex, Mave, Castbox, Overcast, Telegram (↓)

#подкаст #DataHeroes

Время правильных ответов
Задача: определить насколько процентов каждое животное старше среднего по группе.

Решение задачи:

SELECT kind,
name,
weight,
age,
ROUND((age / AVG(age) OVER (PARTITION BY kind) - 1) * 100) AS age_ratio
FROM animals

Результат работы запроса вы можете увидеть в табличке выше.

В комментах совершенно верные ответы, браво!
P.S. Правильных ответов может быть несколько, мы привели один из вариантов.

Параметры оконных функций: 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