Clickhouse - Map an array based on array with indexes and array with values - Stack Overflow

I have:

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

I want to get an array mapped based on [all_days] where days with revenue will have a value and other days will be 0:

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

I have:

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

I want to get an array mapped based on [all_days] where days with revenue will have a value and other days will be 0:

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

• arrays
• clickhouse
• array-map

Share Improve this question Follow edited Mar 27 at 3:52 vladimir 15.3k33 gold badges5151 silver badges8080 bronze badges asked Mar 26 at 19:57 Anastasiya MysiukAnastasiya Mysiuk 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Try this way:

WITH
    [2, 0, 7] AS transaction_day,
    [7, 10, 14] AS revenue_on_transaction_day,
    0 AS default_revenue,
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] AS all_days,
    mapFromArrays(transaction_day, revenue_on_transaction_day) AS map_of_tr_days
SELECT 
    arrayMap(
        x -> mapContains(map_of_tr_days, x) ? map_of_tr_days[x] : default_revenue,
        all_days) AS result

/*
   ┌─result──────────────────┐
1. │ [10,0,7,0,0,0,0,14,0,0] │
   └─────────────────────────┘
*/