Окна в мир аналитических функций Oracle: введение

09.10.2016 в 15:18
14305
+87

Эта статья является переводом статьи из журнала 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.
loader
09.10.2016 в 15:18
14305
+87
Комментарии
К этому посту больше нельзя оставлять новые комментарии
Логические задачи с собеседований