在BigQuery中基于数组计算各部位相对速度的实现方案

要计算各部位的相对速度，核心是基于数组中连续位置的差值，结合帧率（单帧时间为1/frameRate）计算速度。下面提供两种高效实现方式，适配不同的SQL引擎：

方式一：展开数组+窗口函数（通用型，适配多数SQL引擎） #

这种方式通过展开数组元素并结合窗口函数LAG获取前一位置值，计算后再聚合回数组，兼容性强。

SQL代码（以Spark SQL为例） #

WITH exploded_locations AS (
    -- 展开颈部位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'Neck' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.Neck_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开躯干位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'Trunk' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.Trunk_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开左肩位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'L_Shoulder' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.L_Shoulder_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开右肩位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'R_Shoulder' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.R_Shoulder_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开左肘位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'L_Elbow' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.L_Elbow_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开右肘位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'R_Elbow' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.R_Elbow_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开左膝位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'L_Knee' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.L_Knee_Location) exploded AS pos, loc
    
    UNION ALL
    -- 展开右膝位置数组
    SELECT
        r.reportId,
        r.frameRate,
        'R_Knee' AS body_part,
        pos AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId
    LATERAL VIEW posexplode(l.R_Knee_Location) exploded AS pos, loc
),
velocity_calculations AS (
    SELECT
        reportId,
        body_part,
        frame_idx,
        -- 第一帧无前置位置，速度设为NULL，可按需改为0
        CASE WHEN frame_idx = 0 THEN NULL
             ELSE (position - LAG(position) OVER (PARTITION BY reportId, body_part ORDER BY frame_idx)) * frameRate
        END AS velocity
    FROM exploded_locations
)
-- 聚合回数组格式，与原表结构匹配
SELECT
    reportId,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'Neck') AS Neck_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'Trunk') AS Trunk_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'L_Shoulder') AS L_Shoulder_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'R_Shoulder') AS R_Shoulder_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'L_Elbow') AS L_Elbow_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'R_Elbow') AS R_Elbow_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'L_Knee') AS L_Knee_Velocity,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'R_Knee') AS R_Knee_Velocity
FROM velocity_calculations
GROUP BY reportId;

BigQuery适配版 #

只需将LATERAL VIEW posexplode替换为UNNEST ... WITH OFFSET：

WITH exploded_locations AS (
    SELECT
        r.reportId,
        r.frameRate,
        'Neck' AS body_part,
        offset AS frame_idx,
        loc AS position
    FROM report r
    JOIN location l ON r.reportId = l.reportId,
    UNNEST(l.Neck_Location) AS loc WITH OFFSET
    
    -- 其余部位的UNION ALL结构与Spark版一致，替换posexplode为UNNEST WITH OFFSET
),
velocity_calculations AS (
    SELECT
        reportId,
        body_part,
        frame_idx,
        CASE WHEN frame_idx = 0 THEN NULL
             ELSE (position - LAG(position) OVER (PARTITION BY reportId, body_part ORDER BY frame_idx)) * frameRate
        END AS velocity
    FROM exploded_locations
)
-- 聚合部分与Spark版完全一致
SELECT
    reportId,
    ARRAY_AGG(velocity ORDER BY frame_idx) FILTER (WHERE body_part = 'Neck') AS Neck_Velocity,
    -- 其余部位的聚合语句省略，与Spark版一致
FROM velocity_calculations
GROUP BY reportId;

方式二：数组原生函数（高效型，适配Spark 3.x+、BigQuery等） #

如果你的SQL引擎支持数组操作函数（如zip_with、slice、concat），可以直接在数组层面计算，无需展开聚合，效率更高。

SQL代码（Spark SQL为例） #

SELECT
    r.reportId,
    -- 颈部速度数组：开头补NULL（第一帧无速度），后续为连续位置差值*帧率
    concat(
        array(NULL),
        transform(
            zip_with(
                slice(l.Neck_Location, 2, size(l.Neck_Location)-1),
                slice(l.Neck_Location, 1, size(l.Neck_Location)-1),
                (curr_pos, prev_pos) -> (curr_pos - prev_pos) * r.frameRate
            ),
            vel -> vel
        )
    ) AS Neck_Velocity,
    -- 躯干速度数组
    concat(
        array(NULL),
        transform(
            zip_with(
                slice(l.Trunk_Location, 2, size(l.Trunk_Location)-1),
                slice(l.Trunk_Location, 1, size(l.Trunk_Location)-1),
                (curr_pos, prev_pos) -> (curr_pos - prev_pos) * r.frameRate
            ),
            vel -> vel
        )
    ) AS Trunk_Velocity,
    -- 左肩速度数组
    concat(
        array(NULL),
        transform(
            zip_with(
                slice(l.L_Shoulder_Location, 2, size(l.L_Shoulder_Location)-1),
                slice(l.L_Shoulder_Location, 1, size(l.L_Shoulder_Location)-1),
                (curr_pos, prev_pos) -> (curr_pos - prev_pos) * r.frameRate
            ),
            vel -> vel
        )
    ) AS L_Shoulder_Velocity,
    -- 其余部位的速度数组计算格式与上述一致，省略重复代码
    r.frameRate
FROM report r
JOIN location l ON r.reportId = l.reportId;

说明 #

• 速度计算公式：(当前位置 - 前一位置) * frameRate，等价于(当前位置 - 前一位置) / (1/frameRate)，避免除法运算更高效。
• 第一帧速度处理：默认补NULL，若需要设为0，将array(NULL)改为array(0.0)即可。
• 数组长度：通过concat保证速度数组与原位置数组长度一致，若不需要第一帧的占位值，可直接使用transform+zip_with的结果。

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