Выборка данных. Фраза SELECT предложения SELECT

Уточнение DISTINCT (UNIQUE)

Пусть нужно узнать, в каких отделах есть сотрудники:

SQL> SELECT deptno FROM emp;
    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
        30
        20
        10

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

SQL> SELECT DISTINCT deptno FROM emp;
    DEPTNO
----------
        30
        20
        10

Особенности технического отсева повторений в результате употребления слова DISTINCT:

• Он требует дополнительного времени на свое осуществление.
• Он не нужен, когда строки гарантированно разные (например, отбираются первичные ключи таблицы).
• До версии 10 его осуществление имеет побочный эффект в виде упорядочивания строк результата. Хотя он был и "вне закона", некоторые программисты им пользовались, "потому что так было всегда". С версии 10 побочное упорядочение результата пропало, так что заставить СУБД обрабатывать DISTINCT по-старому все еще можно, но уже искусственным путем.

Ограничения использования:

• длина выбираемой строки должна быть меньше размера блока;
• отсев дубликатов невозможен при наличии столбцов с типами LOB, LONG и некоторых других.

Чтобы подчеркнуть отсутствие отсева повторений, в противовес DISTINCT можно явно указать умолчательное ALL:

SELECT ALL job, sal FROM emp;

На равных правах со словом DISTINCT во фразе SELECT Oracle допускает указание UNIQUE. Так, один из предшествующих запросов может быть записан иначе с полным сохранением смысла:

SELECT UNIQUE deptno FROM emp;

С реляционной точки зрения DISTINCT (UNIQUE) должно было бы не то что подразумеваться по умолчанию, но "быть" по умолчанию единственно возможным.

Учет отсутствующих значений при отсеве дубликатов

Отсутствующие значения в полях строк при внутреннем, техническом сравнении с уже отобранными в процессе отсева дубликатов строками считаются равными друг другу:

SQL> SELECT DISTINCT comm, job FROM emp;
      COMM JOB
---------- ---------
           CLERK
       300 SALESMAN
           PRESIDENT
         0 SALESMAN
       500 SALESMAN
           MANAGER
      1400 SALESMAN
           ANALYST
8 rows selected.

Такое поведение противоречит правилу, согласно которому явно указанное в запросе сравнение с отсутствующим значением дает отсутствующий логический результат (NULL, то есть не TRUE и не FALSE), смысл которого — "сравниваемые величины не равны". Это же исключение из общего правила сравнения значений в SQL имеет место при группировке GROUP BY и при операции UNION результатов SELECT (приводятся ниже). Это вынужденная мера: не будь этого исключения, SQL значительно потерял бы в своей практической ценности.

Агрегатные функции в предложении SELECT

Ниже перечисляются некоторые примеры популярных стандартных агрегатных (обобщающих) функций, аргументом для которых выступает столбец значений. Функции COUNT, MIN, MAX работают на типах: числа, строки текста, моменты времени, интервалы времени, объекты (MIN и MAX — не всегда); остальные работают только на числовых выражениях.

Функция COUNT

COUNT используется для подсчета строк и для подсчета значений в столбце, задаваемом выражением.

Примеры:

SELECT COUNT ( * ) FROM emp     /* количество строк */;
SELECT COUNT ( comm ) FROM emp  /* количество значений в столбце */;

Подсчет строк в таблице — это частный случай. COUNT ( * ) можно применять и в запросе к нескольким источникам данных.

Подсчет количества значений принимает во внимание именно имеющиеся в столбце значения (в последнем запросе их будет четыре).

Указание в выражении-аргументе для агрегатной функции слова DISTINCT (или UNIQUE) позволит подсчитать обобщение на выборке из разных значений, имеющихся в столбце:

SELECT COUNT ( DISTINCT deptno ) FROM emp /* количество разных значений */;

Формально это же уточнение DISTINCT допускается и во всех остальных агрегатных функциях, но не всегда при этом оно имеет смысл (сравните с SELECT MAX ( DISTINCT …), SELECT SUM ( DISTINCT …)).

Функции MIN и MAX

Выдают минимальное и максимальное значения из наличествующих в столбце. Примеры следуют ниже.

"Выдать максимальный оклад сотрудников":

SELECT MAX ( sal ) FROM emp;

"Выдать минимальный оклад сотрудников из Далласа":

SELECT MIN ( sal ) 
FROM   emp
WHERE  deptno IN ( SELECT deptno FROM dept WHERE LOC = 'DALLAS' );

"Сколько сотрудников пришло первыми?":

SELECT COUNT ( * ) 
FROM   emp
WHERE
   TRUNC ( hiredate ) 
 = TRUNC ( ( SELECT MIN ( hiredate ) FROM emp ) )
;

"Какова разница между максимальным и минимальным окладами в центах?":

SELECT ( MAX ( sal ) - MIN ( sal ) ) * 100 FROM emp;

Другие примеры

Пример использования функции суммирования значений SUM.

"Выдать сумму разных значений окладов сотрудников из Далласа":

SELECT SUM ( DISTINCT sal ) 
FROM   emp
WHERE  deptno IN ( SELECT deptno FROM dept WHERE LOC = 'DALLAS' )
;

Пример подсчета среднего значения из наличествующих в столбце.

"Выдать должности, для которых оклад выше среднего":

SELECT DISTINCT job 
FROM   emp
WHERE  sal > ( SELECT AVG ( sal ) FROM emp )
;

Общие правила для стандартных агрегатных функций

Для стандартных агрегатных функций выполняются общие правила вычисления.

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

Исходя из этого следующие выражения при обращении к EMP в общем случае не равнозначны:

AVG ( NVL ( comm, 0 ) )
NVL ( AVG ( comm ), 0 )
SUM ( comm ) / COUNT ( * )

Употреблять агрегатные функции в запросе следует с осторожностью, отдавая себе отчет об их поведении на пустом множестве значений или строк. Исключение, сделанное для COUNT в таких случаях неинтуитивно. В самом деле, известно, что COUNT — не самостоятельная по сути операция, сводимая к SUM. Например, COUNT ( * ) по сути равносильно SUM ( 1 ), а COUNT ( выражение ) по сути равносильно SUM ( CASE WHEN выражение IS NOT NULL THEN 1 END ), однако на пустом множестве SQL (стандарт, и в исполнении Oracle) эти формулировки оценивает по-разному.

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

Примеры. Следующее предложение некорректно (ошибочно) синтаксически:

SELECT COUNT ( * ), ename FROM emp;

Следующее предложение корректно:

SELECT SUM ( comm ) / COUNT ( * ) + 123 FROM emp;

Упражнение. Ответьте прямой речью, что выдаст последний запрос. Сравните выражения AVG ( comm ) и SUM ( comm ) / COUNT ( comm ).