如何有效地进行数据库的历史数据查询？

Question

抱歉问题有点长！

我们有两个数据库表，例如Car和Wheel。它们之间存在关联，即车轮属于汽车，汽车有多个车轮。但是，可以更换车轮而不影响汽车的“版本”。可以更新汽车记录（例如喷漆），而不会影响车轮的版本（即没有级联更新）。

例如，Car表目前如下：

CarId, CarVer, VersionTime, Colour
   1      1       9:00       Red
   1      2       9:30       Blue
   1      3       9:45       Yellow
   1      4      10:00       Black

轮子表格如下所示（这辆车只有两个轮子！）

WheelId, WheelVer, VersionTime, CarId
   1         1           9:00     1
   1         2           9:40     1
   1         3          10:05     1
   2         1           9:00     1

所以，这款两轮汽车有4个版本。它的第一个轮子（WheelId 1）没有变化。第二个轮子在10:05被更改（例如涂漆）。

如何高效地进行关联查询？需要按照要求加入其他表。请注意，这是一个新数据库，我们拥有架构并可以更改或添加审核表以使查询更容易。我们尝试了一种审核表方法（列为：CarId、CarVersion、WheelId、WheelVersion、CarVerTime、WheelVerTime），但并没有真正改善我们的查询。

示例查询：显示Car ID 1的状态，包括其轮子记录到9:50。此查询应返回这两行：

WheelId, WheelVer, WheelVerTime, CarId, CarVer, CarVerTime, CarColour
   1         2         9:40        1       3       9:45      Yellow
   2         1         9:00        1       3       9:45      Yellow

我们能想到的最好查询是这样的：

select c.CarId, c.VersionTime, w.WheelId,w.WheelVer,w.VersionTime,w.CarId
from Cars c, 
(    select w.WheelId,w.WheelVer,w.VersionTime,w.CarId
    from Wheels w
    where w.VersionTime <= "12 Jun 2009 09:50" 
     group by w.WheelId,w.CarId
     having w.WheelVer = max(w.WheelVer)
) w
where c.CarId = w.CarId
and c.CarId = 1
and c.VersionTime <= "12 Jun 2009 09:50" 
group by c.CarId, w.WheelId,w.WheelVer,w.VersionTime,w.CarId
having c.CarVer = max(c.CarVer)

如果您想尝试此操作，则可以使用以下SQL语句创建表格并插入记录：

create table Wheels
(
WheelId int not null,
WheelVer int not null,
VersionTime datetime not null,
CarId int not null,
 PRIMARY KEY  (WheelId,WheelVer)
)
go

insert into Wheels values (1,1,'12 Jun 2009 09:00', 1)
go
insert into Wheels values (1,2,'12 Jun 2009 09:40', 1)
go
insert into Wheels values (1,3,'12 Jun 2009 10:05', 1)
go
insert into Wheels values (2,1,'12 Jun 2009 09:00', 1)
go


create table Cars
(
CarId int not null,
CarVer int not null,
VersionTime datetime not null,
colour varchar(50) not null,
 PRIMARY KEY  (CarId,CarVer)
)
go

insert into Cars values (1,1,'12 Jun 2009 09:00', 'Red')
go
insert into Cars values (1,2,'12 Jun 2009 09:30',  'Blue')
go
insert into Cars values (1,3,'12 Jun 2009 09:45',  'Yellow')
go
insert into Cars values (1,4,'12 Jun 2009 10:00',  'Black')
go

- ng5000

Answer 1

这种表在文献中被称为有效时间状态表。普遍认为，每一行应通过具有开始日期和结束日期来模拟一个时间段。基本上，在SQL中工作的单位是行，行应完全定义实体; 仅通过每行一个日期，你的查询不仅变得更加复杂，而且设计也会因将亚原子部分拆分到不同行而受到损害。

正如Erwin Smout所提到的，在这个主题上最权威的书之一是：

Richard T. Snodgrass（1999年）。在SQL中开发面向时间的数据库应用程序 它已经绝版，但幸运的是可作为免费下载PDF使用（上面的链接）。

我实际上已经阅读了它，并且实施了许多概念。其中大部分文本都在ISO / ANSI标准SQL-92中，并且尽管某些专有SQL语法已经实现，包括SQL Server（也可作为下载使用），但我发现概念信息更有用。

Joe Celko也写了一本书，叫做《集合思维：SQL中的辅助、时间和虚拟表》，这本书在很大程度上源自Snodgrass的工作，尽管我必须说，在两者分歧的地方，我发现Snodgrass的方法更可取。

我同意，我们目前所拥有的SQL产品中实现这些内容非常困难。我们在将数据变为临时数据之前会仔细考虑；如果我们可以仅使用“历史”来解决问题，那么我们就会这样做。SQL-92中许多关于时间的功能在SQL Server中都缺失，例如INTERVAL、OVERLAPS等。某些基本的东西，比如用于确保期间不重叠的序列化“主键”，在SQL Server中无法使用CHECK约束实现，因此需要触发器和/或UDF。

