高效存储日期范围的方法

Question

我需要存储简单的数据 - 假设我有一些产品，其中代码作为主键，还有一些属性和有效范围。因此，数据可能看起来像这样：

Products
code    value   begin_date  end_date
10905   13      2005-01-01  2016-12-31
10905   11      2017-01-01  null

这些范围不重叠，因此每个日期都有一个独特产品及其属性的列表。为了方便使用，我创建了以下函数：

create function dbo.f_Products
(
    @date date
)
returns table
as
return (
    select
    from dbo.Products as p
    where
        @date >= p.begin_date and
        @date <= p.end_date
)

这是我将要使用它的方式:

select
    *
from <some table with product codes> as t
    left join dbo.f_Products(@date) as p on
        p.code = t.product_code

这些都很好，但我该如何让优化器知道这些行是唯一的，以获得更好的执行计划呢？

我做了一些搜索，并找到了几篇关于DDL的文章，可以防止在表中存储重叠的范围：

- 自我维护、连续的时间段表格 - 存储没有重叠的时间间隔 但即使我尝试这些约束条件，我发现优化器无法理解生成的记录集将返回唯一的代码。

我想要的是一种方法，它可以给我基本相同的性能，就像我在某个日期上存储了那些产品列表并使用 date = @date 进行选择一样。

我知道一些关系型数据库（比如PostgreSQL）有专门的数据类型来处理这个问题（Range Types）。但是SQL Server没有类似的东西。

我是否遗漏了什么，或者在SQL Server中没有正确的方法来处理这个问题？

- Roman Pekar

---

如果你关注性能，就不要使用UDF函数。直接与表进行连接。 - Gordon Linoff

我不同意这个观点，但这里不是讨论它的场合，所以我们不要进行激烈的讨论。 - Roman Pekar

这些范围并没有重叠，但实际上它们是有重叠的。你的第二行应该从“2017-01-01”开始。 - Jeroen Mostert

我认为你想让优化器知道你的结果行是唯一的这个想法是错的。你应该更关心如何让它高效地检索你感兴趣的行。截至2018年，SQL Server仍然没有单独支持范围。没有“唯一性”的情况下，优化器将根据你加入的其他表的基数来决定连接类型，这应该是可以接受的。CREATE UNIQUE CLUSTERED INDEX IX_Products ON products([code], [begin_date], [end_date]) 这就是你需要的... - Jeroen Mostert

您当前的查询无法处理 @date 大于最近的 begin_date 的情况。我会在 code、begin_date 上创建一个聚集主键，并使用 WHERE code = 10905 AND begin_date <= @Date AND @date <= COALESCE(end_date, @date) 进行查询。这将使用索引查找。 - Steve Ford

Answer 1

编辑：我的原始答案使用了INNER JOIN，但是提问者想要一个LEFT JOIN。

CREATE TABLE Products
  (
  [Code] INT NOT NULL
  , [Value] VARCHAR(30) NOT NULL
  , Begin_Date DATETIME NOT NULL
  , End_Date DATETIME NULL
  )

/*
Products
code    value   begin_date  end_date
10905   13      2005-01-01  2016-12-31
10905   11      2017-01-01  null
*/
INSERT INTO Products ([Code], [Value], Begin_Date, End_Date) VALUES (10905, 13, '2005-01-01', '2016-12-31')
INSERT INTO Products ([Code], [Value], Begin_Date, End_Date) VALUES (10905, 11, '2017-01-01', NULL)

CREATE NONCLUSTERED INDEX SK_ProductDate ON Products ([Code], Begin_Date, End_Date) INCLUDE ([Value])

CREATE TABLE SomeTableWithProductCodes
  (
  [CODE] INT NOT NULL 
  )

 INSERT INTO SomeTableWithProductCodes ([Code]) VALUES (10905)

这里是一个典型的查询，带有日期谓词。请注意，有更优化的方法以弹性的方式完成这个操作，在上限使用“小于”运算符，但那是另一个讨论。

SELECT
  P.[Code]
  , P.[Value]
  , P.[Begin_Date]
  , P.[End_Date]
FROM
   SomeTableWithProductCodes ST
   LEFT JOIN Products AS P ON
     ST.[Code] = P.[Code]
     AND '2016-06-30' BETWEEN P.[Begin_Date] AND ISNULL(P.[End_Date], '9999-12-31')

这个查询将在Product表上执行索引查找。

这里有一个SQL Fiddle：SQL Fiddle - Products and Dates

- Pittsburgh DBA

Answer 2

