Использование COUNT со строками, а не значениями

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля, как, например, в следующем примере, вывод из которого показан на Рисунке 6.4:

SELECT COUNT (*)
FROM Customers

=============== SQL Execution Log ============
| SELECT COUNT (*) |
| FROM Customers; |
| ==============================================|
| |
| ------- |
| 7 |
===============================================

Рисунок 6.4. Подсчет строк вместо значений

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

Включение дубликатов в агрегатные функции

Агрегатные функции могут также (в большинстве реализаций) использовать аргумент ALL, который помещается перед именем поля, подобно DISTINCT, но означает противоположное — включать дубликаты. ANSI технически не позволяет этого для COUNT, но многие реализации ослабляют это ограничение.

Различия между ALL и * когда они используются с COUNT:

ALL использует имя поля как аргумент.

ALL не может подсчитать значения NULL.

Пока * является единственным аргументом, который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает (COUNT) число не NULL значений в поле rating в таблице Заказчиков (включая повторения):

SELECT COUNT (ALL rating)
FROM Customers;

Агрегаты, построенные на скалярном выражении

До этого, вы использовали агрегатные функции с одиночными полями как аргументами. Вы можете также использовать агрегатные функции с аргументами, которые состоят из скалярных выражений, включающих одно или более полей. (Если вы это делаете, DISTINCT не разрешается.) Предположим, что таблица Заказов имеет еще один столбец, который хранит предыдущий неуплаченный баланс (поле blnc) для каждого заказчика. Вы должны найти этот текущий баланс, добавлением суммы приобретений к предыдущему балансу. Вы можете найти наибольший неуплаченный баланс следующим образом:

SELECT MAX (blnc + amt)
FROM Orders;

Для каждой строки таблицы этот запрос будет складывать blnc и amt для этого заказчика и выбирать самое большое значение, которое он найдет. Конечно, пока заказчики могут иметь многочисленные Заказы, их неуплаченный баланс оценивается отдельно для каждого Заказа. Возможно, заказ с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе, старый баланс должен быть выбран, как в запросе выше.

Фактически, имеются большое количество ситуаций в SQL, где вы можете использовать скалярные выражения с полями или вместо полей, как вы увидите это в Главе 7.

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

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, вы хотите найти наибольшую сумму приобретений, полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Заказов для каждого значения поля snum. GROUP BY, однако, позволит Вам поместить их все в одну команду:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum;

Вывод для этого запроса показывается в Рисунке 6.5.

=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 1713.23 |
| 1003 75.75 |
| 1014 1309.95 |
| 1007 1098.16 |
=================================================

Рисунок 6.5. Нахождение максимальной суммы продажи у каждого продавца.

GROUP BY применяет агрегатные функции независимо от серий групп, которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением поля snum, и функция MAX применяется отдельно для каждой такой группы. Это значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость, которая позволяет агрегатам и полям объединяться таким образом. Вы можете также использовать GROUP BY с многочисленными полями. Совершенствуя вышеупомянутый пример далее, предположим, что вы хотите увидеть наибольшую сумму приобретений получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Заказов по продавцам и датам, и применить функцию MAX к каждой такой группе, подобно этому:

SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate;

Вывод для этого запроса показывается в Рисунке 6.6.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/03/1990 767.19 |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
| 1002 10/04/1990 75.75 |
| 1002 10/06/1990 1309.95 |
| 1003 10/04/1990 1713.23 |
| 1014 10/03/1990 1900.10 |
| 1007 10/03/1990 1098.16 |
=================================================

Рисунок 6.6. Нахождение наибольшей суммы приобретений на каждый день

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

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

Предположим, что в предыдущем примере, вы хотели бы увидеть только максимальную сумму приобретений, значение которой выше $3000.00. Вы не сможете использовать агрегатную функцию в предложении WHERE (если вы не используете подзапрос, описанный позже), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах группы строк. Это означает, что вы не сможете сделать что-нибудь подобно следующему:

SELECT snum, odate, MAX (amt)
FROM Oreders
WHERE MAX (amt) > 3000.00
GROUP BY snum, odate;

Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение HAVING. Предложение HAVING определяет критерии, используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Правильной командой будет следующая:

SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate
HAVING MAX (amt) > 3000.00;

Вывод для этого запроса показывается в Рисунке 6.7.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate |
| HAVING MAX (amt) > 3000.00; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
=================================================

Рисунок 6.7. Удаление групп агрегатных значений

Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящем из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода. Следующая команда будет запрещена:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum
HAVING odate = 10/03/1988;

Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь (и действительно имеет) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:

SELECT snum, MAX (amt)
FROM Orders
WHERE odate = 10/03/1990
GROUP BY snum;

// для Interbase:

WHERE odate = CAST('10/03/1988' AS DATE)

 

Вывод показывается в Рисунке 6.8.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 5160.45 |
| 1014 1900.10 |
| 1007 1098.16 |
=================================================

Рисунок 6.8: Максимальное значение суммы приобретений у каждого продавца на 3 Октября.

Поскольку поля odate нет, не может быть и выбранных полей, значение этих данных меньше, чем в некоторых других примерах. Вывод должен, вероятно, включать что-нибудь такое, что говорит: "это — самые большие Заказы на 3 Октября". В Главе 7, мы покажем, как вставлять текст в ваш вывод.

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть наибольшие Заказы для Serres и Rifkin:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum
HAVING snum IN (1002,1007);

Вывод для этого запроса показывается в Рисунке 6.9.

=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum |
| HAVING snum IN (1002, 1007); |
| =============================================== |
| snum |
| ------ -------- |
| 1002 5160.45 |
| 1007 1098.16 |
=================================================

Рисунок 6.9. Использование HAVING с полями GROUP BY.