Join

Access (Jet) SQL：TableB 中的 DateTime 標記位於 TableA 中每個 DateTime 標記的兩側

  • October 17, 2015

第一句話

您可以放心地忽略以下（包括）JOIN 部分：如果您只是想破解程式碼，就開始吧。背景和結果只是作為上下文。如果您想查看程式碼最初的樣子，請查看 2015-10-06 之前的編輯歷史記錄。

客觀的

最終，我想根據表中可用 GPS 數據的 DateTime 標記計算發射器（X或）的插值 GPS 座標，該數據直接位於表中觀察的兩側。Xmit``SecondTable``FirstTable

我完成最終目標的直接目標是弄清楚如何最好地加入FirstTable以SecondTable獲得那些側翼時間點。稍後我可以使用該資訊來計算中間 GPS 座標，假設沿著 equirectangular 座標系進行線性擬合（花哨的話，我不在乎地球是這個比例的球體）。

問題

  1. 是否有更有效的方法來生成最接近的前後時間戳？
  • 由我自己修復，只需抓住“之後”，然後只獲取與“之後”相關的“之前”。
  1. 有沒有更直覺的不涉及(A<>B OR A=B)結構的方式。
  • Byrdzeye提供了基本的替代方案，但我的“真實世界”體驗與他執行相同的所有 4 種加入策略並不相符。但完全歸功於他解決替代連接方式。
  1. 您可能有的任何其他想法、技巧和建議。
  • 到目前為止，byrdzeye和Phrancis在這方面都非常有幫助。我發現Phrancis 的建議非常出色，並在關鍵階段提供了幫助，所以我將在這裡給他優勢。

對於問題 3，我仍然希望得到任何額外的幫助。 要點反映了我認為在個別問題上對我幫助最大的人。

表定義

半視覺表示

第一表

Fields
 RecTStamp | DateTime  --can contain milliseconds via VBA code (see Ref 1) 
 ReceivID  | LONG
 XmitID    | TEXT(25)
Keys and Indices
 PK_DT     | Primary, Unique, No Null, Compound
   XmitID    | ASC
   RecTStamp | ASC
   ReceivID  | ASC
 UK_DRX    | Unique, No Null, Compound
   RecTStamp | ASC
   ReceivID  | ASC
   XmitID    | ASC

第二表

Fields
 X_ID      | LONG AUTONUMBER -- seeded after main table has been created and already sorted on the primary key
 XTStamp   | DateTime --will not contain partial seconds
 Latitude  | Double   --these are in decimal degrees, not degrees/minutes/seconds
 Longitude | Double   --this way straight decimal math can be performed
Keys and Indices
 PK_D      | Primary, Unique, No Null, Simple
   XTStamp   | ASC
 UIDX_ID   | Unique, No Null, Simple
   X_ID      | ASC

ReceiverDetails表

Fields
 ReceivID                      | LONG
 Receiver_Location_Description | TEXT -- NULL OK
 Beginning                     | DateTime --no partial seconds
 Ending                        | DateTime --no partial seconds
 Lat                           | DOUBLE
 Lon                           | DOUBLE
Keys and Indicies
 PK_RID  | Primary, Unique, No Null, Simple
   ReceivID | ASC

ValidXmitters表

Field (and primary key)
 XmitID    | TEXT(25) -- primary, unique, no null, simple

SQL小提琴…

…這樣您就可以使用表定義和程式碼這個問題是針對 MSAccess 的，但正如 Phrancis 指出的那樣，Access 沒有 SQL 小提琴風格。因此，您應該可以根據Phrancis 的回答到這裡查看我的表定義和程式碼：http://sqlfiddle.com/#!6/e9942/4（外部連結）

加入：開始

我目前的“內膽”加入策略

首先使用列順序和復合主鍵創建一個 FirstTable_rekeyed (RecTStamp, ReceivID, XmitID)all indexed/sorted ASC。我還分別在每一列上創建了索引。然後像這樣填充它。

INSERT INTO FirstTable_rekeyed (RecTStamp, ReceivID, XmitID)
 SELECT DISTINCT ROW RecTStamp, ReceivID, XmitID
 FROM FirstTable
 WHERE XmitID IN (SELECT XmitID from ValidXmitters)
 ORDER BY RecTStamp, ReceivID, XmitID;

上面的查詢用 153006 條記錄填充新表，並在 10 秒左右的時間內返回。

使用 TOP 1 子查詢方法時，當整個方法包含在“SELECT Count(*) FROM (…)”中時，以下操作在一兩秒內完成

