一个很简单但又很头大的非典型sql问题.......

Question

本帖最后由 trytoremember 于 2011-05-03 01:28:07 编辑

Answer 1

with t as(
     select 1 val from dual union all
     select 3 from dual union all
     select 5 from dual union all
     select 7 from dual union all
     select 9 from dual)
select rownum,
       sum(sum(val))
       over (order by rownum rows between unbounded preceding and current row)
       sum_val
from t
group by rownum
order by rownum;
    ROWNUM    SUM_VAL
---------- ----------
         1          1
         2          4
         3          9
         4         16
         5         25

Answer 2

SQL> with t as(
  2       select 1 val from dual union all
  3       select 3 from dual union all
  4       select 5 from dual union all
  5       select 7 from dual union all
  6       select 9 from dual)
  7  select val,
  8         sum(sum(val))
  9         over (order by rownum rows between unbounded preceding and current row)
 10         sum_val
 11  from t
 12  group by rownum,val
 13  order by rownum;       VAL    SUM_VAL
---------- ----------
         1          1
         3          4
         5          9
         7         16
         9         25

Answer 3

sum(sum(val))
over (order by rownum rows between unbounded preceding and current row)是连在一起的吗？

Answer 4

研究一下oracle分析函数的使用

Answer 5

如果改一下,f(t)=x-f(t-1)呢?
实际这个f(t)可能会包含非常复杂的逻辑,这里我只是给出了一个最简单的形式.
仅用分析函数怕是搞不定的.

Answer 6

搞定搞不定，你试试不就知道了啊！
逻辑复杂，if-then-elsif , case when , for loop...
这些不是可以处理吗？

Answer 7

我没搞定.我看了你的代码,主要是通过分析函数窗口来获取f(t-1)的值的.但分析函数窗口只能使用sum,avg这样的标准聚合函数,这些函数无法嵌入复杂逻辑. 比如当 f(t)=x+f(t-1)时,你的代码中直接使用了一个sum函数,但当 f(t)=x-f(t-1)时呢? sum函数就失效了.

Answer 8

你这个问题很像一个c++里面的递归问题，
关于oracle中的递归，还真的不懂......

Answer 9

WITH cte AS (
SELECT c1,ROWNUM  AS Row_num FROM t1
)
SELECT m.c1,sum(n.c1) FROM cte m,cte n
WHERE n.row_num < =m.row_num
GROUP BY m.c1
ORDER BY m.c1;------res
1 1 1
2 3 4
3 5 9
4 7 16
5 9 25

Answer 10

with t as(
  select 1 val
    from dual
  union all
  select 3
    from dual
  union all
  select 5
    from dual
  union all
  select 7
    from dual
  union all
  select 9 from dual)
      select val,
             decode(mod(rownum, 2), 1, 1, -1) * sum(val * decode(mod(rownum, 2), 1, 1, -1)) over(order by val)
        from t
1 1
3 2
5 3
7 4
9 5

Answer 11

select val,
             decode(mod(rownum, 2), 1, 1, -1) *
             sum(val * decode(mod(rownum, 2), 1, 1, -1)) over(order by val)
        from t

Answer 12

呵呵,你更厉害,直接用上了decode(我承认oracle的各种函数里面我最怕decode).但正如我前面所说,函数内部的逻辑可能是非常复杂的,decode是无法实现分支,循环这样的结构的.这就是为什么需要编写一个单独的function的缘故.

Answer 13

