Выборка данных. Фразы ORDER BY, GROUP BY и CONNECT BY предложения SELECT. Множественные операции

Фраза ORDER BY предложения SELECT

Фраза ORDER BY дает единственно законный способ получить упорядоченный результат запроса на SQL, за исключением запросов с CONNECT BY.

Простейшая сортировка

SELECT ename, sal FROM emp ORDER BY sal;

Строки ответа сортируются по возрастанию величины зарплаты. Убывающий порядок должен задаваться явным способом с помощью слова DESC:

SELECT ename, hiredate FROM emp ORDER BY hiredate DESC;

В противовес этому ради ясности можно сослаться на возрастающий порядок с помощью формально необязательного слова ASC.

Упорядочение строк ответа по нескольким столбцам задается перечислением в ORDER BY столбцов через запятую.

Пусть имеется предложение

SELECT ename, sal FROM emp;

Допустимые варианты формулировок фразы ORDER BY:

ORDER BY ename DESC
ORDER BY sal ASC, ename DESC
ORDER BY sal, ename
ORDER BY emp.sal
ORDER BY emp.ename DESC, sal, hiredate DESC

Упорядочение по значению выражения

SELECT ename FROM emp ORDER BY NVL ( sal, 0 ) + NVL ( comm, 0 );
SELECT ename, hiredate FROM emp ORDER BY TRUNC ( hiredate ) DESC;

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

SELECT ename FROM emp ORDER BY DBMS_RANDOM.VALUE;

Указание номера столбца

Если в выражении для упорядочения указано явное целое число, то оно воспринимается не как признак упорядочения, а как номер столбца во фразе SELECT, по значениям которого следует упорядочить результат:

SELECT job, AVG ( sal ) FROM emp 
GROUP  BY job
ORDER  BY 2;

Указание номера во фразе ORDER BY может сделать формулировку запроса более надежной, если результат следует упорядочить по столбцу, построенному на основе "полноценного" выражения. Менее надежная, но идентичная по результату формулировка запроса выше:

SELECT job, AVG ( sal ) FROM emp 
GROUP  BY job
ORDER  BY AVG ( sal );

Ее недостаток: повторяя (или исправляя) выражение, программист может ошибиться и в том, что для сложных выражений СУБД может не распознать их идентичность и вычислять дважды (в простых случаях оптимизатор запросов в Oracle двукратного вычисления делать не будет).

Третий, равносильный по результату вариант формулировки запроса сохраняет преимущество первой формулировки перед второй, но лишен ее недостатка:

SELECT job, AVG ( sal ) avgsal FROM emp 
GROUP  BY job
ORDER  BY avgsal;

Она позволяет СУБД единожды, а не дважды вычислить выражение, устраняет риск ошибки программиста при повторении записи выражения и не опирается на номер столбца. Ее-то и можно рекомендовать для использования в приложении. Интересно, что в ней SQL дает очередной пример собственной непоследовательности: нарушает свою же логическую схему обработки запроса, позволив сослаться в предпоследней по порядку выполнения фразе ORDER BY на название AVGSAL, определенное позже, в завершающей фразе SELECT.

Упорядочение текстовых значений: двоичное и по правилам языка

В отличие от данных других видов, в основе упорядочения строк текста имеется два содержательно разных способа их сравнения: по двоичным кодам отдельных символов и по правилам языка. Требуемый способ определяется параметром СУБД NLS_SORT, допускающим установку индивидуально для сеансов. Проверка:

INSERT INTO emp ( empno, ename ) VALUES ( 1111, 'adams' );
ALTER SESSION SET NLS_SORT = BINARY;
SELECT ename FROM emp ORDER BY ename;
Результат:
ENAME
----------
ADAMS
ALLEN
BLAKE
...
WARD
adams

Результат приведен для русских кодировок для Unix/Windows, построенных на основе ASCII. В случае кодировки на основе EBCDIC порядок результата будет фиксированный, но иной. Сравнение текстов на основе кодов символов — наиболее быстрое.

Далее:

ALTER SESSION SET NLS_SORT = RUSSIAN;
SELECT ename FROM emp ORDER BY ename;
Результат:
ENAME
----------
ADAMS
adams
ALLEN
BLAKE
...
WARD

Этот результат соответствует традиционному для русского языка порядку строк, знакомому по словарям докомпьютерных времен. Он достигается определенными дополнительными затратами на обработку. На латинских буквах, как в этом примере, с равным успехом можно было применить NLS_SORT= LATIN.

