SQL实现:无需WHILE循环统计2020年至今停工工时频率率
2020年1月1日至今停工工时频率率无循环实现方案
核心计算规则说明
统计目标为2020年1月1日到当前日期的停工工时频率率,禁止使用WHILE循环实现,涉及两张业务表:
event表:存储停工事件数据,harret字段记录单条事件对应的停工工时employe表:存储在职员工基础信息
固定计算公式:
停工工时频率率 = 统计周期内总停工工时 * 1,000,000 /(单员工统计周期总工时 * 统计范围内员工数)
原SQL写法的问题
当前编写的片段存在两个核心逻辑错误:
- 将
event表和员工统计查询直接用逗号拼接在FROM子句后,未设置关联条件,会触发笛卡尔积运算,导致停工工时求和结果被员工数成倍放大 - 未给停工事件增加时间范围过滤,统计的是全表所有历史停工工时,不符合2020年1月1日起的统计要求
修正后的可运行SQL实现
SELECT SUM(ev.total_stop_work) * 1000000 / (emp.emp_cycle_total_work_hour * emp.emp_num) AS stop_work_freq_rate FROM -- 独立计算符合时间范围的总停工工时 ( SELECT COALESCE(SUM(harret), 0) AS total_stop_work FROM event -- 替换为实际的事件时间字段即可 WHERE event_occur_time >= '2020-01-01' AND event_occur_time <= NOW() ) ev CROSS JOIN -- 独立计算员工数、单员工周期总工时 ( SELECT COUNT(*) AS emp_num, (DATEDIFF(NOW(), '2020-01-01') * 11 / 7) AS emp_cycle_total_work_hour FROM employe ) emp
实现说明
- 全程采用集合化运算完成,完全没有使用WHILE循环,满足实现要求
- 两个聚合子查询独立计算,避免了笛卡尔积导致的结果失真
- 增加了
COALESCE逻辑做空值兜底,当统计周期内没有停工记录时,结果会返回0而不是NULL - 单员工总工时的计算逻辑完全保留原代码里的换算规则:按统计周期的自然天差折算周数,每1周对应11小时的工作时长
内容的提问来源于stack exchange,提问作者legend never die
相关产品推荐
相关产品推荐

