multidimensional-array - 如何从完整的订单转换为更新？

Question

当前的表结构是：

ts UInt64,
bids_p Array(Float64),
bids_q Array(Float64),
asks_p Array(Float64),
asks_q Array(Float64)

如何构建一个 clickhouse 查询以具有这样的结构，它只包含更新

ts UInt64,
price Array(Float64),
qty Array(Float64)

知道价格网格不是固定的，但数据是按价格排序的（投标递减，要价递减）。

例如。这里的转换与投标

1, [(100, 5),(99, 5)] --> 1, []
2, [(100, 6),(98, 3)] --> 2, [(100, 1), (99, -5), (98,3)]
3, [(99, 5), (98,3)]  --> 3, [(100, -6),(99, +5)]
...

这没有给出任何结果：

select distinct 
            exchange,
            symbol,
            exchange_ts,
            arrayMap(([x,y]) -> map([x, y]),arrayZip( bids_prices, bids_quantities)) as bids,
            arrayMap(([x,y]) -> map([x, y]),arrayZip( asks_prices, asks_quantities)) as asks
        from
            orderbooks
        where
            exchange = 'test'
            and symbol = 'ethusdt'

而且我还尝试了这种方法来减去数组映射以获得基于事件的结构，但它没有按预期工作。

select distinct 
    exchange,
    symbol,
    exchange_ts,
    arrayMap(x -> (map(toInt32(x.1 * 100), x.2)), (arrayZip(bids_prices, bids_quantities))) as bids,
    arrayMap(x -> (map(toInt32(x.1 * 100), x.2)), (arrayZip(asks_prices, asks_quantities))) as asks
from
    test.s3_orderbooks so 
where
    exchange = 'binance'
    and symbol = 'ethusdt'
    and toDateTime64(exchange_ts/1000,3) > '2021-09-23 07:00:01'
    and toDateTime64(exchange_ts/1000,3) > '2021-09-23 07:00:02'

标签： multidimensional-arraydata-structuresclickhousealgorithmic-trading