如何在Oracle SQL中按order_id分组筛选列最大值对应行？

现有一张存储每日订单记录的表，需为每个order_id筛选出对应月份中订单数量最多的行，最终结果符合目标输出要求。表包含数百万条记录，以下是针对Oracle SQL的高效解决方案：

核心解决方案 #

使用窗口函数结合分区排序的方式，适合百万级数据的高效处理：

WITH monthly_order_cte AS (
    SELECT 
        t.*,
        -- 计算当前order_id当月的总订单数
        COUNT(*) OVER (PARTITION BY order_id, TRUNC(order_date, 'MONTH')) AS monthly_order_cnt,
        -- 按订单量降序、日期降序排名，取排名第一的行
        ROW_NUMBER() OVER (
            PARTITION BY order_id, TRUNC(order_date, 'MONTH')
            ORDER BY COUNT(*) DESC, order_date DESC
        ) AS rank_num
    FROM your_order_table t
)
SELECT 
    order_id,
    order_date,
    -- 替换为你需要保留的其他字段
    monthly_order_cnt
FROM monthly_order_cte
WHERE rank_num = 1;

关键说明 #

1. 按月分组：TRUNC(order_date, 'MONTH')将日期截断至当月起始，实现同一order_id的月度数据分组。
2. 订单量计算：通过窗口函数COUNT(*) OVER (...)直接统计每个order_id当月的总订单数，无需额外子查询聚合。
3. 排序取顶：ROW_NUMBER()为每组内的行按订单量降序排序，若订单量相同，可通过order_date DESC取最晚的记录；若需保留所有订单量相同的行，将ROW_NUMBER()替换为RANK()即可。

性能优化建议 #

针对百万级数据，需配合索引提升查询效率：

• 创建联合索引：

  CREATE INDEX idx_order_date ON your_order_table(order_id, order_date);
  

  该索引可覆盖分区和排序的字段需求，大幅减少全表扫描的开销。

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