我有一个这样的表:
| prodID | date | perm
---------------------------------
|200 |8/7/2011 | 81.742
|200 |8/7/2011 | 81.644
|200 |8/7/2011 | 81.302
|200 |8/7/2011 | 81.057
|201 |8/7/2011 | 80.932
|201 |8/7/2011 | 80.839
|201 |8/7/2011 | 80.622
|201 |8/7/2011 | 80.557
|201 |8/7/2011 | 80.541
(除了稍微大一点)发生的情况细分:我想取前10个值(和后10个值)的平均值,其中prodid=somevalue在本例中为200。
代码:
declare @myid int
set @myid = 200
;with high as --top ten average
(
select prodid, CONVERT(CHAR(10), DATEADD(DAY, AVG(DATEDIFF(DAY, 0, CONVERT
(SMALLDATETIME, [date]))), 0), 101) as date, max(perm)as max_perm, avg(perm)
as
high_perm from
( select prodid, date, perm,
row_number() over(partition by date order by perm desc) as nt
from live_pilot_plant
where prodid = @myid) as T
where nt <= 10
group by prodid
),
low as -- bottom ten average
(
select prodid, CONVERT(CHAR(10), DATEADD(DAY, AVG(DATEDIFF(DAY, 0, CONVERT
(SMALLDATETIME, [date]))), 0),101) as date, min(perm) as min_perm, avg(perm)
as low_perm from
( select prodid, date, perm,
row_number() over(partition by date order by perm asc) as nt
from live_pilot_plant
where prodid = @myid) as T
where nt <= 10
group by prodid
)
select l.prodid, l.date, l.low_perm as low_avg, m.high_perm as high_avg,
(m.high_perm - l.low_perm) as delta
from low l
left outer join high m
on l.prodid = m.prodid
它产生了这样的东西:
| prodID | date | low_avg | high_avg | delta |
| 200 | 08/07/2011 | 68.752 | 79.1976 | 10.444 |
这些数字不准确--
这一切都很好,除了不太花枝招展。我的意思是有很多prodID,基于prodID做一个太慢了。如何根据日期获取low_avg和high_avg(按prodID分组)
类似这样的东西:
| date | prodID | low_avg | high_avg | delta |
| 08/07/2011 | 200 | 60 | 80 | 20 |
| 08/07/2011 | 201 | 70 | 100 | 100 |
注意:你可能已经注意到一个疯狂的转换日期。原因是一些prodID的日期重叠,即2011年8月7日和2011年8日的200,我需要对日期进行平均(这是一个varchar)。因此,类似于如果有100行2011年8月7日,然后有9行2011年7月8日,最终查询将产生日期为2011年8日的
以下查询一次对所有产品执行此操作:
select lpp.prod_id, lpp.date,
AVG(case when seqnum_asc <= 10 then perm end) as avg_bottom10,
AVG(case when seqnum_desc <= 10 then perm end) as avg_top10,
(AVG(case when seqnum_desc <= 10 then perm end) - AVG(case when seqnum_asc <= 10 then perm end)) as delta
from (select lpp.*,
ROW_NUMBER() over (partition by prodid, date order by perm) as seqnum_asc,
ROW_NUMBER() over (partition by prodid, date order by perm desc) as seqnum_desc
from live_pilot_plan lpp
) lpp
group by lpp.prod_id, lpp.ate