IIIII DATEDAY P T
56386 2006-04-04 00:00:00.000 9581 198
56386 2006-04-05 00:00:00.000 9647 164
56386 2006-04-06 00:00:00.000 9669 167
56386 2006-04-07 00:00:00.000 9631 193
56386 2006-04-08 00:00:00.000 9569 205
56386 2006-04-09 00:00:00.000 9571 212
56386 2006-04-10 00:00:00.000 9529 217
56386 2006-04-11 00:00:00.000 9506 221 数据格式如上,我要统计任意时段的历史同期距平,语句该怎么写
比如计算要素T在2006-04-04到2006-04-11的距平,要首先计算出T前30年04-04到04-11的平均值,然后与2006-04-04到2006-04-11的平均值相减
关键在于怎样查询并计算过去30年04-04到04-11间T的平均值
56386 2006-04-04 00:00:00.000 9581 198
56386 2006-04-05 00:00:00.000 9647 164
56386 2006-04-06 00:00:00.000 9669 167
56386 2006-04-07 00:00:00.000 9631 193
56386 2006-04-08 00:00:00.000 9569 205
56386 2006-04-09 00:00:00.000 9571 212
56386 2006-04-10 00:00:00.000 9529 217
56386 2006-04-11 00:00:00.000 9506 221 数据格式如上,我要统计任意时段的历史同期距平,语句该怎么写
比如计算要素T在2006-04-04到2006-04-11的距平,要首先计算出T前30年04-04到04-11的平均值,然后与2006-04-04到2006-04-11的平均值相减
关键在于怎样查询并计算过去30年04-04到04-11间T的平均值
56386 2006-04-04 00:00:00.000 9581 198
56386 2006-04-05 00:00:00.000 9647 164
56386 2006-04-06 00:00:00.000 9669 167
56386 2006-04-07 00:00:00.000 9631 193
56386 2006-04-08 00:00:00.000 9569 205
56386 2006-04-09 00:00:00.000 9571 212
56386 2006-04-10 00:00:00.000 9529 217
56386 2006-04-11 00:00:00.000 9506 221
P,T是气压和气温
其实关键的问题就是在于怎样判断日期
查询过去30年4月4日至4月11日的数据,然后做个AVG()
DATEDAY是日期数据,要怎样达到让DATEDAY BETWEEN '04-04' AND '04-11'DATENAME(MONTHDAY,DATEDAY)=01这个只能有月或者日,能不能把DATEDAY处理成MM-DD
set @begintime='2006-04-04'
set @endtime='2006-04-11'SELECT (select AVG(t)FROM tb WHERE DATEDAY BETWEEN DATEDIFF(YEAR,-30,@begintime) AND DATEDIFF(YEAR,-30,@endtime))
-
(select AVG(t)FROM tb WHERE DATEDAY BETWEEN @begintime AND @endtime)
declare @begintime datetime,@endtime datetime
set @begintime='2006-04-04'
set @endtime='2006-04-11'
SELECT (select AVG(t)FROM tb WHERE T.iiiii=iiiii AND DATEDAY BETWEEN DATEDIFF(YEAR,-30,@begintime) AND DATEDIFF(YEAR,-30,@endtime))
-(select AVG(t)FROM tb WHERE T.iiiii=iiiii AND DATEDAY BETWEEN @begintime AND @endtime)
FROM tb T
dateadd(year,-30,'2006-07-11')
declare @begintime datetime,@endtime datetime,@n int ,@k bigint
set @begintime='2006-04-04'
set @endtime='2006-04-11'
set @n=1
set @k=0
while @n<=30
begin
set @k=@k+ (select AVG(t)FROM tb WHERE DATEDAY BETWEEN DATEDIFF(YEAR,-@n,@begintime) AND DATEDIFF(YEAR,-@n,@endtime))
set @n=@n+1
end
set @k=@k/30
SELECT @k-(select AVG(t)FROM tb WHERE DATEDAY BETWEEN @begintime AND @endtime)
FROM tb T
if object_id('ta')is not null drop table ta
go
create table ta(IIIII int,DATEDAY datetime ,p int , t int)
go
insert ta select
56386, '2006-04-04 00:00:00.000', 9581, 198 union all select --测试。1、以'2006-04-04'到'2006-04-06' 计算
56386, '2006-04-05 00:00:00.000', 9647, 164 union all select --测试。2、以'2006年前3年算(2003,2004,2005) 计算
56386, '2006-04-06 00:00:00.000', 9669, 167 union all select56386, '2003-04-04 00:00:00.000', 9631, 193 union all select
56386, '2003-04-05 00:00:00.000', 9569, 205 union all select
56386, '2003-04-06 00:00:00.000', 9571, 212 union all select56386, '2004-04-04 00:00:00.000', 9529, 217 union all select
56386, '2004-04-05 00:00:00.000', 9529, 207 union all select
56386, '2004-04-06 00:00:00.000', 9506, 221 union all select56386, '2005-04-04 00:00:00.000', 9631, 193 union all select
56386, '2006-04-05 00:00:00.000', 9569, 205 union all select
56386, '2007-04-06 00:00:00.000', 9571, 212 declare @begintime datetime,@endtime datetime,@begintime30 varchar(4),@endtime30 varchar(4)
set @begintime='2006-04-04'
set @endtime='2006-04-06'
set @begintime30=right(CONVERT(varchar(12) ,@begintime, 112),4) --0404
set @endtime30=right(CONVERT(varchar(12) ,@endtime, 112),4) --0406
select t=t1-t from (
select avg(t)t from ta where DATEDAY between @begintime and @endtime)a,
(select avg(t)t1 from ta where right(CONVERT(varchar(12),DATEDAY, 112),4) between @begintime30 and @endtime30 and year(DATEDAY)<year(@endtime))bt
-----------
23(1 行受影响)
drop table ta
and year(DATEDAY) between dateadd(year,-30,@endtime) and year(@endtime)
我楼上的少了一个,加上。如下:
select t=t1-t from (
select avg(t)t from ta where DATEDAY between @begintime and @endtime)a,
(select avg(t)t1 from ta where (right(CONVERT(varchar(12),DATEDAY, 112),4) between @begintime30 and @endtime30 ) and (year(DATEDAY) between dateadd(year,-30,@endtime) and year(@endtime))
可以啊。。
where substring(CONVERT(varchar(10),DATEDAY,120),6,5) between '04-04' and '04-11'
(SELECT Avg(T) FROM qxcyday,station WHERE station.tzmc='乐山'
AND qxcyday.iiiii=station.iiiii
AND DATENAME(YEAR,DATEDAY) BETWEEN '1971' AND '2000'
AND SUBSTRING(CONVERT(VARCHAR(10),DATEDAY,120),6,5)
BETWEEN '04-04' AND '04-11' AND T<>32766 AND T<>32700)[结果]
FROM qxcyday,station WHERE station.tzmc='乐山' AND qxcyday.iiiii=station.iiiii
AND dateday BETWEEN '2006-04-04' AND '2006-04-11' AND T<>32766 AND T<>32700