sqlarraysamazon-athenaprestotrino

How to calculate an array of differences from an original array?


I have an array of integers and I want to calculate the differences between adjacent elements and return an array of differences.

SELECT ARRAY[3, 2, 5, 1, 2] AS my_arr -- original array

-- my_arr               |
-- ---------------------+
-- {'3','2','5','1','2'}|

desired output

-- my_arr               |arr_of_diffs       |
-- ---------------------+-------------------+
-- {'3','2','5','1','2'}|{'-1','3','-4','1'}|

Is there a direct way to calculate arr_of_diffs from my_arr using trino's array functions?


Solution

  • Trino doesn't have a built-in function for this so you'll have to use transform and manually compute the difference using array indexing :

    WITH data AS (
        SELECT ARRAY[3, 2, 5, 1, 2] AS my_arr
    )
    SELECT
        my_arr,
        transform(
            SEQUENCE(2, cardinality(my_arr)), -- generates indices from 2 to the length of the array
            i -> element_at(my_arr, i) - element_at(my_arr, i - 1) 
        ) AS arr_of_diffs
    FROM data;