Базы данных. Лекции БД. Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5

• Название: Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5
• Анкор: Базы данных
• Дата: 05.01.2022
• Размер: 1.07 Mb.
• Имя файла: Лекции БД.doc
• Тип: Лекция; #324711
• страница: 12 из 24

Лекция 9.

8.6.4.Предложение GROUP BY

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и т.д.). Если это предложение отсутствует и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют запросу. В противном случае все столбцы списка SELECT, не вошедшие в агрегатную функцию, должны быть «сгруппированы» с помощью предложения GROUP BY. Все выходные строки запроса, которые сгруппированы по равенству значений столбцов, образуют единую группу (для GROUP BY все значения NULL трактуются как равные). Агрегатная функция будет применяться к каждой из таких групп. Рассмотрим простой пример:
SELECT snum, AVG(amount), MAX(amount)

FROM S

RGOUP BY snum;
В этом запросе предполагается, что таблица S имеет, как минимум, два столбца (snum, amount) и, скорее всего, содержит записи с платежными транзакциями. Эта таблица имеет одну строку на каждую транзакцию, содержащую имя продавца и сумму (если это базовая таблица, а не представление, то она должна иметь первичный ключ). Все транзакции с одинаковыми значениями snum (имя продавца) образуют группу, и на выходе SELECT вычисляются максимальные и средние значения для каждой группы. Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти цифры для каждой конкретной даты. Для этого нужно было бы задать дату в качестве группирующего столбца, и тогда агрегатные функции будут вычисляться для каждой комбинации значений (продавец-дата). Такую группировку можно реализовать с помощью соединения, при записи которого необходимо использовать имена таблиц или связанные префиксы с именами столбцов (чтобы избежать неоднозначности).

8.6.5.Предложение HAVING

Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк декартова произведения, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE.

UNION, INTERSECT, EXCEPT и CORRESPONDING

Эти операторы используют в качестве аргумента весь оператор SELECT (запрос), исключая предложение ORDER BY, в соответствии со следующим синтаксисом:

Запрос А {UNION | INTERSECT | EXCEPT} [ALL] запрос В
Предложение TABLE является традиционным способом записи простого запроса:
TABLE S
Этот оператор эквивалентен следующему оператору:
SELECT * FROM S
Кроме того, можно использовать конструктор значений таблиц (смотри ниже) для построения таблиц в памяти. Выходные столбцы каждого из запросов должны быть сравнимы между собой с учетом указанного порядка сравнения: первый столбец запроса А с первым столбцом запроса В, второй со вторым и т.д., - так как эти столбцы должны быть слиты.

Предложение UNION включает все выходные строки каждого из запросов. Если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае оставляется только одна строка. Если определено предложение EXCEPT, то из результата исключаются все строки второго запроса, а в выходных данных остается только одна из дублирующих строк. Если определено EXCEPT ALL, то число дублированных строк запроса В вычитается из числа дублированных строк запроса А, и полученное число строк появляется на выходе, если, конечно, оно не равно 0. Если определено INTERSECT, то все строки из запросов А и В появляются на выходе в одном экземпляре. Если определено INTERSECT ALL, то дублированные строки будут появляться на выходе то число раз, которое является минимумом числа их дублей в запросах А и В.

Заметим, что можно связывать вместе любое число запросов, используя описанные выше операторы. Кроме того, с помощью скобок можно задавать порядок обработки.

Оператор CORRESPONDING ограничивает операции со столбцами, имеющими одинаковые имена и сравнимые типы данных, в каждом из предложений SELECT обоих запросов. (конечно, это можно сделать с помощью предложения AS, которое назначает имена, или с помощью выражения CAST для преобразования типа данных). Если задан список столбцов, то они должны иметь общие имена и типы данных. Если список не задан, используются только столбцы с совпадающими именами и типами данных. В любом случае на выходе будут только такие столбцы. Они будут выводиться в порядке, заданном в списке, если такой список не задан, то в порядке их появления в первом запросе. При использовании оператора CORRESPONDING отпадает необходимость в том, чтобы каждый список SELECT имел одинаковое число столбцов с совпадающими типами данных, расположенными в одном и том же порядке. В противном случае такое условие является обязательным.

