查看: 7019|回复: 36

[SQL] 【讨论】递归的累计值如何用SQL写(只能用Model来实现)

[复制链接]
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-1-6 18:05 | 显示全部楼层 |阅读模式



题目在“http://www.itpub.net/thread-1901493-1-1.html”, 由于此题用分析函数是实现不了的,因此如何用Model来实现。

Create table t1
(
a date,
c number,
d number
);

Insert into t1  Values(to_date('2013-12-01', 'yyyy-mm-dd'),0, 1896446);
Insert into t1  Values(to_date('2014-01-01', 'yyyy-mm-dd'),254779.6, null);
Insert into t1 Values(to_date('2014-02-01', 'yyyy-mm-dd'),600, null);
Insert into t1 Values(to_date('2014-03-01', 'yyyy-mm-dd'),17490, null);
Insert into t1 Values(to_date('2014-04-01', 'yyyy-mm-dd'),165062.8, null);
Insert into t1 Values(to_date('2014-05-01', 'yyyy-mm-dd'),256571.2, null);


题目(2) 由于前面的问题, 这里的a列正好是按月递增的,如果这里的a列没有任何递增规则,这是从小到大,那有如何用Model来实现, 新的脚本如下。

Create table t2
(
a date,
c number,
d number
);

Insert into t1  Values(to_date('2013-12-11', 'yyyy-mm-dd'),0, 1896446);
Insert into t1  Values(to_date('2014-02-09', 'yyyy-mm-dd'),254779.6, null);
Insert into t1 Values(to_date('2014-02-21', 'yyyy-mm-dd'),600, null);
Insert into t1 Values(to_date('2014-04-19', 'yyyy-mm-dd'),17490, null);
Insert into t1 Values(to_date('2014-06-01', 'yyyy-mm-dd'),165062.8, null);
Insert into t1 Values(to_date('2014-09-15', 'yyyy-mm-dd'),256571.2, null);

得到结果的规则, 原帖的LZ已经说明了。





论坛徽章:
737
季节之章:春
日期:2015-07-31 17:16:29ITPUB季度 技术新星
日期:2014-07-17 14:37:00季节之章:秋
日期:2015-07-31 17:16:14季节之章:夏
日期:2015-07-31 17:16:29股神
日期:2014-10-15 09:23:31衰神
日期:2014-10-20 22:47:12季节之章:冬
日期:2015-07-31 17:16:14红钻
日期:2014-12-16 17:51:41洛杉矶湖人
日期:2016-09-23 08:18:15布鲁克林篮网
日期:2016-09-23 08:17:18
发表于 2015-1-6 18:31 | 显示全部楼层
坐等newkid,peter等高手出手

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2015-1-6 19:28 来自手机 | 显示全部楼层
看原题是sql server

使用道具 举报

回复
论坛徽章:
469
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2015-1-6 20:50 | 显示全部楼层
--依据公式:
---D2=C2-C1
---D3=C3-D2=C3-C2+C1
---D4=C4-D3=C4-C3+C2-C1
---D5=C5-D4=C5-C4+C3-C2+C1
---D6=C6-D5=C6-C5+C4-C3+C2-C1




SQL> Create table t2
  2  (
  3  a date,
  4  c number,
  5  d number
  6  );

Table created


SQL>
SQL> Insert into t2  Values(to_date('2013-12-11', 'yyyy-mm-dd'),0, 1896446);

1 row inserted
SQL> Insert into t2  Values(to_date('2014-02-09', 'yyyy-mm-dd'),254779.6, null);

1 row inserted
SQL> Insert into t2 Values(to_date('2014-02-21', 'yyyy-mm-dd'),600, null);

1 row inserted
SQL> Insert into t2 Values(to_date('2014-04-19', 'yyyy-mm-dd'),17490, null);

1 row inserted
SQL> Insert into t2 Values(to_date('2014-06-01', 'yyyy-mm-dd'),165062.8, null);

