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