如何在 T-SQL 中计算 ABCIndicator

Question

例如，我有一个销售数据表：

SELECT ItemCode, ItemDesc, TotalYearlySales, ShareOfBusiness, ABCIndicator
FROM Sales
WHERE Yir = Year(getdate())
AND Manth = Month(getdate())
ORDER BY TotalYearlySales DESC

ShareOfBusiness 计算为项目的 (TotalYearlySales/SUM(TotalYearlySales))*100

ABCIndicator 是 A，表示总和为 80% 的项目列表。 SUM（业务份额）。 B 代表接下来的 15%，C 代表最后 5%。

如何获取每个项目的 ABCIndicator？

样本数据：

ItemCode ItemDesc      TotalYearlySales ShareOfBusiness ABCIndicator
1234     Yellow Flute  3000             .36             A
1235     Brown Violin  2000             .24             A
1236     Silver Flute  1800             .21             A
1236     Pink Drums    1500             .18             B

Answer 1

您可以在子查询中计算总和和运行总和。运行总和是该行以及总和较高的行的销售总额。

select
    ItemCode
,   ItemDesc
,   TotalYearlySales
,   TotalYearlySales / SalesSum as Share
,   case when RunningSum > 0.2 * SalesSum then 'A'
         when RunningSum > 0.05 * SalesSum then 'B'
         else 'C'
    end as ABCIndicator    
from (
    select
        ItemCode
    ,   ItemDesc
    ,   TotalYearlySales
    ,   (
        select sum(TotalYearlySales) 
        from @Sales t1
        where Yir = Year(getdate())
        ) as SalesSum
    ,   (
        select sum(TotalYearlySales) 
        from @Sales t2
        where Yir = Year(getdate()) 
              and TotalYearlySales <= t3.TotalYearlySales
        ) as RunningSum
    from @Sales t3
    where Yir = Year(getdate())
) sub

这打印：

ItemCode  ItemDesc      TotalYearlySales  Share  ABCIndicator
1234      Yellow Flute  3000              0,36   A
1235      Brown Violin  2000              0,24   A
1236      Silver Flute  1800              0,21   A
1236      Pink Drums    1500              0,18   B

Answer 2

我认为你的问题需要应用 NTILE() 函数。
这里有一些阅读内容

Answer 3

也许像

Select *
    , ABCIndicator = case when ShareOfBusiness > 80 then 'A'
                    when ShareOfBusiness > 65 then 'B'
                    else 'C' --or whatever logic you need
                    End
From
(
    Select *
    ,ShareOfBusiness = MonthlySales/TotalYearlySales*100
    From
    (
        SELECT ItemCode
        , ItemDesc
        , Sum(Sales) TotalYearlySales
        ,Sum(case when Manth = Month(getdate()) then Sales else 0 end) MonthlySales
        --, ShareOfBusiness, ABCIndicator
        FROM Sales
        WHERE Yir = Year(getdate())
        Group By ItemCode, ItemDesc
    ) SalesSummary
) ShareOfBusiness
ORDER BY TotalYearlySales DESC