8.6.6.Предложение ORDER BY

И, наконец, предложение ORDER BY используется для сортировки выходных данных. Строки сортируются в соответствии со значениями столбцов, указанных в списке. Первый столбец имеет наивысший приоритет, второй столбец задает порядок сортировки дублируемых значений первого столбца, третий столбец вступает в действие, если совпадают значения во втором столбце, и т.д. можно задать параметр сортировки ASC (по возрастанию, используется по умолчанию) или DESC (по убыванию) отдельно для каждого столбца. Вместо имен столбцов можно указывать целые числа. Эти числа указывают на местоположение столбца в выходных данных, так что 1 будет указывать на первый столбец, а 5 – на пятый столбец, и т.д. если выходные данные не имеют имен, то будут использоваться номера.

Пример

Следующий оператор определяет общую и среднюю сумму продаж для каждого продавца за каждый день, исключая дни, когда общая сумма продаж продавца меньше $100.00.
SELECT snum, SUM(amount), odate

FROM Orders

WHERE odate BETWEEN ’10-01-2000’ AND ’10-01-2001’

GROUP BY snum, odate

HAVING SUM(amount)>100.00;

8.6.7.Агрегатные функции

Эти функции вычисляют единственное значение из множества значений.

Синтаксис:
Агрегатная функция::=

{ COUNT(*)} |

{{AVG

| SUM

| MAX

| MIN

| COUNT }

([DISTINCT | ALL] выражение для вычисления значения)}
Существует два типа функции COUNT. Первый тип использует символ «*». В этом случае функция пересчитывает количество строк, получаемых по запросу. Здесь не учитываются отдельные значения столбцов и результат не зависит от того, указан ли параметр DISTINCT или имеются значения NULL. Второй тип функции COUNT, как и все другие агрегатные функции, автоматически отбрасывает (игнорирует) значения NULL, хотя при обнаружении токовых условие завершения принимает определенное значение, и может появиться предупреждающее сообщение.

Если для функции определен параметр DISTINCT, каждое из одинаковых значений будет участвовать в вычислениях только один раз. Иначе одинаковые значения будут обрабатываться также, как и все остальные независимо от того, определен параметр ALL или нет (по умолчанию используется ALL).

Заметим, что функции AVG (среднее арифметическое значение) и SUM (сумма значений) нельзя применять для типов данных CHARACTER STRING (строка символов), BIT STRING (битовая строка) и DATETIME (дата и время). Ее можно применять только для INTERVAL (интервал) и NUMERIC (цифровой). Функции MAX (наибольшее значение) и MIN (наименьшее значение) могут использоваться для любых типов данных.

Существует несколько определенных правил выполнения этих функций:

• 
  Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других – значений NULL.
  

• 
  В выражениях для вычисления значения не допускается использование агрегатных функций или подзапросов. Здесь следует заметить, что вложение агрегатных функций (функция от функции) может произойти, например, при определении средних значений внутри представления, если затем во внешнем запросе к нему (а не в нем самом) потребовалось определить максимум.
  

• 
  Выражение для вычисления значения может содержать внешнюю ссылку, при этом оно не должно иметь ссылки ни на какие другие столбцы. (Под внешней ссылкой понимается ссылка в рамках подзапроса к значениям в основном запросе). Агрегатная функция, использующая внешнюю ссылку, может содержаться либо в предложении SELECT, либо в подзапросе в предложении HAVING. В последнем случае выражение должно входить в подзапрос, определенный в предложении HAVING (а не WHERE), а ссылка на таблицу должна содержаться в запросе того же уровня вложения, какой определен для предложения HAVING.
  
• 
  Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
  
• 
  Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка.
  

Если агрегатные функции, включенные в предложение SELECT запроса, не содержат предложения GROUP BY, они исполняются над всеми результирующими строками для запроса. Если запрос содержит предложение GROUP BY, каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY, составляют группу, и агрегатные функции выполняются для каждой группы отдельно. Предложение HAVING фильтрует группы на основании результатов агрегатных функций, а не по конкретным значениям столбца.

