如何统计Transactions表中任意2小时内的最大交易次数？

首先修正你的插入语句（字符串值和关键字字段需加单引号/反引号）：

INSERT into Transactions(`Timestamp`, Transaction)
values('2022-10-10 19:58:08','A'),
('2022-10-19 18:46:05','B'),
('2022-10-19 18:49:05','C'),
('2022-10-19 19:49:20','D'),
('2022-10-20 18:49:20','E'),
('2022-10-20 19:20:05','F');

方法1：自连接统计（兼容多数SQL版本） #

通过自连接匹配每个交易时间点后2小时内的所有交易，统计数量后取最大值：

SELECT MAX(transaction_count) AS max_transactions_in_2h
FROM (
    SELECT t1.`Timestamp`, COUNT(t2.Transaction) AS transaction_count
    FROM Transactions t1
    JOIN Transactions t2 
        ON t2.`Timestamp` BETWEEN t1.`Timestamp` AND DATE_ADD(t1.`Timestamp`, INTERVAL 2 HOUR)
    GROUP BY t1.`Timestamp`
) AS transaction_counts;

方法2：窗口函数（适用于MySQL 8.0+/PostgreSQL等支持范围窗口的数据库） #

利用范围窗口函数高效计算每个时间点往后2小时内的交易数，再取最大值：

SELECT MAX(transaction_count) AS max_transactions_in_2h
FROM (
    SELECT 
        `Timestamp`,
        COUNT(Transaction) OVER (
            ORDER BY `Timestamp`
            RANGE BETWEEN CURRENT ROW AND INTERVAL 2 HOUR FOLLOWING
        ) AS transaction_count
    FROM Transactions
) AS window_counts;

结果说明 #

在你的数据中，2022-10-19 18:46:05至20:46:05的2小时区间内包含B、C、D三笔交易，因此最终返回结果为3，符合预期。

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