MySQL基于最近日期行的Inner Join实现及历史表查询问题

问题描述 #

现有AgentProducts表（字段：ProductId, AgentId, Fees），当该表更新时，产品的AgentId、Fees变更记录会存入AgentProductsHistory表（字段：ApId, ProductId, AgentId, AssignmentDate）。需针对指定日期DATE_IN_QUESTION生成某产品的费用报表，要求通过Inner Join关联两张表，先按ProductId分组并按AssignmentDate对AgentProductsHistory表排序，找到AssignmentDate刚大于DATE_IN_QUESTION的行。需明确：

1. 关联两张表的表达式内容是什么？
2. 是否必须为AgentProductsHistory表添加DateFrom和DateTo字段替代AssignmentDate？
   示例：当DATE_IN_QUESTION为2022年6月20日时，查询对应产品的费用。

一、关联两张表的表达式实现 #

要找到每个产品中AssignmentDate刚大于指定日期的第一条历史记录，可借助窗口函数先对历史记录分组排序，再关联主表。以下是SQL示例（假设AgentProductsHistory表包含Fees字段，若未包含则从AgentProducts表取当前Fees）：

-- 先对历史记录分组排序，筛选出每个产品符合条件的第一条记录
WITH RankedHistory AS (
    SELECT 
        ProductId,
        AgentId,
        Fees,
        AssignmentDate,
        -- 按产品分组，按AssignmentDate升序排序，取第一条大于指定日期的记录
        ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY AssignmentDate ASC) AS rn
    FROM AgentProductsHistory
    WHERE AssignmentDate > DATE '2022-06-20' -- 替换为实际的DATE_IN_QUESTION
)
-- 关联主表与筛选后的历史记录
SELECT 
    p.ProductId,
    h.AgentId,
    h.Fees
FROM AgentProducts p
INNER JOIN RankedHistory h 
    ON p.ProductId = h.ProductId 
    AND h.rn = 1 -- 关联条件：产品ID匹配，且是分组后的第一条记录
WHERE p.ProductId = '指定产品ID'; -- 替换为目标产品ID

这里的核心关联表达式是p.ProductId = h.ProductId AND h.rn = 1，通过窗口函数标记的rn=1来锁定每个产品中符合条件的第一条历史记录。

二、是否必须添加DateFrom和DateTo字段 #

不是必须的，但添加这两个字段会让查询逻辑更简洁、性能更优，具体分析：

• 不添加的场景：可以通过窗口函数（如LAG()/LEAD()）动态计算每条历史记录的生效时间段，比如用LEAD(AssignmentDate)获取下一条记录的日期作为当前记录的失效时间。但这种方式每次查询都要重新计算，数据量大或查询频繁时，性能会明显下降。
• 添加的场景：每次插入变更记录时，同步更新上一条记录的DateTo为当前记录的AssignmentDate，当前记录的DateFrom设为AssignmentDate。查询时直接通过DATE_IN_QUESTION BETWEEN DateFrom AND DateTo就能快速定位生效记录，无需重复计算窗口函数，逻辑更直观，查询效率更高。

如果数据量小、查询频率低，用窗口函数即可满足需求；若查询频繁或数据量大，建议添加DateFrom和DateTo字段优化。

内容的提问来源于stack exchange，提问作者rahulserver