求日均余额问题

[binxigogo]

帐户表account


accountNO accountMoney accountDate
001 10000 2008-08-01
002 2000 2008-08-20
003 3000 2008-09-01

注：accountNO 是帐户表 accountMoney是开户金额 accountDate：开户日期
余额变动表 account_balance
accountNO Balance AccDate
001 9000 2008-08-03
002 1000 2008-09-03
001 8000 2008-09-04

注：accountNO 是帐户表 Balance：余额 AccDate：变动时间（每个AccDate只可能在同一天有一个）

我想查询某个时间短日均余额 （例如：2008-09-01 至2008-09-30之间的日均余额）
请给出完整的sql语句
这是我的sql语句可以参考不过不知道怎么求出查询时间段之前的最后余额变动信息
数据库是oracle

select ab.balance*lag(acc.time,1,to_date('2008-09-30','yyyy-MM-dd')) over (partition by ab.accountno order by ab.time desc)

from account acc
left join account_balance ab
on acc.accountNo=ab.accountNo

这只是求出了部分值不知道如何求002在 2008-09-03之前的每天的余额的和还有就是001在 2008-09-04之前的每天的余额的和
望高手指教
需要的是查询效率高的sql
毕竟数据量超过千万条

[hebo2005]

你这种要求余额的，没有效率高的，天天做个日结才会效率好点

[BlueskyWide]

如果仅为普通的统计查询分析，且统计的日期段在收益中较为均匀，以下即可：

select (t1.a-t2.b)/31 日均余额 from
(select sum(Balance) a from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-01') t1,
(select sum(Balance) b from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-31') t2;


但以上的统计方法不够精确，原因是统计的日期段中总有开户和转户的，所以必须进行改良：

select (t1.a+t4.d-t2.b-t3.c)/31 日均余额 from
(select sum(Balance) a from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-01') t1,
(select sum(Balance) b from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-31') t2'
(select sum(accountMoney) c from account where to_char(accountDate,'yyyy-mm-dd')>'2008-08-01'
and to_char(accountDate,'yyyy-mm-dd')<'2008-08-31') t3,
(select sum(Balance) d from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-01'
and accountNO in(select tt1.m from
(select accountNO m from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-01'
and Balance is not null) tt1,
(select accountNO n from account_balance where to_char(AccDate,'yyyy-mm-dd')='2008-08-31'
and Balance is null) tt2
where tt1.m=tt2.n) t4;



有开户表，当然若有转户表，t4算得要方便些。

[BlueskyWide]

对不起，上面第九行写错了，应改为：select (t1.a+t3.c-t2.b-t4.d)/31 日均余额 from ...

[oracledbalgtu]

SELECT da,sum(nn_b)/(TO_DATE('2008-9-30', 'yyyy-mm-dd') - CASE
        WHEN accountdate > TO_DATE('2008-9-1', 'yyyy-mm-dd') THEN
          TRUNC(accountdate)
        ELSE
          TO_DATE('2008-9-1', 'yyyy-mm-dd')
      END+1) AVG_MONEY 
  FROM(
        SELECT BA, BALANCE,new_b, ACCDATE, DA, ACCOUNTMONEY, N_MONEY, ACCOUNTDATE, VD,
             CASE WHEN new_b IS NULL THEN n_money ELSE new_b END nn_b
          FROM (
                SELECT B.ACCOUNTNO BA,
                       B.BALANCE,
                       (SELECT  balance  FROM account_balance WHERE accdate =(SELECT MAX(accdate) FROM 
                       account_balance WHERE accdate<=d.vd AND accountno=d.ACCOUNTNO)) new_b,
                       B.ACCDATE,
                       D.ACCOUNTNO DA,
                       D.ACCOUNTMONEY,
                       CASE WHEN d.accountdate>d.vd THEN 0 ELSE d.ACCOUNTMONEY END n_money,
                       D.ACCOUNTDATE,
                       D.VD 
                  FROM ACCOUNT_BALANCE B,
                       (SELECT *
                          FROM ACCOUNT A,
                               (SELECT TO_DATE('2008-9-1', 'yyyy-mm-dd') + ROWNUM - 1 VD
                                  FROM DUAL
                                CONNECT BY ROWNUM <= TO_DATE('2008-9-30', 'yyyy-mm-dd') -
                                           TO_DATE('2008-9-1', 'yyyy-mm-dd') + 1) C
                          WHERE  a.accountdate<=TO_DATE('2008-9-30', 'yyyy-mm-dd')
                                           ) D
                 WHERE B.ACCOUNTNO(+) = D.ACCOUNTNO
                   AND B.ACCDATE(+) = D.VD
                   AND b.accdate(+)<=TO_DATE('2008-9-30', 'yyyy-mm-dd')
                   AND b.accdate(+)>=TO_DATE('2008-9-1', 'yyyy-mm-dd') 
                 )ORDER BY DA, VD
         )
 GROUP BY da,accountdate
 ORDER BY da;
--输出:
/*
DA	AVG_MONEY
001	8100
002	1066.66666666667
003	3000
*/

[Quote=引用楼主 binxigogo 的帖子:]
帐户表account


accountNO accountMoney accountDate
001 10000 2008-08-01
002 2000 2008-08-20
003 3000 2008-09-01

注：accountNO 是帐户表 accountMoney是开户金额 accountDate：开户日期
余额变动表 account_balance
accountNO Balance AccDate
001 9000 2008-08-03
002 …
[/Quote]

[binxigogo]

如果数据量很大，就花的时间太长太长了

[qwx312347236]

像这样balance类型的表一般都有sysdate字段。如果有的话：
select sum(act_bale.balance)/floor(ToDate('20080131','yyyy-mm-dd')-ToDate('20080102','yyyy-mm-dd') '日均余额'
from act_no a,act_bal b
where a.actno=b.actno
group by a.actno


如果你的act_bal表没有sysdate的话就比较麻烦了，可以参考楼上多为高手的

[最爱冰红茶]

楼主找到答案了吗，能否分享下~！急需啊