在SQL中针对特定量计算数组列的成交量加权价格

我有如下结构的数据表，所有数组字段长度一致（示例长度25）：

bid_prices: array  // 买一到买N的价格数组
bid_quantity: array  // 对应档位的挂单量数组
ask_prices: array  // 卖一到卖N的价格数组
ask_quantity: array  // 对应档位的挂单量数组

需要计算指定成交量规模下的买卖盘VWAP（taker订单成交均价）：以bid侧为例，当目标规模为8时，从买一档开始累加挂单量，直到总量达到8，最后一档取剩余所需量，再计算加权均价。示例：

bid_prices = [101,100,99,98,97]
bid_quantity = [1,2,3,10,5]
size = 8
adjusted_bid_quantity = [1,2,3,2] （累加后总量为8）
resized_prices = [101,100,99,98]
bid_vwap = (101*1 + 100*2 + 99*3 + 98*2)/8

现有ClickHouse代码可运行，但需解决两个问题：

1. 优化现有代码逻辑
2. 实现一次性计算多个不同成交量规模的VWAP

原代码如下：

WITH
     resizing AS (
        SELECT
        exchange_ts,
        arrayFilter(x -> x < 8,arrayCumSum( bids_qty ) ) as temp_bids_qty_cumsum,
        arrayResize(bids_qty,length( temp_bids_qty_cumsum )) as temp_resized_bid_qty,
        arrayPushBack(temp_resized_bid_qty,8 - temp_bids_qty_cumsum[LENGTH(temp_bids_qty_cumsum)]) as resized_bid_qty,
        arrayResize(bids,length(resized_bid_qty)) as resized_bid_prices,
                        
        arrayFilter(x -> x < 8,arrayCumSum( asks_qty ) ) as temp_asks_qty_cumsum,
        arrayResize(asks_qty,length( temp_asks_qty_cumsum )) as temp_resized_ask_qty,
        arrayPushBack(temp_resized_ask_qty,8 - temp_asks_qty_cumsum[LENGTH(temp_asks_qty_cumsum)]) as resized_ask_qty,
        arrayResize(asks,length(resized_ask_qty)) as resized_ask_prices
                        
        FROM my_db.books AS t
        WHERE toDateTime64(exchange_ts/1000, 3) >= '2023-10-27 00:00:00' 
            AND toDateTime64(exchange_ts/1000, 3) <= '2023-10-28 00:00:00'
         )
        SELECT
           toDateTime64(r.exchange_ts/1000, 3) as ts,
           if(length(r.resized_bid_prices) <=  25, arraySum(arrayMap((x, y) -> x * y, r.resized_bid_prices, r.resized_bid_qty)) / arraySum(r.resized_bid_qty), NAN) as bid_vwap,
           if(length(r.resized_ask_prices) <=  25, arraySum(arrayMap((x, y) -> x * y, r.resized_ask_prices, r.resized_ask_qty)) / arraySum(r.resized_ask_qty), NAN) as ask_vwap
        FROM resizing AS r
        ORDER by ts ASC

1. 代码优化：封装通用VWAP计算函数 #

将买卖盘的VWAP计算逻辑封装成通用函数，避免重复代码，同时简化逻辑判断：

CREATE OR REPLACE FUNCTION calculate_vwap(prices Array(Float64), quantities Array(UInt64), target_size UInt64)
RETURNS Float64
LANGUAGE SQL
AS $$
    WITH
        cum_sum = arrayCumSum(quantities),
        -- 定位第一个累积量≥目标规模的档位索引
        cutoff_idx = arrayFirstIndex(x -> x >= target_size, cum_sum),
        -- 计算前cutoff_idx-1档的总成交量
        prev_total = if(cutoff_idx == 1, 0, cum_sum[cutoff_idx-1]),
        -- 生成调整后的成交量数组（最后一档取剩余量，后续档位置0）
        adjusted_qty = arrayMap(
            (q, idx) -> if(idx < cutoff_idx, q, if(idx == cutoff_idx, target_size - prev_total, 0)),
            quantities,
            arrayEnumerate(quantities)
        ),
        -- 过滤掉成交量为0的无效项
        valid_pairs = arrayFilter(pq -> pq.2 > 0, arrayZip(prices, adjusted_qty)),
        total = arraySum(arrayElement(valid_pairs, 2)),
        weighted_sum = arraySum(arrayMap(pq -> pq.1 * pq.2, valid_pairs))
    -- 仅当实际累积量等于目标规模时返回VWAP，否则返回NAN
    SELECT if(total == target_size, weighted_sum / total, NAN)
$$;

优化后的单规模查询代码：

SELECT
    toDateTime64(exchange_ts/1000, 3) AS ts,
    calculate_vwap(bid_prices, bid_quantity, 8) AS bid_vwap_8,
    calculate_vwap(ask_prices, ask_quantity, 8) AS ask_vwap_8
FROM my_db.books
WHERE toDateTime64(exchange_ts/1000, 3) BETWEEN '2023-10-27 00:00:00' AND '2023-10-28 00:00:00'
ORDER BY ts ASC;

优化点说明 #

• 封装通用函数，消除bid/ask逻辑重复，提升可维护性
• 使用arrayFirstIndex直接定位截断档位，比原代码的arrayFilter+length判断更高效
• 增加边界校验：当总挂单量不足目标规模时返回NAN
• 逻辑更清晰，通过arrayZip关联价格与调整后的成交量，过滤无效项

2. 一次性处理多成交量规模 #

通过arrayJoin展开多个目标规模，或用arrayMap生成各规模的VWAP数组，实现批量计算：

方式1：行级展开各规模结果 #

适合需要按时间+规模维度展示数据的场景：

WITH target_sizes = [5, 8, 10]  -- 定义需要计算的多个成交量规模
SELECT
    toDateTime64(exchange_ts/1000, 3) AS ts,
    size,
    calculate_vwap(bid_prices, bid_quantity, size) AS bid_vwap,
    calculate_vwap(ask_prices, ask_quantity, size) AS ask_vwap
FROM my_db.books
LATERAL VIEW arrayJoin(target_sizes) AS size
WHERE toDateTime64(exchange_ts/1000, 3) BETWEEN '2023-10-27 00:00:00' AND '2023-10-28 00:00:00'
ORDER BY ts ASC, size ASC;

方式2：同一行返回多规模结果 #

适合需要保留原始时间行结构的场景：

WITH target_sizes = [5, 8, 10]
SELECT
    toDateTime64(exchange_ts/1000, 3) AS ts,
    arrayMap(size -> calculate_vwap(bid_prices, bid_quantity, size), target_sizes) AS bid_vwaps,
    arrayMap(size -> calculate_vwap(ask_prices, ask_quantity, size), target_sizes) AS ask_vwaps,
    target_sizes AS sizes  -- 标注各VWAP对应的规模
FROM my_db.books
WHERE toDateTime64(exchange_ts/1000, 3) BETWEEN '2023-10-27 00:00:00' AND '2023-10-28 00:00:00'
ORDER BY ts ASC;

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