batchTransaction表聚合产品最新交易值时SQL兼容报错排查

需要从batchTransaction表聚合所有产品的最新交易值，涉及列：

• id - 唯一标识
• productId - 非唯一
• transactionValue - 交易值
• transactionDate - 交易日期

需求：每个产品可能有多条交易记录，仅基于最新的交易记录进行聚合，在指定日期汇总所有产品的总transactionValue。

原SQL语句：

SELECT SUM(transactionQuantity)
FROM batchTransaction
WHERE (id, dateCreated) IN (
    SELECT id, MAX(dateCreated)
    FROM batchTransaction
    WHERE AND transactionDate < 1675189800000
    GROUP BY productId
);

执行报错：this is incompatible with sql_mode=only_full_group_by

报错原因 #

only_full_group_by模式要求GROUP BY子句必须包含SELECT列表中所有非聚合函数的列。原SQL的子查询中，GROUP BY productId，但SELECT了非聚合、非GROUP BY列id，违反了该模式规则，导致报错。此外原SQL还有两处笔误：

1. 外层查询的transactionQuantity应为需求中的transactionValue
2. 子查询的WHERE子句多了一个多余的AND

修正方案 #

方案一：窗口函数（推荐） #

用窗口函数直接为每个产品标记最新交易记录，筛选后求和，逻辑清晰且性能更优：

SELECT SUM(transactionValue) AS totalLatestTransactionValue
FROM (
    SELECT 
        transactionValue,
        ROW_NUMBER() OVER (PARTITION BY productId ORDER BY transactionDate DESC) AS rn
    FROM batchTransaction
    WHERE transactionDate < 1675189800000
) t
WHERE rn = 1;

ROW_NUMBER()按productId分组，每组按transactionDate倒序排序，取每组第一条（rn=1）即为最新交易记录，最后汇总求和。

方案二：关联子查询筛选最新记录 #

通过关联子查询获取每个产品的最新交易日期，再匹配对应交易记录：

SELECT SUM(bt.transactionValue) AS totalLatestTransactionValue
FROM batchTransaction bt
WHERE bt.transactionDate < 1675189800000
AND bt.transactionDate = (
    SELECT MAX(transactionDate)
    FROM batchTransaction
    WHERE productId = bt.productId
    AND transactionDate < 1675189800000
);

方案三：GROUP BY关联查询 #

先获取每个产品的最新交易日期，再关联原表获取对应交易值：

SELECT SUM(bt.transactionValue) AS totalLatestTransactionValue
FROM batchTransaction bt
JOIN (
    SELECT 
        productId,
        MAX(transactionDate) AS latestDate
    FROM batchTransaction
    WHERE transactionDate < 1675189800000
    GROUP BY productId
) latest ON bt.productId = latest.productId AND bt.transactionDate = latest.latestDate
WHERE bt.transactionDate < 1675189800000;

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