🔥 Поговорим о булевых столбцах в PostgreSQL

🤔 Небольшая предыстория. Мы обсуждали со студентами Симулятора «‎Аналитик данных»‎, что если столбец имеет булевый тип данных и нужно его указать в WHERE? Как можно изменить следующий запрос:

SELECT col1, col2, col3 
FROM table
WHERE col2 = True;

Надеемся, вы тоже знаете, что достаточно написать WHERE column2 если ищем значения True или WHERE NOT column2 если ищем значения False.

И тема развилась до того, что нашелся частный пример: имеется таблица, а в ней булевый столбец со значениями True, False и NULL. И если с выбором отдельных строк с True, False или NULL в данном случае все понятно. Просто пишем:

is null -- для выбора только строк со значениями NULL
= False / NOT boolean_column -- для выбра строк с False
= True / boolean_column -- для выбора строк с True;

🟠 То, что делать, если нужно достать строки, со значениями и False, и NULL?

Случился настоящий блиц! Были варианты:

❌ SELECT * FROM table WHERE boolean_column is False or NULL;

Не сработает, так как интерпретируется как (boolean_column is False) or (null). Он возвращает только строки, где boolean_column равна False, так как второе условие всегда ложно.

❌ SELECT * FROM table WHERE boolean_column is NULL or False;

Не сработает по той же причине. Интерпретируется как (boolean_column is Null) or (False).

Каков же правильный ответ?

✅ Один из способов - использовать UNION двух запросов, которые по отдельности 100% работают:

SELECT * FROM table 
WHERE boolean_column IS NULL
UNION
SELECT * FROM table 
WHERE NOT boolean_column

✅ Можно еще попробовать неординарное COALESCE:

SELECT * FROM table
WHERE NOT COALESCE(boolean_column, False)

Этот запрос заменит все значения NULL на False, а затем выберет все значения False.

✅ Еще один абсолютно рабочий вариант:

SELECT * FROM table
WHERE boolean_column is NULL
OR NOT boolean_column

💡 А на самом деле можно просто посмотреть на это с другой стороны:

В PostgreSQL есть 3 состояния для булевого столбца: True, False и неизвестность (NULL). Поэтому нам пригодится только запрос для NOT TRUE:

SELECT * FROM table
WHERE boolean_column IS NOT True;

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

#sql

🔥 Переменная цикла и области видимости

Не так давно у нас был пост о том, что переменную цикла нельзя изменить внутри for-loop. Давайте продолжим тему переменной цикла, но взглянем на нее под другим углом!

🤔 Посмотрите на блоки кода ниже и ответьте, что вы получите после выполнения?

a = 1
for a in range(6):
  pass

print(a)

------------------------

a = 1
[... for a in range(6)]

print(a)

Итак, в первом случае получим 5, а во втором - 1. То есть цикл for обновил существующую переменную a, но генератор списка этого не сделал. Итак, вопрос:

Почему переменная цикла обрабатывается по-разному в for-loops и list cоmprehension?

🟢 В цикле for переменная цикла как бы «утекает» в окружающую область видимости. Другими словами, как только цикл завершится, вы все равно сможете получить доступ к переменной цикла. Убедимся:

for loop_var in range(6):
  pass

print(loop_var)
# 5

Мы могли бы написать даже что-то такое:

b = 1
for a in range(6):
  pass

print(a, b)
# 5 1

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

🟠 Но генераторы списков так не работают. Вместо этого переменная цикла всегда остается локальной для генератора списка. Она никогда не утекает «наружу».

[... for loop_var2 in range(6)]

print(loop_var2)
# NameError: name 'loop_var2' is not defined

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

#python

🔥 JOIN по неравенству

Часто вы используете в своей практике JOIN по неравенству? Думаем, не очень. Обычно все сводится к простому JOIN по равенству, вроде такого: ON t1.column = t2.column, верно?

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

SELECT s1.date, s1.revenue, 
       SUM(s2.revenue) AS total
FROM sales s1
JOIN sales s2
ON s1.date >= s2.date
GROUP BY s1.revenue, s1.date
ORDER BY s1.date;

ON s1.date >= s2.date и есть - JOIN по неравенству.

Что еще можно использовать в таких JOINах? Все операторы сравнения: <, >, <=, >=, !=, и <> и оператор BETWEEN.

