有效地查找给定时间范围内的最后一个时间戳

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

您对“计算成本高昂的操作”的担忧：

• 获取行的成本比表达式求值的成本更高。
• 我推荐的访问索引是“覆盖”，因此它只查看索引。