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 时，表示这是一个汇总行，包含所有商店的总销售总额。