SQL解惑 – 谜题2

本文介绍: 处理连续问题标准步骤：（1）按雇员分组，按日期排序，得到rn（2）用日期减r n。

文章 目录

一、谜题描述
二、分析
三、答案
四、总结

一、谜题 描述

创建一个记录雇员缺勤率的数据库。使用的表结构如下：Ab sent ee i sm
主键：PRIMARY KEY (emp _id, ab sent _date)

字段名	字段类型	字段中文名	字段描述
emp _id	INTERGER	雇员 id	–
ab sent _date	STRING	缺勤日期	–
re a son _code	STRING	缺勤原因	–
sever i t y _point s	INTEGER	严重性计分	对缺勤行为进行处罚性计分

如果雇员在一年的时间内严重性计分累计达到40，就自动将该雇员解雇。如果雇员连续缺勤超过一天，就视为长病假，而不是普通的缺勤。这时第二天、第三天和以后的日子中都不会统计该股元的严重性分数，这些天也不算做缺勤。

二、分析

步骤1：将雇员连续缺勤记录的严重性计分置为0；
步骤2：找出一年内严重性计分累计达到40的雇员；
需要用到的关联表：
雇员表：Per son n el

字段名	字段类型	字段中文名	字段描述
emp _id	INTERGER	雇员id	雇员唯一标识
emp _name	STRING	雇员名字	–

日期维度表：Calend ar

字段名	字段类型	字段中文名	字段描述
date	STRING	日期	唯一键
date _type	STRING	日期类型	e g：工作日/周末

三、答案

SparkSQL语法

SQL1：将雇员连续缺勤达到一天的记录的严重性计分置为0；

思路：按雇员分组，按日期排序，得到r n；用日期减r n；用开窗的方式代替group b y 减少表自身关联次数。

select 
	emp_id,
	absent_date,
	reason_code,
	if(absent_cnt &gt; 1,0,severity_points) as severity_points
from 
(
	select 
		emp_id,
		absent_date,
		reason_code,
		severity_points,
		tag_date,
		sum(1)over(partition by emp_id,date_add(absent_date,-rn)) as absent_cnt
	from 
	(
		select 
			emp_id,
			absent_date,
			reason_code,
			severity_points,
			row_number()over(partition by emp_id order by absent_date) as rn
		from Absenteeism
	) in1
) t1

SQL2：找出一年内严重性计分累计达到40的雇员；

select 
	t1.emp_id,
	sum(t1.severity_points)
from t1
left join Calendar t2
where t2.date_type = 'work'
and t1.absent_date between date_add(CURRENT_DATE,-365) and CURRENT_DATE
group by t1.emp_id
having sum(t1.severity_points) &gt;= 40