如何在MySQL中筛选多产品同时间戳的目标数据集

需求 #

• 获取3个指定产品（prod1&exch1、prod2&exch2、prod3&exch3）的500条最优买卖价数据
• 所有数据需落在指定时间范围（如0900-1014、1030-1129）
• 3个产品必须拥有相同的updated_at时间戳，若某时间点prod1无数据，则忽略该时间点prod2和prod3的数据

环境 #

Ubuntu 20.04系统，使用MySQL 8.0.35

表结构 #

symbol(varchar)
exchange(varchar)
bid_price(double)
ask_price(double)
timestamp(bigint) -- 交易所实际接收最优买卖价的时间（毫秒精度）
updated_at(bigint) -- 分钟精度，存储为从epoch开始的秒数，例如 1702954500102

数据示例 #

symbol,exchange,bid_price,ask_price,timestamp,updated_at
prod1,exch1,555.7,555.8,1702956540603,1702956540
prod1,exch1,555.8,555.9,1702956478591,1702956480
prod1,exch1,555.9,556,1702956420102,1702956420
prod1,exch1,556,556.1,1702956360610,1702956360
prod1,exch1,556,556.1,1702956299607,1702956300
prod1,exch1,556.1,556.2,1702956239595,1702956240
prod1,exch1,556.2,556.3,1702956179597,1702956180
prod1,exch1,556,556.1,1702956122111,1702956120
prod1,exch1,556,556.1,1702956061081,1702956060
prod1,exch1,555.8,555.9,1702955998590,1702956000
prod2,exch2,7.1041,7.1042,1702956600942,1702956600
prod2,exch2,7.104,7.1042,1702956541012,1702956540
prod2,exch2,7.1043,7.1045,1702956480465,1702956480
prod2,exch2,7.1043,7.1044,1702956420617,1702956420
prod2,exch2,7.1043,7.1044,1702956360264,1702956360
prod2,exch2,7.1037,7.1038,1702956300269,1702956300
prod2,exch2,7.1039,7.1041,1702956239092,1702956240
prod2,exch2,7.1041,7.1043,1702956180001,1702956180
prod2,exch2,7.1043,7.1045,1702956123891,1702956120
prod2,exch2,7.1045,7.1047,1702956060588,1702956060
prod3,exch3,72.78,72.79,1702956600332,1702956600
prod3,exch3,72.79,72.8,1702956540698,1702956540
prod3,exch3,72.8,72.81,1702956480542,1702956480
prod3,exch3,72.79,72.81,1702956420228,1702956420
prod3,exch3,72.8,72.81,1702956352133,1702956360
prod3,exch3,72.81,72.82,1702956287733,1702956300
prod3,exch3,72.81,72.82,1702956239441,1702956240
prod3,exch3,72.82,72.83,1702956179253,1702956180
prod3,exch3,72.81,72.82,1702956124140,1702956120
prod3,exch3,72.81,72.82,1702956058999,1702956060

已实现的查询语句 #

目前已完成需求1和2的查询，语句如下：

(SELECT *, FROM_UNIXTIME(updated_at, '%H%i') AS u_time
FROM algo_system.bbo_1min
WHERE
(
(symbol = 'prod1' AND exchange = 'exch1')
OR
(symbol = 'prod2' AND exchange = 'exch2')
OR
(symbol = 'prod3' AND exchange = 'exch3')
)
HAVING
(u_time >= 900 AND u_time <= 1014)
OR
(u_time >= 1030 AND u_time <= 1129)
ORDER BY TIMESTAMP DESC LIMIT 500)

当前卡点 #

需要过滤掉prod1无数据的时间点对应的prod2、prod3数据。例如示例中updated_at=1702956600（对应21:00）的时间点，prod2和prod3有数据但prod1没有，这类记录需要被移除。

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