我必须查询一个包含几百万行的表,我想以最优化的方式进行。
让我们假设我们想要控制对具有多个放映室的电影院的访问并像这样保存它:
AccessRecord
(TicketId,
TicketCreationTimestamp,
TheaterId,
ShowId,
MovieId,
SeatId,
CheckInTimestamp)
为简化起见,数据类型“bigint”和“Timestamp”的“Id”列是“datetime”。门票随时出售,人们随机进入剧院。主键(也是唯一的)是 TicketId。
我想为每个电影和剧院和节目(时间)获取访问剧院观看电影的第一个和最后一个人的 AccessRecord 信息。如果两个签到同时发生,我只需要 1 个,其中任何一个。
我的解决方案是在子查询中连接 PK 和分组列以获取行:
select
AccessRecord.*
from
AccessRecord
inner join(
select
MAX(CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25), TicketId)) as MaxKey,
MIN(CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25), TicketId)) as MinKey
from
AccessRecord
group by
MovieId,
TheaterId,
ShowId
) as MaxAccess
on CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25), TicketId) = MaxKey
or CONVERT(nvarchar(25),CheckInTimestamp, 121) + CONVERT(varchar(25), TicketId) = MinKey
转换 121 是数据时间的规范表达式,如下所示:aaaa-mm-dd hh:mi:ss.mmm(24h),因此按字符串数据类型排序它将给出与按日期时间排序的结果相同的结果。
如您所见,此连接不是很优化,有什么想法吗?
更新我如何测试不同的解决方案 :
我已经使用 SQL Server 2008 R2 在一个真实的数据库中测试了你所有的答案,其中有一个超过 300 万行的表,以选择正确的答案。
如果我只得到第一个或最后一个访问的人:
如果我执行相同的访问但按分组列排序结果:
如果我得到两个(第一个和最后一个)以有序结果访问的人:
其余的解决方案(甚至我的)在第一次测试中持续了 60 多秒,所以我取消了它。
最佳答案
试试这个:
select a.*
from AccessRecord a
where not exists(
select 'next'
from AccessRecord a2
where a2.movieid = a.movieid
and a2.theaterid = a.theaterid
and a2.showid = a.showid
and a2.checkintimestamp > a.checkintimestamp
)
通过这种方式,您可以选择最后一行作为同一部电影、teather、show 的时间戳。
每行的票(我想)都不同