基于ClickHouse实现各银行外汇报价的滚动数组聚合

完全可以实现该需求，核心思路是通过排序+累积合并字典的方式，跟踪每个银行的最新报价，并将字典转换为你需要的数组格式。以下是具体实现方案：

实现步骤及SQL代码 #

假设你的表名为fx_prices，执行以下SQL即可得到目标结果：

WITH sorted_data AS (
    -- 按时间戳升序排列数据，确保新报价能正确覆盖旧报价
    SELECT
        timestamp,
        -- 将当前行的银行与报价打包为字典（key为银行名，value为(bid, ask)元组）
        map(bank_name, tuple(tob_bid, tob_ask)) AS price_map
    FROM fx_prices
    ORDER BY timestamp ASC
),
running_maps AS (
    -- 累积合并所有字典，后续行的同银行报价自动覆盖旧值
    SELECT
        timestamp,
        runningAccumulate(mergeMap(price_map)) AS cumulative_price_map
    FROM sorted_data
)
-- 将累积字典转换为目标数组格式
SELECT
    timestamp,
    mapKeys(cumulative_price_map) AS bank_names,
    arrayMap(val -> val.1, mapValues(cumulative_price_map)) AS latest_bids,
    arrayMap(val -> val.2, mapValues(cumulative_price_map)) AS latest_asks
FROM running_maps;

代码说明 #

1. 排序数据：先按timestamp升序排列所有报价，保证后续处理时时间更晚的新报价能覆盖旧报价。
2. 转换为字典：将每行的银行名与对应买卖价打包成map结构，便于后续合并更新操作。
3. 累积合并字典：通过runningAccumulate配合mergeMap函数，逐行累积合并所有字典——当遇到相同银行名时，后续的报价会自动替换之前的旧值，确保字典中始终存储各银行的最新报价。
4. 字典转数组：用mapKeys提取银行名数组，再通过arrayMap从字典值中分别提取买卖价数组，最终得到你需要的输出格式。

结果验证 #

针对你提供的测试数据，执行上述SQL后会生成与你期望完全一致的结果：每个时间点的数组都会包含截至该时刻所有银行的最新报价，且新报价会实时更新对应银行的数值。

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