假设有以下表,我想求得每一行记录日期前13天内的平均 score,我原先以内嵌子查询的方法
select 'recAvg'=(select avg(score) from t2 where thedate<tt2.theDate and thedate>=dateadd(dd,-13,tt2.theDate)) .............,但数据量有几十万,运行速度非常慢,请问应该什么方式的sql语句处理这种运算好?id theDate score
1 2011-12-01 985
2 2011-12-02 456
3 2011-12-03 258
select 'recAvg'=(select avg(score) from t2 where thedate<tt2.theDate and thedate>=dateadd(dd,-13,tt2.theDate)) .............,但数据量有几十万,运行速度非常慢,请问应该什么方式的sql语句处理这种运算好?id theDate score
1 2011-12-01 985
2 2011-12-02 456
3 2011-12-03 258
在theDate 表是建索引,最好是聚集索引.
试试下面的语句:
select a.id,a.theDate,avg(b.score)
from tb a inner join tb b on b.theDate between dateadd(d,-13,a.theDate) and a.theDate
group by a.id,a.theDate
left join tb
on b.thedate between a.thedate-13 and a.thedate
left join tb
on b.thedate between a.thedate-13 and a.thedate
group by a.thedate