如何通过单条SQL查询为每个HOUR分组抽取5条样本数据？

嘿，我来帮你搞定这个需求！你不想手动硬编码所有小时值，还想用单条查询实现每个小时抽5条记录的目标，用窗口函数就能完美解决，再也不用费劲列那一串'00'到'23'的小时值啦。

核心思路 #

利用ROW_NUMBER()窗口函数，先按hour字段把数据拆分成独立的小时分组，给每个组内的记录单独编号，然后只保留每组中编号≤5的记录——不管你的表中有哪些小时段，都会自动为每个小时抽取最多5条数据。

完整SQL示例 #

SELECT hour, location
FROM (
    SELECT 
        hour, 
        location,
        -- 按hour分组，组内可根据需求排序（比如location、时间戳等）
        ROW_NUMBER() OVER (PARTITION BY hour ORDER BY location) AS row_num
    FROM db.table
) grouped_data
WHERE row_num <= 5
ORDER BY hour, row_num;

细节调整说明 #

1. 分组与编号逻辑：PARTITION BY hour负责把数据按小时拆分成独立小组，ROW_NUMBER()给每个小组内的记录从1开始顺序编号，编号规则由ORDER BY后的字段决定。
2. 随机抽取的适配：如果你想每个小时随机抽5条，把ORDER BY location换成对应数据库的随机函数即可：
   • MySQL/MariaDB：ORDER BY RAND()
   • PostgreSQL：ORDER BY RANDOM()
   • SQL Server：ORDER BY NEWID()
3. 输出排序：外层查询通过WHERE row_num <=5筛选出每个小时的前5条，最后按小时和编号排序，就能得到你想要的示例格式。

对比你原来的SQL，原来的LIMIT 3是全局仅取3条，而这个方法是每个小时单独抽取5条，完全符合预期，还不用手动维护小时值列表，扩展性拉满。

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