網(wǎng)站開發(fā)是前端還是后臺有友情鏈接的網(wǎng)站
文章目錄
- 一、謎題描述
- 二、分析
- 三、答案
- 四、總結(jié)
一、謎題描述
創(chuàng)建一個記錄雇員缺勤率的數(shù)據(jù)庫。使用的表結(jié)構(gòu)如下:Absenteeism
主鍵:PRIMARY KEY (emp_id, absent_date)
字段名 | 字段類型 | 字段中文名 | 字段描述 |
---|---|---|---|
emp_id | INTERGER | 雇員id | - |
absent_date | STRING | 缺勤日期 | - |
reason_code | STRING | 缺勤原因 | - |
severity_points | INTEGER | 嚴重性計分 | 對缺勤行為進行處罰性計分 |
如果雇員在一年的時間內(nèi)嚴重性計分累計達到40,就自動將該雇員解雇。如果雇員連續(xù)缺勤超過一天,就視為長病假,而不是普通的缺勤。這時第二天、第三天和以后的日子中都不會統(tǒng)計該股元的嚴重性分數(shù),這些天也不算做缺勤。
二、分析
步驟1:將雇員連續(xù)缺勤記錄的嚴重性計分置為0;
步驟2:找出一年內(nèi)嚴重性計分累計達到40的雇員;
需要用到的關(guān)聯(lián)表:
雇員表:Personnel
字段名 | 字段類型 | 字段中文名 | 字段描述 |
---|---|---|---|
emp_id | INTERGER | 雇員id | 雇員唯一標(biāo)識 |
emp_name | STRING | 雇員名字 | - |
日期維度表:Calendar
字段名 | 字段類型 | 字段中文名 | 字段描述 |
---|---|---|---|
date | STRING | 日期 | 唯一鍵 |
date_type | STRING | 日期類型 | eg:工作日/周末 |
三、答案
SparkSQL語法
SQL1:將雇員連續(xù)缺勤達到一天的記錄的嚴重性計分置為0;
思路:按雇員分組,按日期排序,得到rn;用日期減rn;用開窗的方式代替group by 減少表自身關(guān)聯(lián)次數(shù)。
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:找出一年內(nèi)嚴重性計分累計達到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
四、總結(jié)
處理連續(xù)問題
標(biāo)準(zhǔn)步驟:
(1)按雇員分組,按日期排序,得到rn
(2)用日期減rn