Далее:

ALTER SESSION SET NLS_SORT = RUSSIAN_CI;
SELECT ename FROM emp ORDER BY ename;
Результат:
ENAME
----------
adams
ADAMS
ALLEN
BLAKE
...
WARD

Указание CI в значении для NLS_LANG расшифровывается как case-insensitive, то есть игнорирование регистра. Отсюда иное расположение "маленького Адамса" в результате, которое, впрочем, не гарантировано (регистр не принимается во внимание!) Для чисто латинских букв соответствующее значение записывается как LATIN_CI.

Восстановим данные:

ROLLBACK;

Специальная функция NLSSORT позволяет указать нужный способ сортировки независимо от установок сеанса:

SELECT ename FROM emp ORDER BY NLSSORT ( ename, 'NLS_SORT=RUSSIAN' );

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

SELECT ename 
FROM   emp 
WHERE
         NLSSORT ( ename,   'NLS_SORT=RUSSIAN' )
 BETWEEN NLSSORT ( 'allen', 'NLS_SORT=RUSSIAN' )
     AND NLSSORT ( 'KING',  'NLS_SORT=RUSSIAN' ) 
;

Пример с русскими буквами имеет дополнительную окраску в силу особого расположения в кодировочной таблице буквы "ё":

SQL> ALTER SESSION SET NLS_SORT = BINARY;
SQL> SELECT DECODE ( ROWNUM, 1, 'е', 2, 'ё', 3, 'Ж', 4, 'Ё' ) ltr
  2  FROM dept ORDER BY ltr;
L
-
Ё
ё
Ж
е
SQL> ALTER SESSION SET NLS_SORT = RUSSIAN;
Session altered.
SQL> SELECT DECODE ( ROWNUM, 1, 'е', 2, 'ё', 3, 'Ж', 4, 'Ё' ) ltr
  2  FROM dept ORDER BY ltr;
L
-
е
Ё
ё
Ж
SQL> SELECT DECODE ( ROWNUM, 1, 'е', 2, 'ё', 3, 'Ж', 4, 'Ё' ) ltr
  2  FROM dept ORDER BY NLSSORT ( ltr, 'NLS_SORT=BINARY' );
L
-
Ё
ё
Ж
е

(Примеры с русскими буквами отработают правильно, если перед вызовом клиентской программы, а в данном случае это SQL*Plus, установить корректное значение переменной среды окружения ОС NLS_LANG).

В условных выражениях (фраза WHERE в SQL или в блоках PL/SQL) принимается во внимание не только значение параметра NLS_SORT, но и NLS_COMP. Последнее может быть BINARY или LINGUISTIC. Если NLS_COMP = LINGUISTIC, решение принимается исходя из значения NLS_SORT. Пример разъясняющей последовательности действий:

ALTER SESSION SET NLS_COMP = BINARY;
ALTER SESSION SET NLS_SORT = BINARY;
SELECT 'е < ё' FROM dual WHERE 'е' < 'ё';
ALTER SESSION SET NLS_COMP = BINARY;
ALTER SESSION SET NLS_SORT = RUSSIAN;
SELECT 'е < ё' FROM dual WHERE 'е' < 'ё';
ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = BINARY;
SELECT 'е < ё' FROM dual WHERE 'е' < 'ё';
ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = RUSSIAN;
SELECT 'е < ё' FROM dual WHERE 'е' < 'ё';

Упражнение. Выполните приведенные выше операции и пронаблюдатйте эффект различных комбинаций значений на сравнение величин.

Другие особенности параметров СУБД (сеанса), определяющих различные модели сравнения текстов, приведены в документации по Oracle.

Текущие значения параметров сравнения и сортировки своего сеанса можно посмотреть в таблице словаря-справочника NLS_SESSION_PARAMETERS, выдав:

SELECT * 
FROM 
  nls_session_parameters 
WHERE
  parameter IN ( 'NLS_COMP', 'NLS_SORT' )
;

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

Если не указать особо, отсутствующие значения интерпретируются в Oracle как наибольшие для соответствующего типа. При сортировке по возрастанию они размещаются в конце, а по убыванию — в начале списка (в стандарте SQL это не зафиксировано). Влиять на расположение строк с отсутствующими значениями полей можно во фразе ORDER BY с помощью указаний NULLS FIRST и NULLS LAST.

Пример:

SELECT   ename, comm
FROM     emp
ORDER BY comm 
   NULLS FIRST
;