157 lines
8.1 KiB
Python
157 lines
8.1 KiB
Python
"""
|
||
Пример оптимизации с сырым SQL для самых медленных запросов.
|
||
Использовать только если ORM оптимизация недостаточна.
|
||
"""
|
||
from django.db import connection
|
||
from django.db.models import Count, Sum, Avg, Q
|
||
from decimal import Decimal
|
||
|
||
|
||
def get_detailed_lesson_stats_raw_sql(mentor, start_date, end_date):
|
||
"""
|
||
Оптимизированная версия с сырым SQL.
|
||
Использовать только если get_detailed_lesson_stats все еще медленный.
|
||
|
||
Выигрыш: ~30-50% быстрее за счет одного сложного запроса вместо множественных.
|
||
"""
|
||
with connection.cursor() as cursor:
|
||
# Один большой запрос вместо множественных
|
||
cursor.execute("""
|
||
WITH lesson_stats AS (
|
||
SELECT
|
||
COUNT(*) as total,
|
||
COUNT(*) FILTER (WHERE status = 'completed') as completed,
|
||
COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
|
||
SUM(duration) FILTER (WHERE status = 'completed') as total_duration,
|
||
AVG(duration) FILTER (WHERE status = 'completed') as avg_duration,
|
||
SUM(price) FILTER (WHERE status = 'completed' AND price IS NOT NULL) as total_revenue,
|
||
AVG(price) FILTER (WHERE status = 'completed' AND price IS NOT NULL) as avg_price,
|
||
AVG(mentor_grade) FILTER (WHERE status = 'completed' AND mentor_grade IS NOT NULL) as avg_grade,
|
||
MAX(mentor_grade) FILTER (WHERE status = 'completed' AND mentor_grade IS NOT NULL) as max_grade,
|
||
MIN(mentor_grade) FILTER (WHERE status = 'completed' AND mentor_grade IS NOT NULL) as min_grade,
|
||
SUM(price) FILTER (WHERE status = 'cancelled' AND price IS NOT NULL) as cancelled_revenue,
|
||
COUNT(*) FILTER (WHERE status = 'completed' AND mentor_grade IS NOT NULL) as graded_count
|
||
FROM lessons
|
||
WHERE mentor_id = %s
|
||
AND start_time >= %s
|
||
AND start_time <= %s
|
||
),
|
||
by_hour_stats AS (
|
||
SELECT
|
||
EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC')::int as hour,
|
||
COUNT(*) as count
|
||
FROM lessons
|
||
WHERE mentor_id = %s
|
||
AND start_time >= %s
|
||
AND start_time <= %s
|
||
AND status = 'completed'
|
||
GROUP BY hour
|
||
),
|
||
time_of_day_stats AS (
|
||
SELECT
|
||
COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') >= 6 AND EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') < 12) as morning,
|
||
COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') >= 12 AND EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') < 18) as afternoon,
|
||
COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') >= 18 AND EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') < 24) as evening,
|
||
COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') >= 0 AND EXTRACT(HOUR FROM start_time AT TIME ZONE 'UTC') < 6) as night
|
||
FROM lessons
|
||
WHERE mentor_id = %s
|
||
AND start_time >= %s
|
||
AND start_time <= %s
|
||
AND status = 'completed'
|
||
),
|
||
top_clients_stats AS (
|
||
SELECT
|
||
c.id as client_id,
|
||
u.first_name,
|
||
u.last_name,
|
||
u.email,
|
||
COUNT(l.id) as lessons_count,
|
||
COUNT(l.id) FILTER (WHERE l.status = 'completed') as completed_count,
|
||
SUM(l.price) FILTER (WHERE l.status = 'completed' AND l.price IS NOT NULL) as total_revenue,
|
||
AVG(l.mentor_grade) FILTER (WHERE l.status = 'completed' AND l.mentor_grade IS NOT NULL) as avg_grade
|
||
FROM lessons l
|
||
JOIN clients c ON l.client_id = c.id
|
||
JOIN users u ON c.user_id = u.id
|
||
WHERE l.mentor_id = %s
|
||
AND l.start_time >= %s
|
||
AND l.start_time <= %s
|
||
GROUP BY c.id, u.first_name, u.last_name, u.email
|
||
ORDER BY lessons_count DESC
|
||
LIMIT 10
|
||
)
|
||
SELECT
|
||
(SELECT row_to_json(s) FROM lesson_stats s) as stats,
|
||
(SELECT json_agg(row_to_json(b)) FROM by_hour_stats b) as by_hour,
|
||
(SELECT row_to_json(t) FROM time_of_day_stats t) as time_of_day,
|
||
(SELECT json_agg(row_to_json(tc)) FROM top_clients_stats tc) as top_clients
|
||
""", [
|
||
mentor.id, start_date, end_date, # lesson_stats
|
||
mentor.id, start_date, end_date, # by_hour_stats
|
||
mentor.id, start_date, end_date, # time_of_day_stats
|
||
mentor.id, start_date, end_date, # top_clients_stats
|
||
])
|
||
|
||
row = cursor.fetchone()
|
||
stats_data = row[0]
|
||
by_hour_data = row[1] or []
|
||
time_of_day_data = row[2] or {}
|
||
top_clients_data = row[3] or []
|
||
|
||
# Форматируем результат
|
||
stats = stats_data.get('stats', {})
|
||
by_hour_dict = {item['hour']: item['count'] for item in by_hour_data}
|
||
|
||
return {
|
||
'summary': {
|
||
'total_lessons': stats.get('total', 0),
|
||
'completed': stats.get('completed', 0),
|
||
'cancelled': stats.get('cancelled', 0),
|
||
'completion_rate': round(
|
||
(stats.get('completed', 0) / stats.get('total', 1) * 100) if stats.get('total', 0) > 0 else 0,
|
||
1
|
||
),
|
||
},
|
||
'time': {
|
||
'total_duration_minutes': stats.get('total_duration', 0) or 0,
|
||
'total_duration_hours': round((stats.get('total_duration', 0) or 0) / 60, 1),
|
||
'average_duration_minutes': round(stats.get('avg_duration', 0) or 0, 1),
|
||
'by_hour': [
|
||
{'hour': hour, 'count': by_hour_dict.get(hour, 0)}
|
||
for hour in range(24)
|
||
],
|
||
'by_time_of_day': {
|
||
'morning': time_of_day_data.get('morning', 0) or 0,
|
||
'afternoon': time_of_day_data.get('afternoon', 0) or 0,
|
||
'evening': time_of_day_data.get('evening', 0) or 0,
|
||
'night': time_of_day_data.get('night', 0) or 0,
|
||
},
|
||
},
|
||
'revenue': {
|
||
'total': float(stats.get('total_revenue', 0) or 0),
|
||
'average_per_lesson': float(stats.get('avg_price', 0) or 0),
|
||
'potential_revenue': float(stats.get('cancelled_revenue', 0) or 0),
|
||
},
|
||
'grades': {
|
||
'average': round(stats.get('avg_grade', 0) or 0, 1),
|
||
'max': stats.get('max_grade', 0) or 0,
|
||
'min': stats.get('min_grade', 0) or 0,
|
||
'graded_count': stats.get('graded_count', 0) or 0,
|
||
},
|
||
'top_clients': [
|
||
{
|
||
'name': f"{item['first_name']} {item['last_name']}".strip() or item['email'],
|
||
'email': item['email'],
|
||
'lessons_count': item['lessons_count'],
|
||
'completed_count': item['completed_count'],
|
||
'total_revenue': float(item['total_revenue'] or 0),
|
||
'average_grade': round(item['avg_grade'] or 0, 1),
|
||
}
|
||
for item in top_clients_data
|
||
],
|
||
}
|
||
|
||
|
||
# ВАЖНО: Использовать только после измерения производительности!
|
||
# Сначала убедитесь, что оптимизация ORM недостаточна.
|
||
|