На главную страницу
Характерные ошибки при решении упражнений.
Задача 26
Моисеенко С.И.
Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская
буква). Вывести: одна общая средняя цена.
В FAQ #6 описан пример решения, который схематично можно записать так:
SELECT (SUM(price_PC) + SUM(price_Laptop))/COUNT(PC) +
COUNT(Laptop))
WHERE maker='A'
Этот запрос будет работать неверно, если у производителя А в БД нет продукции
одного из видов. Пусть, например, отсутствуют ПК, тогда COUNT(PC) вернет 0, и
знаменатель будет посчитан правильно. Однако SUM(price_PC) даст NULL. Как
известно, арифметические операции с NULL-значением также дают NULL. В
результате мы получаем не SUM(price_Laptop)/COUNT(Laptop), а NULL.
Ниже приводится еще пара неверных решений, которые содержат другую ошибку.
(1)
SELECT AVG(price) FROM (
SELECT price FROM pc WHERE model IN
(SELECT model FROM product
WHERE maker='A' AND type='PC')
UNION
SELECT price FROM laptop WHERE model IN
(SELECT model FROM product
WHERE maker='A' AND type='Laptop')
) as prod
(2)
SELECT AVG(price) FROM (
SELECT price, model FROM pc
WHERE model IN
(SELECT model FROM product WHERE maker='A' AND
type='PC')
UNION
SELECT price, model FROM laptop WHERE model IN
(SELECT model FROM product
WHERE maker='A' AND type='Laptop')
) as prod
Первое решение дает результат 772.5, второе - 773.0 при правильном значении
734.5454545454545.
В запросе (1) выбираются цены на все модели производителя А из таблицы PC. Затем
они объединяются с ценами на все модели производителя А из таблицы Laptop.
Наконец, вычисляется среднее значение. Что же тут неправильного? Ошибка состоит
в том, как объединяются цены. Оператор UNION исключает дубликаты, поэтому из
нескольких одинаковых цен (если таковые имеются) будет оставаться одна. Как
результат, среднее будет посчитано по неверному количеству.
В запросе (2) выбирается не только цена, но и номер модели. Т.е. объединение
выполняется по паре атрибутов. Это решение было бы правильным, если бы в
соответствующей таблице не было одинаковых моделей с одинаковыми ценами.
Последнее было бы гарантировано, если бы пара {price, model} являлась первичным
ключом. Однако согласно нашей схеме это не так. При этом такая ситуация не
является нереальной. Представим себе, что одна модель комплектуется большим
диском, чем другая модель с тем же номером, а памяти, наоборот, имеет меньше.
Тогда цена у них вполне может быть одинакова.
В результате объединения будут исключены дубликаты пар {price, model} и, как
следствие, получен неверный результат.
» Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
Перейти к
решению задачи #26
На главную страницу