PostgreSQL 中的 Grouping() 等價物？

我有一個在 MS SQL Server 中執行的 SQL 查詢，它有grouping(),rank()和rollup()函式/關鍵字。查詢如下。

如何將其轉換為 PostgreSQL？

select top 100
   sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin  
  ,i_category  
  ,i_class  
  ,grouping(i_category)+grouping(i_class) as lochierarchy  
  ,rank() over (partition by grouping(i_category)+grouping(i_class)
                            ,case when grouping(i_class) = 0 then i_category end   
                order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc
               ) as rank_within_parent  
from  
   ssv_itemv_storev_IJGBAgg  
where  
   d_year = 2001   
   and s_state in ('TN')  
group by rollup(i_category,i_class)  
order by  
   lochierarchy desc  
  ,case when (grouping(i_category)+grouping(i_class) = 0) then i_category end  
  ,rank_within_parent;   

ROLLUP在 Postgres 9.5 中實現。

---

Postgres 9.4 的原始答案：

rollup()ingroup by rollup(i_category,i_class)導致分層步驟中的聚合。它是 SQL Server 中 ISO SQL 標準的擴展，在 Postgres 中沒有實現。對於兩個項目，您需要標準 SQL 以及 Postgres 中的三個步驟：

1. 總計
2. group by i_category
3. group by i_category, i_class

該**grouping()**函式帶有此擴展，並指示給定列是否在每一行中聚合。它在這裡用於在結果中首先對總和和組總和進行排序。

**rank()**是一個標準的視窗函式，在 Postgres 中工作方式相同，但由於上述調整，我們不得不重寫它。

top 100轉換為FETCH FIRST 100 ROWS ONLY標準 SQL，它在 Postgres 和更短的版本中實現LIMIT 100（FETCH FIRST語法也適用於 2012+ 版本的 SQL Server。）在這個聚合查詢中在 100 行之後切斷有點奇怪。

我正在使用CTE在下面的多個聚合中重用中間結果。除了LIMIT一切都應該在 SQL Server 中正常工作：

WITH cte AS (
  SELECT sum(ss_net_profit) AS sum_profit
       , sum(ss_ext_sales_price) AS sum_price
       , i_category, i_class
  FROM   ssv_itemv_storev_IJGBAgg
  WHERE  d_year = 2001
  AND    s_state = 'TN'
  GROUP  BY i_category, i_class
  )
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
    , NULL AS i_category, NULL AS i_class
    , 2 AS lochierarchy  
    , 1 AS rank_within_parent
FROM   cte

UNION ALL
(  -- parentheses required!
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
    , i_category, NULL AS i_class
    , 1 AS lochierarchy  
    , rank() OVER (ORDER BY sum(sum_profit)/sum(sum_price)) AS rank_within_parent
FROM   cte
GROUP  BY i_category
ORDER  BY rank_within_parent, i_category  -- last item = my addition to break ties
)

UNION ALL
(
SELECT sum_profit/sum_price AS gross_margin
    , i_category, i_class
    , 0 AS lochierarchy  
    , rank() OVER (PARTITION BY i_category
                   ORDER BY sum_profit/sum_price) AS rank_within_parent
FROM   cte
ORDER  BY i_category, rank_within_parent, i_class  -- last item = my addition to break ties
)
LIMIT 100;

引用自：https://dba.stackexchange.com/questions/94817