Ключевые слова ASC, DESC определяют направление сортировки: по возрастанию, по убыванию соответственно и действует только на один столбец.
В примере упорядочим идентификационные номера по цене и затем по издательствам, причем по цене упорядочивание проводится по убыванию, а по издательствам по возрастанию.
SELECT PUB_ID, PRICE, TITLE_ID,
FROM TITLE
ORDER BY PRICE DESC, PUB_ID
При выводе возможна сортировка выражений. Допустим, необходимо результаты отсортировать по издателям, затем по продажам. В этом случае в предложении ORDER BY ставится номер позиции (натуральное число без знака), соответствующий выражению в списке выбора. В результате запрос будет выглядеть следующим образом:
SELECT PUB_ID, PRICE * YTD_SALES, PRICE, TITLE_ID
FROM TITLE
ORDER BY PUB_ID, 2
| pub_id | price | title_id |
В список сортировки можно добавить предложение DESC, тогда поле продаж будет отсортировано по убыванию.
5.1.3. Агрегирующие функции
Агрегирующая функция рассматривает множество строк таблицы и выдает только одно значение. Приведем список агрегирующих функций.
SUM([DISTINCT] выражение) - сумма (различных) выражений.
AVG([DISTINCT] выражение) - средняя величина (различных) значений.
COUNT ( [DISTINCT] выражение) - количество (различных) ненулевых значений.
COUNT (*) - полное количество выбранных строк, включая пустые строки и дубликаты.
МАХ (выражение) - максимальное значение.
MIN (выражение) - минимальные значение.
При использовании агрегирующих функций необходимо учитывать следующее:
их можно записывать в списке выбора или в предложении HAWING;
нельзя применять в предложении WHERE;
перед вычислением значения функции все неопределенные значения аргумента исключаются;
если определено предложение DISTINCT, то повторяющиеся значения аргумента в подсчете значения функции не участвуют;
если аргумент пуст, т.е. содержит неопределенные значения, то функция COUNT всегда возвращает значение 0, а другие агрегирующие функции - неопределенное значение;
функции SUM и AVG работают только с аргументами числовых типов;
функции COUNT, MAX, MIN могут использоваться с аргументами любых типов.
Приведем несколько примеров использования функций.
1. SELECT YTD_SALES
FROM TITLE
2. SELECT SUM (YTD_SALES)
FROM TITLE
В первом случае соответствующие значения выдаются виде таблицы, т.е. выдается столько строк, сколько их есть в таблице TITLE. Если использовать агрегирующую функцию SUM, то в результате получим общую сумму, и это будет одно значение.
Одно выдаваемое значение можно пояснить, например, при записи следующего запроса:
SELECT 'ИТОГ ', SUM (YTD_SALES)
FROM TITLE
на экране можно увидеть:
Итог: 97445
Приведем примеры использования функции COUNT.
Допустим, необходимо узнать количество авторов, сотрудничающих с издательством. Запрос будет выглядеть так:
SELECT COUNT (AU_FNAME)
FROM AUTORS
В том случае, когда необходимо узнать количество разных фамилий авторов, сотрудничающих с издательством, запрос запишется с использованием предложения DISTINCT:
SELECT COUNT (DISTINCT AU_FNAME)
FROM AUTORS
5.1.4. Группировка данных и построение отчетов
В менеджменте часто требуется статистическая информация о каждой группе в множестве групп. Для этого используется предложение GROUP BY, которое разделяет таблицу на наборы. GROUP BY неразрывно связано с агрегирующими функциями и предложением НAVING.
Пример.
SELECT AVG (PRICE)
FROM TITLE
SELECT TYPE, AVG (PRICE)
FROM TITLE
GROUP BY TYPE
В результате первого запроса получим среднюю стоимость книг. Это будет единственное значение. Во втором запросе книги группируются по типу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает поле TYPE. Затем фраза SELECT применяется к каждой группе, т.е. внутри каждой группы считается средняя цена. Для каждой группы SELECT выводит TYPE и среднее значение по типу.
| TYPE | |
| спорт | 86.78 |
| Домашнее хозяйство | 190.43 |
Как правило, в большинстве реализаций SQL элемент из списка GROUP BY должен присутствовать в списке выбора SELECT.
Путем сортировки одновременно по нескольким элементам можно создавать группы внутри других групп.
Пусть необходимо подсчитать количество книг по каждому типу, выпущенному каждым издательством. Здесь группировка производится сначала по издательству, затем по типу.
SELECT PUB_ID, TYPE, COUNT (TYPE)
FROM TITLE
GROUP BY PUB_ID, TYPE
Результат:
Pub_id type
0732 спорт 5
0732 бизнес 4
0877 бизнес 2
0877 спорт 6
0877 искусство 4
Если выполнить предложение GROUP BY без агрегирующих функций, то оно будет напоминать предложение DISTINCT, т.е. разделять таблицу на группы и из группы брать одно значение. Фактически предложение GROUP BY и агрегирующие функции были созданы друг для друга.
Допустим, необходимо найти средние затраты и сумму доходов от продаж по каждому типу книг:
SELECT TYRE, AVG(ADVANCE), SUM(YTD_SALES*PRICE)
FROM TITLE
GROUP BY TYPE
| Type | ||
| Бизнес | 62.81 | 307587 |
| Компьютеры | 75.000 | 242784 |
| Спорт | 42.00 | 99398 |
GROUP BY возможно применять вместе с предложением WHERE. В этом случае сначала находятся все строки, удовлетворяющие предложению WHERE, затем оставшиеся строки группируются в соответствии с предложением GROUP BY. GROUP BY разделяет строки на наборы, но при этом не упорядочивает их. Для упорядочивания результатов нужно использовать предложение ORDER BY.
Например. Найти среднюю стоимость книг по каждому типу, затраты на которые превысили 50000, и упорядочить результаты по цене. Запрос выглядит следующим образом:
SELECT TYPE, AVG (PRICE)
FROM TITLE
WHERE ADVANCE > 50000
GROUP BY TYPE
ORDER BY 2
| Type | |
| Бизнес | 2.9.9 |
| Спорт | 30.1 |
| Анатомия | 42.3 |
Условие, накладываемое на группировки, задается предложением НAVING. Предложение НAVING похоже на предложение WHERE, но НAVING работает не с отдельными записями таблицы, а с группами. Последовательность действий следующая. Сначала для всего запроса выполняется предложение WHERE, затем GROUP BY, SELECT и уже к сгруппированным данным применяется условие, записанное в предложении НAVING.
Пусть необходимо группировать данные по типу выпускаемых книг и при этом исключить из рассмотрения наборы, содержащие только одну книгу.
SELECT TYPE, COUNT(*)
FROM TITLE
GROUP BY TYPE
HAVING COUNT(*) > 1
Условия в предложении HAWING могут объединяться с помощью операторов AND, OR, NOT.
Приведем в качестве примера запрос, содержащий выражения WHERE, GROUP BY, ORDER BY, HAWING. Пусть необходимо сгруппировать строки из таблицы TITLES по издателям, при этом включить в конечный результат только группы издателей с идентификационными номерами большими 0800, суммарными затратами большими 750 000 рублей, средней ценой книг меньше 300 рублей и без учета книг стоимостью меньше 50 рублей.
SELECT PUB_ID, SUM(ADVANCE), AVG(PRICE)
FROM TITLE
WHERE PRICE >= 50
GROUP BY PUB_ID
HAVING SUM(ADVANCE) > 750000
AND AVG(PRICE) < 300
AND PUB_ID > ‘0800’
ORDER BY PUB_ID
Использование нулевых значений.
При рассмотрении основных предложений оператора SELECT не рассматривался вопрос, связанный с использованием нулевых значений.
В том случае, когда информация неполна, неизвестна на настоящий момент времени, приходится иметь дело с так называемыми нулевыми (NULL) значениями. Нулевые значения появляются в том случае, если пользователь вводит данные и не знает, какую информацию нужно вводить в некоторых полях. В этом случае система автоматически вводит нулевые значения. При появлении нулевых значений не действуют стандартные правила сравнения: одно неопределенное значение не равно другому неопределенному значению. Нулевые значения приводят к появлению трехзначной логики в логических выражениях.
Таблица истинностей для трехзначной логики приведена ниже.
| a | b | not a | a and b | a or b |
| 1 | 1 | 0 | 1 | 1 |
| 1 | 0 | 0 | 0 | 1 |
| 1 | - | 0 | - | 1 |
| 0 | 1 | 1 | 0 | 1 |
| 0 | 0 | 1 | 0 | 0 |
| 0 | - | 1 | 0 | - |
| - | 1 | - | - | 1 |
| - | 0 | - | 0 | - |
| - | - | - | - | - |
Здесь неопределенное значение показано прочерком (-).
При группировке все нулевые значения, как правило, помещаются в одну группу. При вычислениях строки с нулевым значением столбца можно извлекать из таблицы с помощью специального условия IS [NOT] NULL. Например, чтобы найти все книги с ненулевыми затратами, можно использовать запрос:
SELECT TITLE_ID, ADVANCE
FROM TITLE
WHERE ADVANCE IS NOT NULL
5.1.5. Объединение таблиц и сложный анализ данных
В большинстве систем объединение таблиц осуществляется в предложении WHERE оператора SELECT. В одном операторе SELECT может объединяться несколько таблиц. Объединение, как правило, проводится по ключевым столбцам,
Например. Необходимо узнать имена редакторов книги с идентификационным номером "СW97".