用于时间序列分析的月快照递归雪花查询，删除更改部门的用户更改前的记录

Question

这是这个问题的后继问题，它解释了这个查询的目的，并提供了一个源数据的示例。

在帮助下，我运行了这个递归查询，它比我的非递归查询更有效，重复了36次并合并在一起。

这个查询的目的是知道每个月月底员工在哪个部门。这段代码的问题在于，对于更改部门的员工，它只返回最近更改部门之后几个月的月末部门值，而不返回之前的记录。对于更改部门的员工，输出应包含以下数据:

Month - Department Code
0 - 100
1 - 100
2 - 200
3 - 200

当前返回:

Month - Department Code
0 - 100
1 - 100

下面是查询:

WITH Q AS (
select 
row_number() over(order by null) as q_level,
last_day(dateadd(month, -q_level, CURRENT_DATE), month) as last_day_month
from table(generator(ROWCOUNT=>36))
), Q1 AS (
select 
q.q_level
,q.last_day_month
,v_dept_history_adj.associate_id             
,v_dept_history_adj.home_department_code
,v_dept_history_adj.position_effective_date
,max(position_effective_date) OVER(PARTITION BY v_dept_history_adj.associate_id) AS most_recent_record 
from datawarehouse.srctable
,Q
where v_dept_history_adj.position_effective_date <= q.last_day_month
)
select 
associate_id
,position_effective_date
,home_department_code
,most_recent_record
,last_day_month AS month
FROM Q1
where position_effective_date = most_recent_record
order by month desc, position_effective_date desc

Answer 1

所以你的问题是有意义的:

要获得每个员工每月最新的部门，我将这样写这个查询:

with emp_data(emp_id, dep_id, date) as (
select * from values
(1, 10, '2022-01-01'::date),
(1, 20, '2022-07-10'::date),
(2, 10, '2022-07-14'::date)
), last_36_months as (
select 
row_number() over(order by null) as q_level,
last_day(dateadd(month, -q_level, CURRENT_DATE), month) as last_day_month
--from table(generator(ROWCOUNT=>36))
from table(generator(ROWCOUNT=>12))
), month_end_data as (
select 
e.emp_id
,e.dep_id
,l.last_day_month as month
from last_36_months as l
join emp_data as e
on e.date <= l.last_day_month
qualify row_number() over(partition by e.emp_id, l.last_day_month order by e.date desc) = 1
)
select * 
from month_end_data
order by 1,3 desc;

我将36减少到12，并将数据移动到2022，因此输出不那么冗长，但它给出:

<表类>EMP_IDDEP_ID月tbody><<tr>1202022-10-311202022-09-301202022-08-311202022-07-311102022-06-301102022-05-311102022-04-301102022-03-311102022-02-281102022-01-312102022-10-312102022-09-302102022-08-312102022-07-31