 |
Раздел 5. Группировка и агрегатные функции
|
|
|
|
Рассмотрим таблицу Students со сведениями о студентах, годе обучения и названии группы.
| NAME | YEAR | CLASS |
| John | ||
| Pole | ||
| Mike | ||
| Dane | ||
| Fred | ||
| Ken | ||
| George | ||
| Polie | ||
| Jane | ||
| I | ||
| Ada | ||
| Cate | ||
| Tom |
К результирующему множеству, специфицированному SELECT-запросом, можно применить операцию группировки, записываемой при помощи конструкции GROUP BY список выражений.
При выполнении группировки записи из результирующего множества объединяются на группы по совпадающим значениям выражений из списка, затем из каждой группы удаляются строки-дубликаты. Группировка может выполняться по одному полю (запрос 5.1) и по группе полей (запрос 5.2)
SELECT year FROM Students GROUP BY year; (5.1)
| YEAR |
SELECT year, class FROM Students GROUP BY year, class;(5.2)
| YEAR | CLASS |
Подобные запросы эквивалентны запросам с использованием DISTINCT:
SELECT DISTINCT year, class FROM Students; (5.3)
При использовании группировки каждое выражения после SELECT обязательно должны присутствовать и в списке выражений после GROUP BY. Однако выражения, по которым выполняется группировка, не обязательно должны присутствовать в группе выражений в списке SELECT. В приводимых далее двух запросах первый является некорректным по указанной выше причине, а второй – правильным, и его нельзя переписать с использованием DISTINCT:
SELECT year, class FROM Students GROUP BY year; (5.4)
SELECT year FROM Students GROUP BY year, class; (5.5)
| YEAR |
В запросах с группировкой можно использовать агрегатные функции; наиболее распространенные агрегатные функции приведены в таблице 3.
|
|
|
Таблица 3. Агрегатные функции
| COUNT(выражение) | Количество строк в группе |
| MIN(выражение) | минимальное значение выражения среди строк в группе |
| MAX(выражение) | максимальное значение выражения среди строк в группе |
| SUM(выражение) | сумма всех значений выражений в группе |
Агрегатные функции вычисляются по всем записям каждой группы после группировки строк результирующего множества и до удаления дубликатов из групп. Агрегатные функции – единственный вид выражений, которые могут использоваться в списке SELECT без их указания в списке GROUP BY, и они не могут использоваться в выражениях для группировки в списке выражений GROUP BY.
Рассмотрим практическое использование агрегатных функций. Следующий пример вычисляет, сколько студентов учится на каждом курсе.
SELECT year, COUNT(*) FROM Students GROUP BY year; (5.6)
| YEAR | COUNT(*) |
В первом поле выборки находится значение выражения, по которым была выполнена группировка, а во втором – количество строк в этой группе до удаления дубликатов. Следующий запрос вычисляет, сколько студентов учится в каждой группе каждого курса:
SELECT year, class, COUNT(*) FROM Students
GROUP BY year, class; (5.7)
| YEAR | CLASS | COUNT(*) |
Функции MIN и MAX целесообразно использовать применительно к выражениям, которые не использовались при группировке. Следующий запрос определяет для каждого клиента цены самого дорогого и самого дешевого из заказов.
SELECT customer_name, MAX(price*quantity) "самый дорогой",
MIN(price*quantity) "самый дешевый"
FROM acceptedorders
GROUP BY customer_name (5.8)
| CUSTOMER_NAME | самый дорогой | самый дешевый |
| Mr.Pundleberry | 199.99 | |
| Mr.O'Raily | 16.5 | 12.7 |
| Ms.Magpie | 12.5 |
Агрегатные функции могут использоваться и в запросах без группировки; запрос 5.9 определяет цены самых дорогих и самых дешевых заказов по всем заказам.
|
|
|
SELECT MAX(price*quantity) "самый дорогой",
MIN(price*quantity) "самый дешевый"
FROM acceptedorders (5.9)
| Самый дорогой | самый дешевый |
| 12.5 |
В выражениях группировки можно использовать не только имена полей, но и любые корректные выражения, включая неагрегатные функции: в запросе 5.10 группировка осуществляется по первому символу поля name, а в запросе 5.11 – по значению конкатенации двух числовых полей, преобразованных в строки.
SELECT SUBSTR(name, 1, 1), COUNT(*)
FROM Students
GROUP BY SUBSTR(name, 1, 1); (5.10)
| SUBSTR(NAME,1,1) | COUNT(*) |
| P | |
| K | |
| I | |
| M | |
| D | |
| J | |
| C | |
| A | |
| T | |
| G | |
| F |
SELECT TO_CHAR(year) || '.' || TO_CHAR(class) "Группа",
COUNT(*) "Количество"
FROM Students
GROUP BY TO_CHAR(year) ||'.' || TO_CHAR(class); (5.11)
| Группа | Количество |
| 3.2 | |
| 3.3 | |
| 2.1 | |
| 3.1 | |
| 1.1 | |
| 1.2 | |
| 2.2 | |
| 2.3 |
Следует обратить внимание, что при использовании группировки значения полей исходных записей не могут присутствовать в значении запроса, если они не используются в выражениях группировке. Например, в последнем запросе (5.11) невозможно вывести имена отдельных студентов.
К результирующему множеству после группировки может быть применена повторная селекция при помощи следующей конструкции:
HAVING логическое выражение
Используемое логическое выражение по структуре аналогично выражениям селекции в конструкции WHERE, однако может содержать агрегатные функции. Следующий запрос 5.12 определяет группы первого курса, в которых учатся более одного студента:
SELECT year, class, COUNT(*)
FROM Students WHERE year = 1
GROUP BY year, class HAVING COUNT(*)>1; (5.12)
| YEAR | CLASS | COUNT(*) |
Раздел 6. Пустые значения
В полях записей может храниться специальное значение, называемое пустым значением, которое записывается при помощи служебного слова NULL. Пустые значения могут использоваться, например, когда настоящее значение некоторого поля неизвестно. Следует различать пустую строку “” и пустое значение.
Манипулирование с полями, в которых могут находиться пустые значения, имеет некоторые особенности. Рассмотрим таблицу CourseResult, в которой находятся сведения об оценках студентов, полученные за соответствующие учебные курсы:
CREATE TABLE CourseResult
(student CHAR(20),
course CHAR(20),
|
|
|
result int);
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Polie | Graphics | - |
| Jane | OS | |
| Jane | DBMS | |
| Bill | DBMS | - |
В настоящее время оценки за курс Graphics студентки Polie и курс DBMS студента Bill отсутствуют (например, еще не выставлены), что в базе данных представлено значением NULL в соответствующих строках.
При упорядочивании значение NULL считается максимальным:
SELECT * FROM CourseResult ORDER BY result DESC; (6.1)
| STUDENT | COURSE | RESULT |
| Bill | DBMS | - |
| Polie | Graphics | - |
| Bill | C Programming | |
| Jane | OS | |
| Jane | DBMS |
Значение операций сравнения пустых значений в логических выражениях имеет результат, условно обозначаемый при помощи слова UNKNOWN, приравниваемое к логическому значению FALSE (существует одно исключение). Обратите внимание, что слово UNKNOWN не является ни служебным словом языка SQL, в отличие от слова NULL, ни литеральной константой; это слово нельзя использовать в выражениях SQL. Например, значениями двух следующие запросов будут пустые множества:
SELECT * FROM CourseResult WHERE result = NULL; (6.2)
SELECT * FROM CourseResult WHERE result!= NULL; (6.3)
Для корректного сравнения с пустым значением существует специальные предикаты IS NULL и IS NOT NULL, которые используются следующим образом:
SELECT * FROM CourseResult WHERE result IS NULL; (6.4)
| STUDENT | COURSE | RESULT |
| Polie | Graphics | - |
| Bill | DBMS | - |
SELECT * FROM CourseResult WHERE result IS NOT NULL; (6.5)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Jane | OS | |
| Jane | DBMS |
В логической операции AND значение UNKNOWN интерпретируется как FALSE, а в операции OR – игнорируются, например:
SELECT * FROM CourseResult
WHERE student = 'Bill' AND result!= NULL; (6.6)
no data found
SELECT * FROM CourseResult
WHERE student = 'Bill' OR result!= NULL; (6.7)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Bill | DBMS | - |
Значениями арифметических операций и неагрегатных функций с пустыми значениями также будут пустые значения (кроме некоторых специфических функций):
SELECT result, result + 0.0 FROM CourseResult; (6.8)
| RESULT | RESULT+0.0 |
| - | - |
| - | - |
Однако при вычислении агрегатных функций пустые значения игнорируются, если в качестве параметра используется имя поля, а не символ “*”:
SELECT COUNT(*), COUNT(result) FROM CourseResult; (6.9)
| COUNT(*) | COUNT(RESULT) |
SELECT SUM(result) FROM CourseResult; (6.10)
|
|
|
| SUM(RESULT) |
При группировке по некоторому полю пустые значения в этом поле так же не игнорируется:
SELECT result, COUNT(result), COUNT(*)
FROM CourseResult GROUP BY result; (6.11)
| RESULT | COUNT(RESULT) | COUNT(*) |
| - | ||
|
|
|
