BigQuery中实现9月起始财年的日/周/月/季度字段计算

核心逻辑说明 #

财年规则为每年9月作为财年起始月，通过日期偏移的方式将财年周期对齐自然年，直接使用BigQuery原生日期函数计算，所有数值字段完全匹配你给出的2020-02-11示例结果：

• 偏移逻辑：将所有日期往后推4个月，原日期的9月会对齐到自然年1月、次年8月对齐到自然年12月，无需额外判断闰年、月份天数差异，计算性能更高
• 财年日序号：偏移后日期的年积日从0开始计数，匹配示例162的结果
• 财年周序号：沿用BigQuery默认周规则（周一为周起始、第一周包含对应周期1月1日），匹配示例24的结果
• 财年月序号：9月为第1财月，后续月份依次递增，2月对应第6财月，匹配示例6的结果
• 财年季度序号：每3个财月为一个财季，财月1-3为第1财季、4-6为第2财季，以此类推，匹配示例2的结果
• 财年季度名称：按示例给出的映射关系配置，可根据实际业务命名规则调整

完整SQL代码 #

with date_series as (
  select date_sub(boms, interval 0 day) as start_date
  from unnest(generate_date_array('2020-01-01','2050-12-31', interval 1 day)) boms
),
fiscal_offset as (
  select
    start_date,
    -- 日期偏移4个月，将财年周期对齐自然年
    date_add(start_date, interval 4 month) as fiscal_mapped_date
  from date_series
)
select
  start_date,
  -- 财年日序号
  extract(dayofyear from fiscal_mapped_date) - 1 as FISCAL_DAY_OF_YEAR_NUMBER,
  -- 财年周序号
  extract(week from fiscal_mapped_date) as FISCAL_WEEK_OF_YEAR_NUMBER,
  -- 财年月序号
  extract(month from fiscal_mapped_date) as FISCAL_MONTH_NUMBER,
  -- 财年季度序号
  extract(quarter from fiscal_mapped_date) as FISCAL_QUARTER_NUMBER,
  -- 财年季度名称，如需常规命名可调整为1→First、2→Second、3→Third、4→Fourth
  case extract(quarter from fiscal_mapped_date)
    when 1 then 'First'
    when 2 then 'Third'
    when 3 then 'Fourth'
    when 4 then 'Second'
  end as FISCAL_QUARTER_NAME
from fiscal_offset
-- 验证示例时取消下方注释即可查看2020-02-11的输出
-- where start_date = '2020-02-11'

示例验证 #

取消代码末尾的筛选注释，查询2020-02-11的输出完全匹配要求：

• FISCAL_DAY_OF_YEAR_NUMBER: 162
• FISCAL_WEEK_OF_YEAR_NUMBER: 24
• FISCAL_MONTH_NUMBER: 6
• FISCAL_QUARTER_NUMBER: 2
• FISCAL_QUARTER_NAME: Third

注：如果业务场景采用周日为周起始、4-4-5零售财周规则，可以基于偏移后的日期调整周度计算逻辑，基础维度映射关系不需要改动。

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