帐户表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
毕竟数据量超过千万条
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
毕竟数据量超过千万条
解决方案 »
- 存储过程
- 存储过程变量初始化的问题?
- oracle10G + websphere双机环境配置问题
- 请大家解决一个oracle自定义生成一个序号
- 一个SQL语句优化问题,,急急急
- winXP上新安装的数据库在Enterprise Manager中登陆时报错“没有监听”怎么解决?
- 一个比较有挑战性的问题:怎样提高数据插入或更新的速度?
- 请问各位达人如何在Pl/sql中创建View??Create view不行啊!
- 有没有可能将ACCECC库中某张表的记录插到ORACLE中已有的表中去呢?
- oracle9.2 如何创建回滚锻
- Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1)安装文档 3
- oracle帐户被锁定,导致SQL*plus无法连接,怎么办
(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算得要方便些。
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
*/
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的话就比较麻烦了,可以参考楼上多为高手的