确定Postgres分组集中的总组

debugcn 发表于 Dev

西亚伦·托宾

我如何确定使用“ GROUPING SET？”时的哪一行？

CREATE TABLE sales (
    product TEXT NOT NULL,
    customer TEXT NOT NULL,
    cost NUMERIC NOT NULL 
);

INSERT INTO sales (product, customer, cost)
VALUES ('Igloo', 'John', 5000)
    , ('Igloo', 'John', 5000)
    , ('Donkey', 'John', 1000)
    , ('Donkey', 'Jane', 1000)
    , ('Mortgage', 'John''s Brother, Jim', 1000);

我想总结客户，产品和整体的销售额：

SELECT product, customer, SUM(cost)
FROM sales
GROUP BY GROUPING SETS ((product), (customer), ())
ORDER BY GROUPING(product, customer);

给出以下结果：

product  customer            sum
-------- ------------------- -----
Donkey   (null)              2000
Igloo    (null)              10000
Mortgage (null)              1000
(null)   Jane                1000
(null)   John                11000
(null)   John's Brother, Jim 1000
(null)   (null)              13000

我想添加一is_total列，有什么办法来设置“空”分组GROUPING()？理想情况下，我会这样做，但这是无效的：

SELECT product
, customer
, sum(cost)
, GROUPING(()) == 0 AS is_total
FROM sales
GROUP BY GROUPING SETS ((product), (customer), ())
ORDER BY GROUPING(product, customer);

我知道我可以这样做：

SELECT product
, customer
, sum(cost)
, GROUPING(product, customer) = 3 AS is_total
FROM sales
GROUP BY GROUPING SETS ((product), (customer), ())
ORDER BY GROUPING(product, customer);

但是理想情况下我不会，因为我的查询非常复杂并且组是动态创建的。这只是一个简单的例子。

SQLFiddle在这里是否有帮助：http ://sqlfiddle.com/#!17/f5ba5/12

戈登·利诺夫

通常，coalesce()足够：

select coalesce(product, customer, 'Total')

更正式地，您可以使用grouping()：

select (case when grouping(product) = 0 then product
             when grouping(customer) = 0 then customer
             else 'Total'
        end) as the_key

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。