如何从单一日期列拆分起止日期？用SQL窗口函数或Spark Dataframe实现

现有业务数据表包含Employee ID、Date、DepartmentID、SupervisorID四个字段，同一DepartmentID下存在同一员工多条不同日期的记录。需要将单一Date列拆分为DateStart和DateEnd两个字段，按Employee ID、DepartmentID分组输出：

• 每个部门对应的DateStart为该员工在该部门下的最早日期
• DateEnd为该员工下一个任职部门的最早日期
• 该员工最后一个任职部门的DateEnd取值为Null

Employee ID      Date           DepartmentID    SupervisorID
10001            20130101          001             10009
10001            20130909          001             10019
10001            20131201          002             10018
10001            20140501          002             10017
10001            20141001          003             10015
10001            20141201          003             10014

方案1：SQL窗口函数实现 #

核心逻辑为先按员工、部门分组聚合得到每个部门的最早入职日期，再通过LEAD窗口函数取下一行的部门起始日期作为当前部门的结束日期：

WITH dept_start AS (
    -- 分组计算每个员工在每个部门的最早任职日期
    SELECT 
        `Employee ID`,
        DepartmentID,
        MIN(`Date`) AS DateStart
    FROM 你的业务表名
    GROUP BY `Employee ID`, DepartmentID
)
SELECT 
    `Employee ID`,
    DateStart,
    -- 按员工分区，按日期升序排序，取下一行的DateStart作为当前行的DateEnd
    LEAD(DateStart, 1) OVER (PARTITION BY `Employee ID` ORDER BY DateStart) AS DateEnd,
    DepartmentID
FROM dept_start
ORDER BY `Employee ID`, DateStart;

方案2：Spark DataFrame实现（PySpark为例） #

和SQL逻辑一致，先聚合再调用窗口函数计算：

from pyspark.sql import SparkSession
from pyspark.sql.functions import min, lead
from pyspark.sql.window import Window

# 假设df为读取到的原始业务数据DataFrame
# 1. 聚合得到每个员工对应每个部门的最早任职日期
dept_start_df = df.groupBy("Employee ID", "DepartmentID") \
    .agg(min("Date").alias("DateStart"))

# 2. 定义窗口规则：按员工ID分区，按部门起始日期升序排序
window_spec = Window.partitionBy("Employee ID").orderBy("DateStart")

# 3. 计算DateEnd并输出指定字段
result_df = dept_start_df.withColumn("DateEnd", lead("DateStart", 1).over(window_spec)) \
    .select("Employee ID", "DateStart", "DateEnd", "DepartmentID") \
    .orderBy("Employee ID", "DateStart")

# 查看结果
result_df.show()

Employee ID    DateStart    DateEnd      DepartmentID
10001         20130101      20131201       001
10001         20131201      20141001       002
10001         20141001       Null          003

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