根据当前记录的SKU字段中的值在列中求和

Question

由于其大小(文件大小和复杂性使其异常缓慢(，我正在将我正在构建的工具从excel迁移到SQL。我有一个不同DC的SKU列表，每个都有一个计数。我想根据每个站点的每个SKU的TotalOnHand创建一个SUM。本质上"；将SKU等于当前记录的SKU的其他仓库的值相加"然后我可以把这个值作为分母，得到每个仓库总库存的百分比。我是第一次为这个项目学习SQL。

当前查询：

SELECT  Material, Plant, Unrestricted + [Unrestricted VOI] as TotalOnHand
FROM [ATP_Snapshot].[Tables].[INVENTORY_REPT]
WHERE Unrestricted >0
AND [Unrestricted VOI] >0
AND (
Plant = 'US23'
OR Plant = 'US27'
OR Plant = 'US46'
OR Plant = 'US49'
)
ORDER BY Plant, Material

我正在尝试创建的函数。我被它的结局难住了。

CREATE FUNCTION Inventory.PercentOfTotal (@TotalOnHand integer)
RETURNS decimal (4,3)
AS
BEGIN
DECLARE @PercentTotal decimal (4,3);
SET @PercentTotal = @TotalOnHand/(SELECT SUM(@TotalOnHand) WHERE

此外，我在理解名称的地方得到了一个错误指示器(即Inventory.PercentOfTotal(。这就是我试图命名我的函数的名称。

Answer 1

这取决于您希望如何显示结果。

如果您希望每个Material和Plant组合在一个单独的行上，那么您需要一个窗口函数。请注意在聚合SUM(SUM())上使用窗口。

SELECT
ir.Material,
ir.Plant,
SUM(ir.Unrestricted + ir.[Unrestricted VOI]) AS TotalOnHand,
SUM(ir.Unrestricted + ir.[Unrestricted VOI]) * 1.0
/ SUM(SUM(ir.Unrestricted + ir.[Unrestricted VOI])) OVER (PARTITION BY ir.Material) AS PctOnHand
FROM Tables.INVENTORY_REPT ir
WHERE ir.Unrestricted > 0
AND ir.[Unrestricted VOI] > 0
AND ir.Plant IN ('US23', 'US27', 'US46', 'US49')
GROUP BY
ir.Material,
ir.Plant
ORDER BY
ir.Material,
ir.Plant;

或者，如果你希望它们都在同一行，那么你需要条件聚合，也就是一个枢轴。注意使用CROSS APPLY (VALUES来防止重复计算，您也可以在上面的查询中这样做。

SELECT
ir.Material,
ir.Plant,
SUM(CASE WHEN ir.Plant = 'US23' THEN v.Total END) AS TotalOnHand_US23,
SUM(CASE WHEN ir.Plant = 'US23' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US23,
SUM(CASE WHEN ir.Plant = 'US27' THEN v.Total END) AS TotalOnHand_US27,
SUM(CASE WHEN ir.Plant = 'US27' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US27,
SUM(CASE WHEN ir.Plant = 'US46' THEN v.Total END) AS TotalOnHand_US46,
SUM(CASE WHEN ir.Plant = 'US46' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US46,
SUM(CASE WHEN ir.Plant = 'US49' THEN v.Total END) AS TotalOnHand_US49,
SUM(CASE WHEN ir.Plant = 'US49' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US49
FROM Tables.INVENTORY_REPT ir
CROSS APPLY (VALUES (
ir.Unrestricted + ir.[Unrestricted VOI]
) ) v(Total)
WHERE ir.Unrestricted > 0
AND ir.[Unrestricted VOI] > 0
AND ir.Plant IN ('US23', 'US27', 'US46', 'US49')
GROUP BY
ir.Material
ORDER BY
ir.Material;

Answer 2

您的函数还需要两个输入参数(一个用于所有工厂的总数，一个用于每个工厂的总数(。功能通常也很慢，因为它们处理"；一排排痛苦的排"；(查看Jeff Moden的RBAR以获得进一步的解释(所以我不会在这里真正使用它，但如果你想了解它们，那么你应该创建一个并测试它。

我会使用OVER子句来对不同的分区求和，而不是使用函数。

代码看起来像这样：

SELECT  
distinct(Material)
,Plant
,sum(Unrestricted + [Unrestricted VOI]) OVER(Partition By Plant) as TotalOnHandPerPlant
,sum(Unrestricted + [Unrestricted VOI]) OVER(Partition By Plant)/sum(Unrestricted + [Unrestricted VOI]) OVER()*100 as PercentOnHandPerPlant
,sum(Unrestricted + [Unrestricted VOI]) OVER() as TotalOnHandAllPlants
FROM [ATP_Snapshot].[Tables].[INVENTORY_REPT]
WHERE 
Unrestricted > 0
AND [Unrestricted VOI] > 0
AND Plant in ('US23','US27','US27','US46','US49')
ORDER BY 
Plant
, Material