Фраза SELECT — вторая, вместе с FROM, обязательная для каждого предложения SELECT. Ее назначение состоит в формировании столбцов таблицы — окончательного результата выполнения запроса. Типично она сохраняет количество строк, поступивших ей на входе от предшествующих фраз, и занимается только переформулированием столбцов, но в некоторых случаях она способна вдобавок и сократить количество строк.
Обычно состав фразы SELECT — список через запятую выражений для столбцов окончательного ответа. В отдельных случаях у такой структуры могут существовать свои особенности.
Если делается запрос по одной таблице-источнику данных и требуется выдать все поля строк этой таблицы без изменений, вместо списка выражений во фразе SELECT можно указать символ *:
SELECT * FROM dept;
Символ * может быть предварен именем таблицы:
SELECT dept.* FROM dept;
В данном случае в этом нужды нет, но если бы источников данных было несколько, такое уточнение было бы оправдано.
Два последних примера равносильны и выдадут то же, что и следующая формулировка:
SELECT dept.deptno, dept.dname, dept.loc FROM dept;
Еще пример. Следующие два предложения равносильны:
SELECT emp.ename, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE dept.deptno = emp.deptno; SELECT emp.ename, dept.* FROM dept, emp WHERE dept.deptno = emp.deptno;
Символ * не связан с единичностью источника и означает "все столбцы SELECT, без каких-либо преобразований". Пример употребления в запросе к двум таблицам:
SELECT * FROM dept, emp WHERE dept.deptno = emp.deptno;
Использование SELECT * может показаться привлекательным в силу экономности записи, однако стоит напомнить, что это нереляционная конструкция, так как она полагается на порядок столбцов в таблице, в то время как в реляционной модели атрибуты в отношении порядка не имеют и допускают обращение к ним только по названиям. В предположении порядка столбцов (тем более неявном) кроется определенный риск, так как таблицы в Oracle допускают добавление и удаление столбцов, из-за чего запросы в программе могут потерять
В технических запросах (не в приложении) конструкцией SELECT * можно пользоваться свободнее.
Пример, когда во фразе SELECT приводятся выражения, а не имена столбцов:
SELECT
ename
, ' earns'
, ( sal + NVL ( comm, 0 ) ) / 1000000
, ' million dollars per month'
FROM emp
;
Первый по порядку столбец будет содержать разные имена сотрудников, второй и четвертый — постоянные значения, а третий — разные результаты оценки числового выражения.
Если не предпринять специальных мер, столбцы в таблице-результате именуются автоматически (чаще всего на основе имен столбцов запрошенных таблиц). При желании программист может потребовать СУБД назвать столбец по-своему, указав имя через пробел после формулировки выражения. (Имеется в виду "обобщенный пробел", который может
состоять фактически из нескольких знаков пробела, табуляции или переходов на новую строку).
Примеры:
SELECT ename, sal salary FROM emp; SELECT ename "Сотрудники", sal "Зарплата" FROM emp; SELECT SUM ( comm ) / COUNT ( * ) "Усреднение по всем сотрудникам" FROM emp ;
Правила выбора и записи имен столбцов те же, что и для таблиц в БД.
Вместо обобщенного пробела можно с равным успехом использовать связку AS:
SELECT ename AS "Сотрудники", sal AS "Зарплата" FROM emp;
Использовать пробел или ключевое слово AS — дело вкуса и здравого смысла программиста. Ключевое слово AS добавляет тексту запроса переносимости.
Пусть нужно узнать, в каких отделах есть сотрудники:
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:
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 значительно потерял бы в своей практической ценности.
Ниже перечисляются некоторые примеры популярных стандартных агрегатных (обобщающих) функций, аргументом для которых выступает столбец значений. Функции COUNT, MIN, MAX работают на типах: числа, строки текста, моменты времени, интервалы времени, объекты (MIN и MAX — не всегда); остальные работают только на числовых выражениях.
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 …)).
Выдают минимальное и максимальное значения из наличествующих в столбце. Примеры следуют ниже.
"Выдать максимальный оклад сотрудников":
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 ).
Аналитические функции — это нескалярные функции (за исключением аналитических статистических, скалярных по результату), которые в отличие от стандартных агрегатных могут употребляться только во фразах SELECT и ORDER BY, так как применяются к уже отобранному результату (см. выше схему выполнения предложения SELECT). Свое название получили по той причине, что позволяют средствами SQL (в Oracle) строить запросы, анализирующие данные в БД. Являются вариацией "
Функции этой категории иногда называют "функциями OLAP" ввиду того, что они хорошо подходят для систем типа OLAP (On-Line Analytical Processing), аналитических систем и "аналитических баз данных".
В Oracle они могут быть следующих видов:
LAG /LEADДалее по очереди приводятся примеры употребления аналитических функций каждого из этих видов.
"Раздать сотрудникам места по мере убывания или возрастания их зарплат":
SELECT ename , sal , ROW_NUMBER ( ) OVER ( ORDER BY sal DESC ) AS row_number_desc , ROW_NUMBER ( ) OVER ( ORDER BY sal ) AS row_number_asc , RANK ( ) OVER ( ORDER BY sal ) AS rank , DENSE_RANK ( ) OVER ( ORDER BY sal ) AS dense_rank FROM emp ;
Ответ:
ENAME SAL ROW_NUMBER_DESC ROW_NUMBER_ASC RANK DENSE_RANK -------- ------- --------------- -------------- ---------- ---------- SMITH 800 14 1 1 1 JAMES 950 13 2 2 2 ADAMS 1100 12 3 3 3 MARTIN 1250 11 4 4 4 WARD 1250 10 5 4 4 MILLER 1300 9 6 6 5 TURNER 1500 8 7 7 6 ALLEN 1600 7 8 8 7 CLARK 2450 6 9 9 8 BLAKE 2850 5 10 10 9 JONES 2975 4 11 11 10 SCOTT 3000 3 12 12 11 FORD 3000 2 13 12 11 KING 5000 1 14 14 12
Как видно, разница в поведении проявляется на данных, где критерий определения места оказывается одинаковым у нескольких сотрудников. ROW_NUMBER в таких случаях места раздает случайно. Например, в версии 9 СУБД на этот запрос выдавала Скотту и Форду второе и третье места, а Мартину и Варду — десятое и одиннадцатое. Функции же RANK и DENSE_RANK на одинаковом показателе критерия присваивают строкам одно и то же место с той разницей, что в случае DENSE_RANK следующее по величине критерия место выдается по порядку, а в случае RANK — с пропуском за счет возникших повторений.
"'Растущий итог' выплат на зарплату по мере приема сотрудников на работу":
SELECT ename , sal , SUM ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_over_range FROM emp ;
Ответ:
ENAME SAL SUM_OVER_RANGE ---------- ---------- -------------- SMITH 800 800 ALLEN 1600 2400 WARD 1250 3650 JONES 2975 6625 BLAKE 2850 9475 CLARK 2450 11925 TURNER 1500 13425 MARTIN 1250 14675 KING 5000 19675 JAMES 950 23625 FORD 3000 23625 MILLER 1300 24925 SCOTT 3000 27925 ADAMS 1100 29025
Заметьте, что Джеймс и Форд поступили на работу одновременно, и поэтому значение общей суммы зарплат у них одинаковое. В то же время смысл такого суммирования не совсем ясен. Более понятен запрос, где вместо слова RANGE указать ROWS. Если это сделать, Джеймс и Форд "получат" разные суммы, но снова в случайном порядке (значение HIREDATE в качестве критерия упорядочения у них одинаковое).
"Доли зарплаты сотрудников в общей сумме зарплат":
SELECT ename , sal , RATIO_TO_REPORT ( sal ) OVER ( ) AS ratio_to_report FROM emp ;
Ответ:
ENAME SAL RATIO_TO_REPORT ---------- ---------- --------------- SMITH 800 .027562446 ALLEN 1600 .055124892 WARD 1250 .043066322 JONES 2975 .102497847 MARTIN 1250 .043066322 BLAKE 2850 .098191214 CLARK 2450 .084409991 SCOTT 3000 .103359173 KING 5000 .172265289 TURNER 1500 .051679587 ADAMS 1100 .037898363 JAMES 950 .032730405 FORD 3000 .103359173 MILLER 1300 .044788975
"Изменение зарплаты сотрудника по отношению к предшественнику по мере приема на работу":
SELECT ename , sal , sal - LAG ( sal, 1 ) OVER ( ORDER BY hiredate ) delta FROM emp ;
Ответ:
ENAME SAL DELTA ---------- ---------- ---------- SMITH 800 ALLEN 1600 800 WARD 1250 -350 JONES 2975 1725 BLAKE 2850 -125 CLARK 2450 -400 TURNER 1500 -950 MARTIN 1250 -250 KING 5000 3750 JAMES 950 -4050 FORD 3000 2050 MILLER 1300 -1700 SCOTT 3000 1700 ADAMS 1100 -1900
Обратите внимание на разумное поведение аналитических функций (вообще) на границах упорядоченных множеств данных (строка со Смитом). Неприятность в другом: NULL, который порождает Oracle в своем ответе, имеет здесь смысл "значение неприменимо", а не "неизвестно", как хотелось бы (обсуждение разницы приводилось выше).
"Три из имеющихся видов регрессии для оценки взаимозависимости значений в столбцах":
SELECT REGR_SLOPE ( sal, comm ) AS slope , REGR_AVGX ( sal, comm ) AS avgsal , REGR_AVGY ( sal, comm ) AS avgcomm FROM emp ;
Ответ:
SLOPE AVGSAL AVGCOMM ---------- ---------- ---------- -.20642202 550 1400
Названия функций для имеющихся прочих видов регрессии приведены в документации по Oracle. Обратите внимание на вероятную формальность этого запроса, если не предположить, что связь между зарплатой и комиссионными в жизни вдруг существует, в результате чего запрос приобретает смысл.
Во фразе SELECT (а также в качестве аргумента функции — в составе любого выражения) можно использовать выражение типа "ссылка на курсор". Оно строится с помощью функции CURSOR, аргументом которой передается другое предложение SELECT, и возвращает скаляр — ссылку на курсор. Хотя в SQL*Plus эту функцию и разрешено задействовать, основное применение ей — в программной обработке результатов предложения SELECT. Пример в SQL*Plus:
SELECT dname , CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno ) FROM dept;
В качестве элемента более общего выражения курсорное выражение может войти только будучи предъявленным как аргумент функции; в примере ниже это вымышленная "табличная" функция JOBSEMPS:
SELECT TABLE ( jobsemps ( CURSOR ( SELECT * FROM emp ) ) ) AS "Nested Table:" FROM dual;
Такая техника позволяет передавать подпрограмме для обработки нефиксированный по количеству (но фиксированный по структуре) массив строк.
В SQL фирмы Oracle тип ссылки на курсор отсутствует. Он имеется только в PL/SQL.
Версия Oracle 11 позволила дополнить фразу FROM подчиненными фразами SELECT столбцов, "импортированных" из этих конструкций. Обе формулировки предназначены для переформатирования данных таблиц средствами SQL, без программирования. Они удобны для построения отчетов и анализа имеющихся данных.
Сочетание SELECT … FROM … позволяет развернуть данные одного столбца в отдельные столбцы конечного результата.
Рассмотрим для начала запрос о наличии в разных отделах сотрудников на разных должностях:
SQL> SELECT job, deptno FROM emp; JOB DEPTNO --------- ---------- CLERK 20 SALESMAN 30 SALESMAN 30 MANAGER 20 SALESMAN 30 MANAGER 30 MANAGER 10 ANALYST 20 PRESIDENT 10 SALESMAN 30 CLERK 20 CLERK 30 ANALYST 20 CLERK 10
В каждом отделе имеется ноль или более сотрудников на каждой из вообще существующих должностей. Данные об их количестве удобно представить, посвятив каждому департаменту отдельный столбец:
SQL> SELECT * 2> FROM ( SELECT job, deptno FROM emp ) 3> PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ); JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1 2 1 0 SALESMAN 0 0 4 0 PRESIDENT 1 0 0 0 MANAGER 1 1 1 0 ANALYST 0 2 0 0
Внутренняя отработка такого предложения осуществляется как при группировке GROUP BY job (см. ниже), но приводит к появлению дополнительных столбцов вместо, казалось бы, указанного DEPTNO. Вот как мог бы выглядеть аналог последнего запроса в версиях Oracle до 11:
SELECT JOB , COUNT ( CASE WHEN deptno = 10 THEN 1 END ) "10" , COUNT ( CASE WHEN deptno = 20 THEN 1 END ) "20" , COUNT ( CASE WHEN deptno = 30 THEN 1 END ) "30" , COUNT ( CASE WHEN deptno = 40 THEN 1 END ) "40" FROM ( SELECT job, deptno FROM emp ) GROUP BY job ;
Именно так Oracle и обработает запрос с
Для правильного разворачивания существенно правильно указать структуру источника во фразе FROM. Если в последнем запросе после FROM указать не подзапрос, а таблицу EMP (или же в подзапросе указать другие поля EMP), принцип разворачивания изменится и результат окажется иным.
Вот некоторые другие примеры. Выдать только данные по отделам 10 и 30:
SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 30 ) ) ;
То же самое:
SELECT job, "10", "30" FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Самостоятельное задание имен столбцам результата делается как во фразе SELECT:
SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10 tenth, 30 thirtieth ) ) ;
Выдача сумм окладов сотрудников по каждой должности в указанных отделах:
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( SUM ( sal ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Выдача сразу двух агрегатов (суммы зарплат и количества сотрудников):
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( SUM ( sal ), COUNT ( * ) c FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Сформировать столбцы для сотрудников 10-го отдела с окладами 5000 и 1300 и сотрудников 30-го отдела с окладами 1250:
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( COUNT ( * ) c FOR ( deptno, sal ) IN ( ( 10, 5000), ( 10, 1300), ( 30, 1250 ) ) );
Во фразе
Фраза UNPIVOT выполняет действие, содержательно противоположное фразе
Создадим таблицу по запросу выше:
CREATE TABLE total AS SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Выдача данных со сворачиванием показателей в один столбец:
SQL> SELECT * 2 FROM total 3 UNPIVOT ( jobcount FOR deptno IN ( "10", "20", "30", "40" ) ) 4 ; JOB DE JOBCOUNT --------- -- ---------- CLERK 10 1 CLERK 20 2 CLERK 30 1 CLERK 40 0 SALESMAN 10 0 SALESMAN 20 0 SALESMAN 30 4 SALESMAN 40 0 PRESIDENT 10 1 PRESIDENT 20 0 PRESIDENT 30 0 PRESIDENT 40 0 MANAGER 10 1 MANAGER 20 1 MANAGER 30 1 MANAGER 40 0 ANALYST 10 0 ANALYST 20 2 ANALYST 30 0 ANALYST 40 0
От выдачи JOBCOUNT можно отказаться, указав вместо SELECT * … формулировку SELECT job, deptno ….
Заметьте, что агрегат COUNT в отличие от всех остальных всегда возвращает значение, хоть бы 0. Если бы в столбце JOBCOUNT оказались пропуски (NULL), был бы законным вопрос, как их учитывать при сворачивании данных. Две возможные схемы поведения обозначаются уточнениями UNPIVOT INCLUDE NULLS и UNPIVOT EXCLUDE NULLS.
Подобное сворачивание в столбец (иначе, переворачивание, "транспонирование") таблицы TOTAL позволяет ответить на вопросы типа "В каких отделах число продавцов больше 10?", или даже "больше 10%". Иначе SQL этого делать не умеет.
Фраза SELECT — вторая, вместе с FROM, обязательная для каждого предложения SELECT. Ее назначение состоит в формировании столбцов таблицы — окончательного результата выполнения запроса. Типично она сохраняет количество строк, поступивших ей на входе от предшествующих фраз, и занимается только переформулированием столбцов, но в некоторых случаях она способна вдобавок и сократить количество строк.
Обычно состав фразы SELECT — список через запятую выражений для столбцов окончательного ответа. В отдельных случаях у такой структуры могут существовать свои особенности.
Если делается запрос по одной таблице-источнику данных и требуется выдать все поля строк этой таблицы без изменений, вместо списка выражений во фразе SELECT можно указать символ *:
SELECT * FROM dept;
Символ * может быть предварен именем таблицы:
SELECT dept.* FROM dept;
В данном случае в этом нужды нет, но если бы источников данных было несколько, такое уточнение было бы оправдано.
Два последних примера равносильны и выдадут то же, что и следующая формулировка:
SELECT dept.deptno, dept.dname, dept.loc FROM dept;
Еще пример. Следующие два предложения равносильны:
SELECT emp.ename, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE dept.deptno = emp.deptno; SELECT emp.ename, dept.* FROM dept, emp WHERE dept.deptno = emp.deptno;
Символ * не связан с единичностью источника и означает "все столбцы SELECT, без каких-либо преобразований". Пример употребления в запросе к двум таблицам:
SELECT * FROM dept, emp WHERE dept.deptno = emp.deptno;
Использование SELECT * может показаться привлекательным в силу экономности записи, однако стоит напомнить, что это нереляционная конструкция, так как она полагается на порядок столбцов в таблице, в то время как в реляционной модели атрибуты в отношении порядка не имеют и допускают обращение к ним только по названиям. В предположении порядка столбцов (тем более неявном) кроется определенный риск, так как таблицы в Oracle допускают добавление и удаление столбцов, из-за чего запросы в программе могут потерять
В технических запросах (не в приложении) конструкцией SELECT * можно пользоваться свободнее.
Пример, когда во фразе SELECT приводятся выражения, а не имена столбцов:
SELECT
ename
, ' earns'
, ( sal + NVL ( comm, 0 ) ) / 1000000
, ' million dollars per month'
FROM emp
;
Первый по порядку столбец будет содержать разные имена сотрудников, второй и четвертый — постоянные значения, а третий — разные результаты оценки числового выражения.
Если не предпринять специальных мер, столбцы в таблице-результате именуются автоматически (чаще всего на основе имен столбцов запрошенных таблиц). При желании программист может потребовать СУБД назвать столбец по-своему, указав имя через пробел после формулировки выражения. (Имеется в виду "обобщенный пробел", который может
состоять фактически из нескольких знаков пробела, табуляции или переходов на новую строку).
Примеры:
SELECT ename, sal salary FROM emp; SELECT ename "Сотрудники", sal "Зарплата" FROM emp; SELECT SUM ( comm ) / COUNT ( * ) "Усреднение по всем сотрудникам" FROM emp ;
Правила выбора и записи имен столбцов те же, что и для таблиц в БД.
Вместо обобщенного пробела можно с равным успехом использовать связку AS:
SELECT ename AS "Сотрудники", sal AS "Зарплата" FROM emp;
Использовать пробел или ключевое слово AS — дело вкуса и здравого смысла программиста. Ключевое слово AS добавляет тексту запроса переносимости.
Пусть нужно узнать, в каких отделах есть сотрудники:
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:
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 значительно потерял бы в своей практической ценности.
Ниже перечисляются некоторые примеры популярных стандартных агрегатных (обобщающих) функций, аргументом для которых выступает столбец значений. Функции COUNT, MIN, MAX работают на типах: числа, строки текста, моменты времени, интервалы времени, объекты (MIN и MAX — не всегда); остальные работают только на числовых выражениях.
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 …)).
Выдают минимальное и максимальное значения из наличествующих в столбце. Примеры следуют ниже.
"Выдать максимальный оклад сотрудников":
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 ).
Аналитические функции — это нескалярные функции (за исключением аналитических статистических, скалярных по результату), которые в отличие от стандартных агрегатных могут употребляться только во фразах SELECT и ORDER BY, так как применяются к уже отобранному результату (см. выше схему выполнения предложения SELECT). Свое название получили по той причине, что позволяют средствами SQL (в Oracle) строить запросы, анализирующие данные в БД. Являются вариацией "
Функции этой категории иногда называют "функциями OLAP" ввиду того, что они хорошо подходят для систем типа OLAP (On-Line Analytical Processing), аналитических систем и "аналитических баз данных".
В Oracle они могут быть следующих видов:
LAG /LEADДалее по очереди приводятся примеры употребления аналитических функций каждого из этих видов.
"Раздать сотрудникам места по мере убывания или возрастания их зарплат":
SELECT ename , sal , ROW_NUMBER ( ) OVER ( ORDER BY sal DESC ) AS row_number_desc , ROW_NUMBER ( ) OVER ( ORDER BY sal ) AS row_number_asc , RANK ( ) OVER ( ORDER BY sal ) AS rank , DENSE_RANK ( ) OVER ( ORDER BY sal ) AS dense_rank FROM emp ;
Ответ:
ENAME SAL ROW_NUMBER_DESC ROW_NUMBER_ASC RANK DENSE_RANK -------- ------- --------------- -------------- ---------- ---------- SMITH 800 14 1 1 1 JAMES 950 13 2 2 2 ADAMS 1100 12 3 3 3 MARTIN 1250 11 4 4 4 WARD 1250 10 5 4 4 MILLER 1300 9 6 6 5 TURNER 1500 8 7 7 6 ALLEN 1600 7 8 8 7 CLARK 2450 6 9 9 8 BLAKE 2850 5 10 10 9 JONES 2975 4 11 11 10 SCOTT 3000 3 12 12 11 FORD 3000 2 13 12 11 KING 5000 1 14 14 12
Как видно, разница в поведении проявляется на данных, где критерий определения места оказывается одинаковым у нескольких сотрудников. ROW_NUMBER в таких случаях места раздает случайно. Например, в версии 9 СУБД на этот запрос выдавала Скотту и Форду второе и третье места, а Мартину и Варду — десятое и одиннадцатое. Функции же RANK и DENSE_RANK на одинаковом показателе критерия присваивают строкам одно и то же место с той разницей, что в случае DENSE_RANK следующее по величине критерия место выдается по порядку, а в случае RANK — с пропуском за счет возникших повторений.
"'Растущий итог' выплат на зарплату по мере приема сотрудников на работу":
SELECT ename , sal , SUM ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_over_range FROM emp ;
Ответ:
ENAME SAL SUM_OVER_RANGE ---------- ---------- -------------- SMITH 800 800 ALLEN 1600 2400 WARD 1250 3650 JONES 2975 6625 BLAKE 2850 9475 CLARK 2450 11925 TURNER 1500 13425 MARTIN 1250 14675 KING 5000 19675 JAMES 950 23625 FORD 3000 23625 MILLER 1300 24925 SCOTT 3000 27925 ADAMS 1100 29025
Заметьте, что Джеймс и Форд поступили на работу одновременно, и поэтому значение общей суммы зарплат у них одинаковое. В то же время смысл такого суммирования не совсем ясен. Более понятен запрос, где вместо слова RANGE указать ROWS. Если это сделать, Джеймс и Форд "получат" разные суммы, но снова в случайном порядке (значение HIREDATE в качестве критерия упорядочения у них одинаковое).
"Доли зарплаты сотрудников в общей сумме зарплат":
SELECT ename , sal , RATIO_TO_REPORT ( sal ) OVER ( ) AS ratio_to_report FROM emp ;
Ответ:
ENAME SAL RATIO_TO_REPORT ---------- ---------- --------------- SMITH 800 .027562446 ALLEN 1600 .055124892 WARD 1250 .043066322 JONES 2975 .102497847 MARTIN 1250 .043066322 BLAKE 2850 .098191214 CLARK 2450 .084409991 SCOTT 3000 .103359173 KING 5000 .172265289 TURNER 1500 .051679587 ADAMS 1100 .037898363 JAMES 950 .032730405 FORD 3000 .103359173 MILLER 1300 .044788975
"Изменение зарплаты сотрудника по отношению к предшественнику по мере приема на работу":
SELECT ename , sal , sal - LAG ( sal, 1 ) OVER ( ORDER BY hiredate ) delta FROM emp ;
Ответ:
ENAME SAL DELTA ---------- ---------- ---------- SMITH 800 ALLEN 1600 800 WARD 1250 -350 JONES 2975 1725 BLAKE 2850 -125 CLARK 2450 -400 TURNER 1500 -950 MARTIN 1250 -250 KING 5000 3750 JAMES 950 -4050 FORD 3000 2050 MILLER 1300 -1700 SCOTT 3000 1700 ADAMS 1100 -1900
Обратите внимание на разумное поведение аналитических функций (вообще) на границах упорядоченных множеств данных (строка со Смитом). Неприятность в другом: NULL, который порождает Oracle в своем ответе, имеет здесь смысл "значение неприменимо", а не "неизвестно", как хотелось бы (обсуждение разницы приводилось выше).
"Три из имеющихся видов регрессии для оценки взаимозависимости значений в столбцах":
SELECT REGR_SLOPE ( sal, comm ) AS slope , REGR_AVGX ( sal, comm ) AS avgsal , REGR_AVGY ( sal, comm ) AS avgcomm FROM emp ;
Ответ:
SLOPE AVGSAL AVGCOMM ---------- ---------- ---------- -.20642202 550 1400
Названия функций для имеющихся прочих видов регрессии приведены в документации по Oracle. Обратите внимание на вероятную формальность этого запроса, если не предположить, что связь между зарплатой и комиссионными в жизни вдруг существует, в результате чего запрос приобретает смысл.
Во фразе SELECT (а также в качестве аргумента функции — в составе любого выражения) можно использовать выражение типа "ссылка на курсор". Оно строится с помощью функции CURSOR, аргументом которой передается другое предложение SELECT, и возвращает скаляр — ссылку на курсор. Хотя в SQL*Plus эту функцию и разрешено задействовать, основное применение ей — в программной обработке результатов предложения SELECT. Пример в SQL*Plus:
SELECT dname , CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno ) FROM dept;
В качестве элемента более общего выражения курсорное выражение может войти только будучи предъявленным как аргумент функции; в примере ниже это вымышленная "табличная" функция JOBSEMPS:
SELECT TABLE ( jobsemps ( CURSOR ( SELECT * FROM emp ) ) ) AS "Nested Table:" FROM dual;
Такая техника позволяет передавать подпрограмме для обработки нефиксированный по количеству (но фиксированный по структуре) массив строк.
В SQL фирмы Oracle тип ссылки на курсор отсутствует. Он имеется только в PL/SQL.
Версия Oracle 11 позволила дополнить фразу FROM подчиненными фразами SELECT столбцов, "импортированных" из этих конструкций. Обе формулировки предназначены для переформатирования данных таблиц средствами SQL, без программирования. Они удобны для построения отчетов и анализа имеющихся данных.
Сочетание SELECT … FROM … позволяет развернуть данные одного столбца в отдельные столбцы конечного результата.
Рассмотрим для начала запрос о наличии в разных отделах сотрудников на разных должностях:
SQL> SELECT job, deptno FROM emp; JOB DEPTNO --------- ---------- CLERK 20 SALESMAN 30 SALESMAN 30 MANAGER 20 SALESMAN 30 MANAGER 30 MANAGER 10 ANALYST 20 PRESIDENT 10 SALESMAN 30 CLERK 20 CLERK 30 ANALYST 20 CLERK 10
В каждом отделе имеется ноль или более сотрудников на каждой из вообще существующих должностей. Данные об их количестве удобно представить, посвятив каждому департаменту отдельный столбец:
SQL> SELECT * 2> FROM ( SELECT job, deptno FROM emp ) 3> PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ); JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1 2 1 0 SALESMAN 0 0 4 0 PRESIDENT 1 0 0 0 MANAGER 1 1 1 0 ANALYST 0 2 0 0
Внутренняя отработка такого предложения осуществляется как при группировке GROUP BY job (см. ниже), но приводит к появлению дополнительных столбцов вместо, казалось бы, указанного DEPTNO. Вот как мог бы выглядеть аналог последнего запроса в версиях Oracle до 11:
SELECT JOB , COUNT ( CASE WHEN deptno = 10 THEN 1 END ) "10" , COUNT ( CASE WHEN deptno = 20 THEN 1 END ) "20" , COUNT ( CASE WHEN deptno = 30 THEN 1 END ) "30" , COUNT ( CASE WHEN deptno = 40 THEN 1 END ) "40" FROM ( SELECT job, deptno FROM emp ) GROUP BY job ;
Именно так Oracle и обработает запрос с
Для правильного разворачивания существенно правильно указать структуру источника во фразе FROM. Если в последнем запросе после FROM указать не подзапрос, а таблицу EMP (или же в подзапросе указать другие поля EMP), принцип разворачивания изменится и результат окажется иным.
Вот некоторые другие примеры. Выдать только данные по отделам 10 и 30:
SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 30 ) ) ;
То же самое:
SELECT job, "10", "30" FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Самостоятельное задание имен столбцам результата делается как во фразе SELECT:
SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10 tenth, 30 thirtieth ) ) ;
Выдача сумм окладов сотрудников по каждой должности в указанных отделах:
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( SUM ( sal ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Выдача сразу двух агрегатов (суммы зарплат и количества сотрудников):
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( SUM ( sal ), COUNT ( * ) c FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Сформировать столбцы для сотрудников 10-го отдела с окладами 5000 и 1300 и сотрудников 30-го отдела с окладами 1250:
SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT ( COUNT ( * ) c FOR ( deptno, sal ) IN ( ( 10, 5000), ( 10, 1300), ( 30, 1250 ) ) );
Во фразе
Фраза UNPIVOT выполняет действие, содержательно противоположное фразе
Создадим таблицу по запросу выше:
CREATE TABLE total AS SELECT * FROM ( SELECT job, deptno FROM emp ) PIVOT ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) ) ;
Выдача данных со сворачиванием показателей в один столбец:
SQL> SELECT * 2 FROM total 3 UNPIVOT ( jobcount FOR deptno IN ( "10", "20", "30", "40" ) ) 4 ; JOB DE JOBCOUNT --------- -- ---------- CLERK 10 1 CLERK 20 2 CLERK 30 1 CLERK 40 0 SALESMAN 10 0 SALESMAN 20 0 SALESMAN 30 4 SALESMAN 40 0 PRESIDENT 10 1 PRESIDENT 20 0 PRESIDENT 30 0 PRESIDENT 40 0 MANAGER 10 1 MANAGER 20 1 MANAGER 30 1 MANAGER 40 0 ANALYST 10 0 ANALYST 20 2 ANALYST 30 0 ANALYST 40 0
От выдачи JOBCOUNT можно отказаться, указав вместо SELECT * … формулировку SELECT job, deptno ….
Заметьте, что агрегат COUNT в отличие от всех остальных всегда возвращает значение, хоть бы 0. Если бы в столбце JOBCOUNT оказались пропуски (NULL), был бы законным вопрос, как их учитывать при сворачивании данных. Две возможные схемы поведения обозначаются уточнениями UNPIVOT INCLUDE NULLS и UNPIVOT EXCLUDE NULLS.
Подобное сворачивание в столбец (иначе, переворачивание, "транспонирование") таблицы TOTAL позволяет ответить на вопросы типа "В каких отделах число продавцов больше 10?", или даже "больше 10%". Иначе SQL этого делать не умеет.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.