BigQuery SQL计算员工离职日期前3个月的累计总工时
BigQuery 员工离职前3个月累计工时批量统计实现
核心统计规则
- 统计基准:以每位员工自身最后一次工作记录的日期作为离职参考日,不使用全局统一的时间区间
- 统计范围:从离职参考日向前推3个自然月,到离职参考日当天的所有工作记录
- 输出字段:员工id、对应区间内的累计总工时
计算逻辑验证(匹配示例场景)
以示例员工abc的记录为例:
- 该员工的离职参考日为最后一条工作记录日期
2021-02-04 - 区间左边界为
2020-11-04(即离职日往前3个月) - 落在区间外的记录为2020-10-01的12小时工时,不纳入统计
- 纳入统计的工时累加:4(2020-12-07)+12(2020-12-12)+6(2020-12-25)+9(2021-01-07)+7(2021-02-04)=38,和预期结果一致
可直接运行的BigQuery SQL
WITH employee_stat_range AS ( -- 逐员工计算专属统计区间边界 SELECT id, date, hours, MAX(date) OVER(PARTITION BY id) AS leave_ref_date, DATE_SUB(MAX(date) OVER(PARTITION BY id), INTERVAL 3 MONTH) AS range_start_date FROM `替换为实际业务的工作记录表路径` ) SELECT id, SUM(hours) AS total_hours_in_3m_before_leave FROM employee_stat_range -- 筛选匹配员工自身时间范围的有效记录 WHERE date BETWEEN range_start_date AND leave_ref_date GROUP BY id
实现说明
- 利用窗口函数按员工id分区计算个人专属的时间边界,不需要为单个员工单独写查询,天然支持多员工批量统计
- 自动适配不同员工的离职日期差异,不会出现跨员工的时间区间错位
- 区间采用BETWEEN匹配,自动包含首尾边界日期的工作记录
内容的提问来源于stack exchange,提问作者hustlecorean
相关产品推荐
相关产品推荐

