时间戳

首页 > 解决方案 > 在 TSQL 中将 Pivot 函数与 Sum 函数结合使用

sql-server - 在 TSQL 中将 Pivot 函数与 Sum 函数结合使用

问题描述

我正在尝试从该表创建一个查询：上面是我的表，下面是我需要我的查询显示的内容。

我尝试过的： 首先，我尝试使用 UNION，但它不允许我求和或求平均值。然后我尝试使用 PIVOT 功能，但同样，问题是百分比要求。我不想做的一件事是创建一个表，然后将信息存储在那里。我认为这可以通过查询来解决，所以这不是一个理想的解决方案。通过获取产品类别的总订单数计算占总数的百分比。在示例中为 001，然后将子类别区域的总顺序除以总数。非常非常感谢提前！

查询和结果集

标签： sql-servertsqlsql-server-2016

解决方案

您想要的那种东西更像是 UNPIVOT 功能而不是 PIVOT 功能，但我更喜欢使用 APPLY。让我们从这样做开始，然后转到百分比（将使用 OVER 子句）。

SELECT t.ProductCategory, t.ProductSubCategory, u.Region, u.[Total Orders]
FROM dbo.OriginalTable t
CROSS APPLY
    (SELECT 'North America' as Region, [Total Orders Placed in North America] as [Total Orders]
     UNION ALL
     SELECT 'South America' as Region, [Total Orders Placed in South America] as [Total Orders]
     UNION ALL
     SELECT 'North Africa' as Region, [Total Orders Placed in North Africa] as [Total Orders]
     UNION ALL
     SELECT 'South Africa' as Region, [Total Orders Placed in South Africa] as [Total Orders]
    ) u
;

对，所以现在让我们理清这个百分比。

我们要考虑每个 ProductCategory 的订单总和。要做到这一点并且仍然可以访问其他列，我们使用带有“PARTITION BY”的 OVER 子句，就像这样（而不是带有 的子查询GROUP BY，这会阻止我们重新挂钩u.[Total Orders]）：

SUM(u.[Total Orders]) OVER (PARTITION BY p.ProductCategory)

就这么容易。那么除以u.[Total Orders]它，我们就得到了我们想要的东西。这使它成为 1 的一部分（即 1.5% 将是 0.015），但是您可以处理它在消耗最终查询的任何内容中的显示方式。

SELECT t.ProductCategory, t.ProductSubCategory, u.Region, u.[Total Orders],
    u.[Total Orders] / SUM(u.[Total Orders]) OVER (PARTITION BY t.ProductCategory) as [Percentage of Total]
FROM dbo.OriginalTable t
CROSS APPLY
    (SELECT 'North America' as Region, [Total Orders Placed in North America] as TotalOrders
     UNION ALL
     SELECT 'South America' as Region, [Total Orders Placed in South America] as [Total Orders]
     UNION ALL
     SELECT 'North Africa' as Region, [Total Orders Placed in North Africa] as [Total Orders]
     UNION ALL
     SELECT 'South Africa' as Region, [Total Orders Placed in South Africa] as [Total Orders]
    ) u
;

推荐阅读