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

Функция

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

Спецификация
                   
< агрегатная функция для интервала >::=
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-конструкции.

  3. Если в запросе с GROUP BY нужно использовать в SELECT или в HAVING пользовательскую функцию, рекомендуется поставить в список группировки в точности такое же выражение для пользовательской функции (а не просто столбец или столбцы, от которого она зависит).

    Пример

    Создание пользовательской функции:
    create or replace procedure get_sname(in snum char(7)) result char(20) for debug
    declare
      var c cursor(vc char(20)); //
    code
      open c for "select sname from s where snum=?;", snum; //
      return c.vc; //
    end;
    Корректные запросы:
    1)
    select get_sname(s.snum) sname, sum(qty) x
      from s left join sp on s.snum=sp.snum
     group by get_sname(s.snum)
     order by sname;
    
    2) 
    select get_sname(s.snum) sname, sum(qty) x
      from s left join sp on s.snum=sp.snum
     group by s.snum, get_sname(s.snum)
     order by sname;
    
    Некорректный запрос:
    
    select get_sname(s.snum) sname, sum(qty) x
      from s left join sp on s.snum=sp.snum
     group by s.snum
     order by sname;
Примеры
  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);
    
    select * from payment;
    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;