🔥 Полезная команда для анализа данных!

Сегодня хотим обсудить с вами удивительную тему в PostgreSQL, которая поможет вам ускорить и улучшить свои аналитические запросы - материализованные представления (Materialized Views).

🟢 Что такое материализованные представления?

Это специальный тип представлений (view) в PostgreSQL, которые сохраняют результаты выполнения запроса как физическую таблицу в базе данных. При этом данные материализованного представления обновляются только по мере необходимости, когда базовые таблицы изменяются.

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

Это основное отличие от простого view, которое обращается к источнику каждый раз, когда вы его вызываете. То есть, прежде, чем получить результат, вам придется немного подождать.

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

1️⃣ Во-первых, это ускорит запросы: Поскольку данные уже предварительно вычислены и сохранены в таблице, выполнение запросов становится намного быстрее.

2️⃣ Во-вторых, снизит нагрузки на базу данных: Представления обновляются только по мере необходимости, что позволяет снизить количество обращений к базовым таблицам и оптимизировать работу с данными.

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


🔵 Как создать материализованное представление:

Для создания материализованного представления вам потребуется выполнить следующие шаги:

1️⃣ Написать запрос, который будет формировать данные для представления.

2️⃣ Создать материализованное представление с помощью команды CREATE MATERIALIZED VIEW.

3️⃣ Определить опции материализованного представления (столбцы, условия фильтрации и сортировки данных, а также права доступа для пользователей или ролей).

4️⃣ Указать опцию REFRESH MATERIALIZED VIEW для обновления данных представления по мере необходимости. Вы можете настроить автоматическое обновление представления или выполнять его вручную.

🎯 Пример

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

-- Создание материализованного представления
CREATE MATERIALIZED VIEW 
         monthly_sales AS
SELECT DATE_TRUNC('month', 
         order_date) AS month,
       SUM(sales_amount) AS 
         total_sales
FROM sales_data
GROUP BY month;

-- Обновление данных в представлении
REFRESH MATERIALIZED VIEW monthly_sales;

🔎 Использование материализованных представлений в PostgreSQL - это мощный инструмент для оптимизации аналитических запросов и повышения производительности вашей базы данных. Попробуйте применить эту фишку в своих проектах и убедитесь как улучшится скорость выполнения запросов и облегчится ваша ежедневная рутина! 📈

💥 В нашем Симуляторе «Аналитик данных» мы раскрываем еще больше возможностей SQL, Python и других инструментов аналитика!

#sql

🔥 Pandas vs SQL

Мы уже написали множество постов для тех, кто работает с Pandas, еще больше для тех, кто работает с SQL. А что, если среди нас все еще остались те, кто пользуется только одним из этих инструментов? 🤔

💡По большому счету, можно сказать, что и Pandas, и SQL, позволяют нам манипулировать данными. Кто-то делает это быстрее, кто-то медленнее - везде свои преимущества. Но если вдруг вы, эксперты в Pandas, и до сих пор не работаете с SQL, или профессионально работаете с SQL, но вам так и не поддается Pandas - этот пост для вас!

🔎 В карточках под постом мы представили некоторые операции в Pandas и их соответствующие аналоги в SQL 👇

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

#sql #pandas

🔥 Разбираем что такое CPA!

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

🤔 CPA vs CAC

Многие компании путают эти два показателя и используют их взаимозаменяемо для обозначения маркетинговых затрат, связанных с привлечением новых клиентов. Тем не менее, CPA и CAC технически отличаются:

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

2️⃣ CAC же относится к маркетинговым и другим затратам на привлечение клиентов в больших масштабах. Другие затраты - это стоимость используемых инструментов маркетинга, затраты поставщика рекламы, заработную плату команды и расходы агентства.

⚙️ Как рассчитать CPA

Чтобы рассчитать CPA, просто разделите общую стоимость на количество новых клиентов, привлеченных по тому же каналу/кампании.

