Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Раздел 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(*)
-    
     
     
Поделиться:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...