5 4 7 Користування фрази group by
Пояснення. З концептуальної точки зору, оператор GROUP BY (групувати по) перекомпоновувати таблицю, представлену фразою FROM, в розділи або групи таким чином, щоб у кожній групі всі рядки мали одне і те ж значення поля, зазначеного у фразі GROUP BY. Це, звичайно, не означає, що таблиця фізично перекомпоновувати в базі даних. У розглянутому прикладі рядки таблиці SP групуються таким чином, що в одній групі містяться всі рядки для деталі Р1, в іншій-все рядки для деталі Р2 і т. Д. Далі, до кожної групи перекомпонувати таблиці, а не до кожного рядка вихідної таблиці застосовується фраза SELECT. Кожен вираз у фразі SELECT має приймати єдине значення для групи, т. Е. Воно може бути або самим полем, зазначеним у фразі GROUP BY, або арифметичним виразом, що включає це поле, або константою, або такою функцією, як SUM, яка оперує всіма значеннями даного поля в групі і зводить ці значення до єдиного значенням.
Рядки таблиці можна групувати по будь-якій комбінації її полів. У розділі 5.6 наведено приклад, який ілюструє групування більш ніж по одному полю. Зауважимо, що фраза GROUP BY не припускав ORDER BY (упорядкувати по). Щоб гарантувати впорядкування результату цього прикладу за номерами деталей, слід уточняти фразу ORDER BY НОМЕР-ДЕТАЛІ після фрази GROUP BY. Якщо поле, за значеннями якого здійснюється групування, містить будь-які невизначені значення, то кожне з них породжує окрему групу.
5.4.8. ВИКОРИСТАННЯ ФРАЗИ WHERE з GROUP BY
Видати для кожної поставляти деталі її номер і загальний обсяг поставок, за винятком поставок постачальника S1:
SELECT НОМЕР_ДЕТАЛІ, SUM (КІЛЬКІСТЬ)
WHERE НОМЕР_ПОСТАВЩІКА = 'S1'
GROUP BY НОМЕР_ДЕТАЛІ;
Рядки, що не відповідають фразі WHERE, виключаються до того, як буде здійснюватися якесь групування.
5.4.9. ВИКОРИСТАННЯ HAVING
Видати номери деталей для всіх деталей, що поставляються більш ніж одним постачальником (той же приклад, що і в 5.2.5).
GROUP BY НОМЕР_ДЕТАЛІ
HAVING COUNT (*)> 1;
Фраза HAVING грає таку ж роль для груп, що і фраза WHERE для рядків. (Звичайно, якщо специфікована фраза HAVING, то повинна бути специфікована і фраза GROUP BY.) Іншими словами, HAVING використовується для того, щоб виключати групи, точно так же, як WHERE використовується для виключення рядків. Вираз у фразі HAVING повинне приймати єдине значення для групи.
У прикладі 5.2.5 вже було показано, що цей запит може бути сформульований без GROUP BY (і без HAVING) з використанням корельованого підзапиту. Однак приклад 5.2.5 насправді заснований на дещо іншому сприйнятті логіки, пов'язаної з визначенням відповіді на це питання. Можна також сформулювати запит, використовуючи по суті ту ж логіку, що і в варіанті GROUP BY / HAVING, але без явного використання фраз GROUP BY і HAVING взагалі:
SELECТ DISTINCT НОМЕР_ДЕТАЛІ
WHERE SPY.НОМЕР_ДЕТАЛІ = SPX.HOMEP_ДЕТАЛІ);
Наступний варіант, в якому замість SPX використовується таблиця Р, є, ймовірно, більш ясним:
(SELECT COUNT (НОМЕР_ПОСТАВЩІКА)
WHERE НОМЕР_ДЕТАЛІ = P. НОМЕР_ДЕТАЛІ);
Ще одне формулювання пов'язана з використанням EXISTS:
WHERE SPX. НОМЕР_ДЕТАЛІ = Р. НОМЕР_ДЕТАЛІ
WHERE SPY. НОМЕР_ДЕТАЛІ = Р. НОМЕР_ДЕТАЛІ
AND SPY. НОМЕР_ПОСТАВЩІК = SPX. номер_
Всі ці альтернативні варіанти є в деякому відношенні кращими в порівнянні з варіантом GROUP BY / HAVING в зв'язку з тим, що вони принаймні логічно більш зрозумілі і, зокрема, не вимагають цих додаткових мовних конструкцій. З початкової формулювання завдання на природній мові - «Видати номери деталей для всіх деталей, що поставляються більш ніж одним постачальником» - без сумніву, не ясно, що групування само по собі-це те, що необхідно для відповіді на дане питання, і в ньому, дійсно, немає необхідності. Чи не є також безпосередньо очевидним, що необхідна умова HAVING, а не умова WHERE. Варіант GROUP-BY / HAVING більш схожий на процедурне припис для вирішення завдання, ніж просто на ясну логічну формулювання її істоти. З іншого боку, не можна спростувати той факт, що варіант GROUP-BY / HAVING найбільш лаконічний. Далі, в свою чергу є деякі завдання такого ж загального характеру, для яких GROUP BY і HAVING просто неадекватні, в силу чого слід використовувати один з альтернативних підходів. Приклад такого завдання представлений у вправі 5.24.
Нарешті, конструкції GROUP BY властиво серйозне обмеження - вона працює тільки на одному рівні. Неможливо розбити кожну з цих груп на групи нижчого рівня і т. Д. А потім застосувати деяку стандартну функцію, наприклад SUM або AVG на кожному рівні даної групи 16.
5.5. ОБ'ЄДНАННЯ
Об'єднанням двох множин називається безліч всіх елементів, що належать якомусь одному або обом вихідним множинам. Оскільки ставлення-це безліч (безліч рядків), можна побудувати об'єднання двох відносин. Результатом буде безліч, що складається з усіх рядків, що входять в якесь одне або в обидва первинних відносини. Якщо, проте, цей результат сам по собі повинен бути іншим ставленням, а не просто різнорідної сумішшю рядків, то два вихідних відносини повинні бути сумісними з об'єднання. Нестрого кажучи, рядки в обох відносинах повинні бути однієї і тієї ж «форми». Що стосується SQL, то дві, таблиці сумісні з об'єднання (і до них може бути застосований оператор UNION) тоді і тільки тоді, коли:
а) вони мають однакове число стовпців, наприклад, m;
б) для всіх i (i = 1,2. m) i-й стовпець першої таблиці і i-й стовпець другий таблиці мають в точності однаковий тип даних;
- якщо тип даних-DECIMAL (p, q), то р повинно бути;
однаковим для обох стовпців і q повинно бути однаковим для обох стовпців;
- якщо тип даних-• CHAR (n), то n повинно бути. однаковим для обох стовпців;
- якщо тип даних-VARCHAR; (n), то n повинно бути однаковим для обох стовпців;
- якщо NOT NULL специфіковане для будь-якого з цих стовпців, то така ж специфікацій повинна бути для іншого стовпця.
5.5.1. ЗАПИТ, вимагає використання UNION
Видати номери деталей, які мають вагу понад 16 фунтів або поставляються постачальником S2 (або те й інше).
Пояснення. Фрази пропозиції SELECT застосовуються в такому порядку, в якому вони записані, за винятком самої фрази SELECT, яка застосовується між фразами HAVING і ORDER BY, якщо вони є. В даному прикладі, отже, можна уявити собі, що результат будується наступним чином.
1. FROM. В результаті обробки фрази FROM створюється нова таблиця, яка є декартових твором таблиць Р і SP.
2. WHERE. З результату кроку 1 виключаються всі рядки, що не відповідають фразі WHERE. В даному прикладі виключаються рядки, що не задовольняють предикату:
Р.НОМЕР-ДЕТАЛІ = SP.HOMEP_ДETAЛІ AND Р.ЦВЕТ IN ( 'Червоний', 'Блакитні') AND SP.KOAH5ECTBO> 200.
3. GROUP BY. Результат кроку 2 групується за значеннями поля (полів), зазначеного у фразі GROUP BY. У нашому прикладі це поля Р.НОМЕР-ДЕТАЛІ, Р.ВЕС і Р.ЦВЕТ. Зауваження. Теоретично в якості поля групування було б достатньо використовувати тільки Р.НОМЕР-ДЕТАЛІ, так як Р.ВЕС і Р.ЦВЕТ однозначно визначаються номером деталі. Однак система DB2 не обізнаний про це останньому факті, і якщо Р.ВЕС і Р.ЦВЕТ будуть опущені у фразі GROUP BY, виникне умова помилки, оскільки вони включені у фразу SELECT. Основна проблема полягає тут в тому, що система DB2 не підтримує первинних ключів. Див. Додаток А.
4. HAVING. Групи, які не відповідають умові SUM (КІЛЬКІСТЬ)> 350, виключаються з результату, отриманого на кроці 3.
5. SELECT. Кожна група, отримана на кроці, 4, в такий спосіб генерує єдиний рядок для результату. По-перше, з групи виділяються номер деталі, вага, колір і максимальний обсяг поставки. По-друге, вага перетворюється в грами. По-третє, в відповідні місця отриманого рядка вставляються дві рядкові константи 'вага в грамах =' і 'максимальний обсяг поставки ='.
6. ORDER BY. Результат кроку 5 упорядковується відповідно до специфікації фрази ORDER BY для отримання остаточного результату.
Звичайно, наведений вище запит досить складний, але уявімо собі, яку він виконує роботу. Звичайна програма, наприклад, в мові КОБОЛ, яка виконує ту ж саму роботу, цілком могла б скласти дев'ять сторінок в порівнянні тільки з дев'ятьма рядками, наведеними вище. При цьому робота, необхідна для того, щоб ця програма стала чинною, значно більше, ніж це необхідно для формулювання наведеного варіанту запиту на мові SQL. Більшість запитів на практиці буде, звичайно, у всякому разі значно простіше в порівнянні з ним.
ВПРАВИ
Як і в попередньому розділі. Всі наступні вправи засновані на базі даних постачальників-деталей-виробів (див. Вправи в розділі 3). У кожному з них потрібно записати пропозицію SELECT для зазначеного запиту, за винятком вправ 15-18 і 26. Для зручності повторимо тут структуру розглянутої бази даних:
S (НОМЕР_ПОСТАВЩІКА, ПРІЗВИЩЕ, СТАН, МІСТО)
Р (НОМЕР_ДЕТАЛІ, НАЗВА, КОЛІР, ВЕС, МІСТО)
J (НОМЕР_ІЗДЕЛІЯ, НАЗВА, МІСТО)
SPJ (НОМЕР_ПОСТАВЩІКА, НОМЕР_ДЕТАЛІ, НОМЕР_ІЗДЕЛІЯ,
У кожному розділі вправи впорядковані приблизно в порядку зростання їх складності. Необхідно спробувати виконати принаймні деякі з легких вправ в кожній групі. Вправи 12-18 є досить важкими.
5.1. Видати назви виробів, для яких поставляються деталі постачальником S1.
5.2. Видати кольору деталей, що поставляються постачальником S1.
5.3. Видати номери деталей, що поставляються для будь-якого вироби в Лондоні.
5.4. Видати номери виробів, що використовують принаймні одну деталь, що поставляється постачальником S1.
5.5. Видати номери постачальників, що поставляють принаймні одну деталь, що поставляється по крайней мере одним постачальником, який поставляє принаймні одну червону деталь.
5.6. Видати номери постачальників, які мають статок менше, ніж у постачальника S1.
5.7. Видати номери постачальників, що поставляють деталі для будь-якого вироби з деталлю Р1 в кількості, більшій, ніж середній обсяг поставок деталі Р1 для цього виробу. Примітка. У цій вправі потрібно використовувати стандартну функцію AVG.
5.8. Повторіть вправу 5.3 і використовуйте у Вашому рішенні EXISTS.
5.9. Повторіть вправу 5.4 і використовуйте у Вашому рішенні EXISTS.
5.10. Видати номери виробів, для яких не постачає будь-якої червоної деталі постачальник з Лондона.
5.11. Видати номери виробів, для яких деталі повністю постачає постачальник S1.
5.12. Видати номери деталей, що поставляються для всіх виробів в Лондон.
5.13. Видати номери постачальників, що поставляють одну і ту ж деталь для всіх виробів.
5.14. Видати номери виробів, для яких поставляються принаймні всі деталі, наявні у постачальника S1.
Для наступних чотирьох вправ (5.15-5.18) перетворіть наведене пропозицію SELECT мови SQL назад в його еквівалент на природній мові.
5.15. SELECT DISTINCT НОМЕР_ІЗДЕЛІЯ
WHERE NOT EXISTS
WHERE SPJY.НОМЕР_ІЗДЕЛІЯ = SPJX. номер_
pagination of the final published document will be different. article appears, and the publisher. The References heading should. annotated C ++ reference manual. Reading, Mass: Addison -WesleyPublishingCompany. Inc. Kunz, J. C. M. J. Clayton, and.
20Nar49; AF11777. Helene Weigel Brecht (W), Barbara Brecht. PublishingCompany. employer for hire. 0 251pr50; 11150R60. West PublishingCompany E The Lawyers Co-operative PublishingCompany. Jr. 0 17NovV9; 13R473. Addison Gallery of American Art.
and P.B. Galvin - Operating System Concepts - Addison -WesleyPublishingCompany. A.S. Godbole - Operating Systems - Tata McGraw. A First Course in Mathematical Statistics, AddisonWesleyPublishingCompany. 1 973 4. B.L.Van der Waerden, Mathematical.
u Penulis Sears, Francis W. Penerbit California. Addison -WesleyPublishingCompany Tahun 1 982 Jumlah 1 eksemplar Subyek. f Penulis Feynman, Richard P. Penerbit California. Addison -WesleyPublishingCompany Tahun тисяча дев'ятсот шістьдесят три Jumlah 4 eksemplar Subyek.