Привет, друзья! Меня на днях попросили проанализировать домены почт у клиента если сможем.✉️ Цитата:
Типа такого: яндекс - 1,2 млн / 15% мейл.ру - …. гугл
Я ответила, что можем и
Покажу решение на примере SQL-запроса и Python кода.
📜 SQL-запрос
Для начала я использую SQL-запрос, чтобы извлечь домены почт из базы данных:
query = ""
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1) AS mail_type, COUNT(*)
FROM user.user
WHERE client_id = 1000
AND type = 'user'
GROUP BY 1
"""
users = pd.read_sql(query, connection)
Этот запрос выполняет следующие действия:
✔️SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1)
- SUBSTRING_INDEX(email, '@', -1) извлекает часть строки после символа @ (то есть домен почты).
- SUBSTRING_INDEX(..., '.', 1) затем извлекает часть строки до первой точки в домене, чтобы получить тип почты.
Таким образом, если email выглядит как user@mail.com, результат будет mail. Если email выглядит как user@yandex.ru, результат будет yandex.
✔️COUNT(*) — считает количество пользователей с каждым доменом.
✔️WHERE client_id = 1000 AND type = 'user' — фильтрует данные по определённому аккаунту и типу пользователя.
✔️GROUP BY 1 — группирует результаты по домену.
💻 Python код
После получения данных из SQL-запроса, мы используем Python для дальнейшей обработки. В целом можно и в SQL написать было, но базе и так тяжело, не стала нагружать ее подзапросами.
import pandas as pd
#Сортировка данных по количеству пользователей в порядке убывания
users_sorted = users.sort_values('count(*)', ascending=False)
# Подсчёт общего количества пользователей
total_count = users_sorted['count(*)'].sum()
# Расчёт процента для каждого домена
users_sorted['percentage'] = (users_sorted['count(*)'] / total_count) * 100
P.S. Функция SUBSTRING_INDEX используется в MySQL и не поддерживается PostgreSQL. В PostgreSQL для аналогичной задачи нужно использовать
split_part(split_part(email, '@', 2), '.', 1) AS mail_type
Цифра 2 в функции split_part(email, '@', 2) указывает на то, что мы хотим получить вторую часть строки, разделённую символом '@'. Мы разделяем email на две части: до символа '@' и после него. Нам нужна часть, идущая после символа '@', то есть домен почты.
📝 Результаты
После выполнения этого кода мы получаем таблицу, отсортированную по количеству пользователей для каждого домена, с процентным значением.
Теперь понятно какие почтовые домены наиболее популярны среди пользователей. ✉️