Oracle SQL实现分组获取每组TOP2最长时长记录

要实现按MACHINE和DATE_HOUR分组筛选时长最长的前2条记录，你可以在现有聚合查询的基础上，使用Oracle的窗口函数RANK()或ROW_NUMBER()来实现。具体步骤如下：

方法1：使用RANK()函数（保留并列排名） #

RANK()会为相同时长的记录分配相同的排名，若有并列第2名的情况，会将所有并列记录都保留。

WITH aggregated_data AS (
    SELECT 
        MACHINE, 
        SUM(DURATION/60) AS Min,   
        OBSERVATION,
        TO_CHAR(MIN(START_DATE), 'MM/DD/YYYY_HH24') AS date_hour,
        OBSERVATION || ' (' || RTRIM(TO_CHAR(SUM(DURATION/60), 'FM90.99'), '.') || '  Min) ' AS lost_prod,
        RANK() OVER (
            PARTITION BY MACHINE, TO_CHAR(MIN(START_DATE), 'MM/DD/YYYY_HH24') 
            ORDER BY SUM(DURATION/60) DESC
        ) AS obs_rank
    FROM MyTable
    WHERE TRUNC(START_DATE) = TRUNC(CURRENT_DATE) AND OBSERVATION IS NOT NULL 
    GROUP BY MACHINE, OBSERVATION, TO_CHAR(START_DATE, 'MM/DD/YYYY_HH24')
)
SELECT MACHINE, Min, OBSERVATION, date_hour, lost_prod
FROM aggregated_data
WHERE obs_rank <= 2;

方法2：使用ROW_NUMBER()函数（严格取前2条） #

ROW_NUMBER()会为每条记录分配唯一的排名，即使时长相同，也只会保留其中一条（排序规则由Oracle内部决定）。

WITH aggregated_data AS (
    SELECT 
        MACHINE, 
        SUM(DURATION/60) AS Min,   
        OBSERVATION,
        TO_CHAR(MIN(START_DATE), 'MM/DD/YYYY_HH24') AS date_hour,
        OBSERVATION || ' (' || RTRIM(TO_CHAR(SUM(DURATION/60), 'FM90.99'), '.') || '  Min) ' AS lost_prod,
        ROW_NUMBER() OVER (
            PARTITION BY MACHINE, TO_CHAR(MIN(START_DATE), 'MM/DD/YYYY_HH24') 
            ORDER BY SUM(DURATION/60) DESC
        ) AS obs_rank
    FROM MyTable
    WHERE TRUNC(START_DATE) = TRUNC(CURRENT_DATE) AND OBSERVATION IS NOT NULL 
    GROUP BY MACHINE, OBSERVATION, TO_CHAR(START_DATE, 'MM/DD/YYYY_HH24')
)
SELECT MACHINE, Min, OBSERVATION, date_hour, lost_prod
FROM aggregated_data
WHERE obs_rank <= 2;

说明 #

• PARTITION BY MACHINE, date_hour：按机器和小时分组计算排名
• ORDER BY SUM(DURATION/60) DESC：按时长从长到短排序，确保最长的记录排名靠前
• 最终通过WHERE obs_rank <=2筛选出每组的前2条记录

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