'array_intersect_at_sizes' cannot be used for binary arrays.

[meharc]

I'm comparing a column of arrays called 'fingerprint,' which consists of binary arrays with a size of 30 elements each. The objective is to determine the number of matching elements between two arrays.

Example 1:
Array a = [1,1,1,0] and array b = [1,1,1,1].
Expected overlap size: 3.
Output using array_intersect_at_sizes: 1.

Example 2:
Array a = [1,0,1,0,1,1] and array b = [1,0,1,1,1,1].
Expected overlap size: 5.
Output using array_intersect_at_sizes: 1.

Example 3
Array a = [0,1,1,0] and array b = [0,1,1,0].
Expected overlap size: 4.
Output using array_intersect_at_sizes: 1.

[RobinL]

The function does not output the size of the intersection, it places the size of the intersection in user specified bins. Take a look at the human_readable_description or the docstring of the comparison level

[meharc]

I understand your point. However, isn't the fundamental essence of the function essentially size(array_intersect({col_name_l}, {col_name_r}))? It determines the overlap size and distributes it into user-defined bins. Nevertheless, if my array is binary, then the size will consistently be either 1 or 2. Consequently, regardless of the specified bins, only two bins with values 1 and 2 will be generated.

Reference code
cl.array_intersect_at_sizes("cal_fingerprint_cleaned", size_or_sizes = [30, 27, 23])

[RobinL]

Ah, I understand. Sorry am on my phone. So in your arrays position matters? The function is really designed for the case of an unordered 'bag of things ' like zip codes. I think you'd have to write a custom SQL comparison level to get the behaviour you want (or split the array into individual columns)

[meharc]

Yes, custom SQL or I might encode the binary array into another range. Thanks!