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