SELECT 
   ReceiverRecord.RecTStamp, 
   ReceiverRecord.ReceivID, 
   ReceiverRecord.XmitID,
   (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_rekeyed AS ReceiverRecord
   -- INNER JOIN SecondTable AS XmitGPS ON (ReceiverRecord.RecTStamp < XmitGPS.XTStamp)
        GROUP BY RecTStamp, ReceivID, XmitID;
-- No separate join needed for the Top 1 method, but it would be required for the other methods. 
-- Additionally no restriction of the returned set is needed if I create the _rekeyed table.
-- May not need GROUP BY either. Could try ORDER BY.
-- The three AfterXmit_ID alternatives below take longer than 3 minutes to complete (or do not ever complete).
 -- FIRST(XmitGPS.X_ID)
 -- MIN(XmitGPS.X_ID)
 -- MIN(SWITCH(XmitGPS.XTStamp > ReceiverRecord.RecTStamp, XmitGPS.X_ID, Null))

以前的“內膽” JOIN 查詢

首先（快速……但還不夠好）

SELECT 
 A.RecTStamp,
 A.ReceivID,
 A.XmitID,
 MAX(IIF(B.XTStamp<= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,
 MIN(IIF(B.XTStamp > A.RecTStamp,B.XTStamp,Null)) as AfterXTStamp
FROM FirstTable as A
INNER JOIN SecondTable as B ON 
 (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)
GROUP BY A.RecTStamp, A.ReceivID, A.XmitID
 -- alternative for BeforeXTStamp MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
 -- alternatives for AfterXTStamp (see "Aside" note below)
 -- 1.0/(MAX(1.0/(-(B.XTStamp>A.RecTStamp)*B.XTStamp)))
 -- -1.0/(MIN(1.0/((B.XTStamp>A.RecTStamp)*B.XTStamp)))

第二（較慢）

SELECT
 A.RecTStamp, AbyB1.XTStamp AS BeforeXTStamp, AbyB2.XTStamp AS AfterXTStamp
FROM (FirstTable AS A INNER JOIN 
 (select top 1 B1.XTStamp, A1.RecTStamp 
  from SecondTable as B1, FirstTable as A1
  where B1.XTStamp<=A1.RecTStamp
  order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)
ON A.RecTStamp = AbyB1.RecTStamp) INNER JOIN 
 (select top 1 B2.XTStamp, A2.RecTStamp 
  from SecondTable as B2, FirstTable as A2
  where B2.XTStamp>A2.RecTStamp
  order by B2.XTStamp ASC) AS AbyB2 --MIN (time points after)
ON A.RecTStamp = AbyB2.RecTStamp;

背景

我有一個包含近 100 萬個條目的遙測表（別名為 A），其中包含基於DateTime標記、發射器 ID 和記錄設備 ID 的複合主鍵。由於我無法控制的情況，我的 SQL 語言是 Microsoft Access 中的標準 Jet DB（使用者將使用 2007 及更高版本）。由於 Transmitter ID，這些條目中只有大約 200,000 個與查詢相關。

有第二個遙測表（別名 B）涉及大約 50,000 個具有單個DateTime主鍵的條目

第一步，我專注於從第二個表中找到與第一個表中的標記最接近的時間戳。

加入結果

我發現的怪癖…

…在調試過程中

JOIN編寫邏輯就像FROM FirstTable as A INNER JOIN SecondTable as B ON (A.RecTStamp&lt;&gt;B.XTStamp OR A.RecTStamp=B.XTStamp)@byrdzeye在評論中指出的那樣（已經消失）是一種交叉連接形式，感覺真的很奇怪。請注意，在上面的程式碼中替換似乎LEFT OUTER JOIN對INNER JOIN返回的行的數量或身份沒有影響。我似乎也不能離開 ON 子句或說ON (1=1). 僅使用逗號連接（而不是INNERor LEFT OUTER JOIN）會導致Count(select * from A) * Count(select * from B)在此查詢中返回行，而不是每個表 A 只返回一行，因為 (A<>B OR A=B) 顯式JOIN返回。這顯然是不合適的。FIRST給定複合主鍵類型，似乎無法使用。

第二種JOIN風格雖然可以說更易讀，但速度較慢。JOIN這可能是因為對於較大的表以及CROSS JOIN在兩個選項中找到的兩個 s 都需要額外的兩個 inner 。

另外：用/替換IIF子句似乎返回相同數量的條目。 適用於“之前”（）時間戳，但不能直接用於“之後”（），如下所示： 因為該條件的最小值始終為 0 。此 0 小於任何後紀元（欄位是 Access 中的子集，並且此計算將欄位轉換為）。和/方法 為 AfterXTStamp 值建議的替代方法起作用，因為除以零 ( ) 生成空值，聚合函式 MIN 和 MAX 會跳過這些值。MIN``MAX

MAX(-(B.XTStamp&lt;=A.RecTStamp)*B.XTStamp)
MAX``MIN

MIN(-(B.XTStamp&gt;A.RecTStamp)*B.XTStamp)
FALSE``DOUBLE``DateTime``IIF``MIN``MAX``FALSE

下一步

更進一步，我希望在第二個表中找到直接位於第一個表中的時間戳兩側的時間戳，並根據到這些點的時間距離對第二個表中的數據值進行線性插值（即，如果時間戳來自第一個表是“之前”和“之後”之間的 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, 
               ReceiverRecord.ReceivID, 
               ReceiverRecord.XmitID,
              (SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp &lt; 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, ReceivID, 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可以處理毫秒時間值–Really和隨附的源文件$$ 08080011.txt $$

我必須首先讚揚你用 Access DB 做這樣的事情的勇氣，根據我的經驗，這很難做任何類似 SQL 的事情。無論如何，繼續審查。

首次加入

您的IIF欄位選擇可能會受益於使用Switch 語句。有時情況似乎是這樣，尤其是對於 SQL 來說，當只是在 a 的主體中進行簡單比較時，a SWITCH（在典型的 SQL 中更常見CASE）是相當快的SELECT。儘管可以擴展開關以涵蓋一個欄位中的大量比較，但您的情況下的語法幾乎相同。需要考慮的事情。

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

在較大的語句中，開關還可以提高可讀性。在上下文中：

 MAX(SWITCH(B.XTStamp &lt;= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,
 --alternatively MAX(-(B.XTStamp&lt;=A.RecTStamp)*B.XTStamp) as BeforeXTStamp,
 MIN(SWITCH(B.XTStamp&gt;A.RecTStamp,B.XTStamp,Null)) as AfterXTStamp

至於加入本身，(A.RecTStamp&lt;&gt;B.XTStamp OR A.RecTStamp=B.XTStamp)考慮到您正在嘗試做的事情，我認為與您將獲得的一樣好。它並沒有那麼快，但我也不希望它那麼快。

第二次加入

你說這個比較慢。從程式碼的角度來看，它的可讀性也較低。考慮到 1 和 2 之間同樣令人滿意的結果集，我會說選擇 1。至少很明顯你正在嘗試這樣做。子查詢通常不是很快（儘管通常是不可避免的），尤其是在這種情況下，您會在每個查詢中加入額外的連接，這肯定會使執行計劃複雜化。

一句話，我看到你使用舊的 ANSI-89 連接語法。最好避免這種情況，使用更現代的連接語法，性能將相同或更好，並且它們不那麼模棱兩可或更容易閱讀，更難出錯。

FROM (FirstTable AS A INNER JOIN 
 (select top 1 B1.XTStamp, A1.RecTStamp 
  from SecondTable as B1
  inner join FirstTable as A1
    on B1.XTStamp &lt;= A1.RecTStamp
  order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)

命名事物

我認為你的東西被命名的方式充其量是無益的，最壞的情況是神秘的。A, B, A1, B1等作為表別名我認為可能會更好。另外，我認為欄位名稱不是很好，但我意識到您可能無法控制它。我將快速引用The Codeless Code關於命名事物的主題，然後將其保留在…

“謾罵！” 女祭司回答。“動詞你的髒話名詞！”

“後續步驟”查詢

我無法理解它是如何編寫的，我不得不將它帶到文本編輯器並進行一些樣式更改以使其更具可讀性。我知道 Access 的 SQL 編輯器非常笨重，所以我通常在像 Notepad++ 或 Sublime Text 這樣的好編輯器中編寫查詢。我應用了一些樣式更改以使其更具可讀性：

  • 縮進 4 個空格而不是 2 個空格
  • 數學和比較運算符周圍的空格
  • 大括號和縮進的放置更自然（我使用了 Java 風格的大括號，但也可以是 C 風格的，根據您的喜好）

事實證明，這確實是一個非常複雜的查詢。為了理解它，我必須從最裡面的查詢開始，即您的ID數據集，我理解它與您的 First Join 相同。它返回在您感興趣的設備子集中，前/後時間戳最接近的設備的 ID 和時間戳。所以，ID與其為什麼不呼叫它ClosestTimestampID。

您的Det加入僅使用一次：

在此處輸入圖像描述

其餘時間，它只加入您已經擁有的值ClosestTimestampID。因此，我們應該能夠這樣做：

   ) AS ClosestTimestampID
   INNER JOIN SecondTable AS TL1 
       ON ClosestTimestampID.BeforeXTStamp = TL1.XTStamp) 
   INNER JOIN SecondTable AS TL2 
       ON ClosestTimestampID.AfterXTStamp = TL2.XTStamp
   WHERE ClosestTimestampID.XmitID IN (&lt;limited subset S&gt;)

也許不會帶來巨大的性能提升，但我們可以做的任何事情來幫助可憐的 Jet DB 優化器都會有所幫助！

我不能動搖你用來插值的計算/算法可以做得更好的感覺BeforeWeight，AfterWeight但不幸的是我對這些不太好。

避免崩潰的一個建議（儘管根據您的應用程序並不理想）是將嵌套子查詢分解為它們自己的表並在需要時更新這些表。我不確定您需要多久刷新一次源數據，但如果不是太頻繁，您可能會考慮編寫一些 VBA 程式碼來安排表和派生表的更新，然後將最外層的查詢留給 pull來自這些表而不是原始來源。只是一個想法，就像我說的不理想，但考慮到工具你可能別無選擇。

一切都在一起：

SELECT
   InGPS.XmitID,
   StrDateIso8601Msec(InGPS.RecTStamp) AS RecTStamp_ms,
      -- StrDateIso8601MSec is a VBA function returning a TEXT string in yyyy-mm-dd hh:nn:ss.lll format
   InGPS.ReceivID,
   RD.Receiver_Location_Description,
   RD.Lat AS Receiver_Lat,
   RD.Lon AS Receiver_Lon,
   InGPS.Before_Lat * InGPS.BeforeWeight + InGPS.After_Lat * InGPS.AfterWeight AS Xmit_Lat,
   InGPS.Before_Lon * InGPS.BeforeWeight + InGPS.After_Lon * InGPS.AfterWeight AS Xmit_Lon,
   InGPS.RecTStamp AS RecTStamp_basic
FROM (
   SELECT 
       ClosestTimestampID.RecTStamp,
       ClosestTimestampID.XmitID,
       ClosestTimestampID.ReceivID,
       ClosestTimestampID.BeforeXTStamp, 
       TL1.Latitude AS Before_Lat, 
       TL1.Longitude AS Before_Lon,
       (1 - ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) 
           / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp))) AS BeforeWeight,
       ClosestTimestampID.AfterXTStamp, 
       TL2.Latitude AS After_Lat, 
       TL2.Longitude AS After_Lon,
       (     (ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) 
           / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp)) AS AfterWeight
       FROM (((
           SELECT 
               A.RecTStamp, 
               A.ReceivID, 
               A.XmitID,
               MAX(SWITCH(B.XTStamp &lt;= A.RecTStamp, B.XTStamp, Null)) AS BeforeXTStamp,
               MIN(SWITCH(B.XTStamp &gt; A.RecTStamp, B.XTStamp, Null)) AS AfterXTStamp
           FROM FirstTable AS A
           INNER JOIN SecondTable AS B 
               ON (A.RecTStamp &lt;&gt; B.XTStamp OR A.RecTStamp = B.XTStamp)
           WHERE A.XmitID IN (&lt;limited subset S&gt;)
           GROUP BY A.RecTStamp, ReceivID, XmitID
       ) AS ClosestTimestampID
       INNER JOIN FirstTable AS Det 
           ON (Det.XmitID = ClosestTimestampID.XmitID) 
           AND (Det.ReceivID = ClosestTimestampID.ReceivID) 
           AND (Det.RecTStamp = ClosestTimestampID.RecTStamp)) 
       INNER JOIN SecondTable AS TL1 
           ON ClosestTimestampID.BeforeXTStamp = TL1.XTStamp) 
       INNER JOIN SecondTable AS TL2 
           ON ClosestTimestampID.AfterXTStamp = TL2.XTStamp
       WHERE Det.XmitID IN (&lt;limited subset S&gt;)
   ) AS InGPS
INNER JOIN ReceiverDetails AS RD 
   ON (InGPS.ReceivID = RD.ReceivID) 
   AND (InGPS.RecTStamp BETWEEN &lt;valid parameters from another table&gt;)
ORDER BY StrDateIso8601Msec(InGPS.RecTStamp), InGPS.ReceivID;

引用自：https://dba.stackexchange.com/questions/116552

相關問答

Database-Design

使用聚合訪問 Web 應用程序查詢 3 個表 - 需要一種解決方法

  • March 1, 2022
檢視問答
Postgresql

使用一列中的值作為列名並彙總另一表中的數據

  • March 12, 2022
檢視問答
Postgresql

對具有多個連接的不同行求和

  • September 10, 2021
檢視問答
Ms-Access

如何解釋儲存為 10 位負整數的時間戳？

  • March 28, 2021
檢視問答
Postgresql

相關實體的獨立聚合，同時也按相關排序（在單個語句中）

  • October 18, 2020
檢視問答
Mysql

將左連接的多行結果合併到一個唯一的單元格中

  • September 28, 2020
檢視問答