MySQL关联子查询日期范围查询无结果问题排查

场景说明 #

需要从product_revenues和product_costs表中，匹配近12个月内每条收入记录对应的同一团队、同一产品的最新成本起始日期，并计算利润（例如2023年9月的收入需匹配2023年3月的最新成本）。

表结构与测试数据 #

product_revenues表

注：created为时间戳，对应2023-09-13

product_costs表

原查询语句 #

SELECT 
    MONTH(FROM_UNIXTIME(product_revenues.created)) AS revenueMonth,
    SUM(product_revenues.amount) AS totalRevenue,
    SUM(product_costs.amount) AS totalCost,
    SUM(product_revenues.amount - product_costs.amount) AS totalProfit
FROM
    sample.product_revenues
        JOIN
    sample.product_costs ON product_costs.start_date <= FROM_UNIXTIME(product_revenues.created)
        AND product_costs.start_date = (SELECT 
            Max(start_date)
        FROM
            product_costs
        WHERE
            start_date <= FROM_UNIXTIME(product_revenues.created))
WHERE
    product_revenues.team_id = 1
        AND product_costs.team_id = 1
        AND product_revenues.stripe_product_id = 'prod_1234'
        AND product_costs.stripe_product_id = 'prod_1234'
        AND FROM_UNIXTIME(product_revenues.created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
GROUP BY revenueMonth
ORDER BY revenueMonth ASC

问题现象与期望结果 #

• 期望结果：

• 异常现象：当product_costs.start_date改为2023-04-13及之后时能正常返回结果，但设置为2023-03-13及更早时无结果，且查询日期范围为近12个月。

错误原因 #

1. 子查询未关联团队与产品维度：原子查询仅根据start_date <= 收入日期筛选最大成本日期，但未限定team_id和stripe_product_id，导致返回的是全表所有符合日期条件的最大成本日期，而非当前收入记录对应的同一团队、同一产品的最新成本。
   • 例如：若存在其他团队/产品的成本日期（如2023-04-13）晚于当前团队的2023-03-13，子查询会返回2023-04-13，但当前团队无此日期的成本记录，最终JOIN无匹配结果。
2. 条件冗余且逻辑分散：team_id和stripe_product_id的匹配条件同时出现在JOIN和WHERE中，不仅冗余，还可能导致过滤逻辑混乱。

修正方案 #

方案1：修正关联子查询 #

在子查询中添加team_id和stripe_product_id的关联条件，确保仅查询当前收入对应的团队和产品的最新成本：

SELECT 
    MONTH(FROM_UNIXTIME(r.created)) AS revenueMonth,
    SUM(r.amount) AS totalRevenue,
    SUM(c.amount) AS totalCost,
    SUM(r.amount - c.amount) AS totalProfit
FROM
    sample.product_revenues r
JOIN sample.product_costs c 
    ON c.team_id = r.team_id
    AND c.stripe_product_id = r.stripe_product_id
    AND c.start_date <= FROM_UNIXTIME(r.created)
    AND c.start_date = (
        SELECT MAX(start_date)
        FROM sample.product_costs
        WHERE team_id = r.team_id
          AND stripe_product_id = r.stripe_product_id
          AND start_date <= FROM_UNIXTIME(r.created)
    )
WHERE
    r.team_id = 1
    AND r.stripe_product_id = 'prod_1234'
    AND FROM_UNIXTIME(r.created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
GROUP BY revenueMonth
ORDER BY revenueMonth ASC

方案2：使用窗口函数（更高效） #

通过ROW_NUMBER()窗口函数预先为每个团队、产品的成本记录按日期排序，直接获取最新的成本记录，避免关联子查询的性能损耗：

WITH ranked_costs AS (
    SELECT 
        team_id,
        stripe_product_id,
        amount,
        start_date,
        ROW_NUMBER() OVER (
            PARTITION BY team_id, stripe_product_id 
            ORDER BY start_date DESC
        ) AS rn
    FROM sample.product_costs
)
SELECT 
    MONTH(FROM_UNIXTIME(r.created)) AS revenueMonth,
    SUM(r.amount) AS totalRevenue,
    SUM(rc.amount) AS totalCost,
    SUM(r.amount - rc.amount) AS totalProfit
FROM sample.product_revenues r
JOIN ranked_costs rc 
    ON rc.team_id = r.team_id
    AND rc.stripe_product_id = r.stripe_product_id
    AND rc.start_date <= FROM_UNIXTIME(r.created)
    AND rc.rn = 1
WHERE
    r.team_id = 1
    AND r.stripe_product_id = 'prod_1234'
    AND FROM_UNIXTIME(r.created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
GROUP BY revenueMonth
ORDER BY revenueMonth ASC

额外说明 #

• 若存在收入记录无对应成本的场景，建议将JOIN改为LEFT JOIN，并对SUM(c.amount)使用COALESCE(SUM(c.amount), 0)避免NULL值影响计算。
• 注意DATE_SUB(CURDATE(), INTERVAL 12 MONTH)的计算逻辑：若当前日期为2024-09-15，则近12个月的起始日期为2023-09-15，此时2023-09-13的收入记录会被过滤，需根据实际业务需求调整日期范围（例如使用DATE_SUB(CURDATE(), INTERVAL 1 YEAR)或直接指定固定范围）。

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