如何从聚合查询结果中获取最值及对应SERIES_INSTANCE_UID

你可以通过以下两种方法实现从每日SERIES_INSTANCE_UID统计结果中获取最值及对应的关联字段：

方法一：使用窗口函数（推荐，效率更高） #

通过窗口函数为统计结果中的行标记最值排名，再筛选出排名靠前的行，同时支持处理并列最值的场景：

WITH daily_siu_stats AS (
    SELECT 
        TRUNC(CREATED_DATE) AS stat_date,
        SERIES_INSTANCE_UID,
        COUNT(SERIES_INSTANCE_UID) AS COUNT_SIU
    FROM IMAGE_HISTORY
    WHERE CREATED_DATE >= SYSDATE - 1
    GROUP BY TRUNC(CREATED_DATE), SERIES_INSTANCE_UID
),
ranked_stats AS (
    SELECT 
        stat_date,
        SERIES_INSTANCE_UID,
        COUNT_SIU,
        -- 按数量降序排名，1为最大值
        RANK() OVER (ORDER BY COUNT_SIU DESC) AS max_rank,
        -- 按数量升序排名，1为最小值
        RANK() OVER (ORDER BY COUNT_SIU ASC) AS min_rank
    FROM daily_siu_stats
)
SELECT 
    stat_date,
    SERIES_INSTANCE_UID,
    COUNT_SIU,
    CASE 
        WHEN max_rank = 1 THEN '最大值' 
        ELSE '最小值' 
    END AS value_type
FROM ranked_stats
WHERE max_rank = 1 OR min_rank = 1;

• 若需仅返回单个最值行（即使有并列），可将RANK()替换为ROW_NUMBER()
• 若需保留所有并列最值，使用RANK()或DENSE_RANK()即可

方法二：子查询关联最值 #

通过两次子查询分别筛选出等于最大值、最小值的行，再合并结果：

WITH daily_siu_stats AS (
    SELECT 
        TRUNC(CREATED_DATE) AS stat_date,
        SERIES_INSTANCE_UID,
        COUNT(SERIES_INSTANCE_UID) AS COUNT_SIU
    FROM IMAGE_HISTORY
    WHERE CREATED_DATE >= SYSDATE - 1
    GROUP BY TRUNC(CREATED_DATE), SERIES_INSTANCE_UID
)
-- 获取最大值及对应字段
SELECT 
    stat_date,
    SERIES_INSTANCE_UID,
    COUNT_SIU,
    '最大值' AS value_type
FROM daily_siu_stats
WHERE COUNT_SIU = (SELECT MAX(COUNT_SIU) FROM daily_siu_stats)

UNION ALL

-- 获取最小值及对应字段
SELECT 
    stat_date,
    SERIES_INSTANCE_UID,
    COUNT_SIU,
    '最小值' AS value_type
FROM daily_siu_stats
WHERE COUNT_SIU = (SELECT MIN(COUNT_SIU) FROM daily_siu_stats);

内容的提问来源于stack exchange，提问作者Jyoti Prakash Mallick