Свернуть иерархию данных в postgresql 8.0?

Question

Я пытаюсь накопить в postgresql 8.0. В последней версии postgresql у нас есть функция ROLLUP, но как реализовать накопительный пакет в postgresql 8.0? У кого-нибудь есть опыт работы с тем же?

Я попробовал ниже

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );

Но получаю следующую ошибку:

42883: function rollup( integer, integer, integer) does not exist

следуют из http://www.postgresqltutorial.com/postgresql-rollup/

Answer 1

Как GROUP BY ROLLUP был введен с версии 9.5, у запроса нет шансов на работу. Но если вы думаете о том, что он делает, в вашем случае будет очень легко придумать версию, которая даст тот же результат.

По сути, вы хотите иметь:

1. общая сумма
2. сумма в год
3. и сумма в месяц
4. для ежедневного учета

Я написал выше специальным образом, чтобы стало ясно, что вам действительно нужно:

1. производить ежедневные подсчеты
2. генерировать сумму в месяц из ежедневных подсчетов
3. генерировать сумму в год из ежемесячных сумм или ежедневных подсчетов
4. генерировать общее количество из годовых сумм, ежемесячных сумм или ежедневных подсчетов
5. UNION ALL из перечисленного в том порядке, в котором вы хотите

По умолчанию для GROUP BY ROLLUP состоит в том, чтобы выписать общее количество, а затем отдельные группировочные наборы с NULLS LAST, следующий запрос будет делать то же самое:

WITH
    daily AS (
        SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, EXTRACT (DAY FROM rental_date) d, COUNT (rental_id) AS count
        FROM rental
        GROUP BY 1, 2, 3
    ),
    monthly AS (
        SELECT y, M, NULL::double precision d, SUM (count) AS count
        FROM daily
        GROUP BY 1, 2
    ),
    yearly AS (
        SELECT y, NULL::double precision M, NULL::double precision d, SUM (count) AS count
        FROM monthly
        GROUP BY 1
    ),
    totals AS (
        SELECT NULL::double precision y, NULL::double precision M, NULL::double precision d, SUM (count) AS count
        FROM yearly
    )
SELECT * FROM totals
UNION ALL
SELECT * FROM daily
UNION ALL
SELECT * FROM monthly
UNION ALL
SELECT * FROM yearly
;

Вышеуказанное работает с PostgreSQL 8.4+. Если у вас даже нет этой версии, мы должны вернуться к старой школе UNION без повторного использования данных агрегации:

SELECT NULL::double precision y, NULL::double precision M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, EXTRACT (DAY FROM rental_date) d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1, 2, 3
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1, 2
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, NULL::double precision M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1
;