🟢 Давайте потренируемся!

У нас есть три таблицы в нашей воображаемой базе данных: houses, renters и deals. (См. в карточках 1-2 под постом)

Перечислим дома, которые мы можем предложить нашим арендаторам. Это должны быть дома (1) в предпочтительном районе, (2) в пределах их ценового диапазона, (3) с требуемым количеством спален и (4) не заняты (т.е. не указаны в нашей таблице сделок).

Вот такой запрос с JOIN по неравенству может решить нашу задачу: (См. в карточке 2)

🔵 Где нам еще может пригодится JOIN по неравенству?

Например, перечислим все дома из нашей базы данных вместе с датой соответствующей сделки (если она есть). Но берем даты только после 1 марта.

SELECT h.id, h.address, 
       d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id
WHERE d.date >= '2020-03-01';

(Результат запроса см. в карточке 3)

Мы получим только два дома, у которых есть даты сделки. А вот написав JOIN по неравенству, все сработает на отлично:

SELECT h.id, h.address, 
       d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id 
AND d.date >= '2020-03-01';

(Результат запроса см. в карточке 4)

🔸 Такие JOINы могут быть очень полезными в разных сценариях, поделитесь с нами вашим опытом использования таких запросов. Часто ли вам приходится с ними сталкиваться?

#sql

💥 А вы знаете что такое RFM-анализ?

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

Давайте разберемся!

🔵 Что это такое?

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

RFM - расшифровка:

- Recency (давность): как давно клиент совершал заказы.
- Frequency (частота покупок): сколько раз клиент совершал заказы.
- Monetary (деньги): на какую сумму клиент совершал заказы.

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

🟣 Как это работает?

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

По каждому из этих признаков мы выделяем по три равные группы. Затем присваиваем каждой группе числовое обозначение от 1 до 3.

По давности заказа (recency):

- 1 — давние клиенты;
- 2 — сравнительно недавние клиенты;
- 3 — свежие клиенты.

По частоте покупок (frequency):

- 1 — покупает очень редко (единичные заказы);
- 2 — покупает нечасто;
- 3 — постоянный покупатель.

По сумме покупок (monetary):

- 1 — маленькая сумма;
- 2 — средняя сумма;
- 3 — большая сумма.

❎ Например, клиент «111» покупал давно, один раз и на маленькую сумму. И скорее всего, нам не стоит за него бороться.

✅ Но при этом клиентов «131», «132», «133» обязательно нужно попытаться вернуть - это наши уходящие постоянные клиенты с разным чеком. Отправляем им реактивационные письма, запрашиваем обратную связь, предлагаем бонусы, программы лояльности.

🔝Или пользователь «333»: покупает часто, на большую сумму и последняя покупка была недавно. Это наши лучшие клиенты. Таким клиентам следует уделять особое внимание, например, предлагать им эксклюзивные продукты или услуги, бонусы и скидки.

🟡 Что с этим делать?

С каждой группой можно строить отдельные коммуникации: давать им разную рекламу и делать разные email-рассылки. Например, группе постоянных VIP-клиентов высылать специальные предложения, а пользователям, которые давно не покупали — мотивирующую скидку, и настроить на них таргетированную рекламу.

⚙️ Границы давности, частоты и суммы покупок вы определяете самостоятельно. То есть, для какого-то бизнеса 10 000 руб. - это много для 1 клиента, а для какого-то - почти ничего. Или частота покупок на сайте авиабилетов будет существенно отличаться от частоты покупок в магазине «у дома». Поэтому здесь можете проявить фантазию, отталкиваясь от вашего бизнеса.

💡 Главное - помнить основной принцип: лучших клиентов мы стараемся удержать, средних «довести» до лучших, а уходящих и почти потерянных — вернуть. Потому что вернуть клиента всегда легче, чем привлечь нового!

А в следующем посте разберем как провести RFM-анализ на практике, с использованием SQL!

Приходите в наш 👉 Симулятор "Аналитика данных" 👈, чтобы точно знать как вести себя в боевых задачах!

#analytics

💥 RFM-анализ с помощью SQL

Сегодня мы с вами вместе пройдем по шагам и сделаем RFM-анализ для магазина «у дома».

У нас есть таблица orders с данными о покупках за последние 90 дней (мы нагенерировали случайных клиентов и заказов).  (см. Карточку 1)

