SQL Server里Grouping Sets的威力

2022-10-14 00:00:00 查询多个运算符分组聚集

在SQL Server里，你有没有想进行跨越多个列/纬度的聚集操作，不使用SSAS许可（SQL Server分析服务）。我不是说在生产里使用开发版，也不是说安装盗版SQL Server。

不可能的任务？未必，因为通过SQL Server里所谓的Grouping Sets就可以。在这篇文章里我会给你概括介绍下Grouping Sets，使用它们可以实现哪类查询，什么是它们的性能优势。

使用Grouping Sets的聚合

假设你有个订单表，你想进行跨多个分组的T-SQL聚集查询。在AdventureWorks2012数据库的Sales.SalesOrderHeader表的环境里，这些分组可以类似如下：

在每列分组
GROUP BY SalesPersonID, YEAR(OrderDate)
GROUP BY CustomerID, YEAR(OrderDate)
GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate)

当你想用传统T-SQL查询进行这些各自分组时，你需要多个语句，对各个记录集进行UNION ALL。我们来看这样的查询：

SELECT * FROM
(
    -- 1st Grouping Set
    SELECT
        NULL AS 'CustomerID',
        NULL AS 'SalesPersonID',
        NULL AS 'OrderYear',
        SUM(TotalDue) AS 'TotalDue'
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL

    UNION ALL

    -- 2nd Grouping Set
    SELECT
        NULL AS 'CustomerID',
        SalesPersonID,
        YEAR(OrderDate) AS 'OrderYear',
        SUM(TotalDue) AS 'TotalDue'
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)

    UNION ALL

    -- 3rd Grouping Set
    SELECT
        CustomerID,
        NULL AS 'SalesPersonID',
        YEAR(OrderDate) AS 'OrderYear',
        SUM(TotalDue) AS 'TotalDue'
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY CustomerID, YEAR(OrderDate)

    UNION ALL

    -- 4th Grouping Set
    SELECT
        CustomerID,
        SalesPersonID,
        YEAR(OrderDate) AS 'OrderYear',
        SUM(TotalDue) AS 'TotalDue'
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate)
) AS t
ORDER BY CustomerID, SalesPersonID, OrderYear
GO
相关文章