你可以创建一个 索引视图，其中包含范围内每个 代码/日期 的一行。

ProductDate (indexed view)
code    value   date
10905   13      2005-01-01
10905   13      2005-01-02
10905   13      ...
10905   13      2016-12-31
10905   11      2017-01-01
10905   11      2017-01-02
10905   11      ...
10905   11      Today

像这样：

create schema digits
go

create table digits.Ones (digit tinyint not null primary key)
insert into digits.Ones (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Tens (digit tinyint not null primary key)
insert into digits.Tens (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Hundreds (digit tinyint not null primary key)
insert into digits.Hundreds (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Thousands (digit tinyint not null primary key)
insert into digits.Thousands (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.TenThousands (digit tinyint not null primary key)
insert into digits.TenThousands (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
go

create schema info
go

create table info.Products (code int not null, [value] int not null, begin_date date not null, end_date date null, primary key (code, begin_date))
insert into info.Products (code, [value], begin_date, end_date) values 
(10905, 13, '2005-01-01', '2016-12-31'),
(10905, 11, '2017-01-01', null)

create table info.DateRange ([begin] date not null, [end] date not null, [singleton] bit not null default(1) check ([singleton] = 1))
insert into info.DateRange ([begin], [end]) values ((select min(begin_date) from info.Products), getdate())
go

create view info.ProductDate with schemabinding 
as
select
    p.code,
    p.value,
    dateadd(day, ones.digit + tens.digit*10 + huns.digit*100 + thos.digit*1000 + tthos.digit*10000, dr.[begin]) as [date]
from
    info.DateRange as dr
cross join
    digits.Ones as ones
cross join
    digits.Tens as tens
cross join
    digits.Hundreds as huns
cross join
    digits.Thousands as thos
cross join
    digits.TenThousands as tthos
join
    info.Products as p on
    dateadd(day, ones.digit + tens.digit*10 + huns.digit*100 + thos.digit*1000 + tthos.digit*10000, dr.[begin]) between p.begin_date and isnull(p.end_date, datefromparts(9999, 12, 31))
go

create unique clustered index idx_ProductDate on info.ProductDate ([date], code)
go

select *
from info.ProductDate with (noexpand)
where 
    date = '2014-01-01'

drop view info.ProductDate
drop table info.Products
drop table info.DateRange
drop table digits.Ones
drop table digits.Tens
drop table digits.Hundreds
drop table digits.Thousands
drop table digits.TenThousands
drop schema digits
drop schema info
go

- Aducci

---

那个不错，我自己也在考虑这个问题，但长时间运行会占用很多空间。 - Roman Pekar

@RomanPekar，您到底想要实现什么目标？您是想获取所有产品和日期组合，没有间隙，还是只想高效地查询您已经拥有的表格，即Code、Value、Begin_Date和End_Date？如果是后者，这些解决方案就会失控。如果是前者，您需要编辑您的问题以明确这一点，因为目前不清楚。 - Pittsburgh DBA

@RomanPekar - 我无法想象它会占用那么多的空间...有多少个产品？平均时间跨度是多少？ - Aducci

@RomanPekar 搜索“时空权衡”。你不能同时将两者最小化。 - Salman A

如果我们想让最终用户使用起来更加方便，那么它应该能够处理基本上任何传递的日期（期间可能是开放式的）。好吧，假设有6000个产品。如果我们使用从1990-01-01到2100-01-01的时间，那么已经有了110 * 365 * 6000，即2.19亿行。而且它仍然不能处理任何日期（例如9999-12-31）。所以这是一个不错的工作解决方案，但是好的“范围”解决方案仍然更好。 - Roman Pekar

@RomanPekar 你可以使用内联表函数来包装视图，以减少需要存储的行数...使用类似于 where [date] = case when @date > getdate() then getdate() else @date end 的 where 子句。 - Aducci

Answer 3

首先，您需要为（begin_date，end_date，code）创建一个唯一的聚集索引。

然后SQL引擎将能够执行索引查找。

此外，您还可以尝试为dbo.Products表创建一个视图，以将该表与预填充的dbo.Dates表连接起来。

select p.code, p.val, p.begin_date, p.end_date, d.[date]
    from dbo.Product as p
        inner join dbo.dates d on p.begin_date <= d.[date] and d.[date] <= p.end_date

然后在您的函数中，您将该视图用作“where @date = view.date”。结果可能更好，也可能略微变差...这取决于实际数据。

您还可以尝试将该视图索引化（这取决于它被更新的频率）。

或者，如果您为[begin_date]..[end_date]范围内的每个日期填充dbo.Products表，则可以获得更好的性能。

- Anton

Answer 4

使用ROW_NUMBER方法可以一次扫描整个Products表。如果您的Products表中有很多产品代码，但每个代码的有效范围很少，那么这是最佳方法。

WITH
CTE_rn
AS
(
    SELECT
        code
        ,value
        ,ROW_NUMBER() OVER (PARTITION BY code ORDER BY begin_date DESC) AS rn
    FROM Products
    WHERE begin_date <= @date
)
SELECT *
FROM
    <some table with product codes> as t
    LEFT JOIN CTE_rn ON CTE_rn.code = t.product_code AND CTE_rn.rn = 1
;

如果您在Products表中有少量产品代码和大量有效期范围，那么最好使用OUTER APPLY来查找每个代码的Products表。

SELECT *
FROM
    <some table with product codes> as t
    OUTER APPLY
    (
        SELECT TOP(1)
            Products.value
        FROM Products
        WHERE
            Products.code = t.product_code
            AND Products.begin_date <= @date
        ORDER BY Products.begin_date DESC
    ) AS A
;

两个变量都需要在(code, begin_date DESC) include (value)上建立唯一索引。

请注意查询甚至不考虑end_date，因为它们假定间隔没有间隙。它们将在SQL Server 2008中运行。

- Vladimir Baranov

Answer 5

一个没有间隙的解决方案可能是这样的：

DECLARE @tbl TABLE(ID INT IDENTITY,[start_date] DATE);
INSERT INTO @tbl VALUES({d'2016-10-01'}),({d'2016-09-01'}),({d'2016-08-01'}),({d'2016-07-01'}),({d'2016-06-01'});

SELECT * FROM @tbl;

DECLARE @DateFilter DATE={d'2016-08-13'};

SELECT TOP 1 * 
FROM @tbl
WHERE [start_date]<=@DateFilter
ORDER BY [start_date] DESC

重要提示：请确保 start_date 上有唯一索引。

更新：针对不同产品

DECLARE @tbl TABLE(ID INT IDENTITY,ProductID INT,[start_date] DATE);
INSERT INTO @tbl VALUES
--product 1
(1,{d'2016-10-01'}),(1,{d'2016-09-01'}),(1,{d'2016-08-01'}),(1,{d'2016-07-01'}),(1,{d'2016-06-01'})
--product 1
,(2,{d'2016-10-17'}),(2,{d'2016-09-16'}),(2,{d'2016-08-15'}),(2,{d'2016-07-10'}),(2,{d'2016-06-11'});

DECLARE @DateFilter DATE={d'2016-08-13'};

WITH PartitionedCount AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY [start_date] DESC) AS Nr
          ,*
    FROM @tbl
    WHERE [start_date]<=@DateFilter
)
SELECT *
FROM PartitionedCount
WHERE Nr=1

- Shnugo

---

是的，这是解决任务的好方法。然而，性能仍然不够优化。我想要的是一种方法，它可以给我基本上与存储在某个日期的产品列表并使用“date = @date”进行选择相同的性能。 - Roman Pekar

@RomanPekar，如果您基于相等性抓取索引或者需要在区间内定位值，则必须有所区别。不过，如果存在索引（请检查EP是否已使用！），则 WHERE [start_date] <= @DateFilter 应该会非常快，而 TOP 1 ORDER BY [start_date] DESC 应该会立即选择相关行... 这让我想知道为什么您没有观察到更好的性能... - Shnugo

一个行没问题，但如果我想获得特定日期有效产品列表怎么办？ 当然我可以将表格展开到日期并使用等值检查，但我真的想知道是否有更好的解决方案。 - Roman Pekar

@RomanPekar 如果您的所有产品都具有相同的开始日期（即每月的第一天），只需使用 SELECT TOP 1 WITH TIES... 这对您有帮助吗？ - Shnugo

@RomanPekar 我刚刚添加了一种使用 ROW_NUMBER 的方法，可以一次性获取所有产品。只需将此结果集连接到您的主查询即可... - Shnugo

@RomanPekar 这个问题解决了吗？您需要进一步的帮助吗？请允许我给您一个提示：如果这个问题已经解决，那么请您很友善地在（最佳）答案的投票计数器下方打勾接受它。这将1）标记此问题为已解决2）使关注者更容易找到最佳解决方案3）向回答者支付积分4）向您支付积分。由于您自己已经超过了15分的边界，因此还要求您对贡献进行投票。这是SO表达感谢之道。愉快编码！ - Shnugo