🔥 А вы знаете разницу между range between и rows between?

Давайте проведем с вами небольшой код ревью! Рассмотрим два небольших запроса и сравним их результаты.

1️⃣ Запрос первый:

SELECT SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    ROWS UNBOUNDED PRECEDING
  ) AS cumulative_revenue
FROM revenue

2️⃣ Запрос второй:

SELECT SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    RANGE UNBOUNDED PRECEDING
  ) AS cumulative_revenue
FROM revenue

Что скажете? Одинаково ли они отработают?

Затрудняетесь с ответом? 🤔 Мы сейчас все расскажем!

Несомненно, вы знаете, что как ROWS, так и RANGE в SQL ограничивают строки внутри партиции оконных функций.

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

В то время как RANGE ограничивает строки «логически». Это означает, что он учитывает не только количество предыдущих/следующих строк, но также берет во внимание значения в других строках по сравнению с текущей строкой.

Показываем!

Пройдемся построчно по первому коду, использующему ROWS.

🟢 Берём доход (для текущей строки) + cumulative_revenue (из предыдущей строки) = cumulative_revenue (для текущей строки). И так для каждого shop в таблице revenue.

В случае, если у нас появятся «дубликаты» (shop и period будут иметь одинаковые значения, а revenue будет отличаться) ROWS просто не обратит на это внимания, он продолжит брать доход текущей строки, прибавит cumulative_revenue предыдущей и выдаст новую cumulative_revenue.

🔵 Вы наверное уже догадались в чем подвох RANGE? Он, в свою очередь, будет использовать все предыдущие строки, текущую строку, а еще учтет строки содержащие одинаковые shop и period.

И как же он с ними будет работать? Он обработает их вместе, сложив их revenue. То есть, доход (для текущей строки) + доход (для всех остальных строк с таким же shop и period) + cumulative_revenue (для предыдущей строки) = новый cumulative_revenue. Таким образом, мы получим единое значение совокупного дохода за период.

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

- - - - -

🎁 Присоединяйтесь к симулятору «Аналитик данных» со скидкой 50%, там мы подробно разбираем бизнес-кейсы с использованием SQL и Python → ссылка на Симулятор

#sql

🔥 Ускоряем цикл в Python в 33000 раз

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

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

lst = random.sample(range(-10000000, 10000000), 100000)

🟢 А теперь, воспользуемся тремя методами, и применим к списку встроенную функцию abs.

- For Loop

lst_pos = []
for i in lst:
    lst_pos.append(abs(i))

Время работы: 7.98 ms.

- List Comprehension

lst_pos = [abs(i) for i in lst]

Время работы: 5.07 ms.

- Map

lst_pos = map(abs, lst)

Время работы: 419 ns.

Феноменально, при работе со встроенными функциями, map не просто в лидерах, он в 12000 раз быстрее! Может остальные методы нам и вовсе не нужны?

🔵 Давайте проверим map на невстроенных функциях, например, возведем элементы в квадрат.

- For Loop затратит 23 ms.

- List Comprehension - 20.9 ms.

- Map (используем lambda):

map(lambda x: x**2, lst)
Время работы: 628 ns.

Это ли не чудо? Map быстрее в 33000 раз! Мы вам скажем, по секрету, что map вообще-то не очень жалует использование lambda (это несколько снижает скорость). А если бы мы преобразовали возвращаемый map объект в список, то затраченное время стало бы примерно таким же, как у list comprehension. Но! Map очень любит работать с готовыми функциями, в том числе с определенными пользователем. Поэтому напишем функцию pow2, которая принимает x и возводит его в квадрат - x * x.

- List Comprehension:

[pow2(x) for x in lst]
Время: 8.6 ms.

- Map

map(pow2, lst)
Время: 401 ns.

Никого уже, наверное, не удивляет, map опять быстрее, в 20000 раз. Причем обратите внимание, ускорилась работа list comprehension - 8.6 ms против 20.9 ms, хотя делали мы одно и то же, просто разными способами. Берем на заметку!

🟣 Последнее, что мы еще не проверили, это добавление условий. Допустим, в результирующем списке должны быть только те числа, квадраты которых кратны 8.

- For Loop отработает за 26.3 ms.

- List Comprehension за 25.8 ms.

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

filter(lambda x: x**2 % 8 == 0, lst)
И время: 542 ns.

Ну что же, можно забывать про list comprehension и циклы, зачем они вообще нужны, когда есть map и filter, которые всегда в тысячи раз быстрее. Да?

Нет! Наверняка, знакомые с функцией map, уже хотят подловить нас на обмане. А именно, map никогда не возвращает список, только map object, и это именно то, что отличает его от “конкурентов” и позволяет map оставаться впереди.

