SQL Server - 查询最近的日期范围

2021-09-10 00:00:00 tsql sql-server

如果我有这样的表结构:

If I have a table structure like this:

ProductCode  Date
Foo          4/1/2012
Foo          4/2/2012
Foo          4/3/2012
Foo          4/6/2012
Foo          4/7/2012
Foo          4/8/2012
Foo          4/9/2012
Foo          4/10/2012
Foo          4/15/2012
Foo          4/16/2012
Foo          4/17/2012

有没有办法查询给定 ProductCode 和 Date 的日期范围(假设范围必须是连续的)?换句话说，对于这个表，Foo 存在于 3 个日期范围内:4/1-4/3;4/6-4/10;和 4/15-4/17，我正在寻找给定日期的日期范围.

Is there a way to query for the date range for a given ProductCode and Date (assuming that ranges MUST be sequential)? In other words, for this table, Foo exists on 3 date ranges: 4/1-4/3; 4/6-4/10; and 4/15-4/17 and I'm looking for the date range given a date.

请注意 Foo 没有日期的 4/4、4/5、4/11>、4/12、4/13 和 4/14.

Please note that Foo doesn't have date's 4/4, 4/5, 4/11, 4/12, 4/13 and 4/14.

示例:
ProductCode=Foo, Date=4/2 将返回 4/1-4/3 因为条目是连续的.
ProductCode=Foo, Date=4/4 不会返回任何内容
ProductCode=Foo, Date=4/7 将返回 4/6-4/10 因为条目是连续的.
ProductCode=Foo, Date=4/12 不会返回任何内容
等

Examples:
ProductCode=Foo, Date=4/2 would return 4/1-4/3 because the entries are sequential.
ProductCode=Foo, Date=4/4 would return nothing
ProductCode=Foo, Date=4/7 would return 4/6-4/10 because the entries are sequential.
ProductCode=Foo, Date=4/12 would return nothing
etc.

推荐答案

本来可以使用 LAG，如果 SQL Server 2005 支持它.不幸的是，LAG 窗口函数仅适用于 SQL Server 2012，并且 PostgreSQL 8.4 及更高版本 ;-)

Could have used LAG, if SQL Server 2005 supported it. Unfortunately LAG window function works on SQL Server 2012 only, and PostgreSQL 8.4 and above ;-)

我认为可以在 SQL Server 2005 上运行，SQLFiddle 不支持 SQL 2005，只尝试了 SQLFiddle 的 SQL Server 2008，而不是 2012:

Works on SQL Server 2005 I supposed, SQLFiddle has no SQL 2005 support, tried SQLFiddle's SQL Server 2008 only, not 2012:

with DetectLeaders as
(
    select cr.ProductCode, CurRowDate = cr.Date, PrevRowDate = pr.Date
    from tbl cr
    left join tbl pr 
    on pr.ProductCode = cr.ProductCode AND cr.Date = DATEADD(DAY,1,pr.Date)
),
MembersLeaders as
(
    select *, 
        MemberLeader = 
            (select top 1 CurRowDate 
            from DetectLeaders nearest
            where nearest.PrevRowDate is null 
                and nearest.ProductCode = DetectLeaders.ProductCode
                and DetectLeaders.CurRowDate >= nearest.CurRowDate 
            order by nearest.CurRowDate desc)   
    from DetectLeaders
)
select BeginDate = MIN(CurRowDate), EndDate = MAX(CurRowDate) 
from MembersLeaders
where MemberLeader = 
  (select MemberLeader 
   from MembersLeaders
   where ProductCode = 'Foo' and CurRowDate = '4/7/2012')

现场测试:http://sqlfiddle.com/#!3/3fd1f/1

基本上它是这样工作的:

Basically this is how it works: 

PRODUCTCODE     CURROWDATE  PREVROWDATE MEMBERLEADER
Foo             2012-04-01              2012-04-01
Foo             2012-04-02  2012-04-01  2012-04-01
Foo             2012-04-03  2012-04-02  2012-04-01
Foo             2012-04-06              2012-04-06
Foo             2012-04-07  2012-04-06  2012-04-06
Foo             2012-04-08  2012-04-07  2012-04-06
Foo             2012-04-09  2012-04-08  2012-04-06
Foo             2012-04-10  2012-04-09  2012-04-06
Foo             2012-04-15              2012-04-15
Foo             2012-04-16  2012-04-15  2012-04-15
Foo             2012-04-17  2012-04-16  2012-04-15
Bar             2012-05-01              2012-05-01
Bar             2012-05-02  2012-05-01  2012-05-01
Bar             2012-05-03  2012-05-02  2012-05-01
Bar             2012-05-06              2012-05-06
Bar             2012-05-07  2012-05-06  2012-05-06
Bar             2012-05-08  2012-05-07  2012-05-06
Bar             2012-05-09  2012-05-08  2012-05-06
Bar             2012-05-10  2012-05-09  2012-05-06
Bar             2012-05-15              2012-05-15
Bar             2012-05-16  2012-05-15  2012-05-15
Bar             2012-05-17  2012-05-16  2012-05-15

http://sqlfiddle.com/#!3/35818/11

相关文章