($) общая сумма, потраченная на привлечение новых клиентов по определенному каналу или кампании 
/
(#) новые клиенты, привлеченные по тому же каналу или кампании
=
($) Стоимость приобретения

Пример

На маркетинговое сотрудничество с инфлюенсерами было потрачено 10000. Привлеченные клиенты - 1000.

CPA = 10000 / 1000 = 10

🟢 Преимущества

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

🔴 Недостатки

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

📌 А какой CPA хороший?

Это один из тех вопросов, на который нельзя ответить конкретным числом, как и в других метриках. Чтобы понять, что считается хорошим CPA, следует учитывать многие факторы:

1. Ваша отрасль: что вы продаете? Еда, мода, фитнес-продукты, электроника и т. д.
2. CLV: Сколько стоит этот клиент после приобретения и сколько прибыли он приносит?
3. Частота повторных покупок: сколько покупок совершают существующие клиенты по сравнению с новыми.
4. Средняя продолжительность жизни клиента.
5. CR: Сколько ваших клиентов в конечном итоге отменят подписку или просто перестанут покупать у вас?

💎 Бенчмарки

Cредний CPA составляет 59,18 - 60,76 долларов. Например, для приложений дейтинга CPA составляют 6.91 доллар, а для отрасли права и медицины - более 126 долларов.

Лучший способ определить, ниже ли нормального ваш CPA, выше или просто средний, - это сравнить его с внутренним эталоном - вашим LTV.

❗️Дополнительно

Иметь очень низкий CPA не всегда хорошо. Это может означать, что вы не инвестируете достаточно, чтобы расти. Лучше установите некоторую цель CPA в контексте вашего соотношения LTV:CAC, чтобы вы достигли такого CPA, который достаточно низок, чтобы расти прибыльно, но не так низок, чтобы расти недостаточно быстро.

👉 Приходите в наш Симулятор «Аналитик данных», чтобы понимать, как работать с бизнес-метриками, как их считать с помощью разных инструментов (Python, SQL) и как их правильно анализировать!

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

🔥 Экономим память при работе с Pandas

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

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

🟢 Пример

Создадим DataFrame с помощью Pandas и NumPy, используя случайно сгенерированные целые значения от 1 до 100:

import pandas as pd
import numpy as np

df = pd.DataFrame(
     np.random.randint(1, 100, 
             size=(10**7, 2)), 
     columns=["A", "B"])

Теперь, чтобы узнать, сколько места занимает столбец 'A', мы можем использовать метод info() для этого столбца:

df['A'].info()
# dtypes: int64(1)
# memory usage: 76.3 MB

Как видим, тип данных этого столбца - int64, и занимает он - 76.3 МБ памяти. Это очень прилично!

Мы точно должны что-то с этим сделать! Вообще, для диапазона от 1 до 100, нам вполне будет достаточно типа int8. Так и сделаем, преобразуем столбец:

df['A'] = df['A'].astype(np.int8)

После преобразования давайте снова проверим информацию о столбце 'A':

df['A'].info()
# dtypes: int8(1)
# memory usage: 9.5 MB

Теперь тип данных столбца 'A' - int8, и занимает он всего 9.5 МБ памяти. Это значительное сокращение используемой памяти по сравнению с исходным значением.

🔵 Видите, простое преобразование типов данных может значительно сократить использование памяти. Это особенно полезно, когда мы имеем дело с огромными объемами данных и хотим оптимизировать производительность.

#pandas

🔥 Когда пригодится SELF JOIN?

Как часто вы используете соединение таблицы с самой собой? Это не часто встречающаяся практика. Но на самом деле self join помогает решить удивительно большой список задач. Давайте посмотрим!

1️⃣ Поиск пробелов

Имеется таблица foo (см. Карточку 1)

Иногда необходимо найти места, так называемых пробелов в значениях. В нашем случае - число, с которого этот разрыв начинается. Например, у нас есть строки: 1, 4, 9 - разрывы здесь это 2-3, 5-8. И нам нужно получить первое число каждого разрыва, то есть 2 и 5. И для этого мы можем соединить таблицу саму с собой.


2️⃣ Поиск дублирующихся строк

Допустим, таблица foo теперь содержит имена, фамилии и почту клиентов. (см. Карточку 2)

Для поиска дублирующихся строк на основе конкретного столбца, например email, можно использовать тот же подход:


3️⃣ Ранжирование

Имеем таблицу player. (см. Карточку 3)

Чтобы назначить определенное место каждому спортсмену мы снова соединим таблицу саму с собой.


💥 Есть еще множество необычных вещей, на которые способен self join, о которых мы вам еще расскажем. Но сначала поделитесь, приходилось ли вам использовать подобные конструкции в своей работе? Может быть у вас есть свои лайфхаки? Пишите в комментариях!

#sql

🔍 Целостность данных при merge в Pandas

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

Но есть решение! У метода merge() есть параметр validate, который проверяет, имеет ли merge определенный тип.

🔹 Объединение с Уникальными Ключами (One-to-One Merge)

При таком объединении ключи должны быть уникальными в обеих таблицах. Давайте посмотрим на пример:

import pandas as pd

df1 = pd.DataFrame({
      'colA': ['A', 'B', 'C'], 
      'colB': [1, 2, 3]})

df2 = pd.DataFrame({
      'colA': ['A', 'B', 'C'], 
      'colC': [4, 5, 6]})
# ключи уникальны

result = pd.merge(df1, df2, 
         on='colA', 
         validate='one_to_one')
print(result)

Все получилось и мы не получим никакой ошибки:

  colA  colB  colC
0    A     1     4
1    B     2     5
2    C     3     6

🔹 Обработка Повторяющихся Ключей

Если в одной из таблиц есть повторяющиеся ключи, merge выдаст ошибку:

import pandas as pd

df1 = pd.DataFrame({
      'colA': ['A', 'B', 'C'], 
      'colB': [1, 2, 3]})

df2 = pd.DataFrame({
      'colA': ['A', 'B', 'B'], 
      'colC': [4, 5, 6]})
# повторяющиеся ключи: 
'colA': ['A', 'B', 'C'] и 
'colA': ['A', 'B', 'B']

try:
   result = pd.merge(df1, df2, 
        on = 'colA', 
        validate='one_to_one')
except pd.errors.MergeError as e:
   print(f"Ошибка: {e}")

Результат:

Ошибка объединения: Merge keys are not unique in right dataset; not a one-to-one merge

🔹 Какие еще есть объединения?

- one_to_one: Ключи должны быть уникальными в обоих датафреймах
- one_to_many: Ключи должны быть уникальными в левом датафрейме.
- many_to_one: Ключи должны быть уникальными в правом датафрейме.
- many_to_many: Ключи могут быть уникальными, а могут и не быть в обоих датафреймах

И Pandas выдаст ошибку слияния, если merge не соответствует указанному типу.

✅ Это поможет вам предотвратить ошибки, которые часто могут остаться незамеченными.

#python #pandas

🔥 Знакомимся с новым помощником Pandas

Pandas - самая популярная библиотека для анализа данных! Но не всем студентам (по опыту) эта библиотека дается легко. Поэтому хотим познакомить вас с новым помощником - Dovpanda!

Часто бывает, что мы используем неоптимизированные методы обработки, что снижает производительность кода и может привести к незамеченным ошибкам. Так что показываем вам в деле ассистента Dovpanda, который может давать подсказки и направления при работе с Pandas.

Он работает как компаньон, давая предложения/предупреждения о ваших шагах по обработке данных. Давайте посмотрим!

📥 Как установить?

Просто выполните следующую команду:

pip install dovpanda

import pandas as pd
import dovpanda

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

🟣 Что может Dovpanda

Пример 1

df = pd.DataFrame([[1,2],[3,4]], columns = ["A","B"])

def apply_func(row):
    pass

iter_df = df.iterrows()

# df.iterrows is not recommended. 
# Essentially it is very similar to iterating the rows of the frames in a loop. 
# In the majority of cases, there are better alternatives that utilize pandas' vector operation

Пример 2

merged_df = pd.concat((df, df))

# All dataframes have the same columns and same number of rows. 
# Pay attention, your axis is 0 which concatenates vertically
# After concatenation you have duplicated indices - pay attention

Пример 3

pd.read_csv("../some_dataset.csv")

# Dataframe has 1000 rows. 
# Column Company_Name has only 15 values which suggests it's a categorical feature.
# After df is created, Consider converting it to categorical by using df['Company_Name'] = df['Company_Name'].astype('category')

Это лишь малая часть, еще больше подсказок можете посмотреть под постом.

🟢 Зная эти рекомендации, мы можем сделать код более оптимизированным и чистым. Успехов! 🎉

💥 Решаем необычную задачку по SQL

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

🟢 Условие

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

Например, если период подписки user1 совпадает с периодом подписки любого другого пользователя, запрос должен возвращать значение True для user1.

Пример входной таблицы и результата запроса смотрите на карточке 1 под постом

🔵 Решение

На первый взгляд понятно, что мы должны сравнить каждую подписку с каждой другой. Давайте рассмотрим даты начала и конца подписки UserA как startA и endA, и аналогично для UserB - startB и endB.

Тогда, если startA ≤ endB и endA ≥ startB, то мы можем сказать, что два диапазона дат перекрываются. Давайте возьмем два примера - сравним U1 и U2:

- Начало = 2020-01-01
- Конец = 2020-01-31
- Начало = 2020-01-16
- Конец = 2020-01-26

Здесь мы видим, что startA(2020-01-01) меньше, чем endB(2020-01-26), и аналогично, endA(2020-01-31) больше, чем startB(2020-01-16), и, следовательно, можно сделать вывод, что даты перекрывают друг друга. Аналогично, если сравнить U1 и U4 (из таблицы), условия не выполнятся и вернется значение false.

Алгоритм мы уже подобрали, но есть несколько нюансов:

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

SELECT *
FROM subnoscriptions AS s1
LEFT JOIN subnoscriptions AS s2
   ON s1.user_id != s2.user_id
      AND s1.start <= s2.end
      AND s1.end >= s2.start

Результат запроса смотрите на карточке 2 под постом 😉

Видим, что для каждого пользователя существует другой пользователь в случае, если даты совпадают. Для user1 есть 2 строки, указывающие на то, что он совпадает с 2 пользователями. А для user4 соответствующий id равен null, что указывает на то, что он не совпадает ни с каким другим пользователем.

Теперь, объединив все это вместе, мы можем сгруппировать по полю s1.user_id и просто проверить, существует ли какое-либо значение true для пользователя, где s2.user_id НЕ равен NULL.

SELECT s1.user_id, 
   (CASE WHEN s2.user_id 
         IS NOT NULL THEN 1 
         ELSE 0 END) AS overlap
FROM subnoscriptions AS s1
LEFT JOIN subnoscriptions AS s2
   ON s1.user_id != s2.user_id
      AND s1.start <= s2.end
      AND s1.end >= s2.start
GROUP BY s1.user_id

Мы использовали CASE чтобы присвоить 1 и 0 в зависимости от значения s2.user_id для данного пользователя. Конечный результат смотрите на карточке 3 под постом.

📚 Интересная задача, согласитесь? Не часто встречаются соединения по неравенству. Вернее, use кейс встретить можно часто, но не всегда вспоминается, что так тоже можно! Если вдруг вы забыли о такой крутой опции - приглашаем в наш недавний пост про Non-Equi JOIN!

🔥 Супер-удобная функция Matplotlib

Это, должно быть, самая крутая вещь, которую мы когда-либо узнавали о Matplotlib.

🤔 Обычно, для создания графиков мы используем метод plt.subplots(). Но иногда это превращается в довольно громоздкие конструкции. А еще:

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

💡 Вместо этого мы можем использовать метод plt.subplot_mosaic(). Так, мы сможем создать график и расположить его как угодно, определив структуру графика простой строкой.

Например, расположение строк:

- AB
- AC

Создаст три подграфика, в которых:

- подграфик A будет занимать весь первый столбец
- подграфик B будет занимать верхнюю половину второго столбца
- подграфик C будет занимать нижнюю половину второго столбца

ax = fig.subplot_mosaic("""AB
                           AC""")

ax['A'].plot(...) # область А
ax['B'].plot(...) # область B
ax['C'].plot(...) # область C

Результат смотрите под постом

Затем создайте подграфик в определенной области, просто через доступ к словарю ax по ключу ('A', 'B' или 'C' ).

type(ax)
dict

ax['A'].plot(...) # область А
ax['B'].plot(...) # область B
ax['C'].plot(...) # область C

📌 Попробуйте вместе с нами!

import matplotlib.pyplot as plt
import numpy as np

fig = plt.figure(
          layout="constrained", 
          figsize = (8,6))

mosaic = """abc
            add
            eee"""

ax = fig.subplot_mosaic(mosaic)
x = np.linspace(0.1, 1, 10)

ax['a'].plot(x, np.sqrt(x))
ax['b'].scatter(x, x**2)
ax['c'].scatter(x, np.log(x))
ax['d'].bar([1,2,3,4,5],
            [1,2,3,4,5])
ax['e'].hist(
   np.random.normal(0,1, 100))

plt.show();

Скажите, классная функция?! С помощью нее гораздо проще создавать дашборды 😉

#python #visualization