如何用SQL按账龄区间分组统计库存数量并优化大表查询效率

核心逻辑采用条件聚合实现，仅需单次扫描全表即可完成计算，彻底避免子查询多轮扫表带来的额外IO开销，百万级数据下通常可以获得3倍以上的性能提升。

核心SQL实现 #

SELECT 
    AGENT_ID,
    ITEM_ID,
    WAREHOUSE_ID,
    SUM(CASE WHEN AGE >= 1 AND AGE <=10 THEN QTY ELSE 0 END) AS R1,
    SUM(CASE WHEN AGE >= 11 AND AGE <=20 THEN QTY ELSE 0 END) AS R2,
    SUM(CASE WHEN AGE >=21 THEN QTY ELSE 0 END) AS R3
FROM inventory_table -- 替换为你的实际表名
GROUP BY AGENT_ID, ITEM_ID, WAREHOUSE_ID;

额外性能优化建议 #

• 覆盖索引优化：针对高频查询场景，创建联合索引 (AGENT_ID, ITEM_ID, WAREHOUSE_ID, AGE, QTY)，查询时无需回表读取原始数据，直接从索引中就能获取所有计算需要的字段，性能可以再提升2~10倍。
• 表分区优化：如果表数据量持续增长，可以按AGE字段建立范围分区，或者按AGENT_ID、WAREHOUSE_ID建立哈希分区，进一步缩小查询时需要扫描的数据范围。
• 预聚合优化：如果该统计为非实时需求，可通过定时任务预计算聚合结果存入专门的汇总表，查询时直接读取汇总表，响应速度可达到毫秒级。

性能对比说明 #

常规子查询实现方案需要为每个AGE区间单独做一次过滤、聚合再关联，相当于要扫描3次全表，百万级数据下IO开销是条件聚合方案的3倍以上，数据量越大性能差距越明显。

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