Книги и статьи по SQL Rambler's Top100 Switch language to: English 29 октября 2020 г. 5:13:26


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Характерные ошибки при решении упражнений. Задача 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

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.