SQL解惑 - 谜题2
文章目录
- 一、谜题描述
- 二、分析
- 三、答案
- 四、总结
一、谜题描述
创建一个记录雇员缺勤率的数据库。使用的表结构如下：Absenteeism
主键：PRIMARY KEY (emp_id, absent_date)
| 字段名 | 字段类型 | 字段中文名 | 字段描述 |
|---|---|---|---|
| emp_id | INTERGER | 雇员id | - |
| absent_date | STRING | 缺勤日期 | - |
| reason_code | STRING | 缺勤原因 | - |
| severity_points | INTEGER | 严重性计分 | 对缺勤行为进行处罚性计分 |
如果雇员在一年的时间内严重性计分累计达到40，就自动将该雇员解雇。如果雇员连续缺勤超过一天，就视为长病假，而不是普通的缺勤。这时第二天、第三天和以后的日子中都不会统计该股元的严重性分数，这些天也不算做缺勤。
二、分析
步骤1：将雇员连续缺勤记录的严重性计分置为0；
步骤2：找出一年内严重性计分累计达到40的雇员；
需要用到的关联表：
雇员表：Personnel
| 字段名 | 字段类型 | 字段中文名 | 字段描述 |
|---|---|---|---|
| emp_id | INTERGER | 雇员id | 雇员唯一标识 |
| emp_name | STRING | 雇员名字 | - |
日期维度表：Calendar
| 字段名 | 字段类型 | 字段中文名 | 字段描述 |
|---|---|---|---|
| date | STRING | 日期 | 唯一键 |
| date_type | STRING | 日期类型 | eg：工作日/周末 |
三、答案
SparkSQL语法
SQL1：将雇员连续缺勤达到一天的记录的严重性计分置为0；
思路：按雇员分组，按日期排序，得到rn；用日期减rn；用开窗的方式代替group by 减少表自身关联次数。
select emp_id,absent_date,reason_code,if(absent_cnt > 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_cntfrom (select emp_id,absent_date,reason_code,severity_points,row_number()over(partition by emp_id order by absent_date) as rnfrom 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) >= 40
四、总结
处理连续问题
标准步骤：
（1）按雇员分组，按日期排序，得到rn
（2）用日期减rn