Équivalent PostgreSQL pour SQL Server GROUP BY avec ROLLUP

J'ai une Requête Sql Server qui utilise le ROLLUP clause, lors du regroupement. J'ai envie d'une requête équivalente dans Postgres. Requête dans SQl Server est:

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

source d'informationauteur Satish Sharma

  1. 16
    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
  2. 9

    C'est une réponse générique à l'équivalent de rouler dans Postgresql.

    Donné un tableau 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);

    Et cette requête SQL Server: SQL Violon

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

    C'est la façon de le faire dans Postgresql: SQL Violon

    Créer de l'agrégation d'expressions de table communes a partir du détail allant vers le haut niveau:

    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
)

    Avis que pour la performance de la prochaine niveau des agrégats à partir de la précédente en bas niveau.

    Avec qui fait juste de l'union les expressions de table communes de créer les étiquettes appropriées:

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

    Dans Postgres 9.5 apparu GROUPING SETS, le CUBE et CUMULATIF: http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS