ЛИНТЕР

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

Функция

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

Спецификация

<агрегатная функция для интервала>::=

COUNT (*) | функция

<функция>::=

тип функции ([классификатор] значимое выражение)
[OVER-спецификация]

<тип функции>::=

{AVG | MEDIAN | MAX | MIN | SUM | COUNT | VARIANCE | STDDEV}

Синтаксические правила

<Значимые выражения> в задают столбцы выборки, по которым выполняется разбивка выборки на интервалы агрегирования.
<Имена столбцов> в внутри задают имена упорядочиваемых столбцов.
Опция 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;

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

Общие правила

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

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 |

Значения функций вычисляются после обработки GROUP BY и HAVING-спецификаций, но до выполнения DISTINCT и ORDER BY-спецификаций. Т.е. в случае наличия в запросе конструкции GROUP BY сначала формируется временная таблица – результат GROUP BY с обычными агрегатными функциями, без OVER-конструкции, а уже затем вычисляется функция с OVER-конструкцией, в качестве входных данных для которой берутся данные из временной таблицы, сформированной при обработке GROUP BY-конструкции.

Если в запросе с 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;

Примеры

Запрос, который находит суммарное, среднее и медианное значение зарплаты. У этого запроса два интервала агрегирования: по столбцу «вид работы» (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        |

Запрос, который находит сотрудника с самой высокой зарплатой по каждому виду работ и самую высокую зарплату в каждом отделе. Для этого он создает два интервала агрегирования (по столбцам 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;
```

Запросы, выбирающие список наиболее популярных автомобилей из всех производителей.

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;