一个想n多天没有解决的问题

Question

帐户表（accountentity）  accountName代表：开户人  starttime：开户日期
id  accountName accountstate accounttype starttime endtime  account
1    test1        可用         活期   2008-3-1          2000
2    test2        可用         活期      2007-8-1          30000帐户详情表(accoundetailentity)
id  time        accountno  money  detailtype
1   2008-7-23     1         500     取  
2   2008-7-31     1         300     取
3   2008-8-1      2         100     取
 
如何算出从2008-05-01 到 2008-08-01 的平均余额
也就是每天的余额相加除以2008-08-01到2008-05-01的日期差

Answer 1

SELECT A.ACCOUNTNAME,
       A.STARTTIME,
       (C.M + A.ACCOUNT) / (TO_DATE('2008-8-1', 'yyyy-mm-dd') - CASE
         WHEN A.STARTTIME > TO_DATE('2008-5-1', 'yyyy-mm-dd') THEN
          TRUNC(A.STARTTIME)
         ELSE
          TO_DATE('2008-5-1', 'yyyy-mm-dd')
       END) AVG_MONEY
  FROM ACCOUNTENTITY A,
       (SELECT ACCOUNTNO, SUM(DECODE(DETAILTYPE, '取', - (MONEY), MONEY)) M
          FROM ACCOUNDETAILENTITY B
         WHERE B.TIME BETWEEN TO_DATE('2008-5-1', 'yyyy-mm-dd') AND
               TO_DATE('2008-8-1', 'yyyy-mm-dd')
         GROUP BY ACCOUNTNO) C
 WHERE A.ID = C.ACCOUNTNO
   AND A.STARTTIME < TO_DATE('2008-8-1', 'yyyy-mm-dd');

Answer 2

这个要写存储过程解决了
大体思路如下：
1、游标获取每个帐户的信息，利用游标循环处理每个帐户需求；
2、针对每一个帐户，利用循环获取每一次帐户变化情况就坚持的天数，相加求平均值；
3、应该会用到复杂的子查询，注意信息的匹配。也可以用分析函数，但还是要写存储过程，单个SQL语句貌似不能解决问题。

Answer 3

Declare
v_Num    Number; ---时间段总计天数
v_Date   Varchar2(20);--计数每一天日期值
v_Daynum Number(18, 2);--每天余额小计
v_Total  Number(18, 2):=0;--时间段内余额总计
Begin
Select To_Date('2008-08-01', 'YYYY-MM-DD') -
 To_Date('2008-05-01', 'YYYY-MM-DD')
Into v_Num
From Dual
 For i In 0..v_Num 
     Loop 
     v_Date := To_Char(To_Date('2008-05-01', 'YYYY-MM-DD') + i, 'YYYY-MM-DD');
Select Sum(Account)
Into v_Daynum
From (
--当天新开户的
Select Id, Account
From Accountentity
 Where Starttime = v_Date
Union
--当前s日期所有用户帐户余额
Select Id, Account
From (Select Id, Sum(Account) As Account
From (Select Id, Account
From Accountentity
 Where Starttime <= v_Date
Union All
Select Accountno,
 Decode(Detailtype, '取', -money, Money) As Money
From Accoundetailentity
 Where Time <= v_Date)
 Group By Id));
          v_Total := v_Total+v_Daynum
End Loop 
dbms_output.put_line(v_Total/v_Num);
End;

Answer 4

对不起有点问题
Declare
v_Num    Number; ---时间段总计天数
v_Date   Varchar2(20);--计数每一天日期值
v_Daynum Number(18, 2);--每天余额小计
v_Total  Number(18, 2):=0;--时间段内余额总计
Begin
Select To_Date('2008-08-01', 'YYYY-MM-DD') -
 To_Date('2008-05-01', 'YYYY-MM-DD')
Into v_Num
From Dual
 For i In 0..v_Num 
     Loop 
     v_Date := To_Char(To_Date('2008-05-01', 'YYYY-MM-DD') + i, 'YYYY-MM-DD');
Select Sum(Account)
Into v_Daynum
From (
Select Id, Account
From (Select Id, Sum(Account) As Account
From (Select Id, Account
From Accountentity
 Where Starttime <= v_Date
Union All
Select Accountno,
 Decode(Detailtype, '取', -money, Money) As Money
From Accoundetailentity
 Where Time <= v_Date)
 Group By Id));
          v_Total := v_Total+v_Daynum
End Loop 
dbms_output.put_line(v_Total/v_Num);
End;

Answer 5

兄弟，能给个sql的吗？不要存储过程的

Answer 6

你可以把这个写成函数 然后通过 select getpercent('开始时间','结束时间') from dual得到你想要的值  应该比直接把语句嵌入到程序中更易于维护 

Answer 7