Да, ни в одном из наших примеров, мы не формировали из возвращаемого map object - список. Это существенно замедлило бы работу map, но даже если сделали бы - map все равно остался бы быстрее приблизительно в 1,5 - 2 раза (опять же, кроме случаев с использованием lambda). Но если вам так же, не нужны все вычисляемые значения сразу - выбирайте map, и тоже будете быстрее в 33000 раз!

- - - - -

🎁 А вы уже успели присоединиться к симулятору «Аналитик данных» со скидкой 50%? → ссылка на Симулятор

#python

🔥 Стандартное отклонение в Python считается неправильно?!

Кто замечал, что при вычислении стандартного отклонения в Pandas и NumPy, вы получаете разные числа? Наверняка никто 🙂 А на днях, мы получили такой вопрос от студента.

Давайте проверим, действительно ли это так, если да - как это возможно, и как же добиться одинакового результата.

Создадим датафрейм для наших экспериментов.

df = pd.DataFrame({'height' : [161, 156, 172], 'weight': [67, 65, 89]})

Сосредоточимся только на столбце weight и посмотрим стандартное отклонение в Pandas и в NumPy.

- Pandas

df.weight.std()

>> 13.316656236958787

- NumPy

np.std(df.weight)

>> 10.873004286866728

Мы получаем 13,31 и 10,87. Это действительно совершенно разные цифры, так почему же?

Различие кроется в том, что Pandas и NumPy используют разные формлы для расчета стандартного отклонения. NumPy по умолчанию использует стандартное отклонение генеральной совокупности (в знаменателе N). А Pandas использует стандартное отклонение выборки (в знаменателе N - 1).

Время погрузиться в статистику! Когда мы вычисляем стандартное отклонение, необходимо понимать работаем мы с генеральной совокупностью или же с выборкой. Если с генеральной совокупностью - тогда делим на N (как в NumPy), а если с выборкой - на N - 1 (как в Pandas). Это называется одной степенью свободы, мы вычитаем 1, чтобы получить несмещенную оценку.

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

В основном, в работе приходится иметь дело с выборками, поэтому в Pandas по умолчанию заложена формула для работы именно с ними (N-1). Значит, стандартное отклонение Pandas является правильным?

А если вы хотите использовать определенную библиотеку, вы можете использовать параметр ddof - Delta Degrees of Freedom - для управления степенями свободы как в Pandas, так и в NumPy.

Давайте вернемся к старому примеру, где мы получили σ = 13,31 с помощью Pandas и σ = 10,87 с помощью NumPy.

Мы можем изменить степень свободы в NumPy, чтобы получить несмещенную оценку, используя параметр ddof:

np.std(df.weight, ddof=1)

>> 13.316656236958787

И мы видим, что теперь результат совпадает со стандартным отклонением из Pandas.

Аналогично можно изменить вычисление стандартного отклонения Pandas, чтобы вовсе не использовать степени свободы:

df.weight.std(ddof=0)

>> 10.873004286866728

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

- - - - - -

😏 Раз уж заговорили про библиотеки Python, Вы хорошо знаете Numpy? Пройдите тест и узнаете 😉

#python #pandas #numpy

🔥 Задача с собеседования в e-commerce или про рекурсию в SQL

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

💡 Давайте разбираться. Типичный пример иерархичной структуры - таблица сотрудников, где сами сотрудники также являются чьими-то менеджерами. То есть каждый сотрудник подотчетен другому, второй - третьему и так до тех пор, пока не останется тот, который никому не отчитывается. Это будет сотрудник верхнего уровня, и его manager_id будет равен 0 или NULL.

Теперь предположим, что нам нужно найти всех людей, которыми прямо или косвенно управляет сотрудник с employee_id = 2. Как мы можем сделать это в одном запросе? Можно было бы использовать JOIN, но мы не знаем сколько раз его придется написать, поэтому JOIN нам тут не помощник. Но мы знаем, что поиск должен начаться с employee_id = 2 и с каждым разом искать подчиненных глубже и глубже, пока не зайдет в тупик. Звучит как рекурсия!

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

WITH RECURSIVE subordinates_of_2 AS (
    SELECT employee_id,
           manager_id,
           full_name
    FROM employees WHERE employee_id = 2
    UNION ALL
    SELECT e.employee_id,
           e.manager_id,
           e.full_name
    FROM employees e 
    JOIN subordinates_of_2 s2 ON e.manager_id = s2.employee_id
)
SELECT * FROM subordinates_of_2 WHERE employee_id <> 2;

