Аналитика баз данных с Python: практические решения и варианты реализации

Расширенные примеры анализа базы данных

Ниже приведены более сложные и нестандартные сценарии анализа, включающие оконные функции, создание сводных таблиц и визуализацию.

Пример 1: Анализ с использованием оконных функций (pandas + SQL)

Рассмотрим задачу: для каждого клиента найти его порядковый номер по сумме покупок (ранжирование). Вместо выполнения оконной функции на стороне БД, можно сделать это в pandas после загрузки.

import pandas as pd
import sqlite3

conn = sqlite3.connect('orders.db')
df = pd.read_sql('SELECT customer_id, order_total FROM orders', conn)
conn.close()

# Ранжирование клиентов по сумме заказов (убывание)
df['rank'] = df.groupby('customer_id')['order_total'].rank(method='dense', ascending=False)
print(df[df['rank'] == 1].head(10))

   customer_id  order_total  rank
0          101        350.0   1.0
5          205        280.0   1.0

Пример 2: Сложная сводная таблица с несколькими группировками

Допустим, есть таблица продаж с полями year, region, category, revenue. Требуется построить матрицу "год x регион" со средним чеком по каждой категории. Для этого используется pivot_table.

import pandas as pd

data = {
    'year': [2023, 2023, 2024, 2024, 2023, 2024],
    'region': ['North', 'South', 'North', 'South', 'North', 'South'],
    'category': ['A', 'A', 'B', 'B', 'B', 'A'],
    'revenue': [100, 150, 200, 120, 180, 210]
}
df = pd.DataFrame(data)

pivot = df.pivot_table(values='revenue', index='year', columns='region', aggfunc='mean')
print(pivot)

region  North  South
year              
2023    140.0  150.0
2024    200.0  165.0

Пример 3: Анализ временных рядов из БД с ресемплингом

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

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('sqlite:///weather.db')
df = pd.read_sql('SELECT date, temp FROM measurements', engine, parse_dates=['date'])
df.set_index('date', inplace=True)

# Месячные средние
monthly = df.resample('M').mean()
print(monthly.head())

                 temp
date                 
2023-01-31   3.456667
2023-02-28   4.120000

Пример 4: Объединение данных из нескольких таблиц с последующей визуализацией

Часто требуется соединить таблицы заказов и товаров, а затем построить график продаж по категориям. Используется matplotlib.

import pandas as pd
import matplotlib.pyplot as plt
import sqlite3

conn = sqlite3.connect('store.db')
query = '''
SELECT c.name, SUM(s.amount) as total
FROM sales s
JOIN categories c ON s.category_id = c.id
GROUP BY c.name
'''
df = pd.read_sql(query, conn)
conn.close()

plt.bar(df['name'], df['total'])
plt.title('Продажи по категориям')
plt.xlabel('Категория')
plt.ylabel('Сумма')
plt.show()

Результат - график (не выводится в текстовом виде, но код иллюстрирует подход).

Пример 5: Потоковая обработка больших данных с chunksize

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

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('postgresql://user:pass@localhost:5432/bigdb')
chunks = pd.read_sql('SELECT * FROM massive_table', engine, chunksize=10000)

total_mean = 0
count = 0
for chunk in chunks:
    total_mean += chunk['value'].sum()
    count += len(chunk)

overall_mean = total_mean / count
print(f'Общее среднее: {overall_mean:.2f}')

Общее среднее: 504.12

Пример 6: Использование pandas-profiling для автоматического отчёта

Библиотека ydata-profiling (ранее pandas-profiling) позволяет сгенерировать HTML-отчёт по загруженным данным. Это удобно для первичного анализа.

import pandas as pd
from ydata_profiling import ProfileReport

df = pd.read_sql('SELECT * FROM table', engine)
profile = ProfileReport(df, title='Отчёт по данным', explorative=True)
profile.to_file('report.html')

После выполнения файл report.html содержит статистики, корреляции, пропуски, визуализации.