SQL‑кейс: у вас есть таблица tests(student_id, subject, score) и запрос SELECT student_id, AVG(score) FROM tests; объясните, почему этот запрос некорректен, перепишите его с учётом группировки и фильтрации, предложите индексную стратегию и обсудите компромисс нормализации и денормализации при проектировании схемы для аналитической отчётности больших объёмов данных
SQL‑кейс: у вас есть таблица tests(student_id, subject, score) и запрос SELECT student_id, AVG(score) FROM tests; объясните, почему этот запрос некорректен, перепишите его с учётом группировки и фильтрации, предложите индексную стратегию и обсудите компромисс нормализации и денормализации при проектировании схемы для аналитической отчётности больших объёмов данных
Похожие вопросы
Не можешь разобраться в этой теме?
Обратись за помощью к экспертам
Гарантированные бесплатные доработки в течение 1 года
Быстрое выполнение от 2 часов
Проверка работы на плагиат
Поможем написать учебную работу
- В стандарте SQL нельзя в SELECT включать неагрегированное поле вместе с агрегатной функцией без соответствующего GROUP BY. Запрос
SELECT student_id, AVG(score)\operatorname{AVG}(score)AVG(score) FROM tests;
некорректен, потому что student_id\text{student\_id}student_id не агрегирован и не указан в GROUP BY.
- Некоторые СУБД (старые MySQL при выключённом ONLY_FULL_GROUP_BY) позволят выполнить такой запрос, но результат будет недетерминированным.
Правильная версия с группировкой и фильтрацией
- Базовый корректный запрос:
SELECT student_id, AVG(score)\operatorname{AVG}(score)AVG(score) AS avg_score
FROM tests
GROUP BY student_id;
- Пример с фильтрацией студентов, у которых не менее 3 оценок:
SELECT student_id, AVG(score)\operatorname{AVG}(score)AVG(score) AS avg_score, COUNT(∗)\operatorname{COUNT}(*)COUNT(∗) AS cnt
FROM tests
GROUP BY student_id
HAVING COUNT(∗)≥3\operatorname{COUNT}(*) \ge 3COUNT(∗)≥3 ORDER BY avg_score DESC;
Короткое замечание про формулу
- По сути AVG(score)=∑scoreCOUNT(score)\operatorname{AVG}(score)=\dfrac{\sum score}{\operatorname{COUNT}(score)}AVG(score)=COUNT(score)∑score .
Индексная стратегия для больших объёмов
- Простая рекомендация: индекс по ключу группировки — например, составной индекс на (student_id,score)(student\_id, score)(student_id,score):
CREATE INDEX idx_tests_student_score ON tests(student_id, score);
Это помогает:
- ускорять группировку по student_id;
- при поддержке index-only scans — покрывать запросы без обращения к таблице.
- Если обычно фильтруете по дате или предмету, добавляйте эти колонки в индекс в порядке селективности, напр. (student_id,subject,examdate,score)(student\_id, subject, exam_date, score)(student_id,subject,examd ate,score) или партиционируйте по дате.
- Для очень больших наборов данных: использовать партиционирование (по дате или по диапазонам student_id), columnar-хранилище (OLAP движки), или материализованные агрегаты/summary tables.
Компромисс нормализация ↔ денормализация для аналитики
- Нормализация:
- + меньше дублирования, проще поддерживать целостность при транзакционных операциях.
- − медленнее аналитика (много JOIN'ов), сложнее агрегировать на больших объёмах.
- Денормализация / звёздная схема / предварительные агрегации:
- + быстрые отчёты, простые запросы, меньше JOIN'ов; хорошо для OLAP.
- − увеличение хранения, сложность обновлений и ETL; возможна рассинхронизация данных.
- Практический компромисс:
- Хранить фактовую таблицу (tests) нормализованной, но строить отдельные витрины/summary tables или материализованные представления для часто используемых агрегатов;
- использовать ETL/CDC для инкрементального обновления агрегатов;
- применять star/schema: маленькие dimension-таблицы (денормализованные при необходимости) + крупная факт-таблица;
- выбирать индекс/партиционирование и columnar-хранилище в зависимости от характера запросов (много сканов/агрегатов → денормализация + columnar + pre-agg).
Если нужно, дам конкретные примеры индексов/партиций и схему витрины под ваш сценарий (объёмы данных, характер запросов).