Доступные версии документации

Агрегатные функции для интервалов агрегирования

Функция

Определяет агрегатные функции для интервалов агрегирования.

Спецификация
                   
<агрегатная функция для интервала>::=
COUNT (*) | функция
<тип функции>::=
{ AVG | MEDIAN | MAX| MIN | SUM | COUNT | VARIANCE | STDDEV }
Синтаксические правила
  1. <Значимые выражения> в <OVER-спецификации> задают столбцы выборки, по которым выполняется разбивка выборки на интервалы агрегирования.

  2. <Имена столбцов> в <ORDER BY-спецификации> внутри <OVER-спецификации> задают имена упорядочиваемых столбцов.

  3. Опция DISTINCT запрещена. При необходимости её следует выносить на верхний уровень.

Получить количество автомобилей разных производителей.

SELECT distinct make, count(*) FROM auto GROUP BY make;

Получить количество моделей автомобилей разных производителей.

Правильный запрос:

select distinct mk, mo, cnt from
select make mk, model mo, count(*) over (partition by make, model) cnt FROM auto);

Неправильный запрос:

select distinct make mk, model mo, count(*) over (partition by make, model) cnt from auto;

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

Общие правила
  1. Агрегатные функции для интервалов агрегирования выполняются аналогично соответствующим агрегатным функциям для множества значений. Отличие в том, что в первом случае агрегирование применяется отдельно для каждого интервала агрегирования, во втором – сразу для всего множества значений.

    select distinct make, count(*)  from auto group by make;
    
    MAKE
     ----
    | ALPINE              |         7 |
    | AMERICAN MOTORS     |        91 |
    | BMW                 |        10 |
    | CHRYSLER            |       168 |
    | CITROEN             |         7 |
    | DATSUN              |        10 |
    | DE TOMASO           |        12 |
    | FERRARI             |        30 |
    | FIAT                |        25 |
    | FORD                |       118 |
    | GENERAL MOTORS      |       284 |
    | ISO                 |         6 |
    | JAGUAR              |        23 |
    | LAMBORGHINI         |         7 |
    | LANCIA              |         8 |
    | LOTUS               |        17 |
    | MASERATI            |        24 |
    | MATRA               |         7 |
    | MAZDA               |         5 |
    | MERCEDES-BENZ       |        33 |
    | MG                  |        10 |
    | MINI                |         2 |
    | MITSUBISHI          |        11 |
    | MONTEVERDI          |         6 |
    | MORGAN              |         9 |
    | NSU                 |         6 |
    | OPEL                |         7 |
    | PORSCHE             |         6 |
    | ROLLS-ROYCE         |        16 |
    | TRIUMPH             |         9 |
    | VOLKSWAGEN          |        10 |
    | VOLVO               |         1 |
    | VW-PORSCHE          |        15 |
  2. Значения функций вычисляются после обработки GROUP BY и HAVING-спецификаций, но до выполнения DISTINCT и ORDER BY-спецификаций. Т.е. в случае наличия в запросе конструкции GROUP BY сначала формируется временная таблица – результат GROUP BY с обычными агрегатными функциями, без OVER-конструкции, а уже затем вычисляется функция с OVER-конструкцией, в качестве входных данных для которой берутся данные из временной таблицы, сформированной при обработке GROUP BY-конструкции.

Примеры
  1. Запрос, который находит суммарное, среднее и медианное значение зарплаты. У этого запроса два интервала агрегирования: по столбцу «вид работы» (job) и по столбцу «отдел» (dept_id). В первом вычисляется сумма зарплат в зависимости от различных видов работ. Во втором – средняя зарплата по отделам.

    create or replace table payment(dept_id int, job  char(20),
     manager char(20), salary numeric);
     insert into payment values(101,'Инженер', 'Ivanov', 100);
     insert into payment values(101,'Инженер', 'Ivanov_1', 105);
     insert into payment values(101,'Инженер', 'Ivanov_2', 110);
     insert into payment values(101,'Старший инженер', 'Petrov', 120);
     insert into payment values(101,'Зав. сектором', 'Sidorov', 130);
     insert into payment values(101,'Нач. отдела', 'Kozlov', 10000);
     insert into payment values(201,'Программист', 'Иванов', 300);
     insert into payment values(201,'Программист', 'Иванов_1', 320);
     insert into payment values(201,'Программист', 'Иванов_2', 335);
     insert into payment values(201,'Администратор', 'Петров', 370);
     insert into payment values(201, 'Дизайнер', 'Сидоров', 280);
     insert into payment values(201,'Нач. группы', 'Козлов', 1500);
    DEPT_ID     JOB        SUM_SAL_JOB  AVG_SAL_DEPT  MEDIAN_SAL_DEPT
    -------     ---        -----------  ------------  ---------------
    |101   |Нач. отдела    |10000.0   |1760.8333333333|115.0        |
    |101   |Инженер        |315.0     |1760.8333333333|115.0        |
    |101   |Зав. сектором  |130.0     |1760.8333333333|115.0        |
    |101   |Старший инженер|120.0     |1760.8333333333|115.0        |
    |201   |Дизайнер       |280.0     |517.5          |352.5        |
    |201   |Администратор  |370.0     |517.5          |352.5        |
    |201   |Нач. группы    |1500.0    |517.5          |352.5        |
    |201   |Программист    |955.0     |517.5          |352.5        |
  2. Запрос, который находит сотрудника с самой высокой зарплатой по каждому виду работ и самую высокую зарплату в каждом отделе. Для этого он создает два интервала агрегирования (по столбцам job и dept_id) из таблицы Employee. Запрос использует одну и ту же функцию MAX для агрегирования, но применяет ее к этим двум интервалам раздельно. Раздельное применение необходимо потому, что самая высокая зарплата по каждому виду работ не имеет ничего общего с самой высокой зарплатой в каждом отделе.

    SELECT job, dept_id, last_name AS name, salary, max_dept_sal
    FROM (SELECT dept_id, job, last_name,
    MAX(salary) OVER (PARTITION BY job) max_job_sal, salary,
    MAX(salary) OVER (PARTITION BY dept_id) max_dept_sal
    FROM employee) AS part_deptid
    WHERE salary = max_job_sal;
  3. Запросы, выбирающие список наиболее популярных автомобилей из всех производителей.

    select mk, mo, cnt, rn from
    (
    select mk, mo, cnt, row_number() over (partition by mk order by cnt desc) rn from
    (select distinct mk, mo, cnt
    from
    (select auto.make mk,auto.model mo,
       count(*) over(partition by auto.make, auto.model order by auto.model) cnt
     from auto,person
     where
       auto.personid=person.personid)))
    where
      rn = 1;
    
    select mk, mo, cnt, fv from
    (
    select mk, mo, cnt, first_value(cnt) over (partition by mk order by cnt desc) fv from
    (
    select distinct mk, mo, cnt
    from
    (select auto.make mk,auto.model mo,
       count(*) over(partition by auto.make, auto.model order by auto.model) cnt
     from auto,person
     where
       auto.personid=person.personid)))
    where cnt = fv;
Заметили ошибку?
Выделите текст и нажмите Ctrl + Enter