Часть WITH ... называется общим табличным выражением (CTE), за ней следует ключевое слово RECURSIVE, которое сообщает PostgreSQL, что следующее выражение должно быть выполнено рекурсивно. Что это значит?

Чтобы выполнить запрос рекурсивно, мы должны сообщить ему три вещи:

- Базовое условие - это первая часть запроса, в которой мы выбираем employee_id = 2.
- Ключевое слово UNION - сообщает PostgreSQL продолжать выполнять объединение для всех промежуточных строк, сгенерированных в результате рекурсии.
- Рекурсивная часть самого запроса - в нашем случае это условие, которое сопоставляет manager_id с employee_id, сгенерированными в предыдущих вызовах.

🤯 Голова кипит? У нас тоже! На первый взгляд довольно сложно понять, но посмотрим пошагово как это работает. Запрос начинается с создания списка сотрудников с employee_id = 2, затем продолжает объединять данные до тех пор, пока не останется ни одного сотрудника, с непустым manager_id. Поскольку конечный результат также включает менеджера верхнего уровня, мы отфильтровали его, используя WHERE employee_id <> 2.

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

👨🏻‍🎓 А чтобы глубже разобраться в SQL, приходите в наш Симулятор SQL

#sql #interview_problems

🔥 Full и return retention. Шпаргалка.

Сегодня мы завершаем нашу серию шпаргалок по метрикам retention на full и return retention.

Full retention - дает понимание того, насколько хорошо продукт или услуга удерживают пользователей с течением времени. Причем не просто удерживают в n-day, а каждый день.

🎧 Возьмем, например, музыкальный стриминговый сервис.

На начало месяца насчитывалось 100 000 пользователей. В течении этого месяца 80 000 из них ежедневно пользовались продуктом. Чтобы рассчитать full retention мы возьмем активных пользователей на конец периода (80 000 в нашем случае) и сравним с количеством пользователей на начало периода (100 000 в нашем случае).

80 000/100 000 = 0,8

Итого: full retention 80%.

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

🛍 В качестве примера возьмем сайт по продаже одежды.

В прошлом у него было 100 000 клиентов, в течение месяца, для которого мы рассчитываем return retention, 5 000 вернулись и совершили покупку. Таким образом, взяв количество вернувшихся клиентов (5 000) и поделив на общее количество (100 000) получим return retention 5%.

5 000/100 000=0,05

В прошлой шпаргалке у нас была табличка с наглядной демонстрацией rolling retention, мы её обновили, добавив full и return (см. картинку под постом).

Таким образом, full retention - это постоянно остающиеся с нами клиенты. Например, человек покупающий ежемесячную подписку на журнал на протяжении пяти лет.

Return retention - возвращающиеся клиенты. Тот же пятилетний клиент, например, отменил подписку на месяц, а потом вернулся.

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

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

🔴 Будьте аккуратны при выборе метрик, периодов расчета и интерпретации результатов! Приходите на симулятор Аналитика Данных, чтобы стать незаменимым в команде любого продукта.

#продуктовые_метрики

🔥 Ускоряем работу в Pandas в 1000 раз

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

И сегодня мы ускорим работу Pandas в 1000 раз, внеся всего несколько корректировок в код.

Начнем, как всегда, с импортов и создадим датафрейм для наших экспериментов.

import pandas as pd
import numpy as np

def get_data(size = 10_000):
    df = pd.DataFrame()
    df['age'] = np.random.randint(0, 100, size)
    df['time_in_bed'] = np.random.randint(0, 9, size)
    df['pct_sleeping'] = np.random.rand(size)
    df['favorite_food'] = np.random.choice(['pizza', 'taco', 'ice-cream'], size)
    df['hate_food'] = np.random.choice(['broccoli', 'candy corn', 'eggs'], size)
    return df

Теперь мы хотим каждому человеку в соответствии с условиями - дать вознаграждение. Это будет наша функция reward_calc:

def reward_calc(row):
    if row['age'] >= 90:
        return row['favorite_food']
    if (row['time_in_bed'] > 5) & (row['pct_sleeping'] > 0.5):
        return row['favorite_food']
    return row['hate_food']

Рассмотрим 3 способа применить подобную логику к набору данных, пойдем от самого медленного к самому быстрому. Что бы вам пришло в первую очередь при такой формулировке задачи?

1️⃣ Наш первый кандидат ЦИКЛ

Запрашиваем новый датафрейм, итерируем по каждой строке и применяем reward_calc().

df = get_data()
for index, row in df.iterrows():
    df.loc[index, 'reward'] = reward_calc(row)

И время - 597 ms. Неплохо, можем ли мы лучше?


2️⃣ Проверим APPLY

