基于累计总耗时计算单圈耗时的PySpark/SQL/Pandas高效实现

核心规则 #

输入数据每行对应1名人员完成1圈的统计条目，仅包含两个字段：

• PersonID：人员ID
• Total Time Elapsed (sec)：当前圈结束时的计时累计总耗时（单位：秒）
  需计算生成Lap Time（单圈耗时，单位：秒），规则为：
• 同一人员首圈单圈耗时 = 该圈累计总耗时
• 同一人员后续每圈单圈耗时 = 当前圈累计总耗时 - 该人员上一圈累计总耗时

校验示例：PersonID=1的三圈累计耗时为200、300、550秒时，对应单圈耗时应为200、100、250秒。

所有方案均采用向量化/原生窗口计算实现，完全替代低效逐行for循环，性能随数据量增长优势明显。

Pandas 实现 #

基于pandas向量化运算+分组移位实现，千万级以内数据量性能最优：

# 先按人员、累计耗时升序排序，保证圈次顺序正确
df = df.sort_values(by=["PersonID", "Total Time Elapsed (sec)"], ignore_index=True)
# 分组取上一圈累计耗时，首圈默认填充0，直接做向量减法
df["Lap Time"] = df["Total Time Elapsed (sec)"] - df.groupby("PersonID")["Total Time Elapsed (sec)"].shift(fill_value=0)

标准SQL 实现 #

基于ANSI SQL标准窗口函数实现，兼容MySQL8.0+、Hive、SparkSQL、PostgreSQL等绝大多数主流数据库/查询引擎：

SELECT
    PersonID,
    `Total Time Elapsed (sec)`,
    `Total Time Elapsed (sec)` - LAG(`Total Time Elapsed (sec)`, 1, 0) OVER (
        PARTITION BY PersonID
        ORDER BY `Total Time Elapsed (sec)`
    ) AS `Lap Time`
FROM your_running_table;

PySpark 实现 #

基于Spark原生窗口函数实现，分布式计算逻辑下TB级大数据量性能最优，避免使用Python UDF、collect后循环等低效写法：

from pyspark.sql import Window
import pyspark.sql.functions as F

# 定义窗口分区排序规则：同人员分区，按累计耗时升序对应圈次先后
lap_window = Window.partitionBy("PersonID").orderBy("Total Time Elapsed (sec)")

result_df = raw_df.withColumn(
    "Lap Time",
    F.col("Total Time Elapsed (sec)") - F.lag("Total Time Elapsed (sec)", 1, 0).over(lap_window)
)

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