有一个贷款明细表,记录的贷款明细情况:================================
DT |CUSTID |BALANCE
================================
01/01/07 |1001 |100.00
01/01/07 |1002 |100.00
01/02/07 |1002 |100.00
01/03/07 |1003 |100.00
01/04/07 |1003 |100.00
01/05/07 |1004 |100.00请问一下如何统计出每天新增的客户数量,新增的贷款数量,和累计的贷款余额,就像下面这样:
===========================================
DT |NEW_CUST |NEW_BALANCE |TOTAL
===========================================
01/01/07 |2 |200.00 |200.00
01/02/07 |0 |.00 |200.00
01/03/07 |1 |100.00 |300.00
01/04/07 |0 |.00 |300.00
01/05/07 |1 |100.00 |400.00尽量越简单越好,谢谢!
DT |CUSTID |BALANCE
================================
01/01/07 |1001 |100.00
01/01/07 |1002 |100.00
01/02/07 |1002 |100.00
01/03/07 |1003 |100.00
01/04/07 |1003 |100.00
01/05/07 |1004 |100.00请问一下如何统计出每天新增的客户数量,新增的贷款数量,和累计的贷款余额,就像下面这样:
===========================================
DT |NEW_CUST |NEW_BALANCE |TOTAL
===========================================
01/01/07 |2 |200.00 |200.00
01/02/07 |0 |.00 |200.00
01/03/07 |1 |100.00 |300.00
01/04/07 |0 |.00 |300.00
01/05/07 |1 |100.00 |400.00尽量越简单越好,谢谢!
01/02/07 |0 |.00 |200.00
这一笔,TOTAL应该是300,为什么还是200?
说下大致方法吧
建一个临时表 字段DT ,NEW_CUST ,NEW_BALANCE ,TOTAL
做个日期的游标
select DT into 变量 from table
之后循环
作一个flag 比如变量1:=0;
if 变量1:=0; THEN
select COUNT(CUSTID),,sum(NEW_BALANCE) into 变量2,变量3 from table
where DT=变量;
insert into 临时表 (DT ,NEW_CUST ,NEW_BALANCE ,TOTAL)
value(变量,变量1,0.00,变量2);
变量1:=1;
if 变量1:=1; THEN
select COUNT(CUSTID),sum(NEW_BALANCE) into 变量2,变量3 from table
where DT=变量;
select COUNT(CUSTID),sum(NEW_BALANCE)into 变量4,变量5 from table
where CUSTID in (select CUSTID from table where DT<变量)
and where DT=变量;
insert into 临时表 (DT ,NEW_CUST ,NEW_BALANCE ,TOTAL)
value(变量,变量2-变量4,变量3-变量5,变量2);
结束循环
最后select * from 临时表
大体思路这样的 可能里面写错了
希望能带给你灵感还有目前冲裤衩中 希望看到给点分
DT |NEW_CUST |NEW_BALANCE |TOTAL
===========================================
01/01/07 |2 |200.00 |200.00
01/02/07 |0 |.00 |300.00
01/03/07 |1 |100.00 |400.00
01/04/07 |0 |.00 |500.00
01/05/07 |1 |100.00 |600.00
以后就执行
if 变量1=1 THEN 的操作
客户编号 (自动增长)
创建日期
其他信息字段明细表就是上面的表
每天新增的客户数可从客户表查询
每天新增的的客户的存款总数可从客户表和存款明细表连接(JOIN)查询
每天累计存款数可从存款明细表计算(sum,goup by)
然后将以上信息联合(UNION)……具体的,有时间我试试看……走了~
insert into t1 values(date '2007-01-01', '1001', 100);
insert into t1 values(date '2007-01-01', '1002', 100);
insert into t1 values(date '2007-01-02', '1002', 100);
insert into t1 values(date '2007-01-03', '1003', 100);
insert into t1 values(date '2007-01-04', '1003', 100);
insert into t1 values(date '2007-01-05', '1004', 100);
end;
,new_cust
,new_balance
,sum(new_balance) over(order by dt asc)
from
(
select distinct
dt
,(
select count(1)
from t1 x
where x.dt = t1.dt
and x.custid not in
(
select custid
from t1 y
where y.dt < t1.dt
)
) new_cust
,(
select sum(balance)
from t1 x
where x.dt = t1.dt
and x.custid not in
(
select custid
from t1 y
where y.dt < t1.dt
)
) new_balance
from t1
) ha_ha