Мы снова запросим датафрейм и применим к нему reward_calc() построчно. По сути, мы делаем то же самое, что и в цикле, но apply делает это эффективнее.

df = get_data()
df['reward'] = df.apply(reward_calc, axis = 1)

Итого - 107 ms. Это, конечно, не в миллион раз быстрее, чем цикл, а всего в 5,5. Но в больших датасетах, не как наш фиктивный на 10 000 строк, это будет крайне существенное ускорение.


3️⃣ И все-таки у нас есть третий кандидат - векторизация

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

((df['pct_sleeping'] > 0.5) &
(df['time_in_bed'] > 5)) |
(df['age'] > 90)

И возвращать серию булевых значений.

Теперь мы запрашиваем новый датафрейм, создаем reward, который будет равен hate_food, кроме случаев, где удовлетворены наши условия.

df['reward'] = df['hate_food']
df.loc[((df['pct_sleeping'] > 0.5) &
            (df['time_in_bed'] > 5)) | 
            (df['age'] > 90), 'reward'] = df['favorite_food']

И мы получаем 4.41 ms, и бóльшая часть этого времени — это вызов get_data(). Без неё код отработает за 567 µs.

Вот так мы смогли ускорить наш код в 1000 раз и потому, очень рекомендуем вам использовать векторизацию там, где это возможно. Ведь по мере того, как вы начнете работать со все бóльшими и бóльшими наборами данных, этот трюк может стать незаменимым!

- - - - - - - -

🏋️‍♀️ Потренируйтесь решать рабочие задачи с помощью Pandas

#python #pandas

🔥 А как вы ответите на элементарный вопрос с собеседования?

Элементарный вопрос с собеседования. Объясните разницу между WHERE и HAVING в SQL? Проверим совпадает ли ваш ответ с тем, чего ожидают рекрутеры?

🔹 Условие HAVING используется для фильтрации результатов запроса на основе агрегированных значений. Например, сгруппируем сотрудников по отделам, а потом выберем те, где сотрудников больше 10:

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 10;

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

SELECT department, COUNT(employee_id)
FROM employees
WHERE salary > 50000
GROUP BY department;

Без сомнений, и WHERE, и HAVING можно и нужно использовать вместе. Если до группировки можно отфильтровать неподходящие строки, то зачем их оставлять в HAVING?

SELECT department, COUNT(employee_id)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(employee_id) > 10;

✅ Также стоит заметить, что HAVING вполне может использоваться и без GROUP BY. Например, подобный запрос (без группировки) вернет нам одну строку - общее количество сотрудников, и только в том случае, если оно больше 10:

SELECT COUNT(employee_id)
FROM employees
HAVING COUNT(employee_id) > 10;

❓ А теперь вопрос! Отработает ли данный код?

DELETE FROM table
HAVING COUNT(value) > 10;

- - - - - - -

🧑‍💻 Найдите среднемесячное количество продаж, используя WHERE и HAVING

#sql #interview_problems

🔥 Что такое процентиль и как его посчитать в Numpy

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

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

Один из способов это сделать - использовать процентили.

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

Следите за руками, 25-й процентиль равен 7 - это значит, что 25% значений выборки будут меньше или равны 7. 100–й процентиль - это такое значение, что 100% выборки будут меньше или равны ему. Что это - догадываетесь? Это то же, что и максимальное значение. И если вам кажется, что 50-й процентиль очень напоминает медиану, то вы абсолютно правы!

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

🔵 Итак, рассчитать процентиль нам позволяет numpy. Синтаксис следующий:

numpy.percentile(массив, для которого вычисляем процентиль; искомый процентиль или последовательность процентилей)

Например:

import numpy as np

data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
percentiles = np.percentile(data, [25, 50, 75])

print(percentiles)

Вывод: [3.25, 5.5, 7.75]


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

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

Пройдите тест по библиотеке Numpy, чтобы понять как хорошо вы ее знаете 😏

#python #numpy

🔥 Как операторы SQL могут подпортить жизнь

Все мы знакомы с предикатом сравнения != или <>. Это необходимый в работе, и довольно понятный оператор.

А сталкивались ли Вы когда-нибудь с IS DISTINCT FROM? Это еще один оператор, который также проверяет неравенство одного значения другому.

🔴 На днях один из наших студентов столкнулся проблемой: запросы IS DISTINCT FROM 1 и !=1 дали разные результаты.

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

А вы знаете в чем их отличие?

🟢 Давайте разбираться!

Допустим есть таблица компаний со столбцом company_id. Так получилось, что некоторым компаниям еще не присвоили id и пока их значения NULL. Она выглядит так:

| company_id |
|------------|
| 1          |
| 2          |
| 3          |
| 1          |
| NULL       |