我再补充说明一下,这个问题的关键点在于构造一个函数,这个函数对,1.每一行输入的数据,2.它自身在前1行计算得到函数值,这样两个部分进行混合的逻辑运算,然后返回一个函数值.其最简单的特例是f(t)=x+f(t-1),其一般化的表达式是f(t)=f(x,f(t-1).问题的难点在于,如何在这个function内部,获得上一行计算得出的函数值.
我目前在试图使用oracle data cartridges中的pipeline table function做. 
似乎是有个希望的方向.

Answer 14

网上查了一下,oracle还是支持递归函数的调用的
如果能增加一个id列的话create or replace function 递归(x in int) return integer is
  n INTEGER;
begin
  SELECT num_col INTO n FROM t1 WHERE id=x;
  IF x=1 THEN
    RETURN n;
  ELSE
    RETURN n+递归(x-1);
  END IF;
end 递归;
SQL> SELECT id,num_col from t1;
 
 ID                                 NUM_COL
--- ---------------------------------------
  1                                       0
  2                                       1
  3                                       2
  4                                       3
  5                                       4
  6                                       5
  7                                       6
  8                                       7
  9                                       8
 10                                       9
 11                                      10
 
11 rows selected
 
SQL> SELECT id,num_col,递归(id) from t1;
 
 ID                                 NUM_COL     递归(ID)
--- --------------------------------------- ----------
  1                                       0          0
  2                                       1          1
  3                                       2          3
  4                                       3          6
  5                                       4         10
  6                                       5         15
  7                                       6         21
  8                                       7         28
  9                                       8         36
 10                                       9         45
 11                                      10         55
 
11 rows selected

Answer 15

不想增加字段的话应该可以create or replace function 递归(x in int) return integer is
  n INTEGER;
begin
  SELECT num_col INTO n FROM 
         (SELECT num_col,rownum rn from t1) WHERE rn=x;
  IF x=1 THEN
    RETURN n;
  ELSE
    RETURN n+递归(x-1);
  END IF;
end 递归;

Answer 16

因为,需要在sql语句中调用这种类型的函数,例如select f(c1) from t1 

Answer 17

当然,这种函数可以算是一种递归函数,但递归这种处理方式却不适合这种函数.
因为递归是嵌套调用的,假设这个表中有100万行记录,那就要嵌套调用100万次.
pl/sql的堆栈能支持100万次嵌套吗?如果是1000万记录呢,1亿记录呢.....终归不是个办法.

Answer 18

呵呵,pipeline table function,搞定!

Answer 19

把你的代码贴出来，咋也学习学习!
create function
  gen_numbers(n in number default null)
  return array
  PIPELINED
  as
  begin
     for i in 1 .. nvl(n,999999999)
     loop
         pipe row(i);
     end loop;
     return;
  end;
/Function created.select * from TABLE(gen_numbers(3)); COLUMN_VALUE
 ------------
           1
           2
           3
select *
  from (
  select *
    from (select * from table(gen_numbers(49)))
  order by dbms_random.random
  )
where rownum <= 6
/ COLUMN_VALUE
 ------------
          47
          42
          40
          15
          48
          23

Answer 20

代码因为是在一个大系统里的,较为冗长,贴出来看起来可能会很麻烦,我只讲一下简单原理吧.pipeline函数,它的作用是产生一个嵌套表,这个嵌套表可以象一个普通表一样被sql语句使用.
那么piepline函数如何产生嵌套表的数据呢,2种方法,1是根据某种逻辑直接产生(如你上面的例子),2是调用者传一个ref cursor给它,它对这个ref cursor里的数据做出某种加工,在放到嵌套表里.我做的工作,就是创建一个object,在object的成员函数中把f(t)的逻辑封装进去.同时,通过这个object的attribute来记录下我需要的f(t-1),f(t-2)...值最后就是调用工作,通过cursor语句打开一个ref cursor,把数据传给pipeline函数,pipeline函数调用我的定义object的成员函数,计算出结果,返回给pipeline函数,pipeline函数再把结果返回给调用它的sql语句.sql语句拿到的数据,就跟普通表一样,没啥区别.假设pipeline函数名为pf,那么最终的调用形式就是这样的
SELECT * FROM TABLE(pf(CURSOR(SELECT * FROM T1)));
这里,CURSOR子句是oracle自带的,它的作用是返回SELECT * FROM T1的ref cursor.
pf返回一个嵌套表,但无法直接被sql语句使用,需要外面再加一个TABLE(),这也是oracle自带的.先说这么多,关于pipeline函数的详细内容,大家可参考Oracle® Database Data Cartridge Developer's Guide第13章. 在oracle网站上有.

Answer 21

翻译了一篇关于lz所说的pipelined table funcion，供大家参考参考：

Oracle Pipelined Table Functions简介 

[个人能力有限]