具有日期条件的SQL窗口函数未返回期望值

debugcn 发表于 Dev

用户名

我想使用窗口函数在当前日期之前但在值的1年内找到最大值。我的尝试没有给我正确的价值，不确定为什么吗？

[MaxPrevious] 是理想的结果

[MaxPrevious2] 是具有错误值的窗口函数结果。

我需要使用窗口函数，因为最终查询更为复杂，但日期条件部分无法正常工作。

所需输出：

全表数据和查询：

--DROP TABLE [dbDelete].[dbo].[tblData]

--CREATE TABLE [dbDelete].[dbo].[tblData]
--([Date] datetime, [Part] varchar(10), [Tolerance] float);

--INSERT INTO [dbDelete].[dbo].[tblData] ([Date], [Part], [Tolerance])
--VALUES
--('2012-01-19 00:00:00', 'X1', 6.8),
--('2011-12-15 00:00:00', 'X1', 6.7),
--('2011-10-25 00:00:00', 'X1', 7.8),
--('2010-05-06 00:00:00', 'X1', 8.3),
--('2010-04-13 00:00:00', 'X1', 7.2),
--('2010-01-21 00:00:00', 'X1', 4.7),
--('2009-12-28 00:00:00', 'X1', 6.9),
--('2009-01-01 00:00:00', 'X1', 7.8),
--('2008-11-16 00:00:00', 'X1', 7.4),
--('2008-11-08 00:00:00', 'X1', 7.9),
--('2012-01-19 00:00:00', 'X2', 3.8),
--('2011-12-15 00:00:00', 'X2', 3.7),
--('2011-10-25 00:00:00', 'X2', 4.8),
--('2010-05-06 00:00:00', 'X2', 5.3),
--('2010-04-13 00:00:00', 'X2', 4.2),
--('2010-01-21 00:00:00', 'X2', 1.7),
--('2009-12-28 00:00:00', 'X2', 3.9),
--('2009-01-01 00:00:00', 'X2', 4.8),
--('2008-11-16 00:00:00', 'X2', 4.4),
--('2008-11-08 00:00:00', 'X2', 4.9)
--;

select t1.*
-- Find max before current record but within 1 year
,(select top (1) t2.[Tolerance] from [dbDelete].[dbo].[tblData] t2
    where t2.[Date] < t1.[Date]
    and t2.[Date] >= dateadd(year, -1, t1.[Date])
    and t2.[Part] = t1.[Part]
    order by t2.[Tolerance] desc) as [MaxPrevious]

-- Find max before current record but within 1 year
,max(case when t1.[Date] >= dateadd(year, -1, t1.[Date]) then t1.[Tolerance] else 0 end) over
(partition by t1.[Part]
    order by t1.[Date]
    rows between unbounded preceding and 1 preceding
) as [MaxPrevious2]

from [dbDelete].[dbo].[tblData] t1
order by t1.[Part], t1.[Date] desc

萨齐努克

--DROP TABLE [dbDelete].[dbo].[tblData]

--CREATE TABLE [dbDelete].[dbo].[tblData]
--([Date] datetime, [Part] varchar(10), [Tolerance] float);

--INSERT INTO [dbDelete].[dbo].[tblData] ([Date], [Part], [Tolerance])
--VALUES
--('2012-01-19 00:00:00', 'X1', 6.8),
--('2011-12-15 00:00:00', 'X1', 6.7),
--('2011-10-25 00:00:00', 'X1', 7.8),
--('2010-05-06 00:00:00', 'X1', 8.3),
--('2010-04-13 00:00:00', 'X1', 7.2),
--('2010-01-21 00:00:00', 'X1', 4.7),
--('2009-12-28 00:00:00', 'X1', 6.9),
--('2009-01-01 00:00:00', 'X1', 7.8),
--('2008-11-16 00:00:00', 'X1', 7.4),
--('2008-11-08 00:00:00', 'X1', 7.9),
--('2012-01-19 00:00:00', 'X2', 3.8),
--('2011-12-15 00:00:00', 'X2', 3.7),
--('2011-10-25 00:00:00', 'X2', 4.8),
--('2010-05-06 00:00:00', 'X2', 5.3),
--('2010-04-13 00:00:00', 'X2', 4.2),
--('2010-01-21 00:00:00', 'X2', 1.7),
--('2009-12-28 00:00:00', 'X2', 3.9),
--('2009-01-01 00:00:00', 'X2', 4.8),
--('2008-11-16 00:00:00', 'X2', 4.4),
--('2008-11-08 00:00:00', 'X2', 4.9)
--;

;with cte as (
select DATEADD(year, -1, [Date]) as PrevDate, * from [dbDelete].[dbo].[tblData]
)

select b.[Date], b.Part, b.Tolerance, max(a.Tolerance) as MaxPrevious from cte a
right join cte b
    on a.Part = b.Part and a.[Date] >= b.[PrevDate] and a.[Date] < b.[Date]
group by b.[Date], b.Part, b.Tolerance
order by b.[Part], b.[Date] desc

我不知道这是否可以仅使用窗口函数来实现。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。