Presto - How can i filter array values that are occurring consecutively

user_7347146 0
Viewed 2247

I want to filter out the values from an array that are occurring consecutively.

I tried using the "array_distinct" & "transform" function in presto, but couldn't progress. As array_distinct only gives distinct values not what i expect

Input : [1,2,2,3,4,4,4,2]

Expected Output : [1,2,3,4,2]

presto arrays
2 Answers

You can inspect consecutive elements by combining ngrams, filter, transform, like this:

presto> SELECT
     ->     ARRAY[a[1]] ||
     ->     transform(
     ->         filter(
     ->             ngrams(a, 2),
     ->             pair -> pair[1] IS DISTINCT FROM pair[2]),
     ->         pair -> pair[2])
     -> FROM (VALUES ARRAY[1,2,2,3,4,4,4,2]) t(a);
      _col0
-----------------
 [1, 2, 3, 4, 2]
(1 row)

Explanation:

  • ngrams(a, 2) builds sibling pairs
  • filter is then used to retain only the elements that are not proceeded by identical one; IS DISTINCT FROM is used for more NULL-friendly comparison that ordinary =.
  • transform is used to convert from siblings back to flat array
  • first element is added at the front (otherwise it would be always missing)

See https://trino.io/docs/current/functions/array.html for all array functions available.

user_654580
user_6392560
Related