join – Access(Jet)SQL：TableB中的DateTime标记位于TableA中每

我有一个远程测量表(别名为A),其中包含一个基于DateTime标记,发送器ID和记录设备ID的复合主键.由于我无法控制的情况,我的SQL语言是Microsoft Access中的标准Jet数据库(用户将使用2007及更高版本).由于Transmitter ID,这些条目中只有大约200,000个与查询相关.

存在第二遥测表(别名B),其涉及具有单个DateTime主键的大约50,000个条目

第一步,我专注于从第二个表中找到第一个表中邮票的最接近的时间戳.

加入结果

我发现的怪癖……

……在调试期间一路走来

将JOIN逻辑作为FROM FirstTable写为A INNER JOIN SecondTable作为B ON(A.RecTStamp<> B.XTStamp OR A.RecTStamp = B.XTStamp)感觉真的很奇怪,正如@byrdzeye在评论中所指出的那样已经消失了)是一种交叉连接的形式.请注意,在上面的代码中用LEFT OUTER JOIN替换INNER JOIN似乎对返回的行的数量或标识没有影响.我似乎也无法放弃ON条款或者说ON(1 = 1).只使用逗号加入(而不是INNER或LEFT OUTER JOIN)会导致在此查询中返回的Count(select * from A)* Count(select * from B)行,而不是每个表A只有一行,如( A<> B OR A = B)显式JOIN返回.这显然不合适.在给定复合主键类型的情况下,FIRST似乎无法使用.

第二种JOIN风格虽然可以说更清晰,但却更慢.这可能是因为较大的表以及两个选项中找到的两个CROSS JOIN需要额外的两个内部JOIN.

除此之外：用MIN / MAX替换IIF子句似乎返回相同数量的条目.
MAX( – (B.XTStamp&LT = A.RecTStamp)* B.XTStamp)
适用于“之前”(MAX)时间戳,但不能直接用于“之后”(MIN),如下所示：
MIN( – (B.XTStamp> A.RecTStamp)* B.XTStamp)
因为FALSE条件的最小值始终为0.此0小于任何post-epoch DOUBLE(DateTime字段是Access的子集,并且此计算将字段转换为). IIF和MIN / MAX方法为AfterXTStamp值建议的替代方法有效,因为除零(FALSE)会生成空值,其中MIN和MAX的聚合函数会跳过.

下一步

更进一步,我希望在第二个表中找到第一个表中直接位于第一个表中时间戳的时间戳,并根据到这些点的时间距离对第二个表中的数据值进行线性插值(即,如果时间戳来自第一个表是“之前”和“之后”之间的25％,我希望25％的计算值来自与“之后”点相关的第二个表值数据和来自“之前”的75％ ).使用修改后的连接类型作为内部胆量的一部分,并在下面的建议答案之后生成…

SELECT
        AvgGPS.XmitID,StrDateIso8601Msec(AvgGPS.RecTStamp) AS RecTStamp_ms,-- StrDateIso8601MSec is a VBA function returning a TEXT string in yyyy-mm-dd hh:nn:ss.lll format
        AvgGPS.ReceivID,RD.Receiver_Location_Description,RD.Lat AS Receiver_Lat,RD.Lon AS Receiver_Lon,AvgGPS.Before_Lat * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lat * AvgGPS.AfterWeight AS Xmit_Lat,AvgGPS.Before_Lon * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lon * AvgGPS.AfterWeight AS Xmit_Lon,AvgGPS.RecTStamp AS RecTStamp_basic
    FROM ( SELECT 
        AfterTimestampID.RecTStamp,AfterTimestampID.XmitID,AfterTimestampID.ReceivID,GPSBefore.BeforeXTStamp,GPSBefore.Latitude AS Before_Lat,GPSBefore.Longitude AS Before_Lon,GPSAfter.AfterXTStamp,GPSAfter.Latitude AS After_Lat,GPSAfter.Longitude AS After_Lon,( (AfterTimestampID.RecTStamp - GPSBefore.XTStamp) / (GPSAfter.XTStamp - GPSBefore.XTStamp) ) AS AfterWeight
        FROM (
            (SELECT 
                ReceiverRecord.RecTStamp,(SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
             FROM FirstTable AS ReceiverRecord 
             -- WHERE ReceiverRecord.XmitID IN (select XmitID from ValidXmitters)
             GROUP BY RecTStamp,XmitID
            ) AS AfterTimestampID INNER JOIN SecondTable AS GPSAfter ON AfterTimestampID.AfterXmit_ID = GPSAfter.X_ID
        ) INNER JOIN SecondTable AS GPSBefore ON AfterTimestampID.AfterXmit_ID = GPSBefore.X_ID + 1
    ) AS AvgGPS INNER JOIN ReceiverDetails AS RD ON (AvgGPS.ReceivID = RD.ReceivID) AND (AvgGPS.RecTStamp BETWEEN RD.Beginning AND RD.Ending)
    ORDER BY AvgGPS.RecTStamp,AvgGPS.ReceivID;

…返回152928条记录,符合(至少大约)最终预期记录数.我的i7-4790,16GB RAM,无SSD,Win 8.1 Pro系统的运行时间大概是5-10分钟.

参考文献1：MS Access Can Handle Millisecond Time Values–Really和 accompanying source file [08080011.txt]

解决方法

首先加入

您的IIF字段选择可能会因使用Switch statement而受益.有时情况似乎是这样,特别是对于事物SQL,当在SELECT的主体中进行简单的比较时,SWITCH(在典型的SQL中通常称为CASE)非常快.您的情况下的语法几乎相同,但可以扩展一个开关以覆盖一个字段中的大量比较.需要考虑的事情.

SWITCH (
    expr1,val1,expr2,val2,val3        -- default value or "else"
  )

在较大的陈述中,开关也可以帮助提高可读性.在上下文中：

MAX(SWITCH(B.XTStamp <= A.RecTStamp,--alternatively MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp) as BeforeXTStamp,MIN(SWITCH(B.XTStamp>A.RecTStamp,Null)) as AfterXTStamp

至于连接本身,我认为(A.RecTStamp<> B.XTStamp OR A.RecTStamp = B.XTStamp)与你想要做的一样好.它不是那么快,但我不指望它也是如此.

第二次加入

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!