DB2优化查询：批量获取指定会员最新3条订单

现有orders表结构及数据 #

+----------+-------------+---------+--------------+
| Name     | orderDate   | memberId| OrderId      |
+----------+-------------+---------+--------------+
|      Tom |  01-01-2023 |     ABC | 111          |
|     Dick |  01-01-2023 |     XYZ | 222          |
|    Harry |  01-01-2023 |     PQR | 666          |
|     Dick |  01-01-2023 |     XYZ | 222          |
|      Tom |  02-01-2023 |     ABC | 111          |
|    Harry |  03-01-2023 |     PQR | 666          |
|     Dick |  03-01-2023 |     XYZ | 222          |
|      Tom |  04-01-2023 |     ABC | 111          |
|     Dick |  06-01-2023 |     XYZ | 222          |
|     Dick |  07-01-2023 |     XYZ | 222          |
|    Harry |  04-01-2023 |     PQR | 666          |
|     Dick |  08-01-2023 |     XYZ | 222          |
|      Tom |  05-01-2023 |     ABC | 111          |
|    Harry |  05-01-2023 |     PQR | 666          |
|    Harry |  06-01-2023 |     PQR | 666          |
|    Harry |  07-01-2023 |     PQR | 666          |
+----------+-------------+---------+--------------+

需求说明 #

给定最多200个memberId的列表（如ABC、XYZ、PQR），需查询每个会员的最新3条订单，预期结果如下：

+----------+-------------+---------+--------------+
| Name     | orderDate   | memberId| OrderId      |
+----------+-------------+---------+--------------+
|      Tom |  01-01-2023 |     ABC | 111          |
|      Tom |  02-01-2023 |     ABC | 111          |
|      Tom |  04-01-2023 |     ABC | 111          |
|     Dick |  02-01-2023 |     XYZ | 111          |
|     Dick |  01-01-2023 |     XYZ | 111          |
|     Dick |  03-01-2023 |     XYZ | 111          |
|    Harry |  01-01-2023 |     PQR | 111          |
|    Harry |  03-01-2023 |     PQR | 111          |
|    Harry |  04-01-2023 |     PQR | 111          |
+----------+-------------+---------+--------------+

当前实现与问题 #

当前通过Hibernate动态生成DB2 SQL，循环拼接UNION ALL查询每个会员的最新3条订单，示例SQL如下：

(SELECT * FROM orders WHERE memberId='ABC' ORDER BY orderDate DESC FETCH FIRST 3 ROWS ONLY)
UNION ALL
(SELECT * FROM orders WHERE memberId='XYZ' ORDER BY orderDate DESC FETCH FIRST 3 ROWS ONLY)
UNION ALL
(SELECT * FROM orders WHERE memberId='PQR' ORDER BY orderDate DESC FETCH FIRST 3 ROWS ONLY)

当memberId数量达到200时，会触发DB2错误-101：语句过长或过于复杂。

解决方案建议 #

一、SQL查询优化（推荐方案） #

利用DB2支持的窗口函数ROW_NUMBER()，仅需一次查询即可获取所有目标会员的最新3条订单，彻底避免语句过长问题。

优化后的SQL语句

SELECT Name, orderDate, memberId, OrderId
FROM (
    SELECT 
        o.*,
        -- 按memberId分组，同组内按orderDate倒序编号
        ROW_NUMBER() OVER (PARTITION BY o.memberId ORDER BY o.orderDate DESC) AS row_num
    FROM orders o
    -- 过滤目标memberId列表
    WHERE o.memberId IN ('ABC', 'XYZ', 'PQR')
) t
-- 只保留每个会员的前3条订单
WHERE t.row_num <= 3
-- 可选：按会员ID和订单日期排序结果
ORDER BY t.memberId, t.row_num;

方案优势

1. 无论memberId数量多少，仅生成一条SQL语句，不会触发长度限制；
2. 性能更优：避免了多次查询和UNION ALL的开销，配合索引可大幅提升效率；
3. 维护简单：无需动态拼接复杂SQL，直接通过参数绑定传入memberId列表即可。

性能优化建议

给orders表创建联合索引：CREATE INDEX idx_orders_member_date ON orders(memberId, orderDate DESC);，窗口函数的分组排序操作会直接利用该索引，减少全表扫描。

二、Java实现层面优化 #

若因业务或框架限制无法使用窗口函数，可采用批量拆分查询+内存聚合的方式：

1. 拆分memberId批次：将200个memberId拆分为多个小批次（例如每50个一组，需根据DB2对IN子句参数数量的限制调整）；
2. 分批执行查询：对每个批次执行查询（推荐用IN子句结合窗口函数的方式，而非原有的UNION ALL拼接）；
3. 内存聚合结果：将各批次的查询结果在Java代码中合并，最终得到完整数据。

Java伪代码示例（基于Hibernate）

List<String> targetMemberIds = ...; // 目标memberId列表
int batchSize = 50; // 每批次处理的memberId数量
List<Order> allOrderResults = new ArrayList<>();

for (int i = 0; i < targetMemberIds.size(); i += batchSize) {
    int endIndex = Math.min(i + batchSize, targetMemberIds.size());
    List<String> batchMemberIds = targetMemberIds.subList(i, endIndex);
    
    // 用原生SQL执行窗口函数查询
    List<Order> batchResults = session.createNativeQuery(
        "SELECT Name, orderDate, memberId, OrderId " +
        "FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.memberId ORDER BY o.orderDate DESC) AS row_num " +
        "      FROM orders o WHERE o.memberId IN (:memberIds)) t " +
        "WHERE t.row_num <=3", Order.class
    ).setParameter("memberIds", batchMemberIds)
     .getResultList();
    
    allOrderResults.addAll(batchResults);
}

// 可选：按会员ID和订单日期排序最终结果
allOrderResults.sort((o1, o2) -> {
    int memberCmp = o1.getMemberId().compareTo(o2.getMemberId());
    if (memberCmp != 0) return memberCmp;
    // 倒序排列，最新订单在前
    return o2.getOrderDate().compareTo(o1.getOrderDate());
});

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