表:AAA
字段两个times和fh,一个date型一个number
times fh
2005-1-25 0:00:18 88.64
2005-1-25 0:01:10 88.64
2005-1-25 0:02:02 88.02
2005-1-25 0:02:55 88.02
2005-1-25 0:03:47 88.64
2005-1-25 0:04:40 90.51
2005-1-25 0:05:33 92.06
2005-1-25 0:06:24 92.99
2005-1-25 0:07:17 91.75
2005-1-25 0:08:09 91.13
2005-1-25 0:09:02 89.26
2005-1-25 0:09:54 90.20
........
要求计算电量啊,电量=负荷×时间(千瓦时)
字段两个times和fh,一个date型一个number
times fh
2005-1-25 0:00:18 88.64
2005-1-25 0:01:10 88.64
2005-1-25 0:02:02 88.02
2005-1-25 0:02:55 88.02
2005-1-25 0:03:47 88.64
2005-1-25 0:04:40 90.51
2005-1-25 0:05:33 92.06
2005-1-25 0:06:24 92.99
2005-1-25 0:07:17 91.75
2005-1-25 0:08:09 91.13
2005-1-25 0:09:02 89.26
2005-1-25 0:09:54 90.20
........
要求计算电量啊,电量=负荷×时间(千瓦时)
算法方面,你按什么都可以
select yourfunc(参数随便啦) from AAA;返回计算结果么?
还是您有更好得方法,盼望指点,谢谢,谢谢!
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
fhold:=fh;
timesold:=times;
end loop;
我不要算法,要实现过程啊
select ljdl from 表么?不行,这样会执行很多次
我只有将就select ljdl from 表 where rownum<2啦,可我觉得又很别扭,所以来问create or replace function LJDL return number is
Result number;
fhold number;
fh number;
times date;
timesold date;
CURSOR MyCursor IS
select times,C2
from ls_000_200501 t
where times>to_date('2005-01-25','yyyy-mm-dd')
and times<to_date('2005-01-26','yyyy-mm-dd');
begin
Result:=0;
fhold:=0;
timesold:=to_date('2005-01-25','yyyy-mm-dd');
OPEN MyCursor;
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
fhold:=fh;
timesold:=times;
end loop;
close MyCursor;
return(Result);
end LJDL;
group by to_char(times,'yyyy-mm-dd hh24');
select deptno,strcat(ename||'-') from emp group by deptno;
DEPTNO STRCAT(ENAME||'-')
------ --------------------------------------
10 CLARK-KING-MILLER-
20 SMITH-FORD-ADAMS-SCOTT-JONES-
30 ALLEN-BLAKE-MARTIN-TURNER-JAMES-WARD-create or replace type str_cat_type as object
(
total varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT str_cat_type )
return number,
member function
ODCIAggregateIterate(self IN OUT str_cat_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN str_cat_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT str_cat_type,
ctx2 IN str_cat_type)
return number
);
/create or replace type body str_cat_type
is
static function ODCIAggregateInitialize(sctx IN OUT str_cat_type)
return number
is
begin
sctx := str_cat_type( null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT str_cat_type,
value IN varchar2)
return number
is
begin
self.total := self.total || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN str_cat_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT str_cat_type,
ctx2 IN str_cat_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/body created.CREATE or replace
FUNCTION strcat(str varchar2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING str_cat_type;
/
用你的函数加个输入参数 日期(到天的日期)。
计算的时候查参数的日期和参数日期加一的这段时间的数据,返回结果。查的时候就这样
select cdate ,LJDL(cdate) from (
select distinct to_char(times,'yyyy-mm-dd') cdate from aaa)方法不怎么灵活,只是用你的函数扩展的。
实现更多的需求
Result number;
fhold number;
fh number;
times date;
timesold date;
CURSOR MyCursor IS
select times,C2
from ls_000_200501 t
where times>to_date(cdate ,'yyyy-mm-dd')
and times<to_date(cdate ,'yyyy-mm-dd')+1;
begin
Result:=0;
fhold:=0;
timesold:=to_date('2005-01-25','yyyy-mm-dd');
OPEN MyCursor;
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
fhold:=fh;
timesold:=times;
end loop;
close MyCursor;
return(Result);
end LJDL;
用:
select cdate ,LJDL(cdate) from (
select distinct to_char(times,'yyyy-mm-dd') cdate from aaa)
from (
select fh,lag(fh,1,0) over(order by times) fhold,
times, lag(times,1,trunc(times,'dd')) over(order by times) timesold,
trunc(times,'dd') the_day
from aaa )
group by the_day返回每天的值。
as
select the_day,sum((fh+fhold)*(times-timesold)*24*1000/2)
from (
select fh,lag(fh,1,0) over(order by times) fhold,
times, lag(times,1,trunc(times,'dd')) over(order by times) timesold,
trunc(times,'dd') the_day
from aaa )
group by the_dayselect sum(df) from df_view where the_day >= 'AAAAAAAAA' and the_day < 'VVVVVVVVV'
return number is
Result number;
fhold number;
fh number;
times date;
timesold date;
CURSOR MyCursor IS
select times,C2
from ls_000_200501 t
where times>to_date(sdate,'yyyy-mm-dd')
and times<to_date(edate,'yyyy-mm-dd');
begin
Result:=0;
fhold:=0;
timesold:=to_date('2005-01-25','yyyy-mm-dd');
OPEN MyCursor;
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
fhold:=fh;
timesold:=times;
end loop;
close MyCursor;
return(Result);
end LJDL;注意使用时的表
=================================================SELECT LJDL('2005-01-25','2005-01-26') FROM DUAL================================================
就不会出现如你上面的select ljdl from 表 where rownum<2 这样的“别扭”问题了
from (
select fh,lag(fh,1,0) over(order by times) fhold,
times, lag(times,1,trunc(times,'dd')) over(order by times) timesold,
times from aaa where times>='2005-1-25 0:00:18' and times<='2005-1-25 0:09:54')按这个思路,不同应用稍做修改应该都可以解决的了
可以仔细看一下我的回帖,已经把你的SQL改过了。
不存在trunc(times,'dd')的限制了。
select sum((b.times-a.times)/24*b.fh) from
(
select rownum no,times from aaa where times>=起始时间 and times<结束时间
) a,
(
select rownum no,times,fh from aaa where times>起始时间 and times<=结束时间
) b
where a.no=b.no;
select sum((b.times-a.times)/24*b.fh) from
(
select rownum no,times
from (select 起始时间 from dual
union
select times from aaa where times>起始时间 and times<结束时间)
) a,
(
select rownum no,times,fh from aaa where times>起始时间 and times<=结束时间
) b
where a.no=b.no;另外,他还要算梯形,所以还得变
select sum((b.times-a.times)/24*(b.fh+a.fh)/2) from
(
select rownum no,times
from (select 起始时间,0 from dual
union
select times ,fh from aaa where times>起始时间 and times<结束时间)
) a,
(
select rownum no,times,fh from aaa where times>起始时间 and times<=结束时间
) b
where a.no=b.no;
(
select rownum no,times,fh from aaa where times>=起始时间 and times<结束时间
) a,
(
select rownum no,times,fh from aaa where times>起始时间 and times<=结束时间
) b
where a.no=b.no;
那个union不一定要的,条件中的时间范围是不一样的,你仔细看看
select 起始时间,0 from dual
union
select times ,fh from aaa where times>起始时间 and times<结束时间
select times,C2
from ls_000_200501 t
where times>to_date('2005-01-25','yyyy-mm-dd')
and times<to_date('2005-01-26','yyyy-mm-dd');
begin
Result:=0;
fhold:=0;
timesold:=to_date('2005-01-25','yyyy-mm-dd');
=============================
OPEN MyCursor;
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
应改为
Result:=Result+(fh+fhold)*(times-timesold)*1000/48
1天=24小时
例如times-timesold结果1。3天,换成小时不就是*24么?
至于那个sql语句的,我大致明白了意思,但总感觉楼上a,b表有相同的时候,如果我开始时间在数据里没有那就成这种情况了,如果a,b表正好错开一个,这个方法也是很好的,另,其实我的数据存储是要求按月换表的,所以,我还是用函数解决了
附上代码:
create or replace function LJDL(cdate varchar2,jz varchar2) return number is
Result number;
fhold number;
fh number;
times date;
timesold date;
type g_cursor is ref cursor;
MyCursor g_cursor;
sqlstr varchar2(2000);
--统计计算给定日期的电量,接受日期格式'yyyy-mm-dd'
--参数1为日期,参数2为机组,格式实例'C1','C1+C2+C3',......
--动态游标实现换表处理
--Created By Yyt 2005.1.26
begin
sqlstr:='select times,'||jz||' from ls_000_'||
to_char(to_date(cdate,'yyyy-mm-dd'),'yyyymm')||
' where times>to_date('''||cdate||''',''yyyy-mm-dd'')'||
' and times<to_date('''||cdate||''',''yyyy-mm-dd'')+1';
Result:=0;
fhold:=0;
timesold:=to_date(cdate,'yyyy-mm-dd');
--打开游标
OPEN MyCursor for sqlstr;
loop
fetch MyCursor into times,fh;
exit when MyCursor%NOTFOUND;
--计算小梯形面积
Result:=Result+(fh+fhold)*(times-timesold)*24*1000/2;
fhold:=fh;
timesold:=times;
end loop;
close MyCursor;
return(round(Result));
--异常返回0
exception when others then
return(0);
end LJDL;
(
select rownum no,times
from (select 起始时间,0 from dual
union
select times ,fh from aaa where times>起始时间 and times<结束时间)
) a,
(
select rownum no,times,fh from aaa where times>起始时间 and times<=结束时间
) b
where a.no=b.no;应该是可解的
你不觉得他写的那个函数的意思就是从起始日期的00:00:00开始的么?呵呵
Select sum((fh+fhold)*(times-timesold)*24*1000/2)
from (
select fh,lag(fh,1,0) over(order by times) fhold,
times, lag(times,1,times) over(order by times) timesold,
trunc(times,'dd') the_day
from test1 Where times>=to_date('2005-1-25 1:00:18','yyyy-mm-dd hh:mi:ss') And
times<=to_date('2005-1-25 1:09:54','yyyy-mm-dd hh:mi:ss') )
date_begin date;
date_end date;
Psum number;
begin
select min(times) into date_begin from aaa where times>=to_date('起始时间');
select max(times) into date_end from aaa where times<=('结束时间');
select sum((b.times-a.times)/48*(a.fh+b.fh)) into Psum from
(
select rownum no,times,fh from aaa where times>=date_begin and times<date_end
) a,
(
select rownum no,times,fh from aaa where times>date_begin and times<=date_end
) b
where a.no=b.no;
return Psum;
end f1;这样应该可以了吧,如果改动只需改date_begin和date_end的取值。
(select rownum no,times,C1 from(
select rownum no,to_date('2005-01-25','yyyy-mm-dd') as times,0 as C1 from dual union
select rownum no,times,c1 from ls_000_200501 where times>=to_date('2005-01-25','yyyy-mm-dd')
and times<to_date('2005-01-26','yyyy-mm-dd'))) a,
(select rownum no,times,c1 from ls_000_200501 where times>=to_date('2005-01-25','yyyy-mm-dd')
and times<to_date('2005-01-26','yyyy-mm-dd')) b where a.no=b.no;
结果:2603099
和select ljdl('2005-01-25','C1') from dual;
结果:2603099
一样结果!
前面忘了改
to onejune4450 你始终在考虑的缺陷就是起始第一行中的times是什么,
就是总的开始计算的时间,也就是第一个三角形面积的计算。这个应该问问楼主,是表中时间最小的录呢,还是起始日期的0:00:00?
第一笔数据是:第二天起始时间 - 第一天0:00:00
还是:第二天起始时间 - 第二天0:00:00
还是:第二天起始时间 - 第一天最后一笔的时间
??
还是别的什么算法?
select sum((fh+fhold)*(times-timesold)*24*1000/2)
from (
select fh,lag(fh,1,0) over(order by times) fhold,
times, lag(times,1,trunc(times,'dd')) over(order by times) timesold
from aaa where times>='2005-1-25 0:00:18' and times<='2005-1-25 0:09:54'
union all
select fh,0,trunc(times)+1-1/24/60/60,times
from aaa where time=(select max(time) from aaa
where times>='2005-1-25 0:00:18' and times<='2005-1-25 0:09:54') )