只返回滚动平均SQL的最近值

Question

我正在为特定时间框架的所有最小值生成滚动平均值。到目前为止，我编写的查询计算每个时间段的最小值，然后创建按设施名称划分的最小值的滚动平均值。问题是，每当我试图返回最近日期的滚动平均值时，我得到的不是滚动平均值，而是该日期的最小值。这是我的查询的样子:

F.normalized_facility_id, F.name, F.period_end_date, F.minimum, 
avg(minimum) OVER (PARTITION BY F.normalized_facility_id
ORDER BY period_end_date
ROWS BETWEEN 23 preceding and current ROW) as RollingMinimumAvg
FROM
(
--This Select statement is designed to calculate the minimum benzene concentration from each sample period from each refinery.
SELECT  
F.normalized_facility_id, F.name, SR.report_id, RP.period_end_date,
min(benzene_concentration) AS minimum
FROM PassiveBenzene.sampler_results SR
INNER JOIN PassiveBenzene.report_periods RP
ON SR.period_id = RP.period_id
INNER JOIN PassiveBenzene.report R
ON SR.report_id = R.report_id
INNER JOIN PassiveBenzene.facility F
ON R.facility_id = F.facility_id
GROUP BY F.normalized_facility_id, F.name, SR.report_id, RP.period_end_date
) as F
ORDER BY F.normalized_facility_id, F.period_end_date DESC, RollingMinimumAvg

由此得出的结果是每个时期的最小值以及滚动平均最小值，而不是每个设施最近日期期间的滚动平均最小值。当我尝试添加一个返回最大日期值的滚动平均值的子句时，它只返回该日期的最小值，而不是计算的滚动平均值最小值。如果我围绕我的原始查询和子查询创建第三个查询，即查询最大日期值的滚动平均最小值，这会实现我想要寻找的吗?是否可以在SQLServer中只返回滚动平均值的最新值?

Answer 1

您可以使用您的查询作为子查询，然后按每个设施内的下降日期对它们进行编号，并选择第一个:

SELECT
F2.normalized_facility_id, F2.name, F2.period_end_date, F2.minimum 
FROM (
SELECT 
F1.*
, Seq=row_number() over 
(partition by F1.normalized_facility_id 
order by F1.period_end_date desc)
FROM
(
-- insert Your Query ( less the ORDER BY part) here
) F1
) F2
where F2.Seq=1 
ORDER BY F2.normalized_facility_id, F2.period_end_date DESC, F2.RollingMinimumAvg