⏩ Написав запрос company_id != 1, получим:

| company_id |
|------------|
| 2          |
| 3          |

Это именно то, чего мы и ожидали. Все компании, id которых не равны единице.

⏩ А что же с is distinct from? Проверяем, company_id IS DISTINCT FROM 1:

| company_id |
|------------|
| 2          |
| 3          |
| NULL       |

Теперь нули тоже оказались учтены. Все потому, что IS DISTINCT FROM рассматривает NULL как сопоставимое значение. Получается, что 1 IS DISTINCT FROM NULL вернет True, а NULL IS DISTINCT FROM NULL вернет False.

Аналогично, IS NOT DISTINCT FROM проверяет равенство значений, как и оператор =, но, в отличии от равенства, NULL IS NOT DISTINCT FROM NULL вернет True.

⏩ А давайте попробуем получить результат, аналогичный IS NOT DISTINCT FROM, используя старый добрый COALESCE. Итак, COALESCE(company_id, -1) != 1:

| company_id |
|------------|
| 2          |
| 3          |
| NULL       |

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

_ _ _ _ _ _ _

💥 Узнайте как COALESCE может помочь при решении рабочей задачи в нашей статье!

#sql

🔥 Когда не обойтись без CTE и Subqueries?

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

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

Давайте разберёмся что к чему, чтобы не пихать везде одинаковые конструкции.

🔵 Подзапрос, как следует из названия, является запросом внутри запроса. Он возвращает что-то вроде временной таблицы, и после выполнения она никуда не будет сохранена. То есть будет вычисляться каждый раз заново.

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

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

Начнем с CTE:

→ Мы уже упомянули, что CTE может быть «многоразовым», то есть в запросе Вы можете неоднократно ссылаться на него, просто указав имя (не копируя весь код, как в случае с подзапросом). Более того, подзапрос не может ссылаться на другие подзапросы, а CTE позволяет такую операцию.

→ CTE могут быть рекурсивными! Об этом можете почитать в одном из наших прошлых постов.

→ Читаемость! На наш взгляд, это не всегда означает короткий запрос. Но означает, что запрос должен быть понятен как вам, так и другим. И CTE, как раз способствуют аккуратности и читабельности кода, устраняя повторяющиеся блоки и отделяя логику запроса. Это еще и очень помогает в поиске багов 🧐

Это что касается CTE и случаев, когда они необходимы.

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

→ В предложении WHERE можно использовать только подзапросы! А также, он может возвращать одно значение, что может быть просто необходимо при сравнении.

→ Подзапросом вы можете создать новый столбец:

SELECT DISTINCT  contest_id, 
    (SELECT COUNT(length)     
    FROM jump AS inside_jump     
    WHERE inside_jump.contest_id = outside_jump.contest_id       
         AND inside_jump.length > 600   
     ) AS longer_jumps, 
    (SELECT COUNT(length)     
    FROM jump AS inside_jump     
    WHERE inside_jump.contest_id = outside_jump.contest_id       
         AND inside_jump.length <= 600   
    ) AS shorter_jumps 
FROM jump AS outside_jump;

→ И заодно, на примере вышеприведённого запроса покажем коррелированные подзапросы. Обратите внимание, мы сослались на таблицу в запросе внутри подзапроса. Мы даже сослались на значение текущей строки из этой таблицы. Это как раз называется "коррелированным подзапросом" и позволяет использовать значения из внешнего запроса внутри подзапроса. Очень удобная техника!

Итак, что лучше? Ответ - ни одно, ни другое! Как подзапросы, так и CTE имеют свои плюсы и минусы. Главное, быть внимательными и оценивать свой запрос: что нужно именно здесь. Зная обе концепции, вы можете сделать правильный выбор в сторону CTE или подзапроса и не нагромоздить пятиэтажную вложенность там, где в ней нет необходимости!

🎁 Успейте записаться на Симулятор «Аналитик данных» со скидкой 25% по промокоду stud25.

#sql

🔥 А ваш churn rate растет или падает?!

Внимание всем причастным к бизнесу! Следите ли вы за churn rate?

Churn rate - это показатель оттока клиентов за некоторый период времени. Обычно он выражается в процентах от общего числа юзеров на начало периода. Если компания имела 100 клиентов и в течение месяца пятеро ушли (отменили подписку, отказались от продуктов и тд), churn rate выходит 5%.

Давайте посчитаем churn rate в SQL:

WITH customer_start AS (
    SELECT COUNT(*) as 
           start_customers
    FROM customers
    WHERE signup_date < 
           '2021-01-01'
),
cancelled_customers AS (
    SELECT COUNT(*) as
           cancelled
    FROM customers
    WHERE cancel_date BETWEEN 
    '2021-01-01' AND 
    '2021-01-31'
)
SELECT (cc.cancelled / cs.start_customers) * 100 as 
        churn_rate