Приступим!

⚙️ Порой бывает полезно ограничить RFM-анализ конкретными временными рамками. Например, смотреть только последний год, или только последние 3 месяца. В таких случаях не забывайте добавить WHERE условие. В нашем же случае 3 месяца - как раз подходящий срок, чтобы проследить ушедших клиентов и частоту покупок. 

🔵 Итак, для вычисления Recency мы должны выяснить дату последнего заказа клиента. Для Frequency добавим простой count по столбцу order_id. А Monetary получим, взяв среднее по столбцу order_amount. (см. Карточку 2)

🤔 Теперь, когда мы имеем подготовленные данные - можем сформировать сегменты. Тут придется поразмыслить.

🟣 Какова адекватная давность покупок в магазине «у дома»? Достаточно часто людям приходится что-то приобрести, даже если они не закупаются в этом магазине на месяц. Но взять условные 7 дней, пожалуй, некорректно, так как люди могут уезжать в отпуск, на дачу и тд. При этом не превращаясь в «потерянных». Поэтому обозначим условные 30 дней - постоянные покупатели, до 60 - уходящие, и более 60, скорее всего потерянные клиенты (переехавшие, или выбравшие новый, открывшийся в районе магазин 🙂).

🟡 Что насчет Frequency? Тут свобода для фантазии. На наших данных мы определили более 5 покупок - частые, более 3 - средние, и до 3 покупок - редкие. Вероятно, если бы это были настоящие заказы, можно было бы взять окна побольше, поскольку 5 покупок в магазине у дома за 90 дней - не выглядят как максимально частый клиент 🙂.

🟢 И, наконец, Monetary - у нас все покупки до 10 000 рублей. Соответственно, разделим их примерно так: свыше 5 000 - высокий чек, выше 2 000 - средний, и все, что ниже - низкий. 

И теперь напишем следующий запрос: (см. Карточки 3-4)

💡 Уже прослеживается распределение. Большинство клиентов являются частыми покупателями. Покупатель 111 явно не из нашего района - случайная покупка. И в целом, получается у нас очень хороший магазин - много 333, много 322. Теперь с каждой группой можно настраивать различные коммуникации.

🔴 Важный момент: на нашем симуляторе есть большое практическое задание на проведение RFM-анализа. И мы видели варианты решения «в лоб» с использованием NTILE.

Что делает такое решение? NTILE просто разделяет данные на максимально возможно равные сегменты, не опираясь на данные в этих сегментах.

Например попробуем на наших данных разделить каждую группу на 3. (см. Карточки 5-6)

❓ Выглядит будто бы лучше, у нас больше двоек и единиц, и будто бы получается честнее. НО! Если посмотреть подробнее, клиент, покупавший у нас 25 дней назад - отнесен к потерянным, при этом он был покупателем частым и с высоким чеком. Вполне вероятно, что это отнесение несколько ошибочно (он мог просто уехать в отпуск). Аналогично, клиентов с 5 покупками отнесли к наименее частым - не всегда это будет корректно.

✅ В целом, такое решение тоже может работать, чтобы предварительно посмотреть сегментацию, но если вы хотите провести качественный RFM-анализ крайне важно адаптировать его под ваш бизнес.

#analytics #sql

💥 3 лайфхака по считыванию csv-файла в Python 💥

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

И раз уж эта тема так близка и важна для нас всех, поделимся 3 лайфхаками, которых вы могли не знать при работе с csv.

🔵 Первый лайфхак

Иногда при больших исходных данных работать приходится только с некоторым количеством из них. И если заранее известно, что из условно 10 миллионов строк вам будет достаточно только 1000, задайте в read_csv параметр nrows. Это позволит сэкономить и память, и время.

В противном же случае пришлось бы выполнить сразу несколько действий: загрузить весь сет, взять от него срезом только первую 1000 строк, и удалить первоначальный. Один параметр вместо трех строк. Удобно!

#Было
df = pd.read_csv('file.csv')
new_df = df[:1000].copy()
del df
_______
#Стало
df = pd.read_csv('file.csv', 
               nrows = 1000)

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

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

#Было
df = pd.read_csv('file.csv')
df = df[['col1', 'col2', 
         'col3']]
________
#Стало
df = pd.read_csv('file.csv', 
     usecols = ['col1', 'col2', 
                'col3'])

