MySQL按每N小时筛选Product表记录的技术咨询

嘿，我来帮你搞定这个按固定时间间隔筛选记录的需求～针对你的Product表（包含id、name、description、lastSoldOn字段），我整理了几种主流数据库的实现方法，你可以根据自己使用的数据库直接套用：

MySQL 实现方式 #

每1小时取一条记录 #

如果是固定每小时筛选，我们可以把lastSoldOn格式化到小时级别，然后分组取每组的第一条记录：

SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        -- 按小时分组，给每组内的记录排序
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(lastSoldOn, '%Y-%m-%d %H:00:00') 
            ORDER BY lastSoldOn -- 这里可以改成DESC取每组最新的记录
        ) AS rn
    FROM Product
) t
WHERE rn = 1;

任意N小时取一条记录 #

如果需要灵活设置间隔N小时，我们可以用时间戳来分组——把lastSoldOn转成秒级时间戳，除以N*3600（N小时的秒数）取整，相同结果的记录属于同一组：

SET @N = 2; -- 这里替换成你需要的间隔小时数
SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY UNIX_TIMESTAMP(lastSoldOn) DIV (@N * 3600) 
            ORDER BY lastSoldOn
        ) AS rn
    FROM Product
) t
WHERE rn = 1;

PostgreSQL 实现方式 #

每1小时取一条记录 #

PostgreSQL的date_trunc函数可以直接把时间截断到小时级别，非常方便：

SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY date_trunc('hour', lastSoldOn) 
            ORDER BY lastSoldOn
        ) AS rn
    FROM Product
) t
WHERE rn = 1;

任意N小时取一条记录 #

同样用时间戳的思路，通过EXTRACT(EPOCH FROM ...)获取秒级时间戳来分组：

WITH params AS (SELECT 2 AS n) -- 这里设置你的间隔小时数N
SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY EXTRACT(EPOCH FROM lastSoldOn) DIV (params.n * 3600) 
            ORDER BY lastSoldOn
        ) AS rn
    FROM Product, params
) t
WHERE rn = 1;

SQL Server 实现方式 #

每1小时取一条记录 #

用DATEADD和DATEDIFF组合来把时间截断到小时：

SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DATEADD(hour, DATEDIFF(hour, 0, lastSoldOn), 0) 
            ORDER BY lastSoldOn
        ) AS rn
    FROM Product
) t
WHERE rn = 1;

任意N小时取一条记录 #

通过对小时数取整来实现任意间隔的分组：

DECLARE @N INT = 2; -- 设置你的间隔小时数N
SELECT id, name, description, lastSoldOn
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DATEADD(hour, DATEDIFF(hour, 0, lastSoldOn) / @N * @N, 0) 
            ORDER BY lastSoldOn
        ) AS rn
    FROM Product
) t
WHERE rn = 1;

一些实用小贴士 #

• 如果你想取每组最新的记录，只需要把ORDER BY lastSoldOn改成ORDER BY lastSoldOn DESC即可
• 20万条记录不算超大，但建议给lastSoldOn字段加索引，能大幅提升查询速度：CREATE INDEX idx_product_lastsoldon ON Product(lastSoldOn);（不同数据库语法基本一致）
• 测试时可以先加个限制条件（比如MySQL加LIMIT 10），快速验证结果是否符合预期

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