FROM customer_start cs, cancelled_customers cc

Все очень легко, но есть несколько вещей, которые следует знать при работе с churn rate.

1️⃣ Мы уже много раз упоминали в наших шпаргалках по retention и скажем еще раз. Значения различных метрик будут отличаться по отраслям/специфике и churn rate - не исключение. Не надо сравнивать churn rate Spotify и Госуслуг.

2️⃣ На уровень оттока может влиять множество факторов (политика ценообразования, обслуживание, конкуренция, для оффлайн бизнесов даже переезд клиента может быть фактором). Это важно понимать, чтобы суметь вовремя изменить ситуацию. Вернуть уходящих/ушедших пользователей легче, чем привлечь новых!

3️⃣ К сожалению, churn rate не расскажет нам о том, какие юзеры ушли и почему, а также не учтет тех, кто может уйти в скором времени. Churn rate - это сухие факты. А для всего остального есть, например, показатели удовлетворенности клиентов, индекс потребительской лояльности и RFM анализ.

Следить за churn rate необходимо, чтобы в один прекрасный день не обнаружить «спонтанный» отток клиентов. Так что, держите руку на пульсе и повышайте лояльность пользователей. Ведь если вы предоставляете исключительный сервис, и клиент лоялен, то он останется с вами надолго, а churn rate не будет расти!

_ _ _ _ _ _ _

🟢 На нашем Симуляторе «Аналитик данных» одна из первых глав - Продуктовые метрики, где вы научитесь считать и анализировать метрики привлечения, вовлеченности, производительности и другие!

#продуктовые_метрики

🔥 Попрощаемся с длинным форматированием строк в Python

Аналитики, разработчики и все, кто работает с Python, вы замечали что пишут про f-строки практически в каждом источнике? Если нет, то вот цитата «f-строки - новейший и наиболее лаконичный способ форматирования». Согласны? Хотелось бы немного уточнить формулировки.

Новейший? F-строки были впервые представлены в PEP 498, в 2015 году! Уже почти 10 лет назад. 🤔 Даже версия python - 3.6, в которой они были впервые представлены, более не поддерживается командой разработчиков. Так что с новейшим мы бы поспорили, но если вы еще не оценили весь функционал f-строк, скорее читайте пост, ведь знать это нужно было еще вчера.

🔴 Отдадим дань истории

До появления f-строк в Python существовало два популярных способа форматирования строк: %-formatting и format(). %-форматирование считается наименее читаемым по сравнению с другими методами, и его использование постепенно сходит на нет (остаются только самые стойкие - например, некоторые коннекторы к БД все еще используют только %-formatting).

Format() был введен как более читаемая альтернатива, но, порой, код может стать таким длинным (особенно при форматировании сложных строк), что читабельность этого метода окажется под вопросом.

🔵 Наконец перейдем к самому удобному способу!

Суть F-строк в том, что для форматирования достаточно просто встроить выражение в строку. Самый простой пример:

name = "ITResume"
age = 2
f"Hello, my name is {name} and I am {age} years old."

>> Hello, my name is ITResume and I am 2 years old.

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

f"{(lambda x: x*2)(3)}"

>> 6

Еще, с помощью f-строк, мы можем задать параметры форматирования. Например, количество знаков после запятой:

pi = 3.14159265
f"The value of pi is approximately {pi:.2f}."

>> The value of pi is approximately 3.14.

Или даже так:

import datetime

date = datetime.date(1991,10,12)
f"{date} was on a {date:%A}"

>> 1991-10-12 was on a Saturday

Разве этого недостаточно для звания самого удобного инструмента форматирования? Если нет, то для самых искушенных - использование if внутри f-строки:

my_str = 'hello'
f"Result: {my_str.upper() if len(my_str) > 1 
else my_str.capitalize()}"

>> Result: HELLO

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

_ _ _ _ _ _ _

🧠 Попробуйте решить задачу с помощью f-строк

#python

🔥 NULL в SQL больше 10 или меньше?

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

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

Уже знаете как это сделать?

Это именно та ситуация, где вступают в игру NULLS FIRST и NULLS LAST.

🔸 В SQL значение NULL - это специальный маркер, используемый для указания того, что данные отсутствуют. По умолчанию строки с нулевыми значениями сортируются так, будто они больше любого ненулевого значения.

То есть по умолчанию для порядка АSC (по возрастанию) используется NULLS LAST - нулевые значения будут стоять последними, а для DESC - NULLS FIRST - будут первыми. Однако могут быть случаи, как раз как наш, когда необходимо отсортировать нулевые значения по-другому: при сортировке по возрастанию показать нули первыми.

