ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [SQL]在分析函数的“函数”中控制要处理的行

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

原创 Linux操作系统 作者:lk-2010 时间:2011-05-08 11:02:57 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-06-22

  • 博文量
    19
  • 访问量
    68120