Mysql 如何跨多个产品查找相同时间范围的数据集

子联接或WHERE EXISTS查询可以工作.一定要考虑性能--我不知道子查询是否会使您的工作变得太慢.

很抱歉没有在MySQL中回答.

下面是一个在MsSQL中使用内部连接的例子--您可以看到我在哪里稍微修改了一下，以说明MsSQL和MySQL之间的差异.你应该能够与这个 idea 工作.

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

all three if the prod1 is included

none if the prod1 is not showing

更新:增加了"不同的符号"，以确保所有三种产品都被计算在内，以防一个产品可以显示不止一次.