Snodgrass的书基于他为SQL3所做的工作，SQL3是标准SQL的一个提议扩展，旨在为时间数据库提供更好的支持，但遗憾的是，这似乎已经被搁置多年了:(

- onedaywhen

Answer 2

在每一行都有开始时间和结束时间的情况下，As-of 查询变得更加容易。将结束时间存储到表中是最有效的方法，但如果这很困难，您可以进行查询：

select 
    ThisCar.CarId
,   StartTime = ThisCar.VersionTime
,   EndTime = NextCar.VersionTime
from Cars ThisCar
left join Cars NextCar
    on NextCar.CarId = ThisCar.CarId
    and ThisCar.VersionTime < NextCar.VersionTime
left join Cars BetweenCar
    on BetweenCar.CarId = BetweenCar.CarId
    and ThisCar.VersionTime < BetweenCar.VersionTime
    and BetweenCar.VersionTime < NextCar.VersionTime
where BetweenCar.CarId is null

你可以将这个存储在一个视图中。比如说，如果视图叫做vwCars，你可以选择某个特定日期的汽车，例如：

select * 
from vwCars
where StartTime <= '2009-06-12 09:15' 
and ('2009-06-12 09:15' < EndTime or EndTime is null)

你可以将它存储在表值存储过程中，但这可能会导致性能惩罚。

- Andomar

---

您的查询更高效（扫描表更少），但不执行截至查询。您的查询仅获取最新版本，而不是截至09:50的版本。我们可能可以从您的查询中获得一些想法，所以谢谢。 - ng5000

我们将无法使用视图，因为我们需要将查询的时间组件传递到查询中。存储过程可能是一个选项，但由于必须加入其他表，我们可能需要查看表函数。 - ng5000

使用新方法编辑以实际日期为准的内容。 - Andomar

您的查询没有按照我的问题返回我想要的结果 - 无论如何感谢。 - ng5000

Answer 3

如果您在单个汽车ID的情况下有两行具有完全相同的版本时间，则此查询将返回重复项，但这是定义在该情况下您认为什么是“最新”的问题。我还没有测试过这个，但我认为它会给你所需的东西。至少非常接近。

SELECT
     C.car_id,
     C.car_version,
     C.colour,
     C.version_time AS car_version_time,
     W.wheel_id,
     W.wheel_version,
     W.version_time AS wheel_version_time,
FROM
     Cars C
LEFT OUTER JOIN Cars C2 ON
     C2.car_id = C.car_id AND
     C2.version_time <= @as_of_time AND
     C2.version_time > C.version_time
LEFT OUTER JOIN Wheels W ON
     W.car_id = C.car_id AND
     W.version_time <= @as_of_time
LEFT OUTER JOIN Wheels W2 ON
     W2.car_id = C.car_id AND
     W2.wheel_id = W.wheel_id AND
     W2.version_time <= @as_of_time AND
     W2.version_time > W.version_time
WHERE
     C.version_time <= @as_of_time AND
     C2.car_id IS NULL AND
     W2.wheel_id IS NULL

- Tom H

---

只需要进行一些小的更改来统一命名（例如，将car_id更改为CarId），你的查询就可以运行了。 - ng5000

Answer 4

根据您的应用程序，您可能希望将版本控制推送到辅助审计表中，这些表将具有开始和可空的结束日期。在高流量 OLTP 中，我发现使用版本控制方法可能会变得相当昂贵，如果大多数读取拉取最新版本，则可能会受益。

通过使用开始和结束日期，您可以查询辅助表，查找介于开始和结束之间或大于开始日期的日期。

- JoshBerke

Answer 5

将每种情况的结束时间存储在表中确实使查询更容易表达，但是创建了维护完整性规则的问题，例如“同一辆车（轮子/...）的两种不同情况不能重叠”（仍然可以做到合理），以及“任何单个车辆/轮子/...的不同情况的时间系列中不能有空缺”（更麻烦）。

对于每种情况不在表中存储结束时间会强制你每次需要调用Allen运算符（重叠、合并、包含等）时都要编写自连接，这些运算符是由你唯一拥有的时间列所隐含的。

如果你需要处理这种时间上的事务，那么SQL就是一场噩梦。

而且，即使只是准确地用自然语言阐述这些查询也是一场噩梦。例如：你说你需要"As-of"查询，但你的例子排除了“as-of”10:05（轮Ver 3）和10:00（颜色黑）的情况。尽管这些情况明显也是“as-of”09:50。

你可能会对“时间数据和关系模型”感兴趣。请记住，该书的处理完全是抽象的，因为正如书中自己所说，“本书不涉及今天任何可用的技术”。

据说这个领域的另一本标准教材是Snodgrass写的，但我不知道书名。据说这两本书的作者对解决方案的看法完全相反。

- Erwin Smout