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