1 row inserted
SQL> Insert into t2 Values(to_date('2014-09-15', 'yyyy-mm-dd'),256571.2, null);

1 row inserted

SQL> commit;

Commit complete


SQL> select a,c,d, power(-1,rownum)*sum(decode(rownum,1,d,c)*power(-1,rownum)) over(order by a) as e from t2
  2  /

A                    C          D          E
----------- ---------- ---------- ----------
2013/12/11           0    1896446    1896446
2014/2/9      254779.6            -1641666.4
2014/2/21          600             1642266.4
2014/4/19        17490            -1624776.4
2014/6/1      165062.8             1789839.2
2014/9/15     256571.2              -1533268

6 rows selected

SQL>

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2015-1-6 22:47 | 显示全部楼层
加菲猫的方法很好,但是如果公式复杂一点,比如乘以一个系数,就还得用递归的方法。
既然a不连续就创造一个连续的列:

SELECT * FROM
(SELECT t.*,ROWNUM rn FROM (SELECT * FROM T1 ORDER BY A) t)
MODEL
  DIMENSION BY (rn)
  MEASURES (c,d)
  RULES (
   d[rn>1] order by rn=c[CV()]-d[CV()-1]
   )
;

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2015-1-7 08:49 | 显示全部楼层
D1怎么来的?

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2015-1-7 09:35 | 显示全部楼层
只要是顺序排列的就可以用row_number得到连续数字

WITH T AS
(SELECT 1 B, 0 C, 1896446 D FROM DUAL
  UNION ALL
  SELECT 2, 254779.6, NULL FROM DUAL
  UNION ALL
  SELECT 3, 600, NULL FROM DUAL
  UNION ALL
  SELECT 4, 17490, NULL FROM DUAL
  UNION ALL
  SELECT 5, 165062.8, NULL FROM DUAL
  UNION ALL
  SELECT 6, 256571.2, NULL FROM DUAL),
TT AS
(SELECT B, DECODE(B, 1, D, C) C FROM T)
SELECT A.B, DECODE(A.B, 1, 0, A.C) C, NVL(SUM(B.C) - SUM(C.C), A.C) AS D
  FROM TT A
  LEFT JOIN TT B
    ON A.B >= B.B
   AND MOD(A.B + B.B, 2) = 0
  LEFT JOIN TT C
    ON A.B > C.B
   AND MOD(A.B + C.B, 2) = 1
   AND B.B = C.B + 1
GROUP BY A.B, A.C
ORDER BY A.B

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
 楼主| 发表于 2015-1-7 12:30 | 显示全部楼层
本帖最后由 bell6248 于 2015-1-7 12:32 编辑
newkid 发表于 2015-1-6 22:47
加菲猫的方法很好,但是如果公式复杂一点,比如乘以一个系数,就还得用递归的方法。
既然a不连续就创造一个 ...



首先非常感谢楼上大家提供的答案和回复, 尤其谢谢newkid的答复,其实你的语句还可以简化, 这里的“ORDER BY rn(对应于rules SEQUENTIAL ORDER模式)”可以不写,
由于rules左侧表达式是d[rn>1], 的rn已经是连续的,因此,可以不写, 右侧的表达式d[cv(rn) - 1]不会发生计算错误。

测试如下:

SQL> select * from t1;

A                    C          D
----------- ---------- ----------
2013/12/11           0    1896446
2014/2/9      254779.6
2014/2/21          600
2014/4/19        17490
2014/6/1      165062.8
2014/9/15     256571.2

6 rows selected

SQL>
SQL> select a,
  2         c,
  3         d
  4    from (select a, c, d, rownum rn from t1)
  5    model
  6    dimension by(rn)
  7    measures(a, c, d)
  8    rules
  9    (
10      d[rn>1]  = c[cv()] - d[cv(rn) - 1]
11    )
12  /

