SQL技巧:利用阶梯式累加进行累计数量的计算
我们在进行柏拉图/二八分析时,经常要对各类数量,累计数量进行统计。比如缺陷分析,必须先计算各类缺陷数量,累计缺陷数量,缺陷率,累计缺陷率。在计算累计数量时,可以根据先后顺序进行阶梯式累加。
--1 创建一个临时表,数据如下,注意AutoId必须是按序从小到大排列
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 阶梯式累加
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
*/
实际项目中应用阶梯式累加例子:
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