有效地找到給定時間範圍內的最後一個時間戳

Question

問題出在一個假設的線上商店；我將簡化我的特定問題的數據。

假設有兩個表格：一個儲存每位客戶的註冊日期

CREATE TABLE customers (
    customerID int,
    register DATE,
);

，第二個保存客戶的所有線上訪問

CREATE TABLE visits (
    customerID int,
    visit DATE,
);

對於每個用戶，我希望找到用戶在註冊後 7 天內訪問線上商店的最後一天，以最有效的方式。 如果用戶註冊後未造訪過商店，我應該在註冊後 7 天內返回 NULL 作為最後造訪日期。

我當然可以LEFT JOIN客戶透過客戶ID進行訪問，並將訪問日期過濾為註冊日之間 和加七，最後得到每個客戶的最大日期。 然而，初始連接會產生一個巨大的表，我想避免這種計算成本高昂的操作。

非常感謝您的想法！

Answer 1

這可能是最有效的：

SELECT  c.customerID, c.register,
        ( SELECT MAX(v.visit)
            FROM visits AS v
            WHERE v.customerID = c.customerID
              AND v.visit < c.register + INTERVAL 7 DAY
        ) AS last_date
    FROM customers AS c;

索引：

customers:  PRIMARY KEY(customerId)  -- is this what  you have?
visits:     INDEX(customerID, visit)  -- or perhaps PRIMARY KEY

您對「計算成本高昂的操作」的擔憂：

• 取得行的成本比表達式求值的成本更高。
• 我推薦的訪問索引是“覆蓋”，因此它只查看索引。