MSSQL 基础之逐行累计

前言

昨天一位群友提到这样一个问题：源数据如下图，仅有条码和销售金额两列。

第一步要算出累计权重，第二步要算出商品排名。

累计权重：

条码1 累计权重=条码1销售额/所有条码销售额

条码2 累计权重=条码1+条码2销售额/所有条码销售额

依次类推

商品排名：

累计权重<50%属于a

累计权重 >=50%,<=80% b

累计权重 >80% c

问题的核心是累计权重的计算，累计权重是当前行加前面所有行的合计值。

旧方案：子查询

这种方案大家都能想得到，但是效率可想而知……

WITH    tmp

AS ( SELECT   1 AS '条码'

            , 950 '销售金额'

    UNION ALL

    SELECT   2

            , 698

    UNION ALL

    SELECT   3

            , 610

    UNION ALL

    SELECT   4

            , 375

    UNION ALL

    SELECT   5

            , 351

    UNION ALL

    SELECT   6

            , 142

    )

    SELECT  条码, 销售金额

        , (SELECT SUM(t.销售金额) FROM tmp t WHERE t.条码 <= s.条码)

    FROM    tmp s;

尝试1：Lag分析函数

看到这个问题的时候，很自然的想到了Lag这个函数。天真的觉得这个函数能够解决这个问题，只能说明自己对这个函数还不够了解。这个分析函数也是需要配合Over来使用。功能是能够访问结果集上一行的数据，与Lead刚好相反。抱着帮人帮到底的心态，亲自写了下感觉So Easy的SQL。

WITH    tmp

    AS ( SELECT   1 AS '条码', 950 '销售金额' UNION ALL

        SELECT   2 , 698  UNION ALL SELECT   3, 610 UNION ALL SELECT   4 , 375  UNION ALL

        SELECT   5 , 351  UNION ALL SELECT   6 , 142

        )

    SELECT  条码, 销售金额

          , LAG(销售金额,1,0) OVER ( ORDER BY 条码 ) + 销售金额 as 累计金额

    FROM    tmp;

发现结果并不是自己想要的，仅有前两行能够满足需求。这就和LAG这个函数的功能有关系了，学艺不精……

尝试2：With递归

即然是逐行的累计，SQL本身的特性又决定了他没办法在查询的时候实现结果集的相互引用计算。所以是不是可以尝试用循环来做呢？一提循环，肯定很多人想到了游标。是的，我觉得游标能够实现任何的功能，但是效率呢？我这儿说的循环是指With递归。于是做了下尝试，想法是将条码作为递归的条件。主查询查询条码为1的记录，递归查询查询主查询中条码+1的记录，并且累计销售金额。

WITH    tmp

    AS ( SELECT   1 AS '条码', 950 '销售金额' UNION ALL

        SELECT   2 , 698  UNION ALL SELECT   3, 610 UNION ALL SELECT   4 , 375  UNION ALL

        SELECT   5 , 351  UNION ALL SELECT   6 , 142

        )

		,tmp1 as(

			SELECT *,销售金额 AS 累计销售金额 FROM tmp WHERE 条码 = 1

			UNION ALL

			SELECT tmp.*,tmp1.累计销售金额 + tmp.销售金额 FROM tmp

				JOIN tmp1 ON tmp1.条码+1 = tmp.条码

		)

		SELECT * FROM tmp1

貌似是可以实现需求，但是With递归看似是一条SQL，其执行过程却是循环的，效率还是令人担忧。而且默认有循环次数的限制，条码不递增怎么破，等等……

新发现：Sum Over

基于Lag的思路就在想，Lag只能取到上一行的数据，那想累计多行只能是Sum。于是尝试Sum加条件，尝试Pratition By分组，一个偶然的尝试，却意外的收获了结果。

WITH    tmp

    AS ( SELECT   1 AS '条码', 950 '销售金额' UNION ALL

        SELECT   2 , 698  UNION ALL SELECT   3, 610 UNION ALL SELECT   4 , 375  UNION ALL

        SELECT   5 , 351  UNION ALL SELECT   6 , 142

        )

		SELECT  条码, 销售金额

          , SUM(销售金额) OVER ( ORDER BY 条码 ASC ) 累计金额

          , SUM(销售金额) OVER ( ORDER BY 条码 ASC ) * 1.0 / ( SUM(销售金额) OVER ( ) ) AS 累计权重

    FROM    tmp;

不知道大家有没有看出来，最核心的是下面这句

SUM(销售金额) OVER ( ORDER BY 条码 ASC ) 累计金额

这个Sum配合Over的Order by 居然可以实现逐行累计汇总。

PS：With AS

另外昨天那位亲提了这样一个问题：我想请问一下with as 我的意思是说加入我的条码有很多,那么还是要一个个写吗?

如果有不晓得的在此一并解答下：我们通常做测试是不愿意建物理表的，这儿用With AS的目的只是为了构建一个临时表，等同于你实际业务中的业务表。