如何用R计算四月至四月财年中日期对应的自定义周数？

假设财年采用四月至四月的日历规则，需要确定任意日期对应的财年周数（范围1-52）。

示例数据 #

example_df <- data.frame(
    full_date = as.Date(c("2020-04-01", "2020-04-08", "2021-03-25", "2021-04-02"))
)

用户自行实现的解法 #

通过将日期映射到财年起始点后重新计算周数：

library(lubridate)

example_df %>%
  mutate(
    date_obj = as.Date(full_date),
    fiscal_year = ifelse(month(date_obj) < 4, year(date_obj) - 1, year(date_obj)),
    fiscal_year_start = as.Date(paste0(fiscal_year, "-04-01")),
    days_since_fy_start = as.numeric(date_obj - fiscal_year_start),
    fiscal_week = ceiling((days_since_fy_start + 1) / 7)
  )

问题：是否存在其他R实现方式？ #

另一个R函数示例 #

以下是一个单独的函数，用于返回日期对应的财年（格式如"2020-2021"）：

get_april_year <- function(dates) {
  dates <- as.Date(dates)
  
  calendar_year <- as.numeric(format(dates, "%Y"))
  month <- as.numeric(format(dates, "%m"))
  
  start_year <- ifelse(month < 4, calendar_year - 1, calendar_year)
  
  fiscal_year <- paste0(start_year, "-", start_year + 1)
  
  return(fiscal_year)
}

test_dates <- as.Date(c("2020-06-02", "2025-02-15", "2020-04-01"))
get_april_year(test_dates)

SQL等效实现 #

如果用SQL实现相同逻辑，写法如下：

SELECT 
    full_date,

    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 THEN EXTRACT(MONTH FROM full_date) + 9
        ELSE EXTRACT(MONTH FROM full_date) - 3
    END AS fiscal_month,
    
    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 
        THEN (EXTRACT(YEAR FROM full_date) - 1) || '-' || EXTRACT(YEAR FROM full_date)
        ELSE EXTRACT(YEAR FROM full_date) || '-' || (EXTRACT(YEAR FROM full_date) + 1)
    END AS fiscal_year,

    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 
        THEN CEIL((full_date - DATE(EXTRACT(YEAR FROM full_date) - 1 || '-04-01')) / 7.0)
        ELSE CEIL((full_date - DATE(EXTRACT(YEAR FROM full_date) || '-04-01')) / 7.0)
    END AS fiscal_week_number
FROM t
WHERE regexp_like(col1, '^[0-9]+$|^[0-9]+\.[0-9]+$');

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