如何在Spark SQL中计算数组相邻元素差值（无需UDF）

给定如下结构的数据集：

+--------+--------------------+
|user_id |array_of_items      |
+--------+--------------------+
|4049494 |[x1, x2, x3, x4, x5]|
|3046908 |[x6, x7, x8, x9]    |
...

需要生成包含相邻元素差值的数组列，结果如下：

+--------+----------------------------+
|user_id |array_of_items_diff         |
+--------+----------------------------+
|4049494 |[x2-x1, x3-x2, x4-x3, x5-x4]|
|3046908 |[x7-x6, x8-x7, x9-x8]       |
...

可以通过SQL窗口函数和数组拆分聚合实现，无需自定义UDF，以下是Spark SQL/Hive SQL的实现方案：

WITH exploded_data AS (
    SELECT 
        user_id,
        pos,
        item,
        -- 获取同用户下前一个位置的数组元素
        LAG(item) OVER (PARTITION BY user_id ORDER BY pos) AS prev_item
    FROM your_table
    -- 将数组拆分为带位置索引的单行记录
    LATERAL VIEW posexplode(array_of_items) exploded AS pos, item
)
SELECT 
    user_id,
    -- 聚合差值字符串为目标数组
    COLLECT_LIST(CONCAT(item, '-', prev_item)) AS array_of_items_diff
FROM exploded_data
-- 过滤掉数组第一个元素（无前置元素）
WHERE prev_item IS NOT NULL
GROUP BY user_id
ORDER BY user_id;

逻辑说明： #

• posexplode：将数组的每个元素与其对应的位置索引（从0开始）拆分为单独行，每个user_id会生成与数组长度相等的行数。
• LAG窗口函数：按user_id分组、位置排序，获取当前元素的前一个数组元素，为差值计算提供前置值。
• 过滤与聚合：排除没有前置元素的数组首项，再将每个user_id下的差值字符串重新聚合为数组，得到目标列。

内容的提问来源于stack exchange，提问作者Vladimir Shadrin