SQL技巧：利用阶梯式累加进行累计数量的计算

SQL技巧：利用阶梯式累加进行累计数量的计算

我们在进行柏拉图/二八分析时，经常要对各类数量，累计数量进行统计。比如缺陷分析，必须先计算各类缺陷数量，累计缺陷数量，缺陷率，累计缺陷率。在计算累计数量时，可以根据先后顺序进行阶梯式累加。

 --1 创建一个临时表，数据如下，注意AutoId必须是按序从小到大排列

SELECT   *   INTO  #TmpInvIssQuy 
   FROM (
       SELECT   1  AutoId,  60  Inv_Qty,  NULL  Inv_TotalQty  UNION   ALL
      SELECT   2  AutoId,  50  Inv_Qty,  NULL  Inv_TotalQty  UNION   ALL
      SELECT   3  AutoId,  40  Inv_Qty,  NULL  Inv_TotalQty  UNION   ALL
      SELECT   4  AutoId,  30  Inv_Qty,  NULL  Inv_TotalQty  UNION   ALL
      SELECT   5  AutoId,  20  Inv_Qty,  NULL  Inv_TotalQty  UNION   ALL
      SELECT   6  AutoId,  10  Inv_Qty,  NULL  Inv_TotalQty 
  ) T1

SELECT   *   FROM  #TmpInvIssQuy

/*结果
#TmpInvIssQuy ：
AutoId      Inv_Qty           Inv_TotalQty
---------------------------------------------------
1                  60
2                  50
3                  40
4                  30
5                  20
6                  10
*/

 

--2 阶梯式累加 

UPDATE  A
    SET  A.Inv_TotalQty = B.Inv_TotalQty
   FROM  #TmpInvIssQuy A,
       ( SELECT  B1.AutoId, ISNULL ( SUM (B2.Inv_Qty), 0 ) Inv_TotalQty
           FROM  #TmpInvIssQuy B1,#TmpInvIssQuy B2 
          WHERE  B1.AutoId >= B2.AutoId
          GROUP   BY  B1.AutoId) B
  WHERE  A.AutoId = B.AutoId

SELECT   *   FROM  #TmpInvIssQuy

    

--3 结果
/*
#TmpInvIssQuy ：
AutoId      Inv_Qty           Inv_TotalQty
---------------------------------------------------
1                  60                      60
2                  50                      110
3                  40                      150
4                  30                      180
5                  20                      200
6                  10                      210
*/

     

实际项目中应用阶梯式累加例子：

   SET  NOCOUNT  ON
   DECLARE   @TQty   INT
   IF   @PerPrecision   IS   NULL
     SELECT   @PerPrecision   =   2
    
   -- 得到外部的基本数据
   CREATE   TABLE  #TempItem1
  (   
      ItemName  VARCHAR ( 255 ),  -- 名称
      Qty  int   null ,          -- 数量
  )

   EXEC ( ' INSERT INTO #TempItem1 SELECT DISTINCT ItemName,Qty FROM  ' + @GloableTempTable )

   -- 对Top处理
   SELECT  ItemName, Qty,  1  OrderBy 
     INTO  #TempItem2
     FROM  #TempItem1 
    WHERE   1   =   2

   IF   @Top   <=   0  
     -- 全部
     INSERT   INTO  #TempItem2 
       SELECT  ItemName, Qty,  1  OrderBy
         FROM  #TempItem1
   ELSE
   BEGIN
     -- 前几项
     EXEC ( ' INSERT INTO #TempItem2 SELECT TOP  '   +   @Top   +   '  ItemName, Qty, 1 OrderBy FROM #TempItem1 ORDER BY Qty DESC ' )    
     -- 其它
     INSERT   INTO  #TempItem2 
       SELECT   ' [Other] ' ,  SUM ( ISNULL (Qty, 0 )),  0  OrderBy
         FROM  #TempItem1 A
        WHERE  ItemName  NOT   IN  ( SELECT  ItemName  FROM  #TempItem2)
     DELETE   FROM  #TempItem2  WHERE  Qty  IS   NULL
   END

   -- 得到总数
   SELECT   @TQty   =   NULLIF ( SUM ( ISNULL (Qty, 0 )),  0 )
     FROM  #TempItem2
  
   -- 唯一号，排序
   SELECT   IDENTITY ( INT , 1 , 1 )  AS  AutoId, ItemName, Qty,  CAST ( NULL   AS   INT ) TotalQty,  CAST ((Qty + 0.0 ) / @TQty   AS   DECIMAL ( 18 , 8 )) Rate,  CAST ( NULL   AS   DECIMAL ( 18 , 8 )) TotalRate
     INTO  #TempItem3
     FROM  #TempItem2
    ORDER   BY  OrderBy  DESC , Qty  DESC , ItemName 

   -- 阶梯式累加,计算累计缺陷数
   UPDATE  A
      SET  A.TotalQty  =  B.TotalQty
     FROM  #TempItem3 A,
         ( SELECT  B1.AutoId, ISNULL ( SUM (B2.Qty), 0 ) TotalQty
             FROM  #TempItem3 B1, #TempItem3 B2 
            WHERE  B1.AutoId  >=  B2.AutoId
            GROUP   BY  B1.AutoId) B
    WHERE  A.AutoId  =  B.AutoId
  
   -- 计算累计缺陷率
   UPDATE  A
      SET  A.TotalRate  =    CAST (TotalQty  AS   DECIMAL ( 18 , 8 ))  /   @TQty
     FROM  #TempItem3 A

   -- 1 按格式输出：显示数据
   SELECT   IDENTITY ( INT , 1 , 1 ) AutoId, ItemName,  CAST (Qty  AS   INT ) Qty,  CAST (TotalQty  AS   INT ) TotalQty
    INTO  #TempItem4
    FROM  #TempItem3
   WHERE   1   =   2  

   EXEC ( ' ALTER TABLE #TempItem4 ADD Rate DECIMAL(18,  ' + @PerPrecision + ' ), TotalRate DECIMAL(18,  ' + @PerPrecision + ' ) ' )
  
   INSERT   INTO  #TempItem4
     SELECT  ItemName, Qty, TotalQty, Rate  *   100  , TotalRate  *   100  
       FROM  #TempItem3  

   SELECT  ItemName DefectName, Qty DefectQty, TotalQty TotalDefectQty,  CAST (Rate  AS   VARCHAR ) + ' % '  Rate,  CAST (TotalRate  AS   VARCHAR ) + ' % '  TotalRate 
     FROM  #TempItem4