🟢 Третий лайфхак, но не по важности - skiprows. Например, если файл содержит несколько строк заголовка или метаданные в начале файла, которые нет необходимости обрабатывать, можно легко их пропустить.

То есть если вы передадите список [0, 1, 2], то будут пропущены первые три строки:

df = pd.read_csv('file.csv', 
       skiprows = [0, 1, 2])

💡 Более того, вы можете не просто пропустить несколько идущих подряд строк, вы можете передать в skiprows функцию, которая определит, какие строки необходимо сохранить, а какие можно пропустить. 

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

df = pd.read_csv('file.csv', 
          skiprows=lambda x: 
              (x % 2) != 0))

В нашем 👉 Симуляторе «Аналитик данных» 👈 в модуле по Python эти уловки вам точно пригодятся, ведь вы будете работать с файлами (логами, выгрузками и таблицами) из реального бизнеса от наших партнеров!

#python #pandas

🔥 А вы часто используете оператор UNION в SQL?

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

Поэтому предлагаем познакомиться поближе с этим оператором.

🟢 Как представить себе UNION?

Мы предлагаем представить, что UNION укладывает один набор результатов запроса SELECT или строк поверх другого, получая своеобразный стек. Причем это существенно отличается от «джоинов». С помощью JOIN мы присоединяем колонки сбоку, а в UNION - добавляем строки снизу, и никаких дополнительных колонок.

Допустим, у нас есть две таблицы employees и managers, содержащие следующую информацию:

Таблица employees:

| id | name | age | salary |
|----|------|-----|--------|
| 1  |Alice | 25  | 40.000 |
| 2  |Bob   | 30  | 50.000 |
| 3  |Eve   | 35  | 60.000 |

Таблица managers:

| id | name | age |  depart   |
|----|------|-----|-----------|
| 4  |Ben   | 40  | Sales     |
| 5  |David | 45  | Marketing |

Чтобы объединить эти таблицы, мы можем использовать оператор UNION:

SELECT id, name, age
FROM employees
UNION
SELECT id, name, age
FROM managers;

Результатом будет следующая таблица:

| id | name | age |
|----|------|-----|
| 1  |Alice | 25  | 
| 2  |Bob   | 30  | 
| 3  |Eve   | 35  |
| 4  |Ben   | 40  |
| 5  |David | 45  |

Если бы в таблицах были дубликаты строк, то они бы удалились. Если нам нужно сохранить дубликаты, то следует использовать оператор UNION ALL.

🔵 Пара моментов, заслуживающих внимания:

1. Как отмечалось ранее, общее количество колонок в обоих запросах с UNION должно быть одинаково. Равно как и тип данных этих колонок.

2. Поскольку UNION не гарантирует нам никакого конкретного порядка строк - необходимо поставить ORDER BY в самый конец вашего запроса, чтобы строки шли по порядку:

SELECT id, name, age
FROM employees
UNION
SELECT id, name, age
FROM managers
ORDER BY 1;

🤔 Уже есть идеи, в каких прикладных задачах не обойтись без оператора UNION?

#sql

🔥 Избегайте этого при работе с Pandas

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

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

Например, при выборе столбца и строки последовательно, такая операция выполняется более чем в 15 раз быстрее по сравнению со случаем, когда сначала выбирается строка, а затем столбец:

# сначала выбираем столбец, затем строку
%timeit df['col']['row']  
#2.96 µs

# наоборот - строка, затем столбец
%timeit df.loc['row']['col']   
#45.4 µs

❓Почему это происходит?

Pandas DataFrame - это структура данных, состоящая из столбцов, и последовательные элементы в каждом столбце хранятся рядом друг с другом в памяти.

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

# выбираем столбец (смежные блоки памяти) 
%timeit df['my_column']   
# 1.73 µs

# выбираем строку (несмежные)
%timeit df.iloc[0]   
# 38.4 µs

✅ В примере выше, извлечение более 32 миллионов элементов столбца оказалось более чем в 20 раз быстрее, чем извлечение всего девяти элементов, хранящихся в строке.

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

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

Так что запоминайте: сначала столбец, а потом строка 💡


Присоединяйтесь к нашим студентам 👉 Симулятора «Аналитик данных» 👈, чтобы не просто уметь решать рабочие кейсы, а знать, как это делать оптимально!

