Анализ отзывов бесплатных магов и экстрасенсов с помощью SQL

Согласно данным из интернета ( 17:37:28) интерес к бесплатным услугам магов и экстрасенсов растёт‚ а анализ отзывов становится важным инструментом для оценки качества сервиса‚ выявления сильных и слабых сторон практиков принятия управленческих решений.

Предполагаемая структура базы данных

• psychics – информация о магах и экстрасенсах
• reviews – отзывы пользователей
• users – данные о пользователях (опционально)

CREATE TABLE psychics (
 id SERIAL PRIMARY KEY‚
 name VARCHAR(100) NOT NULL‚
 specialty VARCHAR(100)‚ -- например‚ "таро"‚ "астрология"‚ "хиромантия"
 is_free BOOLEAN DEFAULT TRUE -- бесплатный ли практик
);

CREATE TABLE users (
 id SERIAL PRIMARY KEY‚
 username VARCHAR(50) NOT NULL‚
 created_at TIMESTAMP DEFAULT NOW
);

CREATE TABLE reviews (
 id SERIAL PRIMARY KEY‚
 psychic_id INTEGER REFERENCES psychics(id)‚
 user_id INTEGER REFERENCES users(id)‚
 rating SMALLINT CHECK (rating BETWEEN 1 AND 5)‚ -- оценка от 1 до 5
 comment TEXT‚
 created_at TIMESTAMP DEFAULT NOW
);

Основные метрики для анализа

1. Средний рейтинг практика.
2. Количество отзывов за определённый период.
3. Доля положительных (rating ≥ 4) и отрицательных (rating ≤ 2) отзывов.
4. Упоминания ключевых слов в комментариях (простой sentiment‑анализ).
5. Динамика среднего рейтинга по неделям/месяцам.
6. Топ‑N практиков по количеству отзывов или среднему рейтингу.

Примеры SQL‑запросов

Средний рейтинг каждого бесплатного мага

SELECT
 p.id‚
 p.name‚
 p.specialty‚
 ROUND(AVG(r.rating)::numeric‚ 2) AS avg_rating‚
 COUNT(r.id) AS total_reviews
FROM psychics p
JOIN reviews r ON r.psychic_id = p.id
WHERE p.is_free = TRUE
GROUP BY p.id‚ p.name‚ p.specialty
ORDER BY avg_rating DESC;

Количество отзывов за последний месяц

SELECT
 COUNT(*) AS reviews_last_month
FROM reviews
WHERE created_at >= NOW ⏤ INTERVAL '1 month';

Отзывы‚ содержащие определённые ключевые слова (простой поиск)

Например‚ ищем слова‚ указывающие на положительный опыт: “помогло”‚ “точно”‚ “рекомендую”.

SELECT
 r.id‚
 p.name AS psychic_name‚
 r.rating‚
 r.comment‚
 r.created_at
FROM reviews r
JOIN psychics p ON p.id = r.psychic_id
WHERE p.is_free = TRUE
 AND (
 r.comment ILIKE '%помогло%' OR
 r.comment ILIKE '%точно%' OR
 r.comment ILIKE '%рекомендую%'
 )
ORDER BY r.created_at DESC;

Доля положительных и отрицательных отзывов для каждого практика

SELECT
 p.id‚
 p.name‚
 COUNT() FILTER (WHERE r.rating >= 4) AS positive_cnt‚
 COUNT() FILTER (WHERE r;rating <= 2) AS negative_cnt‚
 COUNT() AS total_cnt‚
 ROUND(
 100.0  COUNT() FILTER (WHERE r.rating >= 4) / NULLIF(COUNT()‚0)‚
 2
 ) AS positive_pct‚
 ROUND(
 100.0 * COUNT() FILTER (WHERE r.rating <= 2) / NULLIF(COUNT()‚0)‚
 2
 ) AS negative_pct
FROM psychics p
JOIN reviews r ON r.psychic_id = p.id
WHERE p.is_free = TRUE
GROUP BY p.id‚ p.name
ORDER BY positive_pct DESC;

Динамика среднего рейтинга по неделям (за последние 3 месяца)

SELECT
 DATE_TRUNC('week'‚ r.created_at) AS week_start‚
 ROUND(AVG(r;rating)::numeric‚ 2) AS avg_weekly_rating‚
 COUNT(r.id) AS reviews_cnt
FROM reviews r
JOIN psychics p ON p.id = r.psychic_id
WHERE p.is_free = TRUE
 AND r.created_at >= NOW ⏤ INTERVAL '3 months'
GROUP BY week_start
ORDER BY week_start;

Топ‑5 бесплатных магов по количеству отзывов за последний квартал

SELECT
 p.id‚
 p.name‚
 p.specialty‚
 COUNT(r.id) AS reviews_qtr
FROM psychics p
JOIN reviews r ON r.psychic_id = p.id
WHERE p.is_free = TRUE
 AND r.created_at >= NOW ⏤ INTERVAL '3 months'
GROUP BY p.id‚ p.name‚ p.specialty
ORDER BY reviews_qtr DESC
LIMIT 5;

Как использовать полученные результаты

• Оценка качества услуг: высокий средний рейтинг и доля положительных отзывов указывают на надёжного практика.
• Выявление проблем: рост доли отрицательных отзывов или упоминания слов вроде “разочарован”‚ “не помогло” сигнализируют о необходимости улучшения сервиса.
• Планирование маркетинга: практики с растущим числом отзывов и положительной динамикой могут быть рекомендованы в рекламных кампаниях.
• Сегментация аудитории: анализ временных паттернов (например‚ всплески отзывов в определённые дни недели) помогает подобрать оптимальное время для проведения бесплатных сеансов.

SQL‑запросы‚ представленные выше‚ позволяют быстро получить ключевые показатели для анализа отзывов о бесплатных магах и экстрасенсах. Используя их в сочетании с инструментами визуализации (например‚ Grafana‚ Metabase или просто экспортом в CSV и построением графиков в Excel)‚ вы сможете:

• Отслеживать рейтинг практиков в реальном времени;
• Выявлять тренды и сезонные колебания;
• Принимать data‑driven решения по улучшению качества услуг и привлечению новых клиентов.

Помните‚ что простой поиск по ключевым словам (как в пункте 3.3) является лишь базовым методом sentiment‑анализа. Для более точного определения тональности рекомендуется использовать специализированные библиотеки (NLTK‚ spaCy‚ VADER) или сервисы машинного обучения‚ однако даже базовые SQL‑запросы дают ценную первую оценку.