Рассмотрим ряд примеров. Начнем с примера: получить цвета и города для деталей «не из Парижа» с весом больше десяти. На языке SQL данный запрос можно сформулировать следующим образом:
SELECT P.COLOR, P.CITY

FROM P

WHERE P.CITY <> ‘Paris’

And P.WEIGHT > 10;
Обычные операторы сравнения записываются в SQL так: = , <>, <, >, <=, >=.

Важно отметить, что такой запрос будет возвращать идентичные строки, если такие имеются. Язык SQL не удаляет лишних дублирующих строк из результата оператора SELECT, пока пользователь явно не потребует этого с помощью ключевого слова DISTINCT, как показано ниже:
SELECT DISTINCT P.COLOR, P.CITY

FROM P

WHERE P.CITY <> ‘Paris’

And P.WEIGHT > 10;

Заметим, что в этом примере можно было бы обойтись и без уточнителей «Р.». Согласно общему правилу относительно уточнения имени в SQL неуточненные имена допускаются, если они не вызывают неоднозначности.

Важно также отметить, что последовательность строк в данной результирующей таблице непредсказуема, пока пользователь не задаст некоторую определенную последовательность, как в этом примере:
SELECT DISTINCT P.COLOR, P.CITY

FROM P

WHERE P.CITY <> ‘Paris’

And P.WEIGHT > 10

ORDER BY CITY DESC;
В общем случае инструкция ORDER BY записывается так:
ORDER BY order-item-commalist
Здесь список commalist не должен быть пустым и каждый элемент списка должен содержать неуточненное имя столбца, за которым (необязательно) следуют ключевые слова ASC или DESC (возрастающий или убывающий порядок соответственно); по умолчанию принимается ASC. Следует заметить, что некоторые конкретные реализации языка SQL допускают использование уточненных имен в инструкции ORDER BY (например, в MS Access).
Рассмотрим пример операции расширения: для всех деталей получить номер детали и ее вес в граммах.
SELECT P.P#, P.WEIGHT*454 AS GMWT

FROM P;
Спецификация AS GMWT вводит соответствующее имя результирующего столбца. Таким образом, два столбца результирующей таблицы будут называться P# и GMWT соответственно. Если бы спецификация AS GMWT была опущена, то соответствующий столбец был бы фактически безымянным. Обратите внимание, что хотя SQL на самом деле не требует от пользователя задавать имя результирующего столбца в таких случаях, но мы всегда будем это делать.

Заметим, что псевдоним (GMWT) можно использовать не только для именования полей расширения, но и для обычных полей. Псевдоним представляет имя, которое появится в результирующем отношении, например,
SELECT title AS название, length AS продолжительность

FROM фильмы

WHERE студия = ‘Disney’ AND год = 1999;
В качестве элемента предложения SELECT допускается использовать константу. Это может показаться бессмысленным, но по крайней мере одно из применений константы состоит в том, чтобы вносить полезные слова в вывод SQL, появляющийся на экране. Запрос
SELECT title, length*0.016667 AS продолжительность, ‘час’ AS вЧасах

FROM фильмы

WHERE студия = ‘Disney’ AND год = 1999;
Порождает кортежи типа

Title	Продолжительность	ВЧасах
Pretty Woman	1.98334	Час

Включение всех столбцов: получить полную информацию обо всех поставщиках.
SELECT * -- или ”SELECT S.*” – т.е. «*» можно уточнить)

FROM S;
Результатом будет копия всей таблицы S; звездочка – это сокращение для списка всех имен столбцов в таблице (или таблицах), на которую делается ссылка в инструкции FROM, в порядке слева направо, как эти столбцы определены в таблице (таблицах). Обратите внимание на комментарий в этом примере (комментарии в SQL начинаются с двойного дефиса и заканчиваются символом новой строки).

Отметим, что звездочку очень удобно использовать в интерактивных запросах, так как вводится меньше символов. Однако использование звездочки во встроенном SQL потенциально опасно, так как смысл звездочки (*) может измениться (например, в случае, если из таблицы был убран или в таблицу был добавлен столбец с помощью оператора ALTER TABLE).

Замечание. Согласно стандарту SQL/92 выражение SELECT * FROM T можно упростить до TABLE T.