[Без оффтопа] Развлекаемся с SQL

[Rooslan Khayrov]

Продолжаем развлекаться?

В процессе одного обсуждения родилась пара задачек. Самый редуцированный вариант: для заданной таблицы посчитать некую агрегатную функцию для каждых N строк. Для наглядности примем простейшую схему с двумя колонками: id (целое автоинкремент) и value (вещественное). Надо посчитать суммы value по 10 значений: т.е. сумма первых 10, с 11-го по 20-е и т.д.

Связанная задача с большим реальным смыслом: для той же таблицы найти перцентили распределения value, с 1-й по 100-ю.

Решения для PostgreSQL:

Скрытый текст:

SELECT sum(value) FROM (
    SELECT value, row_number() OVER (ORDER BY id) AS i
    FROM data ORDER BY id) AS numbered
GROUP BY (i - 1) / 10;

SELECT ceil(percent) AS n, max(value) AS percentile FROM (
    SELECT value, 100.0 * row_number() OVER (ORDER BY value) / count(id) OVER () AS percent
    FROM data) AS sub
GROUP BY n ORDER BY n;