在 postgresql 8.0 中汇总数据层次结构?

Question

我正在尝试在postgresql 8.0中汇总。在postgresql的最新版本中，我们有ROLLUP功能，但是如何在postgresql 8.0中实现rollup？有人有同样的经验吗？

我尝试了以下方法

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
;