Окна в мир аналитических функций Oracle: введение
Эта статья является переводом статьи из журнала Oracle, который публикуют каждые два месяца. Ссылка на оригинал — тыц
Первая часть является вводной статьей в аналитические функции. В которой показано, как и какие задачи можно решать с помощью аналитических функций.
В последующих частях будет раскрыта тема window_clause в аналитических запросах.
Для выполнения примеров используется схема HR. Если у вас не установлена такая схема, необходимо выполнить скрипт:
REM <<script starts here>>
spool C:\miscellaneous\sql_101\Install_10\sql_101_examples_script.lst
REM 3) Drop the user, SQL_101. This will delete the user, SQL_101, and delete any of its schema objects.
drop user sql_101 cascade;
REM 4) Create the user, SQL_101.
create user sql_101 identified by Ashton0719
default tablespace users
temporary tablespace temp
quota unlimited on users;
REM If you are using a pre-existing database to run this article series'' examples, please ask your database administrator for the name of both the default and temporary
REM tablespace to supply in the above SQL statement. In this instance, you should also ask the administrator for an appropriate value to supply for the quota on the default
REM tablespace.
REM Be sure to include the ending semicolon in each SQL statement in this script. SQL*Plus uses the semicolon character as a SQL statement terminator to know when to send the
REM statement to the database for execution.
REM 5) Grant privileges to the SQL_101 user to create a database session (to be able to run SQL statements) and to create objects in your SQL_101 database schema.
grant connect, resource to sql_101;
REM 6) Connect as the user, SQL_101.
connect sql_101
REM When prompted for the password (Example: Enter password: ) enter_the_password_you_supplied_above.
REM If you have supplied the correct password, you will receive a confirmation that reads: Connected.
REM 7) Create the objects you will use. (This script will be altered and exanded upon as necessary with each installation of this article series.)
CREATE TABLE employee (
employee_id NUMBER,
first_name VARCHAR2(30),
last_name VARCHAR2(30),
hire_date DATE,
salary NUMBER(9,2),
manager NUMBER,
department_id NUMBER
);
CREATE TABLE department (
department_id NUMBER,
name VARCHAR2(30),
location VARCHAR2(30)
);
CREATE SEQUENCE employee_seq;
CREATE SEQUENCE department_seq;
REM These sequences will be used eventually. For now, in order for your output to match that shown in the series articles'' examples,
REM please insert literal number values into the ID columns of the tables as shown below.
REM 8) Insert records into the employee table.
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (28, 'Emily', 'Eckhardt', to_date('07-JUL-2004', 'DD-MON-YYYY'), 100000, NULL, 10);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (37, 'Frances', 'Newton', to_date('14-SEP-2005', 'DD-MON-YYYY'), 75000, NULL, NULL);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (1234, 'Donald', 'Newton', to_date('24-SEP-2006', 'DD-MON-YYYY'), 80000, 28, 10);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (7895, 'Matthew', 'Michaels', to_date('16-MAY-2007', 'DD-MON-YYYY'), 70000, 28, 10);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6567, 'Roger', 'Friedli', to_date('16-MAY-2007', 'DD-MON-YYYY'), 60000, 28, 10);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6568, 'Betsy', 'James', to_date('16-MAY-2007', 'DD-MON-YYYY'), 60000, 28, 10);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6569, 'michael', 'peterson', to_date('03-NOV-2008', 'DD-MON-YYYY'), 90000, NULL, 20);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6570, 'mark', 'leblanc', to_date('06-MAR-2009', 'DD-MON-YYYY'), 65000, 6569, 20);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6571, 'Thomas', 'Jeffrey', to_date('27-FEB-2010', 'DD-MON-YYYY'), 300000, null, 30);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6572, 'Theresa', 'Wong', to_date('27-FEB-2010 9:02:45', 'DD-MON-YYYY HH24:MI:SS'), 70000, 6571, 30);
INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
VALUES (6573, 'Lori', 'Dovichi', to_date('07-JUL-2011 8:31:57', 'DD-MON-YYYY HH24:MI:SS'), null, 28, 10);
REM 9) Insert records into the department table.
INSERT INTO department (department_id, name, location)
VALUES (10, 'Accounting', 'LOS ANGELES');
INSERT INTO department (department_id, name, location)
VALUES (20, 'Payroll', 'NEW YORK');
INSERT INTO department (department_id, name, location)
VALUES (30, 'IT', 'WASHINGTON DC');
REM 10) Save your newly created records to the database.
Commit;
spool off
Какие задачи?
Для решения большинства задач используется стандартный SQL. Однако не так легко написать запрос «Какова промежуточная сумма зарплаты сотрудников, если суммировать построчно?». И даже если получиться, не факт, что он будет быстро работать. В этом случае, на выручку приходят аналитические функции, которые упрощают чтение кода и повышают скорость выполнения запроса.
В листинге 1 продемонстрирован запрос с использованием аналитической функции SUM. Результатом запроса является список всех сотрудников, в котором отображена сумма заработной платы каждого сотрудника и нарастающий итог по зарплате для всех сотрудников.
Листинг 1 : Запрос на получение заработной платы с нарастающим итогом построчно для всех сотрудников.
SQL> set feedback on
SQL> set lines 32000
SQL> select last_name, first_name, salary,
2 SUM (salary)
3 OVER (ORDER BY last_name, first_name) running_total
4 from employee
5 order by last_name, first_name;
LAST_NAME FIRST_NAME SALARY RUNNING_TOTAL
————————— ——————————— ————————————————————————————— —————————————
Dovichi Lori
Eckhardt Emily 100000 100000
Friedli Roger 60000 160000
James Betsy 60000 220000
Jeffrey Thomas 300000 520000
Michaels Matthew 70000 590000
Newton Donald 80000 670000
Newton Frances 75000 745000
Wong Theresa 70000 815000
leblanc mark 65000 880000
peterson michael 90000 970000
11 rows selected.
Результат этого запроса получен с помощью выражения:
SUM (salary)
OVER (ORDER BY last_name, first_name) running_total
Анатомия аналитических функций
Изучение синтаксиса является неотъемлемой частью для использования эффективной обработки запросов. Синтаксис аналитического предложения в Листинге 1, можно представить в общем виде:
FUNCTION_NAME( column | expression,column | expression,... )
OVER
( Order-by-Clause )
В Листинге 1 имя функции SUM. Аргументом функции SUM является столбец salary (хотя также может быть и выражение). Предложение OVER говорит то, что это аналитическая функция (без него это была бы обычная агрегатная функция). Предложение ORDER BY обозначает часть данных «над» которыми будет выполняться аналитическая функция.
Стоит сказать, что для достижения результата, полученного в Листинге 1 можно использовать скалярные подзапросы. Однако такой запрос будет выполняться значительно медленнее и будет менее читабельным.
Листинг 2: Запрос на получение заработной платы с нарастающим итогом, построчно, для каждого отдела.
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY last_name, first_name) department_total
4 from employee
5 order by department_id, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— —————————— ———————————————— ————————— —————————————————
Dovichi Lori 10
Eckhardt Emily 10 100000 100000
Friedli Roger 10 60000 160000
James Betsy 10 60000 220000
Michaels Matthew 10 70000 290000
Newton Donald 10 80000 370000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Jeffrey Thomas 30 300000 300000
Wong Theresa 30 70000 370000
Newton Frances 75000 75000
11 rows selected.
Запрос в Листинге 2 суммирует значение заработной платы каждого сотрудника построчно в рамках департамента. Предложение PARTITION говорит о том, что аналитическая функция применяется к каждой группе отделов (или разделов — partition) независимо. Если посмотреть на результат запроса в Листинге 2, то можно заметить, что нарастающий итог сбрасывается после изменения департамента с 10 до 20, и снова с 20 до 30, и с 30 до записи сотрудника, который не прикреплен ни к одному департаменту и своего рода является отдельной группой.
Теперь синтаксис аналитической функции можно представить следующем общем виде:
FUNCTION_NAME( argument,argument,… )
OVER
( Partition-Clause Order-by-Clause )
Сортировки в Аналитических функциях
Запросы в Листингах 1 и 2 сортируют возвращаемые строки по фамилии и имени сотрудника. Запрос в Листинге 3 использует несколько иной критерий сортировки для вычисления аналитической функции.
Листинг 3: Вычисление каждой строки на основе значения заработной платы.
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY salary) department_total
4 from employee
5 order by department_id, salary, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— ——————————— ————————————— ————————— ————————————————
Friedli Roger 10 60000 120000
James Betsy 10 60000 120000
Michaels Matthew 10 70000 190000
Newton Donald 10 80000 270000
Eckhardt Emily 10 100000 370000
Dovichi Lori 10 370000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Wong Theresa 30 70000 70000
Jeffrey Thomas 30 300000 370000
Newton Frances 75000 75000
11 rows selected.
Аналитическая функция в Листинге 3 вычисляет суммарные значения департамента на основе заработной платы, в порядке возрастания для каждого раздела, включая значение заработной платы равное NULL, которое при сортировке окажется последним. Таким образом, запись, где last_name = Dovichi Lori — единственная запись с зарплатой NULL, которая имеет значение DEPARTMENT_TOTAL такому же значению как и у сотрудника, который имеет самую высокую зарплату в департаменте.
Предложение ORDER BY в аналитической функции работает независимо от предложения ORDER BY общего запроса, который содержит аналитическую функцию. Кроме этого, между ними существует взаимосвязь, если он используют одни и те же столбцы или выражения в том же порядке.
Например, в Листинге 4, можно увидеть, что несмотря на то, что возвращаемые данные перечисленные в порядке department_id, last_name, first_name (как и в Листингах 1 и 2), возвращаемые значения для DEPARTMENT_TOTAL совпадают со значениями результата запроса в Листинге 3.
Листинг 4: Сортировка данных из запроса Листинга 3 другим способом.
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY salary) department_total
4 from employee
5 order by department_id, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— ——————————— ————————————— ————————— ————————————————
Dovichi Lori 10 370000
Eckhardt Emily 10 100000 370000
Friedli Roger 10 60000 120000
James Betsy 10 60000 120000
Michaels Matthew 10 70000 190000
Newton Donald 10 80000 270000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Jeffrey Thomas 30 300000 370000
Wong Theresa 30 70000 70000
Newton Frances 75000 75000
11 rows selected.
Комментарии