How to compare two arrays and find the the match (0/1) in an array in Clickhouse

user_10695902 0
Viewed 658

Love Arrays in Clickhouse!

At the moment, we are trying to engineer a solve that requires us to find whether elements in array1 exist in array2 regardless of the order.

For e.g.

array1= ['x','y','z']
array2= ['a','x','c']

Then the return that we want is [1,0,0]
  1. Is there a simple out-of-the-box way to accomplish this?
  2. This is part of a bigger query implying that the result from this array comparison will be used as part of the arrayMap((a,b) -> a*b, result, metric_array) -- more of an inline filtering.
  3. If there is any way this can be done, the performance will improve drastically!

Thanks in advance!

clickhouse
3 Answers

Try this query:

SELECT
    ['x', 'y', 'z'] AS a1,
    ['a', 'x', 'c'] AS a2,
    arrayMap(x -> has(a2, x), a1) AS result

/*
┌─a1────────────┬─a2────────────┬─result──┐
│ ['x','y','z'] │ ['a','x','c'] │ [1,0,0] │
└───────────────┴───────────────┴─────────┘
*/

This solution looks ineffective, but make sense to check it too:

SELECT
    ['x', 'y', 'z'] AS a1,
    ['a', 'x', 'c'] AS a2,
    arrayIntersect(a1, a2) AS a3,
    arrayMap(x -> has(a3, x), a1) AS result

/*
┌─a1────────────┬─a2────────────┬─a3────┬─result──┐
│ ['x','y','z'] │ ['a','x','c'] │ ['x'] │ [1,0,0] │
└───────────────┴───────────────┴───────┴─────────┘
*/
user_3032980
user_128384030

Little convoluted, but works for sure!

WITH 
    ['x', 'y', 'z'] AS i, 
    ['a', 'x', 'c'] AS j
SELECT arraySlice(arrayMap(m -> (m - 1), arrayEnumerateUniq(arrayConcat(j, i))), length(j) + 1) AS final

In step by step form

WITH 
    ['x', 'y', 'z'] AS i, 
    ['a', 'x', 'c'] AS j
SELECT 
    arrayConcat(j, i) AS k, 
    arrayEnumerateUniq(k) AS l, 
    arrayMap(a -> (a - 1), l) AS m, 
    arraySlice(m, length(j) + 1) AS final
user_106959020
Related