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?
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;