A                    C          D
----------- ---------- ----------
2013/12/11           0    1896446
2014/2/9      254779.6 -1641666.4
2014/2/21          600  1642266.4
2014/4/19        17490 -1624776.4
2014/6/1      165062.8  1789839.2
2014/9/15     256571.2   -1533268

6 rows selected

SQL>

我还有如下的问题, 希望得到大家的帮助:

(1)问题(1): 为什么如下的语句不能得到正确的结果(昨天我就是想到如下的语句, 可惜得到的结果不是对的,一直没有找出解决的方法)?
               请newkid或者其他精通Model的pubers给出详细的解释.

SQL> select a,
  2         c,
  3         d
  4    from (select a, c, d, rownum rn from t1)
  5    model
  6    dimension by(rn)
  7    measures(a, c, d)
  8    rules SEQUENTIAL ORDER
  9    (
10      d[ANY] ORDER BY rn = c[cv()] - d[cv(rn) - 1]
11    )
12  /

A                    C          D
----------- ---------- ----------
2013/12/11           0
2014/2/9      254779.6
2014/2/21          600
2014/4/19        17490
2014/6/1      165062.8
2014/9/15     256571.2

6 rows selected

SQL>

(2)问题2: Model子句功能是从10g才开始引入的,虽然功能非常强大, 许多功能是在实现Excel提供的功能, 例如数据分析,数据预测的OLAP的特殊功能, 但是就目前看,
                虽然已经是12c时代了, 我也相信11g也一定进一步完善和进一步扩展Model子句功能, 但是目前问题是, 这个Model子句功能是Oracle最先实现和提供的高级SQL特新,
                但是它不想分析函数那样已经被标准化,也就是其他的l主流的数据库厂商(DB2, SQL Server etc.)已经早就接受,并且目前的DB2/SQL Server的对于分析函数功能的提供也越来越完善,
                基本是向Oracle看齐的,但是,Model子句功能从10g开始到想在, 也有进10年的时间了,除了Oracle数据库提供了,其他的数据库好像没有提供Model子句功能, 也没有在数据库市场被标准化,
                不知道是这个Model子句功能用到的机会少(也就是不被广大的数据库从业人员所接受),还是太复杂了, 这个 Model子句功能我也是这个星期才开始学的,老实说,之前的工作确实没有用到,
                我感觉 Model子句功能其实就是把select子句给简化了,比如: 大家喜欢的分析函数, 其实在实现的时候大多都在select子句, 而 Model子句功能, 完全是在rules子句实现, 因此select子句非常简单,
                功能转移了, 不知各位pubers有什么看法, 另外如果各位已经有了非常完整而且通俗易懂的详细实例和说明的文档, 分享一下吧(另外说明一下,网上的关于 Model子句功能网页我已经看过了,不需要提供了,嘿嘿!), 这个不知dingjun有没有啊, 你的PDF文档写的是好啊(格式排版好,居的例子通俗易懂,而且还有详细的说明)!  





使用道具 举报

回复
论坛徽章:
469
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2015-1-7 12:48 | 显示全部楼层
bell6248 发表于 2015-1-7 12:30
首先非常感谢楼上大家提供的答案和回复, 尤其谢谢newkid的答复,其实你的语句还可以简化, 这里的“ ...

http://www.itpub.net/forum.php?mod=viewthread&tid=1835838

这本书的第九章讲的是 MODEL 用法, 过去看过,现在长期不用,又忘得一干二净了。。。

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
 楼主| 发表于 2015-1-7 13:03 来自手机 | 显示全部楼层
solomon_007 发表于 2015-1-7 12:48
http://www.itpub.net/forum.php?mod=viewthread&tid=1835838

这本书的第九章讲的是 MODEL 用法, 过去 ...

你是算法高手啊,数学一定非常好!  你前面提供的语句也是非常非常厉害的!  佩服佩服! 我也觉得Model好像在实践用的不多, 太复杂了, 很多概念很难理解, 不知各位如何看, 也不知这个功能未来的前景如何

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表