select a1.id,
       a1.accountname,
       a1.accountstate,
       a1.accounttype,
       a1.starttime,
       a1.endtime,
       (a1.account +
       sum(decode(a2.detailtype, '取', -a2.money, '存', a2.money, 0))) /
       (case
         when a1.starttime <= to_date('2008-05-01', 'yyyy-mm-dd') and
              nvl(a1.endtime, trunc(sysdate) + 1) >=
              to_date('2008-08-01', 'yyyy-mm-dd') then
          to_date('2008-08-01', 'yyyy-mm-dd') -
          to_date('2008-05-01', 'yyyy-mm-dd') + 1
         when (a1.starttime between to_date('2008-05-01', 'yyyy-mm-dd') and
              to_date('2008-08-01', 'yyyy-mm-dd')) and
              nvl(a1.endtime, trunc(sysdate) + 1) >=
              to_date('2008-08-01', 'yyyy-mm-dd') then
          to_date('2008-08-01', 'yyyy-mm-dd') - a1.starttime + 1
         when (a1.starttime between to_date('2008-05-01', 'yyyy-mm-dd') and
              to_date('2008-08-01', 'yyyy-mm-dd')) and
              (nvl(a1.endtime, trunc(sysdate) + 1) between
              to_date('2008-05-01', 'yyyy-mm-dd') and
              to_date('2008-08-01', 'yyyy-mm-dd')) then
          a1.endtime - a1.starttime + 1
         when a1.starttime > to_date('2008-08-01', 'yyyy-mm-dd') then
          0
         else
          0
       end) avg_amount
  from a1, a2
 where a1.id = a2.accountno(+)
   and a1.starttime <= to_date('2008-08-01', 'yyyy-mm-dd')
   and nvl(a2.time, to_date('2008-06-01', 'yyyy-mm-dd')) between
       to_date('2008-05-01', 'yyyy-mm-dd') and
       to_date('2008-08-01', 'yyyy-mm-dd')
 group by a1.id,
          a1.accountname,
          a1.accountstate,
          a1.accounttype,
          a1.starttime,
          a1.endtime,
          a1.account

Answer 8

上面的写错了,修改下.
太复杂了!!SELECT z.accountno,z.accountname,z.STARTTIME,--COUNT(*),
       SUM(fcm)/(TO_DATE('2008-8-1', 'yyyy-mm-dd') - CASE
         WHEN z.STARTTIME > TO_DATE('2008-5-1', 'yyyy-mm-dd') THEN
          TRUNC(z.STARTTIME)
         ELSE
          TO_DATE('2008-5-1', 'yyyy-mm-dd')
       END+1) AVG_MONEY FROM  (
                              WITH vv AS (SELECT TO_DATE('2008-5-1', 'yyyy-mm-dd') + ROWNUM - 1 VD
                                            FROM DUAL
                                          CONNECT BY ROWNUM <= TO_DATE('2008-8-1', 'yyyy-mm-dd') -
                                                     TO_DATE('2008-5-1', 'yyyy-mm-dd') + 1),
                                   x AS (SELECT D.ACCOUNTNO, D.VD, D.RD, NVL(E.M, 0) M
                                            FROM (SELECT B.ACCOUNTNO,
                                                         B.TIME,
                                                         DECODE(DETAILTYPE, '取', - (NVL(MONEY, 0)), NVL(MONEY, 0)) M
                                                    FROM ACCOUNDETAILENTITY B
                                                   WHERE B.TIME BETWEEN TO_DATE('2008-5-1', 'yyyy-mm-dd') AND
                                                         TO_DATE('2008-8-1', 'yyyy-mm-dd')) E,
                                                 (SELECT VD, ACCOUNTNO, ROWNUM RD
                                                    FROM VV,
                                                         (SELECT DISTINCT ACCOUNTNO
                                                            FROM ACCOUNDETAILENTITY
                                                           WHERE TIME BETWEEN TO_DATE('2008-5-1', 'yyyy-mm-dd') AND
                                                                 TO_DATE('2008-8-1', 'yyyy-mm-dd'))) D
                                           WHERE D.VD = E.TIME(+)
                                             AND D.ACCOUNTNO = E.ACCOUNTNO(+)
                                           ORDER BY D.VD, D.ACCOUNTNO),
                                   y AS (SELECT ID, ACCOUNTNAME, STARTTIME, ACCOUNT, VD
                                            FROM ACCOUNTENTITY A, VV AD
                                           ORDER BY ID, VD)
                              SELECT X.ACCOUNTNO,
                                     X.VD,
                                     X.M,
                                     Y.ACCOUNT,
                                     FIRST_VALUE(Y.ACCOUNT) OVER(PARTITION BY X.ACCOUNTNO ORDER BY X.VD) 
                                     + SUM(M) OVER(PARTITION BY X.ACCOUNTNO ORDER BY X.VD) FCM,
                                     Y.ACCOUNTNAME,
                                     Y.STARTTIME
                                FROM X, Y
                               WHERE X.VD = Y.VD
                                 AND X.ACCOUNTNO = Y.ID) z
   WHERE z.STARTTIME < TO_DATE('2008-8-1', 'yyyy-mm-dd')
   GROUP BY z.accountno,z.accountname,z.STARTTIME
   ORDER BY 1,2
   ;结果如下:
ACCOUNTNO ACCOUNTNAME STARTTIME AVG_MONEY
1            test1 2008-03-01 1939.78494623656
2            test2 2007-08-01 2998.9247311828