Clickhouse：基于索引数组与值数组映射生成目标数组

问题背景 #

现有查询语句：

SELECT [2, 0, 7]                      AS transaction_day,
       [7, 10, 14]                    AS revenue_on_transaction_day,
       [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] AS all_days

需要基于all_days生成新数组：当all_days中的日期存在于transaction_day时，填充对应的revenue_on_transaction_day值，其余日期填充0，期望结果为：

[10, 0, 7, 0, 0, 0, 0, 14, 0, 0]

解决方案1：利用数组转Map实现（推荐） #

用arrayZip将日期和收入数组打包成键值对Map，再通过arrayMap结合mapGet完成映射：

SELECT
    [2, 0, 7] AS transaction_day,
    [7, 10, 14] AS revenue_on_transaction_day,
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] AS all_days,
    -- 核心逻辑：遍历all_days，从映射Map中取值，无匹配则返回0
    arrayMap(
        day -> mapGet(arrayZip(transaction_day, revenue_on_transaction_day), day, 0),
        all_days
    ) AS result_array

逻辑说明

• arrayZip(transaction_day, revenue_on_transaction_day)：把两个数组合并为键值对Map，即{2:7, 0:10, 7:14}
• mapGet(..., day, 0)：根据当前日期day从Map中取对应收入值，若日期不存在则返回默认值0
• arrayMap：遍历all_days的每个元素，执行上述取值逻辑，生成最终数组

解决方案2：利用数组位置匹配实现 #

通过arrayPosition查找日期在transaction_day中的位置，再对应取收入值：

SELECT
    [2, 0, 7] AS transaction_day,
    [7, 10, 14] AS revenue_on_transaction_day,
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] AS all_days,
    arrayMap(
        day -> if(
            arrayPosition(transaction_day, day) != 0,
            revenue_on_transaction_day[arrayPosition(transaction_day, day)],
            0
        ),
        all_days
    ) AS result_array

逻辑说明

• arrayPosition(transaction_day, day)：返回day在transaction_day中的索引位置，不存在则返回0
• 当位置不为0时，取revenue_on_transaction_day对应索引的收入值，否则返回0

两种方案执行后，都能得到期望的结果数组。

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