PostgreSQL按小时间隔分组file_id数据的SQL实现问询

核心需求：基于file_input_date（格式YYYY-MM-DD HH:mm:ss）对file_id分组统计，粒度精确到小时（或示例中的分钟级），而非仅按日期分组。

示例数据 #

file_id | file_input_date
597872  | 2023-01-12 16:06:22.92879
497872  | 2023-01-11 16:06:22.92879
397872  | 2023-01-11 16:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 18:06:22.92879

核心思路 #

通过时间截断函数将file_input_date统一转换为目标粒度（小时/分钟），再按转换后的时间分组，统计每组的file_id数量。

不同数据库的实现代码 #

1. MySQL/MariaDB #

按分钟粒度分组（匹配示例输出格式）

SELECT
    COUNT(file_id) AS file_count,
    CONCAT('for ', DATE_FORMAT(file_input_date, '%Y-%m-%d %H:%i')) AS time_label
FROM
    your_table_name
GROUP BY
    DATE_FORMAT(file_input_date, '%Y-%m-%d %H:%i')
ORDER BY
    DATE_FORMAT(file_input_date, '%Y-%m-%d %H:%i');

执行后输出与示例一致：

file_count | time_label
1          | for 2023-01-12 16:06
2          | for 2023-01-11 16:06
3          | for 2023-01-11 17:06
1          | for 2023-01-11 18:06

按小时粒度分组（YYYY-MM-DD HH:00）

SELECT
    DATE_FORMAT(file_input_date, '%Y-%m-%d %H:00') AS hour_interval,
    COUNT(file_id) AS file_count
FROM
    your_table_name
GROUP BY
    hour_interval
ORDER BY
    hour_interval;

2. PostgreSQL #

按分钟粒度分组（匹配示例）

SELECT
    COUNT(file_id) AS file_count,
    'for ' || TO_CHAR(file_input_date, 'YYYY-MM-DD HH24:MI') AS time_label
FROM
    your_table_name
GROUP BY
    TO_CHAR(file_input_date, 'YYYY-MM-DD HH24:MI')
ORDER BY
    TO_CHAR(file_input_date, 'YYYY-MM-DD HH24:MI');

按小时粒度分组

SELECT
    DATE_TRUNC('hour', file_input_date) AS hour_interval,
    COUNT(file_id) AS file_count
FROM
    your_table_name
GROUP BY
    hour_interval
ORDER BY
    hour_interval;

3. SQL Server #

按分钟粒度分组（匹配示例）

SELECT
    COUNT(file_id) AS file_count,
    'for ' + CONVERT(VARCHAR(16), file_input_date, 120) AS time_label
FROM
    your_table_name
GROUP BY
    CONVERT(VARCHAR(16), file_input_date, 120)
ORDER BY
    CONVERT(VARCHAR(16), file_input_date, 120);

按小时粒度分组

SELECT
    DATEADD(HOUR, DATEDIFF(HOUR, 0, file_input_date), 0) AS hour_interval,
    COUNT(file_id) AS file_count
FROM
    your_table_name
GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, file_input_date), 0)
ORDER BY
    hour_interval;

注意事项 #

• 替换your_table_name为实际表名。
• 如需筛选特定时间范围，可添加WHERE子句，例如：WHERE file_input_date BETWEEN '2023-01-01 00:00:00' AND '2023-01-13 23:59:59'。

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