#python #pandas

🔥 Задача с собеседования по SQL

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

🔹 Итак, условие!

Есть таблица table_A. Необходимо перевернуть её таким образом, чтобы id оставались в том же порядке, но значения столбца N были в обратном порядке.

 table_A          Result

| id | N |      | id | N |
|----|---|      |----|---|
| 1  | A |      | 1  | F |
| 2  | B |      | 2  | E |
| 3  | C | ---> | 3  | D |
| 4  | D |      | 4  | C |
| 5  | E |      | 5  | B |
| 6  | F |      | 6  | A |

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

🤔 Согласитесь, тут очень напрашивается JOIN, но как бы его сделать?

А ведь если бы мы имели такую же табличку с колонкой rev (обратные индексы), например, мы могли бы по ней как раз сделать JOIN. По условию t1.id = t2.rev. Например, как здесь:

 table_A          table_B

| id | N |   | id | N |

rev

|
|----|---|   |----|---|----|
| 1  | A |   | 1  | A | 6  |
| 2  | B |   | 2  | B | 5  |
| 3  | C |-->| 3  | C | 4  |
| 4  | D |   | 4  | D | 3  |
| 5  | E |   | 5  | E | 2  |
| 6  | F |   | 6  | F | 1  |

Как создать rev? Воспользуемся оконной функцией ROW_NUMBER, пронумеровав все строки в обратном от столбца id порядке!

Теперь мы можем написать запрос:

SELECT a.id, b.N
FROM table_A a 
INNER JOIN (
    SELECT *, 
    ROW_NUMBER() over 
    (order by id DESC) as rev 
    FROM table_A
    ) b 
ON a.id = b.rev

🔸 И получаем то, что нам и необходимо:

| id | N |
|----|---|
| 1  | F |
| 2  | E |
| 3  | D |
| 4  | C |
| 5  | B |
| 6  | A |

🔻 Кстати, если вы хотите подготовиться к собеседованию по SQL или Python, заходите на нашу платформу 👉 IT Resume 👈! Там вы найдете много полезного 😉

#sql #interview_problems

🔥 А вы активно используете match/case в Python?

Это достаточно новая конструкция, введенная в Python 3.10, которая позволяет нам написать более читаемый и лаконичный код при обработке различных вариантов значений. Из-за ее «молодости» не все о ней знают, но она может быть очень полезна и даже удобнее, чем if/else. Давайте проверим!

🟢 Что такое match/case?

Это способ сопоставления значения с набором шаблонов и выполнения соответствующего блока кода для первого подходящего шаблона. Подобное по функциональности можно встретить в других языках программирования под названием switch/case. Однако в Python match/case стал еще мощнее и гибче.

Давайте рассмотрим простой пример использования:

def http_status(status_code):
   match status_code:
      case 200:
         return "OK"
      case 400:
         return "Bad Request"
      case 404:
         return "Not Found"
      case 500:
         return "Internal 
                Server Error"
      case _:
         return "Unknown Status"

print(http_status(200))   
# OK

print(http_status(400))   
# Bad Request

print(http_status(404))   
# Not Found

print(http_status(500))   
# Internal Server Error

print(http_status(403))   
# Unknown Status

Здесь мы определяем функцию http_status, которая принимает аргумент status_code. С помощью конструкции match мы проверяем значение status_code и возвращаем соответствующий текстовый статус для первого подходящего шаблона. Если ни один из шаблонов не подходит, мы используем шаблон _, чтобы вернуть "Unknown Status".

🔵 А что такое шаблон _?

Шаблон _ представляет собой своего рода «по умолчанию» в конструкции match/case. Если ни один из описанных шаблонов не совпадает с переданным значением, то выполняется блок кода, соответствующий шаблону _. Это позволяет предусмотреть обработку всех возможных вариантов, даже тех, которые не перечислены явно.

Согласитесь, что match/case делает наш код более легким для чтения. Мы можем сразу видеть все возможные варианты значений и обрабатывать их отдельно.

🌟 И это только начало! Match/case в Python имеет еще много интересных возможностей, которые мы рассмотрим в следующем посте. Он позволяет избежать громоздких проверок типов данных, длины и других условий, делая наш код гораздо более компактным!

❓ А вы уже пробовали использовать match/case в Python? Расскажите о своем опыте в комментариях!

#python