SQL查询：如何从每个分组返回结果集中提取首条记录

需求背景 #

• 现有基础查询已可输出指定FACID集合、一周时间范围内按「日期+机构」维度聚合的日均消息量，结果默认按日均量升序排列
• 目标输出：仅保留每个FACID分组下日均消息量最低的1条记录，直接得到各机构周内最低日均消息量，作为告警阈值设置依据
• 生产约束：单次查询需支持最多50个FACID的批量统计

当前使用的基础SQL：

select mirth_channel, facility, DATE(received_on) as Day,
       round (count(*) / 24)  AS 'average'
from message
where facility in ('FACID1', 'FACID2', 'FACID3', 'FACID4', 'FACID5')
AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
group by DATE(received_on), facility
order by average asc;

注意：上述SQL中mirth_channel字段未加入分组逻辑，若数据库开启ONLY_FULL_GROUP_BY校验模式会抛出语法错误；如果每个facility对应固定的mirth_channel，直接将该字段加入group by子句即可正常运行。

具体实现方案 #

方案1：窗口函数写法（MySQL8.0+、PostgreSQL、SQL Server等主流数据库均支持，性能最优） #

通过ROW_NUMBER()窗口函数按FACID分区、按日均量升序打行号，最终筛选行号为1的记录即可，单次查询即可返回所有传入FACID的结果，适配50个FACID批量查询的性能要求：

WITH daily_stat AS (
    SELECT
        mirth_channel,
        facility,
        DATE(received_on) AS Day,
        ROUND(COUNT(*) / 24) AS average
    FROM message
    WHERE facility IN ('FACID1', 'FACID2', 'FACID3', 'FACID4', 'FACID5') -- 此处可直接扩展为50个FACID
      AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
    GROUP BY DATE(received_on), facility, mirth_channel
)
SELECT mirth_channel, facility, Day, average
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY facility ORDER BY average ASC) AS rn
    FROM daily_stat
) t
WHERE rn = 1;

如果同一FACID存在多天日均量并列最低的场景，需要把所有最低值记录都返回时，将ROW_NUMBER()替换为RANK()即可。

方案2：关联子查询写法（适配不支持窗口函数的旧版本，如MySQL5.x） #

如果数据库版本较低不支持CTE和窗口函数，可以通过子查询关联匹配每个FACID对应的最小日均量实现相同效果：

SELECT
    t1.mirth_channel,
    t1.facility,
    DATE(t1.received_on) AS Day,
    ROUND(COUNT(*) / 24) AS average
FROM message t1
WHERE t1.facility IN ('FACID1', 'FACID2', 'FACID3', 'FACID4', 'FACID5')
  AND t1.received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
GROUP BY DATE(t1.received_on), t1.facility, t1.mirth_channel
HAVING average = (
    SELECT ROUND(COUNT(*) / 24) AS min_avg
    FROM message t2
    WHERE t2.facility = t1.facility
      AND t2.received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
    GROUP BY DATE(t2.received_on), t2.facility
    ORDER BY min_avg ASC
    LIMIT 1
);

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