PostgreSQL 等效于 SQL Server GROUP BY WITH ROLLUP

PostgreSQL equivalent for SQL Server GROUP BY WITH ROLLUP

提问by Satish Sharma

I have an Sql Server Query that is using the ROLLUPclause while grouping. I want an equivalent query in Postgres. Query in SQl Server is:

我有一个 Sql Server 查询，它ROLLUP在分组时使用该子句。我想要 Postgres 中的等效查询。SQL Server 中的查询是：

SELECT (CASE WHEN acnt_dba_name Is Null THEN 'Total' ELSE acnt_dba_name END) as account,
       (CASE WHEN evt_name Is Null THEN '' ELSE evt_name END) as event, 
       COUNT(CASE reg_is_complete WHEN true THEN 1 ELSE Null END) as regsComplete,
       COUNT(CASE WHEN reg_frn_pro_id > 0 AND reg_is_complete = false THEN 1 ELSE Null END) as regsInComplete,
       COUNT(CASE WHEN reg_frn_pro_id > 0 THEN Null ELSE 1 END) as regsClicks
FROM      registrations_view 
LEFT JOIN events ON (evt_id = reg_frn_evt_id)
LEFT JOIN accounts ON (acnt_id = evt_frn_acnt_id)
WHERE reg_date_created < #CreateODBCDate(url.endDate)#
AND   reg_date_created > #CreateODBCDate(url.startDate)#
AND   reg_is_active = true      -- only active regs
AND   reg_is_test = false       -- only live registrations
-- AND  reg_is_denied = false         -- exclude denied reg statuses (include these for now RWB 8/7/2)
GROUP BY rollup(acnt_dba_name, evt_name)
-- Sort with Nulls at the bottom
ORDER BY acnt_dba_name, evt_name

回答by Clodoaldo Neto

with detail as (
    select
        acnt_dba_name as account,
        evt_name as event, 
        count(case reg_is_complete when true then 1 else null end) as regscomplete,
        count(case when reg_frn_pro_id > 0 and reg_is_complete = false then 1 else null end) as regsincomplete,
        count(case when reg_frn_pro_id > 0 then null else 1 end) as regsclicks
    from
        registrations_view 
        left join
        events on evt_id = reg_frn_evt_id
        left join
        accounts on acnt_id = evt_frn_acnt_id
    where
        reg_date_created < #CreateODBCDate(url.endDate)#
        AND reg_date_created > #CreateODBCDate(url.startDate)#
        and reg_is_active = true        -- only active regs
        and reg_is_test = false     -- only live registrations
    group by acnt_dba_name, evt_name
), account as (
    select
        account,
        '' as event, 
        sum(regscomplete) as regscomplete,
        sum(regsimcomplete) as regsincomplete,
        sum(regsclicks) as regsclicks
    from detail
    group by account
), total as (
    select
        'Total' as account,
        '' as event, 
        sum(regsComplete) as regsComplete,
        sum(regsImComplete) as regsInComplete,
        sum(regsClicks) as regsClicks
    from account
)
select * from detail
union
select * from account
union
select * from total
order by account, event

回答by Clodoaldo Neto

This is a generic answer to the equivalent of roll up in Postgresql.

这是对 Postgresql 中汇总的等价物的通用答案。

Given a table t:

给定一个表 t：

create table t (l1 char(1), l2 char(1), i integer);
insert into t (l1, l2, i) values
('A', 'X', 1),
('A', 'Y', 2),
('B', 'X', 3),
('B', 'Y', 4);

And this SQL Server query: SQL Fiddle

这个 SQL Server 查询：SQL Fiddle

select l1, l2, sum(i) total
from t
group by rollup(l1, l2)

This is how to do it in Postgresql: SQL Fiddle

这是在 Postgresql 中的操作方法：SQL Fiddle

Create aggregating CTEs starting from the detail going to the top level:

从细节到顶层创建聚合 CTE：

with detail as (
    select l1, l2, sum(i) total
    from t
    group by l1, l2
), l2 as (
    select l1, sum(total) total
    from detail
    group by l1
), l1 as (
    select sum(total) total
    from l2
)

Notice that for performance the next up level aggregates from the previous down level.

请注意，为了提高性能，下一个上一级聚合了上一个下一级。

With that done just union the CTEs creating the appropriate labels:

完成后，只需联合 CTE 创建适当的标签：

select l1, l2, total
from detail
union
select l1, 'Total', total
from l2
union
select 'Total', '', total
from l1
order by l1, l2

回答by Hubbitus

In Postgres 9.5 appeared GROUPING SETS, CUBE and ROLLUP: http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS

在 Postgres 9.5 中出现了 GROUPING SETS、CUBE 和 ROLLUP：http: //www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS