Шаг 35 - SELECT - "Группировка" данных далее

Как вы, наверное, уже поняли с помощью одного запроса с применением агрегатных функций нельзя получить детальные и промежуточные результаты. Такие приемы возможны только с применением встроенного языка программирования PL/SQL, к которому мы уже почти подобрались и как только разделаемся с SELECT - так как без него никуда, возьмемся за это дело. Тем не менее, в сервере MS SQL существует выражение COMPUTE, которое по своей сути подрывает все основы построения реляционных запросов. Но с его помощью, возможно получение такого рода результатов не прибегая к написанию хранимых процедур. Но это так к слову и для информации о том, что мелко мягкие вообще любят подрывать основы .... :) Так же на запросы с группировкой накладывается ряд ограничений. Например, столбцы с группировкой должны представлять собой реальные столбцы таблиц. Нельзя группировать строки на основе вычисляемого выражения. Так же существуют ограничения на элементы возвращаемых значений. Возвращаемым столбцом может быть:

  1. Константа.
  2. Агрегатная функция, возвращающая одно значение для всех строк входящих в группу.
  3. Столбец группировки, который по определению имеет одно и тоже значение во всех строках группы.
  4. Выражение, включающее в себя перечисленные выше элементы.

Обычно в список возвращаемых столбцов запросов с группировкой входят столбец группировки и агрегатная функция. Если не указать агрегат, то можно просто обойтись выражением DISTINCT без использования предложения GROUP BY! А так же, если в запрос не включить столбец группировки, вы не сможете определить, к какой именно группе относится та или иная строка результата запроса! Так же в SQL92 игнорируется информация о первичных и вторичных ключах, при анализе запроса с группировкой.

Вот, например: Подсчитать общее количество заказов для каждого служащего.

SELECT EMPL_NUM, NAME, SUM(AMOUNT)
FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM
GROUP BY EMPL_NUM
/
Уууупс! А вот и ошибочка!
SQL> SELECT EMPL_NUM, NAME, SUM(AMOUNT)
  2  FROM ORDERS, SALESREPS
  3  WHERE REP = EMPL_NUM
  4  GROUP BY EMPL_NUM
  5  /

SELECT EMPL_NUM, NAME, SUM(AMOUNT)
FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM
GROUP BY EMPL_NUM

ORA-00979: выражение не является выражением GROUP BY

В данном случае имеется в виду поле NAME. Так как оно явно не вписывается в запросе! Хотя, если рассуждать с точки зрения природы данных, все вроде бы правильно, но не совсем. Столбец EMPL_NUM является первичным ключом таблицы SALESREPS, поэтому столбец NAME должен иметь одно значение для каждой группы! Правильно, просто нужно указать этот столбец в выражении группировки вот так: Подсчитать общее количество заказов для каждого служащего.

SELECT EMPL_NUM, NAME, SUM(AMOUNT)
FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM
GROUP BY EMPL_NUM, NAME
/

Вот теперь правильно! :)

SQL> SELECT EMPL_NUM, NAME, SUM(AMOUNT)
  2  FROM ORDERS, SALESREPS
  3  WHERE REP = EMPL_NUM
  4  GROUP BY EMPL_NUM, NAME
  5  /


  EMPL_NUM NAME                           SUM(AMOUNT)
------ ------------------------------ -----------
   101 Игорь Николаев                     176,478
   102 Филип Киркоров                      22,776
   103 Дима Маликов                        602,44
   105 Вася Пупкин                        740,625
   106 Света Разина                        32,958
   107 Маша Сидорова                       685,78
   108 Игорь Петров                      2178,445
   109 Маша Распутина                       7,103
   110 Крис Кельми                          654,5

9 rows selected

А можно сделать еще проще. Вот так: Подсчитать общее количество заказов для каждого служащего.

SELECT NAME, SUM(AMOUNT)
FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM
GROUP BY NAME
/

Получаем:

