Azure Data Factory中员工出勤工作日计算问题求解

方案一：数据流（Data Flow）实现（修正配置逻辑） #

步骤1：源数据预处理 #

• 从CSV读取数据后，添加派生列生成自增行号row_id = row_number()，用于保证同员工记录的处理顺序；同时确保working day列被识别为日期类型，格式指定为MM/dd/yyyy。

步骤2：窗口转换（Window Transformation） #

• 按emp id分组，排序字段选择row_id，确保同员工记录按原始顺序处理。
• 在窗口中定义两个字段：
  • last_valid_work_day：取当前行之前最近一次working=1对应的日期，表达式为max(iff(working == 1, working day, null())) over (partition by emp_id order by row_id rows between unbounded preceding and current row)
  • absent_count：统计从最近一次出勤到当前行的缺勤次数，表达式为count(iff(working == 0, 1, null())) over (partition by emp_id order by row_id rows between last_value(working == 1) over (partition by emp_id order by row_id) + 1 and current row)

步骤3：派生列生成最终工作日 #

• 新增派生列final_working_day，逻辑表达式为：

iif(working == 1, working day, addDays(last_valid_work_day, absent_count))

• 逻辑说明：出勤时直接用当前日期；缺勤时，基于最近一次出勤日期累加缺勤天数，得到对应的下一个工作日。

方案二：管道+Azure SQL存储过程（适合复杂场景） #

如果数据流的窗口逻辑难以处理连续缺勤，可借助SQL的递归CTE实现：

1. 用Copy活动将CSV数据导入Azure SQL临时表#emp_attendance。
2. 添加存储过程活动，调用以下逻辑的存储过程：

WITH cte_attendance AS (
    SELECT 
        emp_id,
        working,
        CONVERT(date, working_day, 101) AS working_day,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY (SELECT NULL)) AS rn
    FROM #emp_attendance
),
cte_filled AS (
    SELECT 
        emp_id,
        working,
        working_day,
        rn
    FROM cte_attendance
    WHERE rn = 1
    UNION ALL
    SELECT 
        ca.emp_id,
        ca.working,
        CASE 
            WHEN ca.working = 1 THEN ca.working_day
            ELSE DATEADD(day, 1, cf.working_day)
        END AS working_day,
        ca.rn
    FROM cte_attendance ca
    JOIN cte_filled cf ON ca.emp_id = cf.emp_id AND ca.rn = cf.rn + 1
)
SELECT emp_id, working, working_day FROM cte_filled ORDER BY emp_id, rn;

3. 用Copy活动将处理后的结果导出到目标存储。

关键注意点 #

• 必须保证同员工的记录顺序唯一且固定，否则所有依赖顺序的逻辑都会出错，建议用自增行号或原始数据中的时间戳作为排序依据。
• 日期格式需统一，转换时指定正确的格式字符串，避免出现日期解析错误。

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