sql - 如何在SQL DW中使用Rollup Grouping Function报错？

Question

我收到错误，ROLLUP 不是函数名称，但文档说它应该可以工作 消息 104162，16 级，状态 1，第 2 行“ROLLUP”不是可识别的内置函数名称。

我尝试过按分组集进行分组，但它告诉我语法错误，就在那时我发现分组集不适用于 DW

SELECT S.[ProjectID]
      ,P.ProjectId
      ,P.Level2
      ,S.[PTDIncurredAmount]
      ,S.[PriorYearIncurredAmount]
      ,S.[YTDIncurredAmount], sum([YTDIncurredAmount]) as CTDActuals
  FROM [Fact].[vProjectSummary] as S
  JOIN dim.vProject as P on S.ProjectID=P.ProjectId
  Group by ROLLUP (P.Level2, S.ProjectID )

如果有人能更具体地指出我的 Azure SQL DW 代码的问题，我将不胜感激!

Answer 1

根据this github不支持文档ROLLUP:

The GROUP BY T-SQL clause aggregates data to a summary set of rows. GROUP BY has some options that SQL Data Warehouse does not support. These options have workarounds.

These options are

GROUP BY with ROLLUP

GROUPING SETS

GROUP BY with CUBE

建议的解决方法是使用UNION ALL 来模拟ROLLUP。如果我采用您的查询的简化版本

SELECT S.projectID, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
    INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectId
GROUP BY ROLLUP ( P.Level2, S.ProjectID )
ORDER BY 1, 2, 3

通过使用ROLLUP，此查询请求以下聚合:

• Level2 和 ProjectId
• 2级
• 总计

您可以使用UNION ALL来模拟这一点:

-- Level 2 and ProjectID
SELECT S.ProjectID, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
    INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID
GROUP BY S.ProjectID, p.Level2

UNION ALL

-- Level 2 
SELECT NULL, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
    INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID
GROUP BY p.Level2

UNION ALL

-- Grand total
SELECT NULL, NULL, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
    INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID

我无法使用 ROLLUP 进行任何查询，尽管此链接似乎表明这是可能的:

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

还有一个反馈项目您可以在这里投票:

https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/35836048-grouping-and-group-by-rollup-functions-needs-to-be