问题描述
目前有员工职位变动明细数据,按天存放。目标是得到员工职位变动的拉链表。
原始数据表tableA,存放员工在每一天的岗位&职位信息。tableA的字段:emp_id, pos_id, pos_nm, job_id, date
date的形式是 yyyymmdd
目标是按照emp_id, pos_id, pos_nm, job_id 聚合,得到员工在每个岗位职位变动的起始&终止时间,目标 字段:
- emp_id, pos_id, pos_nm, job_id, stat_date,end_date
在网上看到的拉链表初始化,都是先写入第一天的数据,然后每一天往里增加,比较麻烦。
这里提供一个直接读取历史全量数据,直接进行初始化的方法:
SQL
直接上代码
--读取数据
with a as (
select emp_id ,pos_id,pos_nm,job_id,concat(substring(date,0,4) ,"-",substring(date,5,2),"-",substring(date,7,2) ) as date
from tableA
)
--然后再按照 emp_id, pos_id, pos_nm, job_id 和组进行聚合
SELECT emp_id, pos_id, pos_nm, job_id,
MIN(date) AS start_date, MAX(date) AS end_date
FROM (
--先使用窗口函数和LAG函数将相邻的日期分到同一个组中
SELECT emp_id, pos_id, pos_nm, job_id, date,
date_add(date, -row_number() OVER (PARTITION BY emp_id, pos_id, pos_nm, job_id ORDER BY date)) AS grp
FROM a
) t
GROUP BY emp_id, pos_id, pos_nm, job_id, grp
ORDER BY emp_id, pos_id, pos_nm, job_id, start_date
代码解读:
这个SQL语句的思路是先使用窗口函数和LAG函数将相邻的日期分到同一个组中,然后再按照 emp_id, pos_id, pos_nm, job_id 和组进行聚合,得到每个组的起始日期和结束日期。最后按照 emp_id, pos_id, pos_nm, job_id 和起始日期进行排序输出。
参考网页
- lag() 函数说明:https://blog.csdn.net/KEVIN_WANG333/article/details/132362551