Redshift中财年起始为2020/12/26周六时fisc_day_of_wk计算方法

你需要将原SQL中fisc_day_of_wk字段的默认ISO周几计算逻辑，替换为基于财年起始日的差值取模逻辑，即可满足需求。 #

修改后完整SQL #

select 
  cast(NEW_SEQ AS INTEGER) as calndr_key
, date (datum)    as calndr_dt
-- 替换原fisc_day_of_wk计算逻辑
, CASE MOD(DATEDIFF(day, '2020-12-26'::date, datum),7) 
    WHEN 0 THEN 7 
    ELSE MOD(DATEDIFF(day, '2020-12-26'::date, datum),7) 
  END as fisc_day_of_wk
, CAST(to_char(datum, 'DD') AS INTEGER ) as fisc_day_of_mth
, CAST(to_char(datum, 'DDD') AS INTEGER ) as fisc_day_of_yr
-- 若需要财周也匹配财年规则，可替换为以下计算逻辑，原WW为自然周不符合要求
, FLOOR(DATEDIFF(day, '2020-12-26'::date, datum)/7) +1 as fisc_wk
, CAST(to_char(datum, 'MM') AS INTEGER ) as fisc_mth
, CAST(to_char(datum, 'YYYYMM') AS INTEGER) as fisc_mth_num
-- 财周开始日期（每周周六）
, datum - MOD(DATEDIFF(day, '2020-12-26'::date, datum),7) as fisc_wk_strt_dt
-- 财周结束日期（每周周五）
, datum - MOD(DATEDIFF(day, '2020-12-26'::date, datum),7) +6 as fisc_wk_end_dt
, date (date_trunc('Month', datum)) as fisc_mth_strt_dt
, last_day(datum) as fisc_mth_end_dt
   FROM
(    SELECT DATEADD('day', SEQ, MAX_DATE) AS datum
                ,(MAX_SEQ + SEQ)  AS NEW_SEQ
   FROM (SELECT ROW_NUMBER() OVER () AS SEQ FROM ads_devl.dim_calndr LIMIT 365) n,
   (SELECT MAX(calndr_key) AS MAX_SEQ FROM ads_devl.dim_calndr) ,
   (SELECT MAX(calndr_DT) AS MAX_DATE FROM ads_devl.dim_calndr where calndr_yr < '2021')
  ORDER BY 1)

逻辑说明 #

• 以财年起始日2020-12-26（周六）为基准，计算当前日期与基准日的间隔天数
• 间隔天数对7取模后，结果为0时返回7（对应财年首日及后续每一周的周六），其余结果直接返回，完全匹配「首日=7、后续依次赋值1/2/3…」的循环规则
• 若后续需要扩展多财年计算，只需将固定基准日'2020-12-26'::date替换为对应日期所属财年的起始日计算逻辑即可

内容的提问来源于stack exchange，提问作者Mala Kumari