求助：如何统计每分钟发生的计数器事务数（SQL实现）

根据你的需求，分两种场景提供适配主流数据库的SQL方案：

场景1：仅统计存在事务的分钟 #

如果只需要输出有记录的分钟的事务数，用基础分组统计即可：

MySQL #

SELECT 
    COUNT(*) AS transaction_count,
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i') AS minute_interval
FROM 
    your_table_name
GROUP BY 
    minute_interval
ORDER BY 
    minute_interval DESC;

PostgreSQL #

SELECT 
    COUNT(*) AS transaction_count,
    TO_CHAR(date_trunc('minute', timestamp), 'YYYY-MM-DD HH24:MI') AS minute_interval
FROM 
    your_table_name
GROUP BY 
    date_trunc('minute', timestamp)
ORDER BY 
    date_trunc('minute', timestamp) DESC;

场景2：包含无事务的分钟（显示0） #

如果需要像示例那样，即使某分钟没有事务也显示0，需要先生成连续的分钟时间序列，再关联原表统计：

MySQL #

WITH RECURSIVE minute_ranges AS (
    -- 生成从表中最早时间到最晚时间的连续分钟序列
    SELECT DATE_FORMAT(MIN(timestamp), '%Y-%m-%d %H:%i:00') AS minute_start
    FROM your_table_name
    UNION ALL
    SELECT DATE_ADD(minute_start, INTERVAL 1 MINUTE)
    FROM minute_ranges
    WHERE minute_start < DATE_FORMAT(MAX(timestamp), '%Y-%m-%d %H:%i:00')
)
SELECT 
    COUNT(t.counters) AS transaction_count,
    DATE_FORMAT(mr.minute_start, '%Y-%m-%d %H:%i') AS minute_interval
FROM 
    minute_ranges mr
LEFT JOIN 
    your_table_name t ON DATE_FORMAT(t.timestamp, '%Y-%m-%d %H:%i') = DATE_FORMAT(mr.minute_start, '%Y-%m-%d %H:%i')
GROUP BY 
    mr.minute_start
ORDER BY 
    mr.minute_start DESC;

PostgreSQL #

WITH minute_ranges AS (
    -- 用generate_series生成连续分钟序列
    SELECT generate_series(
        date_trunc('minute', MIN(timestamp)),
        date_trunc('minute', MAX(timestamp)),
        '1 minute'::interval
    ) AS minute_start
    FROM your_table_name
)
SELECT 
    COUNT(t.counters) AS transaction_count,
    TO_CHAR(mr.minute_start, 'YYYY-MM-DD HH24:MI') AS minute_interval
FROM 
    minute_ranges mr
LEFT JOIN 
    your_table_name t ON date_trunc('minute', t.timestamp) = mr.minute_start
GROUP BY 
    mr.minute_start
ORDER BY 
    mr.minute_start DESC;

注意事项 #

• 替换your_table_name为你的实际表名
• 如果timestamp是UNIX时间戳（数字格式），MySQL需用FROM_UNIXTIME(timestamp)替换原timestamp字段；PostgreSQL需用TO_TIMESTAMP(timestamp)转换

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