postgresql - 在 PostgreSQL 中替代 MS SQL Server 的 GROUP BY ... WITH ROLLUP？

Question

SELECT Category ,CASE WHEN GROUPING([Category]) = 0 THEN [Category] 
        ELSE 'Total Shown' END AS [Category]
    ,SUM([Impressions]) AS [Impressions]
    ,SUM([Clicks]) AS [Clicks]
    ,CASE WHEN SUM([Impressions]) = 0 THEN NULL ELSE SUM([Clicks]) / (SUM([Impressions]) * 1.0) END AS [CTR]
    ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Cost]) / (SUM([Clicks]) * 1.0) END AS [CPClick]
    ,SUM([Cost]) AS [Spend]
    ,SUM([Transactions]) AS [Transactions]
    ,SUM([Conversions]) AS [Conversions]
    ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Transactions]) / (SUM([Clicks]) * 1.0) END AS [Trans Rate]
    ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Actions]) / (SUM([Clicks]) * 1.0) END AS [Action Rate]
    ,SUM([Actions]) AS [Actions]
    ,Grouping( Category ) as TotalShown

INTO #regularMetrics FROM #tmp
GROUP BY Category WITH ROLLUP 
ORDER BY TotalShown,[Impressions] desc, [Category]

在 postgresql 中有没有分组的替代方法？？

---

我被分成两个查询是否有效？

SELECT [Category ] , SUM([Impressions]) AS [Impressions] ,SUM([Clicks]) AS [Clicks] ,CASE WHEN SUM([Impressions]) = 0 THEN NULL ELSE SUM([Clicks]) /
  (SUM([Impressions]) * 1.0) END AS [CTR] ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Cost]) / (SUM([Clicks]) * 1.0) END AS [CPClick] ,SUM([Cost]) AS [Spend]  
  ,SUM([Transactions]) AS [Transactions]  ,SUM([Conversions]) AS [Conversions]  ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Transactions]) / (SUM([Clicks]) * 1.0) 
  END AS "Trans Rate"  ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Actions]) / (SUM([Clicks]) * 1.0) END AS "Action Rate"  ,SUM([Actions]) AS [Actions]  , 0 as TotalShown  
  INTO #regularMetrics  FROM #tmp GROUP BY Category   ORDER BY Category ,[Impressions] desc;

 insert into #regularmetrics(select 'Total Shown', sum(impressions), sum(clicks), CASE WHEN SUM([Impressions]) = 0 THEN NULL ELSE SUM([Clicks]) / (SUM([Impressions]) * 1.0) END AS [CTR], CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([spend]) / (SUM([Clicks]) * 1.0) END AS [CPClick], sum(spend),sum(transactions),sum(conversions), CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Transactions]) / (SUM([Clicks]) * 1.0) END AS "Trans Rate"  ,CASE WHEN SUM([Clicks]) = 0 THEN NULL ELSE SUM([Actions]) / (SUM([Clicks]) * 1.0) END AS "Action Rate", sum(actions), 1 from #regularmetrics); 

Answer 1

如果我正确理解你的问题，你想知道如何在 PostgreSQL 中执行与 MS SQL Server 扩展 WITH ROLLUP 等效的操作，而其余查询似乎是无关紧要的噪音。

如果是，according to MSDN, the WITH ROLLUP option adds subtotals .不幸的是，这不是 PostgreSQL 目前支持的。

在 PostgreSQL 中的解决方法是编写如下内容:

WITH my_real_query AS (
    SELECT a, b
    FROM ...
    -- blah blah
)
SELECT 'line', a, b FROM my_real_query
UNION ALL
SELECT 'sum', sum(a), sum(b) FROM my_real_query;

具体取决于你想做什么。这需要内部表被具体化然后扫描两次，所以它并不是非常高效。如果您想按子集分组，您可以通过将 GROUP BY 添加到 UNION ALL 查询的第二个分支来实现，例如:

WITH my_real_query AS (
    SELECT a, b
    FROM ...
    -- blah blah
)
SELECT a, b FROM my_real_query
UNION ALL
SELECT a, sum(b) FROM my_real_query GROUP BY a;

至于INSERT 部分...您通常应该使用SQL 标准INSERT INTO ... SELECT 语法。假设您想将以上内容插入表格。你会写:

INSERT INTO some_table(col1,col2)
WITH my_real_query AS (
    SELECT a, b
    FROM ...
    -- blah blah
)
SELECT a, b FROM my_real_query
UNION ALL
SELECT a, sum(b) FROM my_real_query GROUP BY a;

如您所见，它实际上是 INSERT INTO ... [QUERY] 其中 [QUERY] 可以是 WITH ... SELECT等等。

对了，请阅读postgresql documentation on basic SQL syntax和 lexical structure学习如何为 PostgreSQL 编写正确的 SQL。一般来说，使用 ANSI 标准样式而不是 MS SQL Server 的样式将是一个很好的起点。

像 #regularMetrics 和 [Name] 这样的东西在 PostgreSQL 中是完全无效的，它们不是有效的语法。标识符引号用双引号引起来，例如 "regularMetrics", "Name"。