mysql 如何在多个产品中查找相同时间范围的数据集

我有一个数据库，在那里我存储的各种交易所的产品的最佳出价和要求1分钟的快照。我有一个要求，我需要找到：

500个最佳出价和询问3个产品（product1 & exch1，product2 & exch2，product3 & exch3）
1.所有500个数据点应在一定的时间范围内，例如0900-1014，1030-1129
1.所有3个产品都应该有相同的时间戳，即如果prod 1没有1030个数据点，查询应该忽略prod 2和prod 3 <--* 这就是我卡住的地方 *
我在Ubuntu 20.04上使用MySQL 8.0.35
我的表具有以下结构

symbol(varchar)
exchange(varchar)
bid_price(double)
ask_price(double)
timestamp(bigint) # actual best bid ask received time from exchange (millisecond precision)
updated_at(bigint) # minutes precision stored as seconds since epoch e.g. 1702954500102

个字符
通过下面的查询，我能够完成需求#1和#2，但#3是我还无法弄清楚的

(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)

型
编辑：从下图中可以看出，突出显示的红色部分具有21：00的prod 2和prod 3数据，但prod 1没有，如果prod 1在21：00 x1c 0d1x没有数据点，我希望删除prod 2和prod 3数据

一个子连接或者一个where exists查询也可以工作。也要考虑性能--我不知道子查询是否会太慢你的工作。
很抱歉没有在MySQL中回答。
下面是一个在MsSQL中使用内部连接的例子--你可以看到我在哪里稍微修改了一下，以说明MsSQL和MySQL之间的区别。

SELECT 
    symbol,
    exchange,
    bid_price,
    ask_price,
    --timestamp,
    FORMAT(dateadd(S, convert(bigint, timestamp) / 1000, '1970-01-01'),'hhmm')+700 AS timestamp,
    --updated_at
    FORMAT(dateadd(S, convert(INT, updated_at), '1970-01-01'),'hhmm')+700 AS updated_at
into #tmp_db
FROM(
    values 
    ('prod1','exch1',7.1041,7.1042, '1702956600942',    '1702956600'), -- 1030 for testing
    ('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'), -- 1030 for testing
    ('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'), -- 1030 for testing
    ('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')

) AS x (symbol, exchange, bid_price, ask_price, timestamp, updated_at)


SELECT top 500
    *
    --FROM_UNIXTIME(updated_at, '%H%i') AS u_time
FROM #tmp_db
JOIN (
        SELECT
            count(DISTINCT symbol) as product_count,
            updated_at as sub_updated_at
        FROM #tmp_db
        WHERE 
            (
                (symbol = 'prod1' AND exchange = 'exch1')
                OR
                (symbol = 'prod2' AND exchange = 'exch2')
                OR
                (symbol = 'prod3' AND exchange = 'exch3')
            )
        GROUP BY updated_at
    ) as x on x.product_count = 3 and x.sub_updated_at = updated_at
WHERE
    (
        (symbol = 'prod1' AND exchange = 'exch1')
        OR
        (symbol = 'prod2' AND exchange = 'exch2')
        OR
        (symbol = 'prod3' AND exchange = 'exch3')
    )
--HAVING
--  (updated_at >= 900 AND updated_at <= 1014)
--  OR
--  (updated_at >= 1030 AND updated_at <= 1129)
and (
        (updated_at >= 900 AND updated_at <= 1014)
        OR
        (updated_at >= 1030 AND updated_at <= 1129)
    )
ORDER BY updated_at DESC --LIMIT 500


drop table #tmp_db

字符串
所有三个，如果包含prod 1 x1c 0d1x
如果prod 1未显示

，则无
更新：添加了“DISTINCT符号”，以确保所有三种产品都被计数，以防产品可以显示多次。

mysql 如何在多个产品中查找相同时间范围的数据集

1条答案

相关问题

热门标签

最新问答