with
w1 as --所有工作日
(
select
day_name, is_workday
from
dw_com.td_com_day
where 1 = 1
and year_name = '2020'
and is_workday = 'Y'
),
w2 as --找所有非工作日的下一个工作日
(
select
distinct
t1.day_name,
t1.is_workday,
min(w1.day_name)over(partition by t1.day_name ) as aft_work_day
from
dw_com.td_com_day t1
left join w1
on t1.year_name = '2020'
and t1.day_name < w1.day_name
where t1.year_name = '2020' and t1.is_workday = 'N'
),
w3 as --找所有非工作日的上一个工作日
(
select
distinct
t1.day_name,
t1.is_workday,
max(w1.day_name)over(partition by t1.day_name )::date + 1 as first_festal_day
from
dw_com.td_com_day t1
left join w1
on t1.year_name = '2020'
and t1.day_name > w1.day_name
where t1.year_name = '2020' and t1.is_workday = 'N'
)
select
t1.day_name::date,
t1.is_workday,
coalesce (w2.aft_work_day,t1.day_name) as aft_work_day,
case when w3.first_festal_day is null then 0 else count() over(partition by w3.first_festal_day) end as festal_days
from
dw_com.td_com_day t1
left join w2 on t1.day_name = w2.day_name
left join w3 on t1.day_name = w3.day_name
where t1.year_name = '2020'
1min目标场景问卷
立即参与
