CODEWITHSUNDEEP
arrayspresto
Arun Kumar Dave
Arun Kumar Dave

Reputation: 121

How can i compare two arrays in Presto?

I want to compare two arrays using presto functions. I have two arrays (A & B), I need to compare and check till what point (Index) the input array matches with the given comparison array (X).

Input_Array - A[1,2,4,5,3,9]
Input_Array - B[1,2,3,7,0,5,4,9]

Comparison_Array - X[1,2,3,4]

Output: 
A - Index : 2
B - Index : 3

Upvotes: 2

Views: 6793

Answers (2)

Ashish
Ashish

Reputation: 5791

I would recommend to write down your custom function to handle this kind of requirement. This way you can manage the code easily and incorporate new changes without much effort.

Guide for writing a new function in presto. https://trino.io/docs/current/develop/functions.html

After writing your function, add the plugin to the plugin directory as explained in SPI Overview.

There is another example for writing presto UDF from Qubole blog. http://www.qubole.com/blog/product/plugging-in-presto-udfs/

You can try it if you can make it work.

Upvotes: 2

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

You can use Presto reduce array reduction function, combined with ROW subscript [n] (available since Presto 314). (For older Presto versions this would be more verbose but still equally possible. Let me know if you're on an older version and need help.)

SELECT
  reduce(
    sequence(0, cardinality(a)), -- numbers [0..n)
    ROW(true, 0), -- (still matching, position matched)
    (state, i) ->
      IF (state[1] AND i < cardinality(a) AND i < cardinality(x) AND a[i + 1] = x[i + 1],
        ROW(true, i + 1),
        ROW(false, state[2])),
    state -> state[2])
FROM (VALUES
    (ARRAY[1,2,4,5,3,9], ARRAY[1,2,3,4]),
    (ARRAY[1,2,3,7,0,5,4,9], ARRAY[1,2,3,4]),
    (ARRAY[1,2], ARRAY[1,2,3,4]),
    (ARRAY[], ARRAY[1,2,3,4])) t(a, x);

This prints:

 _col0
-------
     2
     3
     2
     0
(4 rows)

Upvotes: 3

Related Questions