[SQL]在分析函数的“函数”中控制要处理的行

[SQL]在分析函数的“函数”中控制要处理的行
2011-05-08

介绍：
在一般的分析函数中，比如sum(col_name) over(...),函数处理的是over子句划分出来的窗口中的所有行。
其实，可以在函数中使用CASE来控制函数的计算包括或不包括哪些行。

===================================================================
用以说明问题的例子引用如下：

date             stock           sale
-------            ----------        ------------
11-5-5           30                  5
11-5-4           30                   6
11-5-3           30                  10
11-5-2           30                   8
11-5-1           30                  20

只有5号的stock是对的
销售都是对的
要倒推stock 前一天stock=当天stock+当天sale
4号是35，3号41，2号51    。。。。

有什么好的写法呢

=======================================================
该问题的解题思路是，按date降序排序，对于每一行，其之前的所有行的sale之和再加上第一行的stock，就是各行的正确的stock。
因为凑巧，原表中每行的stock都相等（都等于第一行的stock），所以下面的SQL也能得到正确结果：

10:40:47 sys$orcl@localhost SQL> with t as (
10:40:49   2  select to_date('11-05-05','rr-mm-dd') dat,30 stock,5 sale from dual union all
10:40:49   3  select to_date('11-05-04','rr-mm-dd') ,30,6 from dual union all
10:40:49   4  select to_date('11-05-03','rr-mm-dd') ,30,10 from dual union all
10:40:49   5  select to_date('11-05-02','rr-mm-dd') ,30,8 from dual union all
10:40:49   6  select to_date('11-05-01','rr-mm-dd') ,30,20 from dual
10:40:49   7  )
10:40:49   8  select dat,sum(sale) over(order by dat desc) + stock-sale new_stock,sale
10:40:49   9  from t;

DAT                  NEW_STOCK       SALE
------------------- ---------- ----------
2011-05-05 00:00:00         30          5
2011-05-04 00:00:00         35          6
2011-05-03 00:00:00         41         10
2011-05-02 00:00:00         51          8
2011-05-01 00:00:00         59         20

10:40:50 sys$orcl@localhost SQL>
#^ 该方法是求得当前窗口中所有行的sale之和，减去当前行的sale,再加上“当前”行的stock.
#^ 因为原题中说明只有第一行的stock是正确的，因此实际上这种方法利用了所有行的stock都相等这个条件，离开了这个条件，
   这种解法就站不住脚了。
   
=======================================================
下面是大师的解法：

10:44:28 sys$orcl@localhost SQL> WITH DATA AS (
10:44:31   2            SELECT DATE '2011-5-5' AS THE_DATE,  30 AS STOCK,  5   AS SALE FROM DUAL
10:44:31   3  UNION ALL SELECT DATE '2011-5-4' AS THE_DATE,  30 AS STOCK,   6  AS SALE FROM DUAL
10:44:31   4  UNION ALL SELECT DATE '2011-5-3' AS THE_DATE,  30 AS STOCK,  10  AS SALE FROM DUAL
10:44:31   5  UNION ALL SELECT DATE '2011-5-2' AS THE_DATE,  30 AS STOCK,   8  AS SALE FROM DUAL
10:44:31   6  UNION ALL SELECT DATE '2011-5-1' AS THE_DATE,  30 AS STOCK,  20  AS SALE FROM DUAL
10:44:31   7  )
10:44:31   8  SELECT THE_DATE,SUM(CASE WHEN RN=1 THEN STOCK ELSE 0 END+SALE) OVER(ORDER BY THE_DATE DESC)-SALE AS STOCK, SALE
10:44:31   9    FROM (SELECT DATA.*
10:44:31  10                ,ROW_NUMBER() OVER(ORDER BY THE_DATE DESC) rn
10:44:31  11            FROM DATA
10:44:31  12         );

THE_DATE                 STOCK       SALE
------------------- ---------- ----------
2011-05-05 00:00:00         30          5
2011-05-04 00:00:00         35          6
2011-05-03 00:00:00         41         10
2011-05-02 00:00:00         51          8
2011-05-01 00:00:00         59         20

10:44:31 sys$orcl@localhost SQL>
#^ 这种解法中，使用两遍分析函数，第一遍求得行号，第二遍根据行号，使用CASE语句，对于窗口中的所有行，只有第一行才取它
   的stock，其余行都取0，再与sale相加，在这上面求sum=>这才是每一行真正的stock.
  
这种解法不依赖于其他行的stock值，因此无论其他行的stock为何，求得的结果都是正确的：

10:53:59 sys$orcl@localhost SQL> WITH DATA AS (
10:54:02   2            SELECT DATE '2011-5-5' AS THE_DATE,  30 AS STOCK,  5   AS SALE FROM DUAL
10:54:02   3  UNION ALL SELECT DATE '2011-5-4' AS THE_DATE,  0 AS STOCK,   6  AS SALE FROM DUAL
10:54:02   4  UNION ALL SELECT DATE '2011-5-3' AS THE_DATE,  100 AS STOCK,  10  AS SALE FROM DUAL
10:54:02   5  UNION ALL SELECT DATE '2011-5-2' AS THE_DATE,  -5 AS STOCK,   8  AS SALE FROM DUAL
10:54:02   6  UNION ALL SELECT DATE '2011-5-1' AS THE_DATE,  99 AS STOCK,  20  AS SALE FROM DUAL
10:54:02   7  )
10:54:02   8  SELECT THE_DATE,SUM(CASE WHEN RN=1 THEN STOCK ELSE 0 END+SALE) OVER(ORDER BY THE_DATE DESC)-SALE AS STOCK, SALE
10:54:02   9    FROM (SELECT DATA.*
10:54:02  10                ,ROW_NUMBER() OVER(ORDER BY THE_DATE DESC) rn
10:54:02  11            FROM DATA
10:54:02  12         );

THE_DATE                 STOCK       SALE
------------------- ---------- ----------
2011-05-05 00:00:00         30          5
2011-05-04 00:00:00         35          6
2011-05-03 00:00:00         41         10
2011-05-02 00:00:00         51          8
2011-05-01 00:00:00         59         20

10:54:02 sys$orcl@localhost SQL>
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24131851/viewspace-694758/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24131851/viewspace-694758/