PostgreSQL rollup与窗口函数结合

在 PostgreSQL 中，ROLLUP 和窗口函数可以结合使用，以便在一个查询中执行分组和汇总操作

首先，我们需要创建一个示例表：

CREATE TABLE sales ( id SERIAL PRIMARY KEY, product_id INT, store_id INT, amount NUMERIC(10, 2), sale_date DATE ); 

接下来，我们将插入一些示例数据：

INSERT INTO sales (product_id, store_id, amount, sale_date) VALUES (1, 1, 100.00, '2021-01-01'), (1, 1, 200.00, '2021-01-02'), (1, 2, 300.00, '2021-01-01'), (2, 1, 400.00, '2021-01-01'), (2, 2, 500.00, '2021-01-01'); 

现在，我们将使用 ROLLUP 和窗口函数计算每个商店的销售总额以及总销售总额。这是一个示例查询：

SELECT store_id, SUM(amount) AS total_sales, SUM(SUM(amount)) OVER () AS grand_total_sales FROM sales GROUP BY ROLLUP (store_id) ORDER BY store_id; 

这个查询将返回以下结果：

 store_id | total_sales | grand_total_sales ----------+-------------+------------------- 1 | 700.00| 1900.00 2 | 800.00| 1900.00 NULL | 1900.00| 1900.00 

在这个查询中，我们首先使用 GROUP BY ROLLUP (store_id) 对商店进行分组。然后，我们使用 SUM(amount) 计算每个商店的销售总额。最后，我们使用窗口函数 SUM(SUM(amount)) OVER () 计算所有商店的总销售总额。注意，当 store_id 为 NULL 时，表示这是一个汇总行，包含所有商店的总销售总额。