Тут нам и пригодится NULLS FIRST! Запишем запрос:

SELECT name, age, country
FROM athletes
ORDER BY country, age NULLS FIRST;

Этот запрос отсортирует спортсменов сначала по стране, а затем по возрасту, причем спортсмены с неуказанным возрастом будут отображаться первыми. И для примера, попробуем использовать NULLS LAST для сортировки спортсменов с отсутствующим возрастом - последними, а не первыми:

SELECT name, age, country
FROM athletes
ORDER BY country, age NULLS LAST;

🔹 Таким образом, использование параметров сортировки NULLS FIRST и NULLS LAST может дать вам больше контроля над тем, как сортируются значения NULL, что облегчит поиск тенденций и отсутствующих данных. А также сделает поведение вашего кода более очевидным как для вас, так и для других.

❗️ Попробуйте включать их в свои запросы и убедитесь в том, как они упростят работу со значениями NULL!

#sql

🔥 3 способа вывести первую строку в GROUP BY

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

Имеется таблица товаров products, в ней - id товара, его название, категория и цена. (см. таблицу во вложении)

Задача: собрать сливки - самые дорогие продукты в каждой категории.

🟣 Что приходит первое в голову? Взять MAX(price)? Группируем по категории и берем максимальную цену - все гениальное просто!

SELECT category,
       MAX(price) as price
FROM products
GROUP BY category;

| category    | price |
|-------------|-------|
| Media       | 20    |
| Stationery  | 5     |
| Electronics | 1500  |
| Literature  | 20    |

А если нужно еще показать ID товара? Так просто уже не получится. Но можем воспользоваться оконной функцией ROW_NUMBER. Каждую категорию отсортируем по убыванию цены, присвоим каждой строке в категории номер (1, 2, 3 и т.д.) и возьмем только строку с 1 номером. Элегантно? И главное работает!

SELECT category, id, price
FROM (
  SELECT id, category, price,
         ROW_NUMBER() OVER 
         (PARTITION BY category 
         ORDER BY price DESC) 
         as rn
  FROM products
) x
WHERE rn = 1;

| category    | id | price |
|-------------|----|-------|
| Electronics | 7  | 1500  |
| Literature  | 3  | 20    |
| Media       | 9  | 20    |
| Stationery  | 6  | 5     |

Но есть еще несколько неочевидных способов!

🟢 Альтернативным вариантом будет array_agg - эта функция вернет все ненулевые значения в столбце в виде массива. А указав [1] мы возьмем только первое значение. Неочевидно и оригинально.

SELECT (array_agg(id ORDER BY 
       price DESC))[1] AS id,
       category,
       max(price) AS price
FROM   products 
GROUP BY category;

🔴 И вишенка на торте - DISTINCT ON. Его используют для возврата только первой строки в каждой группе, определенной с помощью ORDER BY. Мы указываем в ORDER BY сортировку по категории и по убыванию цены, таким образом первой строкой для каждой категории (DISTINCT ON (category)) будет строка с самой высокой ценой в категории. Так мы получили самый короткий запрос!

SELECT DISTINCT ON (category)
       id, category, price
FROM   products
ORDER BY category, price DESC;

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

_ _ _ _ _ _ _

🔵 Приходите в Симулятор «Аналитик данных», чтобы профессионально овладеть SQL, Python, Продуктовыми метриками и другими инструментами аналитика!

#sql

Таблица products

🔥 Сокращаем память в 8 раз в Python

По сравнению с другими языками программирования - писать на Python легко и удобно, он универсален. Однако скорость выполнения скрипта или занимаемая память - те вещи, за которые всем нам еще предстоит побороться. Редко бывает, что первый пришедший в голову вариант - самый оптимальный. Поэтому сегодня предлагаем попробовать себя в роли оптимизатора.

🔹 Вот, что мы имеем: скрипт генерирует случайную машину (её марку и цвет) и записывает в список. На выходе мы получаем список из одного миллиона машин.

car_names = ['Toyota', 'Honda', 'Renault', 'Audi', 'Nissan', 'Suzuki']

colors = ['Black', 'Blue', 'Red', 'Yellow', 'White']

def car_list(cnt_cars):
   all_cars = []
   for i in range(cnt_cars):
       car = {
           'id': i,
           'name': random.choice(car_names),
           'color': random.choice(colors)
       }
       all_cars.append(car)
   return all_cars

cars = car_list(1000000)

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

