如何对拥有完全相同Lot子集的Orders订单进行分组？

核心思路是给每个订单关联的Lot集合生成唯一的“指纹标识”，通过这个标识直接在SQL层面完成分组，替代低效的循环处理和冗余存储方案。

具体实现方案：生成批次集合的哈希标识 #

因为集合是无序的，必须先对关联的Lot ID排序，再生成唯一标识，避免因Lot顺序不同导致的标识差异。以下是不同数据库的实现示例：

PostgreSQL 示例 #

-- 生成每个订单的批次集合哈希
WITH OrderLotHashes AS (
    SELECT
        ol.IdOrder,
        MD5(string_agg(l.Id::TEXT, ',' ORDER BY l.Id)) AS LotSetHash
    FROM OrdersLot ol
    JOIN Lot l ON ol.IdLot = l.Id
    GROUP BY ol.IdOrder
)
-- 按哈希分组，得到同批次集合的订单组
SELECT
    LotSetHash,
    ARRAY_AGG(o.Id) AS OrderIds
FROM OrderLotHashes olh
JOIN Orders o ON olh.IdOrder = o.Id
GROUP BY LotSetHash;

MySQL 示例 #

-- 生成每个订单的批次集合哈希
WITH OrderLotHashes AS (
    SELECT
        ol.IdOrder,
        MD5(GROUP_CONCAT(l.Id ORDER BY l.Id SEPARATOR ',')) AS LotSetHash
    FROM OrdersLot ol
    JOIN Lot l ON ol.IdLot = l.Id
    GROUP BY ol.IdOrder
)
-- 按哈希分组，得到同批次集合的订单组
SELECT
    LotSetHash,
    GROUP_CONCAT(o.Id) AS OrderIds
FROM OrderLotHashes olh
JOIN Orders o ON olh.IdOrder = o.Id
GROUP BY LotSetHash;

方案优势 #

• 无需修改现有表结构，避免在Orders表中存储冗余Lot信息带来的数据不一致和性能损耗
• 直接通过SQL完成分组，省去应用层循环处理的开销，数据量越大性能优势越明显
• 哈希标识长度固定，分组查询效率高，适合大数据量场景

性能优化建议 #

• 给OrdersLot(IdOrder, IdLot)建立联合索引，加速关联查询和分组计算
• 若对哈希冲突要求极低，可替换MD5为SHA256等更长的哈希算法（MD5足以覆盖大部分业务场景）
• 若需频繁查询分组结果，可将LotSetHash作为冗余字段存入Orders表，通过触发器或ETL流程维护（当订单关联的Lot变化时自动更新哈希值），进一步提升查询速度

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