Средства выбора вариантов

ЛЕКЦИЯ № 7 3.2.3.5. Средства выбора вариантов.................1 3.2.4. Секция GROUP BY..........................................10 3.2.5. Секция HAVING..............................................17 3.2.6. Секция ORDER BY..........................................21 3.2.7. Статистический анализ данных в SQL...........26 3.2.3.5. Средства выбора вариантов В стандарте SQL определены средства для выбора вариантов действий в зависимости от значений данных. К таким средствам относятся операция выбора CASE, а также различные функции выбора вариантов. В Firebird в качестве функций выбора вариантов используются функции COALESCE, NULLIF, IIF и DECODE, 3 из которых (COALESCE, NULLIF и DECODE) определены в стандарте SQL, а одна (IIF) является расширением языка в СУБД Firebird. Эти функции выбора вариантов при определенных условиях фактически являются сокращенными формами операции CASE. Они всегда могут быть заменены эквивалентными конструкциями CASE, но более сложно записанными. Средства выбора вариантов могут использоваться в списке возвращаемых столбцов секции SELECT, в секции WHERE, а также в качестве элементов списка сортировки секции ORDER BY (см. разд. 3.2.6) и списка группировки секции GROUP BY (см. разд. 3.2.4). Средства выбора вариантов применимы также в запросах языка DML (см. раздел 5.2.1) СУБД Oracle поддерживает определенную стандартом операцию CASE и стандартные функции выбора COALESCE, NULLIF и DECODE и свою функцию NVL. СУБД MS SQL Server поддерживает операцию CASE и функции COALESCE, NULLIF и IIF, а также, дополнительно, свою собственную функцию выбора CHOOSE. Рассмотрим подробно различные средства выбора вариантов. Операция выбора CASE. Операция выбора CASE позволяет определить результат, исходя из определенных условий. Имеются две формы операции CASE: простая и с поиском. Простая форма имеет синтаксис: CASE <выражение> {WHEN <значение1> THEN результат1} [{WHEN <значение2> THEN результат2}] ... [ ELSE результат(N+1)] END. В этой форме последовательно сравниваются значения при фразах WHEN со значением заданного выражения. При первом же совпадении возвращается значение при соответствующей фразе THEN. Если совпадений нет, то возвращается результат(N+1) при фразе ELSE, если она используется. Синтаксис операции CASE с поиском: CASE {WHEN <условие_поиска1> THEN результат1} [{ WHEN <условие_поиска2> THEN результат2}]... [ ELSE результат(N+1) ] END. 1 В случае использования операции CASE с поиском последовательно проверяются все условия при фразах WHEN. При первом истинном выражении возвращается результат соответствующей фразы THEN. Если ни одно условие при фразах WHEN не оказалось истинным, возвращается результат(N+1) при фразе ELSE, если она указана. Условие в CASE с поиском аналогично условию отбора строк в секции WHERE. В CASE в качестве результата после слов THEN или ELSE может быть задано либо выражение (которое может включать константы, имена столбцов, функции, а также арифметические операции и операцию конкатенации), либо NULL. Если фраза ELSE отсутствует, то операция CASE при отсутствии совпадения (или истинного условия) возвращает NULL. Рассмотрим примеры использования операции CASE в списке возвращаемых элементов секции SELECT. Приведем пример простой операции CASE. Пусть необходимо вывести следующую информацию о ремонтных заявках абонента, имеющего лицевой счет с номером '115705': номер заявки, номер лицевого счета абонента, подавшего заявку, код неисправности. В зависимости от значения столбца Executed необходимо вывести, погашена заявка или нет. Запрос в Firebird будет выглядеть таким образом (рис. 3.53): SELECT RequestCD, (' Номер л/с абонента '|| AccountCD) AS "Ab_Info", (' Код неисправности '|| FailureCD) AS "Failure", CASE Executed WHEN False THEN 'Не погашена' ELSE 'Погашена' END FROM Request WHERE AccountCD='115705'; REQUESTCD Ab_Info 2 Номер л/с абонента 115705 15 Номер л/с абонента 115705 16 Номер л/с абонента 115705 17 Номер л/с абонента 115705 18 Номер л/с абонента 115705 Failure CASE Код неисправности 1 Погашена Код неисправности 5 Погашена Код неисправности 3 Не погашена Код неисправности 5 Погашена Код неисправности 3 Погашена Рис. 3.53. Результат выполнения запроса с простой операцией CASE Пусть необходимо вывести информацию об оплатах со значением от 50 до 100 с указанием срока давности оплаты: если оплата была произведена до 2011 г., то вывести 'Давно', если оплата была произведена в 2011 г. или 2 2012 г., то вывести 'Не очень давно', если позднее – 'Недавно'. Запрос в Firebird с использованием операции CASE с поиском (рис. 3.54): SELECT PayFactCD, AccountCD, PaySum, (CASE WHEN PayDate < '01.01.2011' THEN 'Давно' WHEN PayDate BETWEEN '01.01.2011' AND '31.12.2012' THEN 'Не очень давно' ELSE 'Недавно' END) AS "Oplata" FROM PaySumma WHERE PaySum BETWEEN 50 AND 100; PAYFACTtCD 1 3 7 9 10 12 14 15 16 27 29 37 42 ACCOUNTCD 005488 005488 136160 080047 080047 080613 115705 136169 443069 080270 005488 080270 080270 PAYSUM 58,70 56,00 56,00 80,00 80,00 58,50 58,70 58,70 80,00 60,00 65,00 58,10 60,00 Oplata Не очень давно Недавно Недавно Не очень давно Не очень давно Не очень давно Не очень давно Не очень давно Не очень давно Не очень давно Давно Не очень давно Не очень давно Рис. 3.54. Результат выполнения запроса с операцией CASE с поиском Примером использования SUM по CASE может быть запрос SELECT COUNT(*) AS "Всего заявок" , SUM(CASE WHEN ExecutionDate IS NULL THEN 1 ELSE 0 END) AS "невыполненных", SUM(CASE WHEN NOT Executed THEN 1 ELSE 0 END) AS "непогашенных" FROM Request;, возвращающий общее число ремонтных заявок, число невыполненных и непогашенных заявок. Функция COALESCE используется для замены неопределенного значения на любое другое и имеет следующий синтаксис: COALESCE (<выражение1> , <выражение2> [, <выражение3> ]...) . Эта функция имеет два или более параметров и возвращает значение первого из параметров, отличного от NULL. Если значения всех выражений 3 определены как NULL, то функция COALESCE вернет NULL. Функция COALESCE фактически представляет собой сокращение операции CASE и в зависимости от числа аргументов может быть заменена следующими эквивалентными ей конструкциями:  конструкция COALESCE (<выражение1>, <выражение2>) эквивалентна конструкции CASE WHEN <выражение1> IS NOT NULL THEN <выражение1> ELSE <выражение2> END;  конструкция COALESCE (<выражение1>, <выражение2>, ..., <выражениеN>) для N >= 3 эквивалентна конструкции CASE WHEN <выражение1> IS NOT NULL THEN <выражение1> ELSE COALESCE (<выражение2>,..., <выражениеN>) END. Таким образом, эквивалентная конструкция CASE всегда содержит в качестве условия поиска проверку соответствующего выражения из списка функции COALESCE на неравенство NULL. Например, требуется вывести информацию о датах выполнения ремонтных заявок, поступивших от абонентов с номерами лицевых счетов '005488', '115705' и '080270'. Если заявка не выполнена, то вывести дату ее поступления (рис. 3.55). Если ни дата поступления, ни дата выполнения неизвестны, то вывести 'Дата неизвестна'. Соответствующий запрос в Firebird будет выглядеть таким образом: REQUESTCD 1 Date_Info 2011-12-20 2 2011-08-12 5 2011-12-31 13 2010-12-05 14 2013-04-13 15 2010-09-23 16 2011-12-28 17 2011-09-06 18 2013-01-04 19 2011-12-27 Рис. 3.55. Результат выполнения запроса при использовании COALESCE 4 SELECT RequestCD, COALESCE(ExecutionDate,IncomingDate, 'Дата неизвестна') AS "Date_Info" FROM Request WHERE AccountCD IN ('005488', '115705', '080270'); В результате для ремонтных заявок с номерами 5 и 16, которые не выполнены, выведена дата поступления заявки, а для остальных заявок – дата выполнения. Представим данный запрос с помощью операции CASE: SELECT RequestCD, (CASE WHEN ExecutionDate IS NOT NULL THEN ExecutionDate ELSE CASE WHEN IncomingDate IS NOT NULL THEN IncomingDate ELSE 'Дата неизвестна' END END) AS "Date_Info" FROM Request WHERE AccountCD IN ('005488', '115705', '080270'); Результат выполнения данного запроса совпадает с результатом выполнения предыдущего запроса, использующего функцию COALESCE (рис. 3.55), однако форма записи более длинная и сложная для понимания, чем предыдущая. Следует обратить внимание, что приведенные запросы подразумевают, что сервером Firebird будет выполнено неявное преобразование типа даты (значения ExecutionDate, IncomingDate) в строковый тип. Если, например, составлять аналогичный запрос в Oracle, то потребовалось бы использовать функцию явного преобразования TO_CHAR для приведения значений ExecutionDate и IncomingDate к строке. В Oracle, как правило, применяется своя собственная функция NVL. Эта функция имеет два параметра: NVL(<выражение1> , <выражение2>). Если значение <выражение1> определено, то функция возвращает его значение. Если значение <выражение1> является NULL, то вместо него функция возвращает значение <выражение2> SELECT NVL(Phone,'Нет телефона') FROM Abonent; Функция NULLIF производит замену заданного значения на NULL. Синтаксис использования имеет следующий вид: NULLIF (<выражение1>, <выражение2>), где <выражение1> – столбец или вычисляемое выражение; <выражение2> – вычисляемое выражение (может включать константы, имена столбцов, функции, а также арифметические операции и операцию конкатенации), со значением которого сравнивается значение <выражение1>. Функция NULLIF возвращает NULL, если значение <выражение1> совпадает со значением <выражение2>, и значение <выражение1> в противном случае. Использование функции NULLIF аналогично использованию следующей конструкции операции CASE: CASE WHEN <выражение1> = <выражение2> THEN NULL ELSE <выражение1> END. Пусть, например, требуется вывести номера лицевых счетов, ФИО абонентов и номера их телефонов, учитывая, что номер телефона '556893' 5 уже не существует, а новый номер неизвестен (рис. 3.56). Запрос в Firebird будет выглядеть так: SELECT AccountCD, Fio, NULLIF (Phone,'556893') FROM Abonent; ACCOUNTCD FIO CASE 005488 Аксенов С.А. NULL 115705 Мищенко Е.В. 769975 015527 Конюхов В.С. 761699 443690 Тулупова М.И. 214833 136159 Свирина З.А. NULL 443069 Стародубцев Е.В. 683014 136160 Шмаков С.В. NULL 126112 Маркова В.П. 683301 136169 Денисова Е.К. 680305 080613 Лукашина Р.М. 254417 080047 Шубина Т. П. 257842 080270 Тимошкина Н.Г. 321002 Рис. 3.56. Результат выполнения запроса при использовании NULLIF Как следует из результата, у абонента с ФИО Аксенов С.А., имеющего в БД номер телефона 556893, теперь выводится сообщение, что номер его телефона неизвестен. С помощью функции NULLIF можно некоторые или все значения в заданном столбце поменять на NULL (рис. 3.57): SELECT AccountCD, Fio, NULLIF (Phone, Phone) FROM Abonent; ACCOU FIO CASE NTCD 005488 Аксенов С.А. NULL 115705 Мищенко Е.В. NULL 015527 Конюхов В.С. NULL 443690 Тулупова М.И. NULL 136159 Свирина З.А. NULL 443069 Стародубцев Е.В. NULL 136160 Шмаков С.В. NULL 126112 Маркова В.П. NULL 136169 Денисова Е.К. NULL 080613 Лукашина Р.М. NULL 080047 Шубина Т. П. NULL 080270 Тимошкина Н.Г. NULL Рис. 3.57. Результат выполнения запроса на замену всего столбца на NULL Две предыдущие рассмотренные функции соответствовали стандарту SQL. СУБД Firebird и MS SQL Server предоставляют возможность 6 использования также функции IIF, которая является расширением языка и не описана в стандарте SQL. Функция имеет синтаксис: IIF (<условие_поиска>, <выражение1>, <выражение2>) и эквивалентна следующей конструкции операции CASE: CASE WHEN <условие_поиска> THEN <выражение1> ELSE <выражение2> END. Данная конструкция возвращает значение <выражение1>, если <условие_поиска> истинно, и значение <выражение2> в противном случае. Например, необходимо вывести информацию о типах неисправностей, не детализируя неисправности газовой плиты (рис. 3.58): SELECT FailureCD, IIF(FailureNM Like '%плит%', 'Неисправность плиты', FailureNM) AS "Failure_Type" FROM Disrepair; FAILURECD Failure_Type 1 Засорилась водогрейная колонка 2 Не горит АГВ 3 Течет из водогрейной колонки 4 Неисправна печная горелка 5 Неисправен газовый счетчик 6 Неисправность плиты 7 Неисправность плиты 8 Неисправность плиты 12 Неизвестна Рис. 3.58. Результат выполнения запроса при использовании IIF Функция DECODE является сокращенной формой простой операции CASE и имеет формат: DECODE (<выражение>, <значение1>, результат1 [ , <значение2>, результат2 ... ] [,<результат_по_умолчанию>]. Эта функция последовательно сравнивает значение выражения, заданного первым аргументом, с аргументами <значение1>, <значение2> и т. д. Если сравнение оказывается истинным, возвращается соответственно результат1 или результат2 и т. д. Если значение выражения не совпало ни с одним из значений из списка, то возвращается <результат_по_умолчанию> (аналогичен результату, задаваемому после фразы ELSE в операции CASE). Если <результат_по_умолчанию> не задан, то возвращается NULL. Типы значения исходного выражения и его декодированного значения (результат1, результат2 и т. д.) могут не совпадать. С помощью следующего запроса в Firebird можно вывести первые 5 строк из таблицы ремонтных заявок, указав, погашены заявки или нет: 7 SELECT FIRST 5 RequestCD, DECODE (Executed, False, 'Заявка не погашена', True, 'Заявка погашена', 'Неизвестно') FROM Request; Результат выполнения запроса представлен на рис. 3.59. REQUESTCD 1 2 3 5 6 CASE Заявка погашена Заявка погашена Заявка не погашена Заявка не погашена Заявка погашена Рис. 3.59. Результат работы функции DECODE Примерами использования рассмотренных средств выбора вариантов в секции WHERE может быть такой запрос: SELECT * FROM Request WHERE NULLIF(ExecutionDate,Incomingdate) IS NULL;, возвращающий информацию по невыполненным заявкам и заявкам, у которых равны даты регистрации и выполнения, а также следующий запрос: SELECT Accountcd,Servicecd,PaySum FROM Paysumma WHERE Servicecd = CASE WHEN Accountcd=136169 THEN 1 WHEN Accountcd=136160 THEN 3 WHEN Accountcd=080270 THEN 4 ELSE 2 END;, выбирающий для абонентов значения их платежей за заданные услуги. Функция CHOOSE в MS SQL Server по указанному индексу возвращает элемент из списка значений [37]. Функция имеет формат: CHOOSE ( <индекс>, <значение1>, <значение2> [,<значениеN> ] ), где <индекс> - целочисленное выражение, которое представляет отсчитываемый от 1 индекс в списке элементов, следующих за ним. Если указанное значение индекса имеет числовой тип, отличный от типа int, то значение неявно преобразуется в целое. Если значение индекса выходит за границы массива значений, то функция CHOOSE возвращает значение NULL. Функция CHOOSE действует подобно индексу массива, где массив состоит из следующих за аргументом индекса аргументов. Аргумент индекса определяет, какие из следующих за ним значений будут возвращены. Следующий запрос возвращает время года, в котором поступила ремонтная заявка. Функция MONTH используется, чтобы вернуть значение месяца из столбца Incomingdate. SELECT RequestCD, CHOOSE(MONTH(Incomingdate),'Winter','Winter', 8 'Spring','Spring','Spring','Summer','Summer','Summer', 'Autumn','Autumn','Autumn','Winter') AS Season FROM Request; 3.2.4. Секция GROUP BY Запрос, включающий в себя секцию GROUP BY, называется запросом с группировкой, поскольку он объединяет строки исходной таблицы в группы и для каждой группы строк генерирует одну строку НД. Элементы, указанные в секции GROUP BY, называются элементами группировки, и именно они определяют, по какому признаку строки делятся на группы. При этом группой называется набор строк, имеющих одинаковое значение в элементе (элементах) группировки. Синтаксис секции GROUP BY имеет вид: GROUP BY <элемент_группировки1> [, <элемент_группировки2>]…, где <элемент_группировки> := {[<таблица>.] столбец | порядковый_номер_столбца | псевдоним_столбца | <выражение>}. Фактически в качестве элемента группировки может выступать любой возвращаемый элемент, указанный в секции SELECT, кроме значений агрегатных функций. В выражение, представляющее собой <элемент_группировки>, могут входить скалярные функции из различных контекстов или это может быть любая CASE-операция. Следует учесть, что в Oracle и MS SQL Server порядковый_номер_столбца и псевдоним_столбца не могут выступать в качестве элементов группировки. Использование секции GROUP BY имеет смысл только при наличии в списке возвращаемых элементов секции SELECT хотя бы одного вычисляемого столбца или агрегатной функции. Агрегатная функция берет столбец значений и возвращает одно значение. Секция GROUP BY указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каждой группы одну строку результатов. Например, если NACHISLYEAR AVG необходимо вычислить 2010 76,40 средние значения начислений 2011 125,24 за каждый год (рис. 3.60), то в 2012 213,09 Firebird можно воспользоваться 2013 196,91 запросом: Рис. 3.60. Результат выполнения запроса с группировкой SELECT NachislYear, AVG(NachislSum) FROM NachislSumma GROUP BY NachislYear; 9 В Firebird возможна группировка результатов запроса на основании порядкового номера возвращаемого элемента в секции SELECT. Таким образом, запрос SELECT NachislYear, AVG(NachislSum) FROM NachislSumma GROUP BY 1; выдаст такой же результат, как и предыдущий запрос. Summa_20 COUNT Также в Firebird в секции GROUP BY можно 8,30 2 указывать псевдонимы 10,60 1 столбцов. Например, 12,60 2 следующий запрос 15,30 1 позволит для каждого 17,80 1 значения, меньшего 20, 18,30 2 подсчитать количество 18,32 1 фактов его начислений 19,56 1 (рис. 3.61): Рис. 3.61. Результат SELECT NachislSum AS выполнения запроса "Summa_20", COUNT(*) с группировкой по FROM NachislSumma псевдониму WHERE NachislSum < 20 GROUP BY "Summa_20"; Если для каждого абонента требуется вывести общее количество оплат с указанным в этой же строке максимальным для него значением оплаты, то можно сгруппировать результат по номеру лицевого счета и использовать в качестве второго возвращаемого элемента выражение с агрегатными функциями COUNT и MAX. Соответствующий запрос будет выглядеть так: SELECT AccountCD, (COUNT(*) || ' – с максимальной суммой ' || MAX(PaySum)) AS "Pay_Count" FROM PaySumma GROUP BY AccountCD; Результат выполнения запроса представлен на рис. 3.62. ACCOUNTCD Pay_Count 005488 6 – с максимальной суммой 280.00 015527 6 – с максимальной суммой 611.30 080047 7 – с максимальной суммой 271.60 080270 8 – с максимальной суммой 460.00 080613 8 – с максимальной суммой 258.80 115705 9 – с максимальной суммой 553.85 126112 4 – с максимальной суммой 10 136159 136160 136169 443069 443690 435.50 5 – с максимальной суммой 420.00 6 – с максимальной суммой 240.00 8 – с максимальной суммой 528.44 6 – с максимальной суммой 466.69 4 – с максимальной суммой 485.00 Рис. 3.62. Результат выполнения запроса с группировкой В SQL можно группировать результаты запроса на основании двух или более элементов. Например, необходимо для каждого абонента вывести наименьшее значение оплаты за 2011 и 2012 годы (результирующий НД должен включать первые 10 строк). Для этого нужно сгруппировать таблицу PaySumma по номерам лицевых счетов абонентов и по годам (рис. 3.63), применяя к значениям оплат агрегатную функцию MIN: SELECT FIRST 10 AccountCD, PayYear, MIN(Paysum) FROM PaySumma WHERE PayYear IN (2011, 2012) GROUP BY AccountCD, PayYear; ACCOUNTCD 005488 005488 015527 080047 080047 080270 080270 080613 080613 115705 PAYYEAR 2011 2012 2012 2011 2012 2011 2012 2011 2012 2011 MIN 58,70 280,00 20,00 32,56 19,56 30,00 60,00 58,50 11,00 37,80 Рис. 3.63. Результат выполнения запроса с группировкой по двум столбцам В качестве элемента группировки допускается определение выражений, в которых разрешено использовать различные скалярные SQL-функции, а также средства выбора вариантов. Например, требуется для каждой группы номеров лицевых счетов, начинающихся с одинаковых трех символов, вы вести количество абонентов, имеющих такие счета (рис. 3.64): 11 SELECT ('Начало счета ' || SUBSTRING(AccountCD FROM 1 FOR 3)) AS "Acc_3", COUNT(*) FROM Abonent GROUP BY "Acc_3"; Acc_3 COUNT(*) Начало 1 счета 005 Начало 1 счета 015 Начало 3 счета 080 Начало 1 счета 115 Начало 1 счета 126 Начало 3 счета 136 Начало 2 счета 443 Рис. 3.64. Результат выполнения запроса с группировкой по выражению В Oracle запрос, аналогичный предыдущему, будет выглядеть так: SELECT ('Начало счета '|| RPAD(AccountCD,3)) AS Acc_3, COUNT(*) FROM Abonent GROUP BY ('Начало счета '|| RPAD(AccountCD,3)); Следует учесть, что нельзя выводить по отдельности столбцы, участвующие в выражении группировки. Например, в результате попытки выполнить следующий запрос: SELECT AccountCD, COUNT(*) FROM Abonent GROUP BY SUBSTRING (AccountCD FROM 1 FOR 3); будет выдано сообщение об ошибке, так как столбец AccountCD, участвующий в выражении группировки, присутствует в списке возвращаемых элементов секции SELECT. Таблица разделяется на группы по первым трем совпадающим символам номера лицевого счета, и каждый элемент из списка выбора должен иметь по одному значению для группы. В одну группу может входить несколько номеров лицевых счетов, но если попытаться вывести каждый номер лицевого счета AccountCD, будет выдано сообщение об ошибке. Рассмотрим пример использования операции CASE в качестве элемента группировки. Например, требуется вывести средние значения начислений 12 за годы до 2012 г. и за годы после 2011 г. (рис. 3.65). Соответствующий запрос в Firebird будет выглядеть так: SELECT 'В среднем начислено ' || (CASE WHEN NachislYear < 2012 THEN 'до 2012 года' ELSE 'после 2011 года' END) AS "God", AVG (NachislSum) AS "Average_Sum" FROM NachislSumma GROUP BY God; God В среднем начислено до 2012 г. В среднем начислено после 2011 г. Average_Sum 109,98 205,00 Рис. 3.65. Результат выполнения запроса с группировкой по CASE СУБД MS SQL Server и Oracle предоставляют расширенный синтаксис секции GROUP BY засчет использования специальных функций: GROUP BY { <элемент_группировки1> | { ROLLUP | CUBE | GROUPING SETS } ( <элемент> [, <элемент>]... ) } [, { <элемент_группировки2> | { ROLLUP | CUBE | GROUPING SETS } ( <элемент> [, <элемент>]... ) } ] … Функция ROLLUP формирует статистические строки секции GROUP BY и строки подытогов или строки со статистическими вычислениями высокого уровня, а также строки общего итога. Количество возвращаемых группирований равно количеству выражений в списке элементов функции плюс один. Например, если имеется запрос вида SELECT a, b, c, SUM (<выражение>) FROM t GROUP BY ROLLUP (a,b,c); то для каждого уникального сочетания значений (a, b, c), (a, b) и (a) формируется одна строка с подытогом. Вычисляется также строка общего итога. Столбцы свертываются справа налево. Последовательность расположения столбцов влияет на выходное группирование ROLLUP и может отразиться на количестве строк в результирующем наборе. Результирующий набор, созданный функцией ROLLUP, подобен набору, возвращаемому оператором COMPUTE BY. Однако функция ROLLUP имеет следующие преимущества: - ROLLUP возвращает единственный результирующий набор, а COMPUTE BY – несколько результирующих наборов, что приводит к усложнению кода приложения; 13 - ROLLUP, в отличие от COMPUTE BY, может быть использована в серверном курсоре; - в некоторых случаях оптимизатор запросов при работе с функцией ROLLUP дает лучшие результаты, чем при работе с оператором COMPUTE BY. Функция CUBE формирует статистические строки секции GROUP BY, строки со статистическими вычислениями высокого уровня функции ROLLUP и строки с результатами перекрестных вычислений. Выходные данные CUBE являются группированием для всех перестановок выражений в списке элементов оператора. Количество формируемых группирований равно (2n), где n – количество выражений в списке элементов оператора. Например, имеется запрос следующего вида: SELECT a, b, c, SUM (<выражение>) FROM t GROUP BY CUBE (a,b,c); Формируется одна строка для каждого уникального сочетания значений (a, b, c), (a, b), (a, c), (b, c), (a), (b) и (c) с подытогом для каждой строки и строкой общего итога. Выходные данные CUBE не зависят от порядка столбцов. Функция GROUPING SETS указывает несколько группирований данных в одном запросе. Выполняется статистическая обработка только указанных групп, а не полного набора статистических данных, формируемых с помощью функций CUBE или ROLLUP. Результаты эквивалентны тем, что формируются с применением оператора UNION ALL к указанным группам. Функция GROUPING SETS может содержать единственный элемент или список элементов, могут быть указаны группирования, эквивалентные тем, которые возвращают функции ROLLUP или CUBE. Список элементов функции GROUPING SETS может содержать функции ROLLUP или CUBE. Пустая группа ( ) формирует итог. Для того, чтобы улучшить обработку значений NULL в строках, созданных функциями ROLLUP или CUBE, в секции SELECT запросов с группировкой доступно использование специальной функции GROUPING. Функция GROUPING возвращает значение 1, если строка - это подытог, созданный функцией ROLLUP или CUBE, и 0 в противном случае. Следует отметить, что функции ROLLUP, CUBE и GROUPING SETS существенно упрощают программирование на SQL тем, что облегчают анализ данных по нескольким измерениям, устраняют необходимость проектировать запросы с объединением UNION (см. далее п. 3.3.3). Как следует из всех предыдущих примеров, на запросы с группировкой накладываются некоторые ограничения. 14 1. В секции GROUP BY должны быть указаны столбцы или выражения, которые используются в качестве возвращаемых элементов секции SELECT (за исключением агрегатных функций). 2. Все элементы списка возвращаемых столбцов должны иметь одно значение для каждой группы строк. Это означает, что возвращаемым элементом в секции SELECT может быть: константа; агрегатная функция, возвращающая одно значение для всех строк, входящих в группу; элемент группировки, который по определению имеет одно и то же значение во всех строках группы; функция, которая используется в качестве элемента группировки; выражение, включающее в себя перечисленные выше элементы. На практике в список возвращаемых элементов запроса с группировкой всегда входят столбец или функция группировки и агрегатная функция. Если агрегатная функция не указана, значит, запрос можно более просто выразить с помощью ключевого слова DISTINCT без использования секции GROUP BY. Например, запрос с группировкой вида SELECT StreetCD FROM Abonent GROUP BY StreetCD; может быть более просто выражен с помощью DISTINCT (такой запрос был приведен ранее в п. 3.2.1, рис. 3.9). И наоборот, если не включить в результаты запроса столбец или функцию, по которым производится группировка, то станет невозможно определить, к какой группе относится каждая строка результатов. Наконец следует отметить, что по умолчанию строки сортируются в порядке возрастания значений столбцов, заданных в секции GROUP BY. Изменить этот порядок можно с помощью секции ORDER BY. 3.2.5. Секция HAVING Секция HAVING запроса SELECT применяется для наложения условий на строки, возвращаемые при использовании секции GROUP BY. Секция состоит из ключевого слова HAVING, за которым следует <условие_поиска>: <условие_поиска> ::= [NOT] <условие_поиска1> [[AND|OR][NOT] <условие_поиска2>]…, где <условие_поиска> позволяет исключить из результата группы, не удовлетворяющие заданным условиям. Условие поиска совпадает с условием поиска, рассмотренным выше для секции WHERE, однако в качестве значения часто используется значение, возвращаемое агрегатными функциями. Результат совместной работы HAVING с GROUP BY аналогичен результату работы запроса SELECT с секцией WHERE с той разницей, что HAVING выполняет те же функции над строками (группами) возвращаемого 15 набора данных, а не над строками исходной таблицы. Из этого следует, что секция HAVING начинает свою работу после того, как секция GROUP BY разделит базовую таблицу на группы. В противоположность этому использование секции WHERE приводит к тому, что сначала отбираются строки из базовой таблицы и только после этого отобранные строки начинают использоваться. Например, чтобы для каждого из абонентов, которые подавали более одной ремонтной заявки, вывести количество заявок и дату самой ранней из них (рис. 3.66), нужно выполнить запрос: SELECT AccountCD, COUNT(*), MIN(IncomingDate) FROM Request GROUP BY AccountCD HAVING COUNT(*) > 1; ACCOUNTCD 005488 080047 080270 115705 136160 136169 443069 MIN COUNT 3 2 2 5 2 2 2 04.09.2010 11.10.2011 17.12.2011 20.09.2010 18.05.2011 07.05.2011 08.08.2011 Рис. 3.66. Результат выполнения запроса с HAVING Работа этого запроса заключается в следующем. Сначала GROUP BY из таблицы Request формирует группы, состоящие из одинаковых значений столбца AccountCD. После этого в секции HAVING происходит подсчет числа строк, входящих в каждую группу, и в НД включаются все группы, которые содержат более одной строки. Следует отметить, что если задать условие COUNT(*)>1 в секции WHERE, то такой запрос потерпит неудачу, так как секция WHERE производит оценку в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк. В то же время из этого не следует, что секция WHERE не используется с секцией HAVING. Следует учесть, что секция HAVING должна ссылаться только на агрегатные функции и элементы, выбранные GROUP BY, т. е. должны использоваться аргументы, которые имеют одно значение на группу вывода. Например, такой запрос потерпит неудачу: SELECT AccountCD, MAX(IncomingDate) FROM Request GROUP BY AccountCD HAVING FailureCD=1; 16 Столбец FailureCD не может быть использован в секции HAVING, потому что он может иметь (и действительно имеет) больше чем одно значение на группу вывода. Таким образом, следующий запрос является корректным: SELECT AccountCD, MAX(IncomingDate) FROM Request WHERE FailureCD=1 GROUP BY AccountCD; Результат запроса представлен на рис. 3.67. ACCOUNTCD 005488 080270 115705 136169 MAX 17.12.2011 31.12.2011 07.08.2011 06.11.2011 Рис. 3.67. Результат выполнения корректного запроса Если необходимо узнать максимальные значения начислений для абонентов с лицевыми счетами '005488' и '080047' (рис. 3.68), то можно выполнить запрос, в котором секция HAVING ссылается на элемент, указанный в GROUP BY. Данный запрос является корректным: SELECT AccountCD, MAX (NachislSum) FROM NachislSumma GROUP BY AccountCD HAVING AccountCD IN ('005488', '080047'); ACCOUNTCD MAX 005488 279,80 080047 271,60 Рис. 3.68. Результат выполнения запроса Предположим, необходимо для каждой неисправности, с которой последняя заявка поступила позднее 31.08.2011, вывести дату поступления последней заявки, общее количество заявок и сколько из них было выполнено (рис. 3.69). Для решения этой задачи запрос будет выглядеть таким образом: SELECT FailureCD, MAX(IncomingDate),COUNT(*), ' Из них выполнено ' || Count(ExecutionDate) FROM Request GROUP BY FailureCD HAVING MAX(IncomingDate) > '31.08.2011'; FAILURECD MAX 1 31.12.201 1 2 20.10.201 2 3 28.12.201 2 COUNT CONCATENATION 4 Из них выполнено 3 4 Из них выполнено 4 2 Из них выполнено 1 17 6 8 12 04.04.201 3 17.12.201 1 28.02.201 2 3 Из них выполнено 3 2 Из них выполнено 2 2 Из них выполнено 2 Рис. 3.69. Результат выполнения запроса к таблице Request В следующем примере в секции HAVING используется оператор CASE, чтобы ограничить строки, возвращаемые запросом SELECT. Запрос возвращает для каждого абонента максимальное значение оплаты по каждой услуге (рис. 3.70). Секция HAVING ограничивает услуги, оставляя только услугу с кодом 2 и максимальной оплатой более 100 или услугу с кодом 4 и максимальной оплатой более 500. SELECT AccountCD, ServiceCD, MAX(Paysum) FROM PaySumma P GROUP BY P.AccountCD, P.ServiceCD HAVING (MAX(CASE WHEN ServiceCD =2 THEN P.Paysum ELSE NULL END) > 100 OR MAX(CASE WHEN ServiceCD =4 THEN P.Paysum ELSE NULL END) >500);. ACCOUNTCD SERVICECD MAX 015527 4 611,30 115705 2 250,00 115705 4 553,85 136169 4 528,44 Рис. 3.70. Результат выполнения запроса с оператором CASE Как и условие поиска в секции WHERE, условие поиска в секции HAVING может дать 1 из 3 перечисленных результатов:  если условие поиска имеет значение TRUE, то группа строк остается и для нее генерируется одна строка в результате запроса;  если условие поиска имеет значение FALSE, то группа строк исключается и строка в результате запроса для нее не генерируется;  если условие поиска NULL, то группа строк исключается и строка в результате запроса для нее не генерируется. Правила обработки NULL в условиях поиска для секции HAVING точно такие же, как и для секции WHERE. Секцию HAVING имеет смысл использовать в сочетании с секцией GROUP BY, хотя синтаксис запроса SELECT не требует этого. Если базовая 18 таблица интерпретируется одной группой, как, например в следующем запросе SELECT MAX(IncomingDate) FROM Request HAVING MAX(IncomingDate) > '31.08.2011';, то использование HAVING возможно без GROUP BY. В заключение следует рассмотреть типичные ошибки, возникающие при построении запросов с группировкой данных. Пусть требуется вывести список номеров лицевых счетов абонентов с общей суммой оплат больше 1000. Если попытаться эту задачу решить так: SELECT AccountCD, SUM (PaySum) AS Summa FROM PaySumma WHERE PaySum > 1000 GROUP BY AccountCD;, то запрос возвратит пустой набор. Это происходит потому, что фильтр секции WHERE оценивается построчно, а строк со значением отдельных оплат больше 1000 нет. Поэтому нужно использовать фильтр по группе оплат, а не по каждой оплате: SELECT AccountCD, SUM (PaySum) AS Summa FROM PaySumma GROUP BY AccountCD HAVING Summa > 1000; Однако этот запрос не выполнится из-за ошибки – псевдоним Summa невидим для секции HAVING, так как она оценивается перед секцией SELECT. Для устранения ошибки в секции HAVING следует обратиться к самому столбцу: SELECT AccountCD, SUM (PaySum) AS Summa FROM PaySumma GROUP BY AccountCD HAVING SUM (PaySum) > 1000; 3.2.6. Секция ORDER BY Строки НД, как и строки таблиц БД, не имеют определенного порядка. Включив в запрос SELECT секцию ORDER BY, можно отсортировать результаты запроса. Секция ORDER BY состоит из ключевого слова ORDER BY, за которым следует список элементов сортировки, каждый из которых имеет такой синтаксис: <элемент_сортировки> :: ={{[<таблица>.] столбец | порядковый_номер_столбца | псевдоним_столбца | <выражение>} [ASC[ENDING] | DESC[ENDING]] [NULLS FIRST | NULLS LAST]}… . Использование ASCENDING и DESCENDING вместо ASC и DESC допустимо только в Firebird, в других рассматриваемых СУБД – нет. Опция 19 DESC означает сортировку по убыванию. Если указать необязательную и используемую по умолчанию опцию ASC, то сортировка будет произведена по возрастанию. Например, для вывода начислений абонентам за декабрь 2010 г., упорядоченных по убыванию значений (рис. 3.71), следует использовать запрос: SELECT NachislFactCD, AccountCD, NachislSum FROM NachislSumma WHERE NachislMonth=12 AND NachislYear=2010 ORDER BY NachislSum DESC; NACHISLFACTCD 38 2 50 ACCOUNTt NACHISLSUM CD 080270 58,10 005488 46,00 136160 18,30 Рис. 3.71. Результат выполнения запроса с сортировкой по убыванию Можно производить сортировку по нескольким столбцам. Старшинство столбцов, по которым осуществляется сортировка, в секции ORDER BY определяется слева направо. Например, требуется вывести значения оплат абонентов с лицевыми счетами '136169', '005488', '443690'. Результаты запроса упорядочить по возрастанию номеров лицевых счетов, а затем по уменьшению значения оплаты. Соответствующий запрос имеет такой вид: SELECT AccountCD, PaySum ACCOUNTCD PAYSUM FROM PaySumma 005488 280,00 WHERE AccountCD IN 005488 260,00 ('136169','005488','443690') 005488 65,00 ORDER BY AccountCD, 005488 58,70 PaySum DESC; 005488 56,00 Результат выполнения 40,00 запроса представлен на 005488 136169 528,44 рис. 3.72. 349,19 SQL позволяет 136169 346,18 упорядочивать строки НД 136169 58,70 не только по тем столбцам, 136169 которые в нем 136169 28,32 присутсвуют, но и по 136169 28,32 столбцам исходной 136169 25,32 таблицы и выражениям над 136169 20,00 ними. Например, запрос 443690 485,00 SELECT PaySum FROM 443690 295,00 PaySumma 443690 20,00 WHERE AccountCD IN 20 ('136169','005488','443690') ORDER BY AccountCD, PaySum DESC; выведет строки в том же порядке, что и в предыдущем запросе, хотя столбец AccountCD выражения сортировки отсутствует в НД. 443690 19,47 Рис. 3.72. Результат выполнения запроса с сортировкой по нескольким столбцам При работе с MS SQL Server и Oracle следует учесть, что если в запросе SELECT используются и секция ORDER BY, и опция DISTINCT, то секция ORDER BY не может ссылаться на столбцы, которые отсутствуют в НД. Например, в MS SQL Server и Oracle недопустим запрос вида SELECT DISTINCT PaySum FROM PaySumma WHERE AccountCD IN ('136169','005488','443690') ORDER BY AccountCD, PaySum DESC; В списке элементов сортировки для столбцов в НД с именами и без имен (например, вычисляемых столбцов) можно указывать их порядковый номер в списке возвращаемых элементов секции SELECT. Нумерация столбцов в списке начинается с 1 и осуществляется в направлении слева направо. Примером этому может служить запрос: SELECT DISTINCT AccountCD, (NachislSum +100) AS "NachislSum_100" FROM NachislSumma WHERE ServiceCD =2 AND AccountCD LIKE '1%' ORDER BY 2; Здесь сортировка выполняется по значениям вычисляемого столбца (NachislSum+100), который имеет порядковый номер 2 в списке возвращаемых элементов секции SELECT. Результат выполнения запроса представлен на рис. 3.73. ACCOUNTCD 136160 136169 115705 136160 115705 136169 115705 NACHISLSUM_100 120,00 120,00 140,00 156,00 158,70 158,70 350,00 Рис. 3.73. Результат с сортировкой по номеру столбца выполнения запроса В секции ORDER BY можно указать псевдоним столбца, например NachislSum_100 в предыдущем примере. Результат выполнения будет совпадать с результатом выполнения предыдущего запроса. 21 Сортировку по номеру элемента в списке можно использовать при выборке всех столбцов из таблицы. Пусть, например, требуется вывести все данные об улицах, отсортированные по названию улиц (рис. 3.74): SELECT * FROM Street ORDER BY 2; STRE ETCD 3 5 7 6 8 2 4 1 STREETNM ВОЙКОВ ПЕРЕУЛОК ГАГАРИНА УЛИЦА КУТУЗОВА УЛИЦА МОСКОВСКАЯ УЛИЦА МОСКОВСКОЕ ШОССЕ НОВАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ЦИОЛКОВСКОГО УЛИЦА Рис. 3.74. Результат выполнения с сортировкой при выборке всех данных запроса Примечание. Не рекомендуется в приложениях использовать запросы с сортировкой по номерам столбцов. Это связано с тем, что со временем структура таблицы может измениться, например, в результате добавления или удаления столбцов. Как следствие, запрос с сортировкой по порядковому номеру может дать совсем другую последовательность или вообще вызвать ошибку, ссылаясь на отсутствующий столбец [50]. В качестве выражения сортировки можно использовать функцию. В этом случае сортировке подвергается результат, возвращаемый функцией для каждой строки. Например, для вывода информации о первых четырех абонентах, имеющих наиболее длинные ФИО, в Firebird можно использовать запрос SELECT FIRST 4 * FROM Abonent ORDER BY CHAR_LENGTH(Fio) DESC; В данном случае строки НД сортируются в порядке уменьшения числа символов в ФИО абонентов. Результат выполнения запроса представлен на рис. 3.75. ACCOUNTC D 443069 STREETC D 4 HOUSEN O 51 FLATN O 55 080270 6 35 6 136169 4 7 13 080613 8 35 11 FIO Стародубце в Е.В. Тимошкина Н.Г. Денисова Е.К. Лукашина PHON E 683014 321002 680305 254417 22 Р.М. Рис. 3.75. Результат выполнения запроса с сортировкой по значению функции В MS SQL Server аналогичный запрос будет выглядеть так: SELECT TOP(4) * FROM Abonent ORDER BY LEN(Fio) DESC;, а в Oracle так: SELECT * FROM Abonent WHERE ROWNUM <= 4 ORDER BY LENGTH(Fio) DESC; Для возврата трех строк таблицы Abonent, выбранных случайным образом, в Firebird, например, можно использовать запрос SELECT FIRST 3 * FROM Abonent ORDER BY RAND(); Иногда при сортировке может оказаться важным расположение в выходном НД NULL-маркеров. По умолчанию в Firebird и MS SQL Server при сортировке по возрастанию значения NULL располагаются в конце списка, а при сортировке по убыванию – в начале. В Oracle – наоборот. В соответствии с SQL-стандартом существует возможность определить положение NULL в выходном НД. Такую возможность поддерживают Firebird и Oracle. Результаты могут быть отсортированы таким образом, что NULL будут располагаться выше (NULLS FIRST) или ниже (NULLS LAST) остальных результатов запроса, отличных от NULL. Примечание. Если задано NULLS FIRST, то для сортировки не может быть использован индекс (использование индексов будет описано далее). Например, для вывода в Firebird номеров и дат выполнения ремонтных заявок, поданных абонентами, номера лицевых счетов которых начинаются с '08' или с '11', отсортированных по убыванию дат выполнения и с неопределенными значениями в начале списка (рис. 3.76), можно использовать такой запрос: SELECT RequestCD, ExecutionDate, AccountCD FROM REQUEST WHERE (AccountCD LIKE '08%') OR (AccountCD LIKE '11%') ORDER BY ExecutionDate DESC NULLS FIRST; REQUESTCD 16 5 18 7 19 20 17 2 EXECUTIONDAT E NULL NULL 04.01.2013 24.10.2012 27.12.2011 11.10.2011 06.09.2011 12.08.2011 ACCOUNTCD 115705 080270 115705 080047 080270 080047 115705 115705 23 6 15 24.06.2011 23.09.2010 Рис. 3.76. Результат запроса неопределенных значений 080613 115705 с указанием положения Возможна сортировка НД по частям строки. Например, следующий запрос в Firebird возвратит абонентов из таблицы Abonent, упорядоченных по инициалам: SELECT * FROM Abonent ORDER BY RIGHT(Fio, 4); Для сортировки по ключу, зависящему от данных, можно использовать операцию CASE. Например, если код улицы равен 4, следующий запрос возвратит строки таблицы Abonent упорядоченными по Fio, а в противном случае – упорядоченными по Phone: SELECT * FROM Abonent ORDER BY CASE WHEN StreetCD=4 THEN Fio ELSE Phone END; Секция ORDER BY является последней в запросе SELECT. Следовательно, НД можно упорядочивать по группам, например так в Firebird: SELECT AccountCD, SUM(NachislSum) FROM NachislSumma GROUP BY 1 ORDER BY 1; или так: SELECT AccountCD, SUM(NachislSum) FROM NachislSumma GROUP BY 1 ORDER BY 2; 3.2.7. Статистический анализ данных в SQL Оконные (аналитические) функции были введены в стандарте SQL:2003 [40, 43]. Такие функции предназначены для статистического анализа данных в готовой выборке и часто используются в ряде аналитических приложений. Спектр применения аналитических функций достаточно широк – это различного рода распределения, процентирование, линейная регрессия, стандартные отклонения, дисперсии, нарастающие итоги и т. д. [51]. В принципе практически все, что можно получить с помощью аналитических функций, можно получить и без них, с помощью обычных SQL-запросов, но это будет более громоздко и зачастую не так эффективно. Оконные функции не только обеспечивают простые выразительные средства для формулировки аналитических запросов, но также могут способствовать эффективной оптимизации и выполнению запросов, позволяя избежать многочисленных соединений и наличия нескольких блоков запроса. Синтаксис оконных функций выглядит следующим образом [42]: <имя_оконной_функции>( [<выражение> [, <выражение> ...]]) OVER ( [PARTITION BY <выражение> [, <выражение> ...]] [ORDER BY <выражение> [{ASC | DESC}] [NULLS {FIRST | LAST}] [, <выражение> [{ASC | DESC}] [NULLS {FIRST | LAST}] ...] ). Любые оконные функции всегда используются с секцией OVER(). 24 Примечание. Если необходимо использовать две аналитические функции в одном запросе, то для каждой функции конструкция OVER() указывается отдельно. Также может быть указан список группировки (секция PARTITION BY) или сортировки (ORDER BY) обрабатываемых аналитической функцией данных. Одно из главных отличий аналитических функций от обычных, скалярных, заключается в том, что они работают с готовой выборкой. Аналитические функции представляют собой особый вид агрегации исходных данных, при этом исходный набор данных не фильтруется. Сначала формируется выборка, выполняются все объединения, условия WHERE, GROUP BY, HAVING – все, кроме сортировки, и только затем к практически готовому набору данных применяется аналитическая функция. В связи с этим аналитические функции можно указывать лишь в списке выборки SELECT или в секции ORDER BY. В общем случае принцип работы аналитических функций можно представить следующим образом. Допустим, что у нас есть результирующий набор данных, полученный вышеописанным способом, – выполнено все, кроме сортировки. На каждую запись в этом наборе накладывается так называемое «окно», размеры и положение которого определяются в соответствии с некоторым аналитическим условием (отсюда и название этого класса функций – «оконные функции», windows functions). В это виртуальное «окно» попадают несколько других записей из того же набора, то есть целая группа записей. При этом «окно» может быть сформировано таким образом, что в него попадут практически произвольные записи из набора, и даже сама текущая запись, на основании которой формируется «окно», может в это «окно» не попасть (в дальнейшем термин «окно» будет использоваться для обозначения именно такого набора записей). Когда «окно» сформировано, аналитическая функция вычисляет агрегированное значение по записям, входящим в «окно», и переходит к следующей записи. Для этой записи формируется новое «окно», снова вычисляется агрегированное значение – и так для всех записей в выборке. При этом размер и положение «окна» от записи к записи может меняться, в таких случаях используют термин «скользящее окно» (sliding window). Агрегатные функции. Любые агрегатные функции могут быть использованы как оконные путем добавления секции OVER(). Отличие состоит в том, что обычные агрегатные функции уменьшают степень детализации результирующего набора, а в аналитическом варианте степень детализации не уменьшается. Это относится и к другим типам аналитических функций. Рассмотрим пример использования агрегатной функции в качестве аналитической. Допустим, нужно вывести по каждому абоненту соответствующую ему сумму начислений и процент его начислений от общей суммы начислений (рис. 3.77). С помощью обычного запроса без использования аналитической функции требуемая информация может быть получена таким образом: 25 SELECT N.AccountCD, SUM(N.NachislSum), (SUM(N.NachislSum) / (SELECT SUM(N1.NachislSum) FROM NachislSumma N1)) AS "Percent_Summa" FROM NachislSumma N GROUP BY N.AccountCD ORDER BY N.AccountCD; ACCOUNTCD 005488 015527 SUM PERCENT_SUMMA 769,33 0,0594 1619,7 0,125 2 080047 528,48 0,0408 080270 1851,9 0,143 2 080613 867,19 0,0669 115705 1301,8 0,1005 126112 656,00 0,0506 136159 964,99 0,0745 136160 588,78 0,0454 136169 1384,4 0,1069 7 443069 1106,2 0,0854 4 443690 1310,8 0,1012 1 Рис. 3.77. Результат работы аналитической функции в Firebird Результаты выполнения запроса в MS SQL Server и Oracle будут несколько отличаться от результата на рис. 3.77 засчет различий СУБД в области вычисления арифметических выражений (см. п. 2.9). Точность значений в столбце Percent_Summa в MS SQL Server и Oracle будет выше, чем в Firebird. Данные, возвращенные предыдущим запросом, можно получить проще с помощью запроса, использующего вместо подзапроса аналитическую функцию: SELECT N.AccountCD, SUM(N.NachislSum), (SUM(N.NachislSum) / SUM(SUM(N.NachislSum)) OVER()) AS "Percent_Summa" FROM NachislSumma N GROUP BY N.AccountCD ORDER BY N.AccountCD; В приведенном запросе функция SUM(SUM(N.NachislSum)) OVER() вычисляется путем суммирования всех SUM(N.NachislSum) из запроса. Подобно тому, как обычные агрегатные функции могут применяться по отношению к сгруппированным данным, аналитические функции могут оперировать с данными определенной группы (порции, "partition"), которая определяется секцией PARTITION BY. В PARTITION BY указывается список столбцов, по которым производится группировка, и таким образом формируется «окно» для работы аналитической функции. В «окно» попадают все строки, сгруппированные по указанным столбцам, и в общем случае «окно» содержит более одной строки. Набор данных «окна» соединяется с набором данных основного запроса по столбцам, указанным в секции PARTITION BY. 26 Группировка PARTITION BY делает практически то же самое, что и GROUP BY, но с несколькими отличиями: производится по уже сформированной выборке и распространяется только на тот агрегат, после которого идет опция OVER (), а не на все столбцы. Для демонстрации работы аналитических функций с группировкой модифицируем предыдущий пример. Для каждого абонента в столбце Percent_Summa вместо процента от общей суммы всех начислений выведем процент от общей суммы начислений абонентов, проживающих на той же улице (рис. 3.78). Запрос будет выглядеть следующим образом: SELECT A.AccountCD, A.StreetCD, SUM(N.NachislSum), (SUM(N.NachislSum) / SUM(SUM(N.NachislSum)) OVER(PARTITION BY A.StreetCD)) AS "Percent_Summa" FROM Abonent A INNER JOIN NachislSumma N ON A.AccountCD = N.AccountCD GROUP BY A.AccountCD, A.StreetCD ORDER BY A.AccountCD; ACCOUNTCD STREETCD 005488 3 015527 3 080047 080270 8 6 080613 115705 8 3 126112 136159 136160 136169 4 7 4 4 443069 4 443690 7 Рис. 3.78. Результат группировкой в Firebird SUM 769,33 1619,7 2 528,48 1851,9 2 867,19 1301,8 656,00 964,99 588,78 1384,4 7 1106,2 4 1310,8 1 работы PERCENT_SUMMA 0,2084 0,4388 0,3786 1,0000 0,6213 0,3527 0,1756 0,4240 0,1576 0,3706 0,2961 0,5759 аналитической функции с Аналитические функции могут оперировать с данными, отсортированными определенным образом. В этом случае подсчитывается значение, начиная от первой строки «окна» и заканчивая текущей обрабатываемой строкой, т. е. подсчет производится в границах определенного диапазона данных. Сортировка определяется списком столбцов, указанных в секции ORDER BY функции, и может использоваться совместно с секцией PARTITION BY. В одном запросе можно использовать несколько оконных функций с разными сортировками. Допустим, необходимо вывести по каждому абоненту соответствующую ему сумму начислений, а также общую сумму всех начислений, не 27 превышающих величины начисления абонента. Данные необходимо отсортировать по суммам начислений абонентам (рис. 3.79). Запрос, реализующий данную задачу: SELECT N.AccountCD, SUM(N.NachislSum), SUM(SUM(N.NachislSum)) OVER(ORDER BY SUM(N.NachislSum)) AS "Cumul_Sum" FROM NachislSumma N GROUP BY N.AccountCD ORDER BY SUM(N.NachislSum); В данном примере аналитическая функция для каждой строки суммирует все начисления, не превышающие значения столбца Sum. Следовательно, для абонента (номер лицевого счета '080047') с наименьшим начислением значение столбца Cumul_Sum равно значению столбца Sum, для абонента с номером лицевого счета '136160' значение столбца Cumul_Sum равно сумме значений столбца Sum первых двух строк и т. д. ACCOUNTCD 080047 136160 126112 005488 080613 136159 443069 115705 443690 136169 015527 080270 SUM 528,48 588,78 656,00 769,33 867,19 964,99 1106,2 4 1301,8 1310,8 1 1384,4 7 1619,7 2 1851,9 2 CUMUL_SUM 528,48 1117,26 1773,26 2542,59 3409,78 4374,77 5481,01 6782,81 8093,62 9478,09 11097,81 12949,73 Рис. 3.79. Результат работы аналитической функции с сортировкой в Firebird В примере нет повторяющихся значений суммы (столбец Sum). Если бы присутствовали одинаковые значения, то в столбце Cumul_Sum были бы тоже одинаковые значения, так как в секции ORDER BY указан только столбец Sum. Данные для каждого набора значений столбцов секции ORDER BY обрабатываются аналитической функцией только раз. В следующем примере рассматривается применение функций MIN, MAX, AVG и COUNT с секцией OVER для получения статистических значений оплат для каждой из услуг в таблице PaySumma: SELECT DISTINCT ServiceCD, 28 MIN(Paysum) OVER (PARTITION BY ServiceCD) AS MinPay, MAX(Paysum) OVER (PARTITION BY ServiceCD) AS MaxPay, AVG(Paysum) OVER (PARTITION BY ServiceCD) AS AvgPay, COUNT(Paysum) OVER (PARTITION BY ServiceCD) AS CountPay FROM PaySumma ORDER BY ServiceCD; Статистические функции Oracle. В СУБД Oracle поддерживаются различные виды статистических функций, большая часть из которых может использоваться как в качестве агрегирующих функций, так и в качестве аналитических. Ниже перечислены статистические функции Oracle с указанием их форматов и кратким описанием [49].  CORR(число1, число2) [OVER (выражение)] – коэффициент корреляции множества пар чисел. В статистическом смысле, корреляция – это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции;  COVAR_POP (числовое_выражение1, числовое_выражение2) ковариация совокупности множества пар чисел;  COVAR_SAMP (числовое_выражение1, числовое_выражение2) – выборочная ковариация набора пар чисел;  CUME_DIST (выражение1, … выражениеN) WITHIN GROUP ( ORDER BY выражение1, … выражениеN) – кумулятивное распределение значений в группе значений. Функция всегда возвращает число, которое > 0 и <= 1. Это число представляет относительную "позицию" строки в группе из N строк. В группе из трех строк, например, возвращаются следующие значения кумулятивного распределения: 1/3, 2/3 и 3/3;  STDDEV ([DISTINCT|ALL] выражение) – стандартное отклонение списка чисел;  VARIANCE(выражение) – дисперсия набора чисел;  VAR_POP (выражение) – дисперсия совокупности множества чисел;  VAR_SAMP (выражение) – выборочная дисперсия набора чисел. Функции ранжирования. Помимо агрегатных аналитических функций, выделяют аналитические функции ранжирования. Такие функции возвращают ранг каждой строки внутри «окна». В общем случае рангом является некое число, отражающее положение или «вес» записи относительно других записей в том же наборе. Формируется «окно» точно так же, как и в случае агрегатных функций – с помощью группировки. Однако, поскольку результат работы функций ранжирования зависит от порядка обработки записей, то обязательно должен быть указан порядок записей внутри «окна» посредством секции ORDER BY. В зависимости от используемой функции некоторые записи могут получать один и тот же ранг. Функции ранжирования могут быть использованы для создания различных типов счетчиков по возрастанию. Все ранжирующие функции являются 29 недетерминированными, то есть при одних и тех же входных значениях они могут возвращать разный результат. К функциям ранжирования относятся функции ROW_NUMBER(), RANK() и DENSE_RANK(). Функция ROW_NUMBER() служит для нумерации записей в указанном порядке внутри «окна». При этом, если в опции OVER опустить секцию PARTITION BY, то за «окно» будет принята вся выборка, что дает возможность пронумеровать все записи в нужном порядке, причем порядок нумерации может не совпадать с порядком записей в результирующей выборке, то есть секция ORDER BY внутри OVER(), определяющая порядок сортировки записей внутри «окна», и соответственно порядок нумерации записей могут не совпадать с секцией ORDER BY основного запроса SELECT. Нумерация всегда начинается с единицы, порядковые номера меняются, даже когда есть повторяющиеся значения. Функция RANK() предназначена для ранжирования записей внутри «окна», при этом, если колонка для группировки не задана явным образом, то за «окно» принимается вся выборка. Рангом каждой записи является количество уже ранжированных записей с более высоким рангом, чем текущая, плюс единица. Если встретятся несколько записей с одинаковым значением, по которому производится ранжирование, то этим записям будет присвоен одинаковый ранг. Однако при этом следующая запись с новым значением получит такой ранг, как будто бы предыдущие записи получили свой уникальный номер, то есть образуется дырка. Фактически это та же нумерация, что и в ROW_NUMBER(), которая начинается с той же единицы. Различие в том, что одинаковые записи получают одинаковый номер, а следующая отличающаяся от них запись получает такой номер, как если бы использовалась ROW_NUMBER(), и все предыдущие записи получили свои уникальные номера. Таким образом, образуется дырка в нумерации, равная количеству одинаковых записей минус единица. Функция DENSE_RANK() выполняет «плотное» ранжирование, то есть делает то же самое, что и функция RANK(), но без «дырок» в нумерации. Рассмотрим пример использования ранжирующих аналитических функций. Допустим, необходимо вывести по каждому абоненту соответствующую ему сумму начислений, при этом пронумеровав строки результата (рис. 3.80). Модифицируем предыдущий запрос путем замены аналитической функции на функцию ROW_NUMBER(), в результате чего получим запрос: SELECT ROW_NUMBER() OVER(ORDER BY SUM(N.NachislSum)), N.AccountCD, SUM(N.NachislSum) FROM NachislSumma N GROUP BY N.AccountCD ORDER BY SUM(N.NachislSum); ROW_NUMBE R 1 2 ACCOUNTC D 080047 136160 SUM 528,48 588,78 30 3 126112 656,00 4 005488 769,33 5 080613 867,19 6 136159 964,99 7 443069 1106,24 8 115705 1301,80 9 443690 1310,81 10 136169 1384,47 11 015527 1619,72 12 080270 1851,92 Рис. 3.80. Результат работы функции ROW_NUMBER() Навигационные функции. Навигационные функции предназначены для получения простого (неагрегированного) значения выражения из определенной строки «окна». Навигационные функции в Firebird и Oracle представлены статистическими функциями для интервалов агрегирования FIRST_VALUE, LAST_VALUE и NTH_VALUE и функциями сдвига наблюдений LAG и LEAD с запаздывающим и опережающим аргументами. В MS SQL Server функция NTH_VALUE не используется. Функции FIRST_VALUE и LAST_VALUE позволяют для каждой строки выдать первое и последнее значения выражения из упорядоченного «окна». Функции имеют синтаксис FIRST_VALUE(<выражение>), LAST_VALUE(<выражение>). Функция NTH_VALUE получает N-е значение (N задается как <смещение>), начиная с первой (по умолчанию) или последней записи из упорядоченного «окна», т. е. позволяет выделить N-й элемент из аналитического «окна». Если <смещение> равно 1 от начала, то функция эквивалентна функции FIRST_VALUE, если <смещение> равно 1 с конца, то функция эквивалентна функции LAST_VALUE. Функция имеет синтаксис NTH_VALUE(<выражение>, <смещение>) [FROM FIRST | FROM LAST]. Следует учесть, что при использовании функций для интервалов агрегирования FIRST_VALUE и LAST_VALUE и NTH_VALUE «окно» формируется от первой до текущей (а не до последней) строки порции данных. Функции сдвига наблюдений LAG и LEAD являются статистическими функциями соответственно с запаздывающим и опережающим аргументом. Такие функции могут быть полезными в приложениях эконометрики и других задачах, связанных с анализом данных, представляющих собой временные ряды, где часто возникает потребность сдвигать эти ряды относительно друг друга. Это может быть необходимо для расчёта доходностей ценных бумаг, вычисления автокорреляций и т. д. Иногда сдвиг рядов применяется и вне временного контекста, в ходе преобразования данных. Функции имеют синтаксис: 31 LAG(<выражение> [ [, <смещение> [, <значение_по_умолчанию> ] ] ), LEAD(<выражение> [ [, <смещение> [,<значение_по_умолчанию> ] ] ). Функция LAG внутри «окна» ищет значение <выражения> для строки, которая находится выше текущей на <смещение> строчек (по умолчанию <смещение> равно 1). Функция LEAD внутри «окна» ищет значение <выражения> для строки, которая находится ниже текущей на <смещение> строчек. В случае, когда <смещение> выходит за пределы «окна», возвращается <значение_по_умолчанию> (по умолчанию NULL). На практике обычно требуется сдвигать данные с запаздыванием – так, чтобы в текущем наблюдении присутствовали данные за предыдущее время. Модифицируем пример, выводящий суммы начислений для каждого абонента, дополнив его вычислением различных навигационных функций: SELECT N.AccountCD, SUM(N.NachislSum), FIRST_VALUE (SUM(N.NachislSum)) OVER(ORDER BY SUM(N.NachislSum)), LAST_VALUE (SUM(N.NachislSum)) OVER(ORDER BY SUM(N.NachislSum)), NTH_VALUE (SUM(N.NachislSum), 2) OVER(ORDER BY SUM(N.NachislSum)), LAG (SUM(N.NachislSum)) OVER(ORDER BY SUM(N.NachislSum)), LEAD (SUM(N.NachislSum)) OVER(ORDER BY SUM(N.NachislSum)) FROM NachislSumma N GROUP BY N.AccountCD ORDER BY SUM(N.NachislSum); Результат выполнения запроса представлен на рис. 3.81. ACCOUNTCD SUM 080047 136160 126112 005488 080613 136159 443069 115705 443690 136169 015527 080270 528,48 588,78 656,00 769,33 867,19 964,99 1106,2 4 1301,8 1310,8 1 1384,4 7 1619,7 2 1851,9 2 FIRST_VAL UE 528,48 528,48 528,48 528,48 528,48 528,48 528,48 LAST_VAL UE 528,48 588,78 656,00 769,33 867,19 964,99 1106,24 NTH_VAL UE NULL 588,78 588,78 588,78 588,78 588,78 588,78 528,48 1301,80 588,78 528,48 1310,81 588,78 528,48 1384,47 588,78 528,48 1619,72 588,78 528,48 1851,92 588,78 LAG LEAD NULL 528,48 588,78 656,00 769,33 867,19 964,99 588,78 656,00 769,33 867,19 964,99 1106,24 1301,80 1106,2 4 1301,8 1310,8 1 1384,4 7 1619,7 2 1310,81 1384,47 1619,72 1851,92 NULL Рис. 3.81. Результат работы навигационных функций 32      В приведенном примере «окно» для каждой строки содержит значения сумм от самой минимальной до суммы, не превышающей сумму в текущей строке. Из рис. 3.81 понятно: что функция FIRST_VALUE возвращает значение первой суммы из «окна», т. е. наименьшей суммы, которая для всех строк одинаковая; функция LAST_VALUE возвращает значение последней суммы из «окна», т. е. суммы, равной сумме в текущей строке; функция NTH_VALUE возвращает значение второй суммы из «окна», которое одинаково для всех строк, кроме первой (для первой строки «окно» содержит только одну запись, поэтому второе значение возвращает NULL); функция LAG возвращает значение предыдущей суммы из «окна» для текущей строки, т. е. предыдущую меньшую по величине сумму отсортированного ряда (для первой строки «окно» содержит только одну запись, поэтому предыдущее значение возвращает NULL); функция LEAD возвращает значение следующей суммы из «окна» для текущей строки, т. е. следующую по величине сумму отсортированного ряда (для последней строки «окно» не содержит следующего по величине значения, поэтому возвращается NULL). 33