Вернемся к нашему скрипту. Он выполняется за 1,5 секунды в среднем, но это не самое страшное в нем. До генерации списка он использует 8 MB памяти, а после - 334 MB. Конечно, чего мы ожидали от списка в миллион элементов. Но если мы просто сгенерируем его и запишем в переменную - это никуда не годится. Столько используемой памяти может быть непростительной ошибкой, тем более, что мы знаем как это исправить!

car_names = ['Toyota', 'Honda', 'Renault', 'Audi', 'Nissan', 'Suzuki']

colors = ['Black', 'Blue', 'Red', 'Yellow', 'White']

def car_list_gen(cnt_cars):
    for i in range(cnt_cars):
        car = {
            'id':i,
            'name':random.choice(car_names),
            'color':random.choice(colors)
        }
        yield car

for car in car_list_gen(1000000):
    pass

И, внимание! Немного изменив тело функции, мы сократили время работы до 1,37 секунды, но это не самое интересное. Мы сократили используемую память: с 334 MB до 40 MB. А что мы сделали? Вместо return в функции, использовали yield.

🔸 Справка: yield в Python используется для создания генераторов. Генератор - тип коллекции, которая генерирует элементы на ходу и может быть итерирована только один раз.

Конечно, эта уловка сработает, если вам не нужен доступ ко всем элементам списка сразу.

А вообще, генераторы - хороший способ повысить производительность. Главное - понимать когда и где их использовать.

⁉️ Кстати, сделать отдельный пост о том, как работает yield и в каких задачах его лучше использовать?

#python

🔥 Красивое сравнение нескольких столбцов

У нас имеется таблица students, она содержит информацию о студентах (очевидно). В столбце level указан академический уровень учащегося (Senior, Junior, Freshman и т.д.), subject - предмет, который студент изучает (Math, Physics и другие), а grade - текущая оценка студента по этому предмету (A, B, C и т.д.).

Нам нужно достать всех студентов, которые соответствуют следующим критериям:

- Уровень - 'Senior'
- Изучают 'Math'
- Имеют оценку 'А'

Но подождите, это еще не все! Мы хотим написать как можно более короткий и читаемый запрос. Попробуем? 💡

SELECT level, subject, grade
FROM students
WHERE level = 'Senior'
  AND subject = 'Math'
  AND grade = 'A';

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

SELECT level, subject, grade
FROM students
WHERE (level, subject, grade) = ('Senior', 'Math', 'A');

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

Есть пример посложнее! Что если нам понадобятся seniorы, которые лучшие либо в математике, либо в физике?

SELECT level, subject, grade
FROM students
WHERE level = 'Senior'
AND (
    (subject = 'Math' AND 
     grade = 'A')
 OR
    (subject = 'Physics' AND 
     grade = 'A'));

Такой громоздкий запрос может быть сведен к более короткому:

SELECT level, subject, grade
FROM students
WHERE level = 'Senior' 
  AND (subject, grade) 
   IN (('Math', 'A'), 
       ('Physics', 'A'));

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

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

#sql

🔥 Необычная «связка» функций для поиска похожих слов в SQL

Один из основных функционалов SQL, которому учат на любых курсах - поиск по шаблону, например, операторы LIKE или SIMILAR TO.

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

Если бы мы захотели решить такую задачку самостоятельно, то нам пришлось бы изрядно потрудиться. К счастью, у нас есть функция SIMILARITY и расширение trigram. Это расширение использует триграммы - группы из трех букв. Например, набор триграмм в строке «cat» - « c», « ca», «cat» и «at».

Давайте сразу посмотрим эту связку в деле. Табличка documents содержит совершенно разные строки (см. табличку 1).

Включим расширение trigram в PostgreSQL:

CREATE EXTENSION pg_trgm;

И попробуем с помощью функции SIMILARITY найти слово ellefant. Взгляните на таблицу еще раз и убедитесь, что такого слова нет. Но есть множество других очень и очень похожих слов, и trigram может идентифицировать их как совпадающие.

SELECT title, 
       similarity('ellefant', title) AS similarity_score
FROM documents

В столбце similarity_score (см. табличку 2) мы видим числа, описывающие насколько близки два аргумента. Диапазон - от нуля (это значит, что две строки полностью различны) до единицы (две строки идентичны). Что же произошло и как это понимать?

🔵 Наше искомое слово было поделено на триграммы - « e», « el», «ell», «lle», «lef», «efa» и тд, строки в столбце title также поделили на триграммы. И теперь, функция SIMILARITY искала самые похожие триграммы слова ellefant среди триграмм строки title, учитывая порядок. А когда нашла, разделила количество совпадающих триграмм на общее количество триграмм в строке title.

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

😏 А вы знали про такие возможности SQL?!

#sql