На что обращать внимание при тестировании агрегатных функций SQL (SUM, AVG, COUNT)?

Тестирование агрегатных функций требует проверки не только математической корректности, но и поведения при различных условиях данных.

Ключевые аспекты для тестирования:

1. Поведение с NULL:

   • COUNT(column) игнорирует NULL, COUNT(*) — нет.
   • SUM(), AVG(), MIN(), MAX() игнорируют NULL.

     
     -- Пример проверки
     CREATE TABLE test_nulls (id INT, value INT);
     INSERT INTO test_nulls VALUES (1, 10), (2, NULL), (3, 20);

   SELECT COUNT(*) as count_all, -- Ожидаемо: 3 COUNT(value) as count_values, -- Ожидаемо: 2 (NULL проигнорирован) SUM(value) as sum_values, -- Ожидаемо: 30 (10+20) AVG(value) as avg_values -- Ожидаемо: 15 (30/2), а не 10 (30/3) FROM test_nulls;

2. Группировка (GROUP BY):

   • Проверка корректности агрегации внутри каждой группы.
   • Проверка строк с NULL в группирующей колонке (они попадают в одну группу).

3. Фильтрация (WHERE vs HAVING):

   • WHERE фильтрует строки до агрегации.
   • HAVING фильтрует результаты после агрегации.

     
     -- WHERE: Исключает строки до подсчета средней цены в категории
     SELECT category, AVG(price) FROM products WHERE price > 0 GROUP BY category;

   -- HAVING: Исключает категории, где средняя цена уже подсчитана и меньше 100 SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 100;

4. Граничные и особые случаи:

   • Пустая таблица: SUM() и AVG() возвращают NULL, COUNT() возвращает 0.
   • Переполнение: Для SUM() на больших числах.
   • Точность AVG(): Округление в зависимости от типа данных (целочисленное деление vs деление с плавающей точкой).

5. Производительность:

   • Агрегация по неиндексированным колонкам на больших объемах данных.
   • Использование DISTINCT внутри агрегатных функций (например, COUNT(DISTINCT column)) может быть ресурсоемким.

Общий подход: Сначала создать тестовый набор данных, покрывающий все сценарии (NULL, пустые значения, дубликаты, отрицательные числа), затем выполнить запрос и сравнить результат с ожидаемым, рассчитанным вручную или с помощью эталонного инструмента.