代码之家 › 专栏 › 技术社区 › ng5000

如何有效地进行数据库查询?

database-design database sql

ng5000 · 技术社区 · 16 年前

请原谅这个长问题!

我们有两个数据库表,例如汽车和车轮。它们的关系在于一个轮子属于一辆车,一辆车有多个轮子。但是,车轮可以在不影响汽车“版本”的情况下进行更换。可以更新汽车记录(例如喷漆作业),而不影响车轮版本(即无级联更新)。

例如,car table当前如下所示:

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个版本。它的第一个轮子(轮子ID 1)没有改变。第二个车轮在10:05更换(例如喷漆)。

对于可以根据需要联接到其他表的查询,我如何有效地执行这些查询?请注意,这是一个新的数据库,我们拥有这个模式,可以更改它或添加审计表以使查询更容易。我们尝试过一种审计表方法(列:carid、carversion、wheelid、wheelversion、carvertime、wheelvertime),但它并没有真正改善我们的查询。

示例查询:按原样显示汽车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

5 回复 | 直到 16 年前

onedaywhen 16 年前

这种表在文献中被称为有效的时间状态表。人们普遍认为,每一行都应该通过有一个开始日期和一个结束日期来建立一个周期模型。基本上,SQL中的工作单元是行,一行应该完全定义实体;通过每行只有一个日期,不仅查询变得更复杂,而且您的设计也会因将子原子部分拆分到不同的行而受到影响。

正如欧文·斯穆特所提到的,关于这个问题的权威著作之一是:

Richard T.Snodgrass(1999年)。 Developing Time-Oriented Database Applications in SQL

它已经绝版了,但很高兴它可以免费下载pdf(上面的链接)。

我实际上已经阅读了它并实现了许多概念。大部分的文本都是ISO/ANSI标准SQL-92,虽然有些已经用专有的SQL语法实现,包括SQL Server(也可以下载),但我发现概念信息更有用。

Joe Celko还出版了一本书《在集合中思考:SQL中的辅助表、时态表和虚拟表》,很大程度上是从Snodgrass的工作中派生出来的,尽管我不得不说这两个表的分歧在哪里,但我认为Snodgrass的方法更可取。

我同意这些东西很难在我们目前拥有的SQL产品中实现。我们在将数据变为时间之前会考虑很久和困难;如果我们仅仅依靠“历史”就可以摆脱,那么我们会的。SQL Server中缺少SQL-92中的许多临时功能,例如间隔、重叠等。某些基本功能与顺序的“主键”一样,无法使用SQL Server中的检查约束来实现周期不重叠,这就需要触发器和/或UDF。

snodgrass的书是基于他对sql3的研究,这是对标准SQL的一个扩展,目的是为时态数据库提供更好的支持,但遗憾的是,这似乎在多年前就被有效地搁置了:(

Andomar 16 年前

当每行都有开始和结束时间时,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)

您可以将其存储在表值存储过程中,但这样做可能会造成严重的性能损失。

JoshBerke 16 年前

根据您的应用程序的不同,您可能希望将版本控制推送到二级审核表,该表将同时具有开始日期和可以为空的结束日期。我在一个高流量的OLTP中发现,使用版本控制方法可能会变得相当昂贵,如果您的大多数阅读都使用最新版本,那么这可能是有益的。

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

Erwin Smout 16 年前

将每种情况的结束时间存储在表中确实使查询更容易表达,但会造成维护完整性规则的问题,例如“同一辆车的两个不同情况(轮/轮)不能重叠”(仍然可以合理地执行)和“任何单个(车/轮)的不同情况的时间序列中不能存在漏洞”/“…)”(再版)。

如果不为每种情况将结束时间存储在表中,则每次需要在只有时间列所隐含的时间间隔上调用Allen运算符(重叠、合并、包含…)时,都会强制编写自联接。

如果需要做这种暂时的事情,SQL只是一场噩梦。

顺便说一句,即使只是用自然语言准确地表述这些查询也是一场噩梦。举例说明:您说过需要“截止”查询,但是您的示例排除了“截止”10:05(Wheelver 3)和10:00(彩色黑色)的情况。尽管事实上,这些情况也“从”09:50开始。

您可能对“时间数据和关系模型”的阅读感兴趣。请记住,这本书中的处理方法是完全抽象的,因为正如书本身所说,“这本书不是关于当今任何地方都可以使用的技术”。

另一本关于这个主题的标准教科书(我被告知)是由势利小人写的,但我不知道书名。我听说这两本书的作者对解决办法持完全相反的立场。

Tom H zenazn 16 年前

如果您有两行的同一车辆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