SQL> SELECT NAME, SUM(AMOUNT)
  2  FROM ORDERS, SALESREPS
  3  WHERE REP = EMPL_NUM
  4  GROUP BY NAME
  5  /


NAME                           SUM(AMOUNT)
------------------------------ -----------
Вася Пупкин                        740,625
Дима Маликов                        602,44
Игорь Николаев                     176,478
Игорь Петров                      2178,445
Крис Кельми                          654,5
Маша Распутина                       7,103
Маша Сидорова                       685,78
Света Разина                        32,958
Филип Киркоров                      22,776

9 rows selected

Теперь надеюсь, все стало ясно с группировками, если что не понятно можете писать письма! :) А вот теперь давайте вернемся к нашему старому знакомому - ну, конечно же, NULL. Что будет, если в одном из полей группировки будет присутствовать NULL? К какой группе его отнести? В предложении WHERE по правилам сравнение NULL и NULL скажем на равенство не дает TRUE, а будет равно NULL! В предложении GROUP BY это крайне не удобно, так как каждый NULL будет генерировать новую группу. Поэтому в стандарте ANSI/ISO определено, что в предложении GROUP BY значения NULL РАВНЫ!!! Следовательно, он не будут вносить неразбериху в запросах с группировкой! Что, собственно и требовалось в данном случае. Для примера создадим табличку COLORIS и проверим наши рассуждения.

Создаем:

CREATE TABLE COLORIS
(
NM   VARCHAR2(50),
HAIR VARCHAR2(50),
EYES VARCHAR2(50)
)
/

SQL> CREATE TABLE COLORIS
  2  (
  3  NM   VARCHAR2(50),
  4  HAIR VARCHAR2(50),
  5  EYES VARCHAR2(50)
  6  )
  7  /

Table created

Табличка создана! Не забудьте дать оператор COMMIT вот так:

COMMIT
/

Если все верно, то получите следующее:

SQL> COMMIT
  2  /

Теперь операцией копирования через буфер обмена отправьте в SQLPlus следующее:

INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Cindy', 'Brown', 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Louise', NULL, 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Harry', NULL, 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Samantha', NULL, NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Joanne', NULL, NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('George', 'Brown', NULL)	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Mary', 'Brown', NULL)	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Paula', 'Brown', NULL)	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Kevin', 'Brown', NULL)	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Joel', 'Brown', 'Brown')	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Susan', 'Blonde', 'Blue')	
/
INSERT INTO COLORIS(NM, HAIR, EYES)
	VALUES('Marie', 'Blonde', 'Blue')	
/
COMMIT
/

Если все успешно прошло получите примерно следующее:

SQL> INSERT INTO COLORIS(NM, HAIR, EYES)
  2  	VALUES('Cindy', 'Drown', 'Blue')
  3  /

1 row inserted

.
.
.
.

SQL> INSERT INTO COLORIS(NM, HAIR, EYES)
  2  	VALUES('Marie', 'Blonde', 'Blue')
  3  /

1 row inserted

SQL> commit
  2  /

Commit complete

Табличка заполнена данными! Как мы это проделали, напомню, применив оператор INSERT. Он отправляет данные в таблицы БД. О нем мы еще поговорим в дальнейшем. А вот теперь давайте наконец дадим запрос и проверим теорию равенства NULL в выражениях GROUP BY:

SELECT HAIR, EYES, COUNT(*)
FROM COLORIS
GROUP BY  HAIR, EYES
/

Получаем:

SQL> SELECT HAIR, EYES, COUNT(*)
  2  FROM COLORIS
  3  GROUP BY  HAIR, EYES
  4  /

HAIR    EYES   	  COUNT(*)
------- -------   --------
Blonde  Blue             2
Brown   Blue             1
Brown   Brown            1
Brown   NULL             4
NULL    Blue             2
NULL    NULL             2

Хорошо видно, как NULL сформировал собственную группу! Вот теперь кажется разобрались в особенностях работы агрегатов со значениями NULL в БД Oracle. :)


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.