如何在Snowflake中实现SAP HANA的workdays_between()函数功能？

要在Snowflake中复现HANA的workdays_between逻辑，核心是将TFACS的月度工作日标记转换为可按日期查询的结构，再实现日期范围的工作日统计。以下是两种可行方案：

方案一：扁平化TFACS日历视图 + 简单UDF #

先将TFACS的年度月度列结构转为每日记录，再通过UDF统计指定日期范围的工作日数。

1. 创建扁平化日历视图 #

将TFACS的MON01-MON12列拆分为每日的工作日标记（假设每个MON列是字符串，每个字符对应当月一天，1为工作日，0为假日）：

CREATE OR REPLACE VIEW FLATTENED_TFACS AS
SELECT
    IDENT,
    -- 拼接年份、月份、日期生成完整日期
    DATE_FROM_PARTS(JAHR, MONTHS.INDEX + 1, DAYS.INDEX + 1) AS CAL_DATE,
    -- 标记是否为工作日
    CASE WHEN DAYS.VALUE = '1' THEN TRUE ELSE FALSE END AS IS_WORKDAY
FROM TFACS
-- 拆分12个月度列
LATERAL FLATTEN(
    INPUT => [MON01, MON02, MON03, MON04, MON05, MON06, MON07, MON08, MON09, MON10, MON11, MON12],
    INDEX => MONTHS_INDEX
) AS MONTHS
-- 拆分月度字符串为每日字符
LATERAL FLATTEN(
    INPUT => SPLIT(MONTHS.VALUE, ''),
    INDEX => DAYS_INDEX
) AS DAYS
-- 过滤掉超出当月实际天数的无效记录
WHERE (DAYS.INDEX + 1) <= DAYOFMONTH(DATE_FROM_PARTS(JAHR, MONTHS.INDEX + 2, 0));

2. 创建WORKDAYS_BETWEEN UDF #

基于扁平化视图实现日期范围的工作日统计，同时处理起始日期大于结束日期的情况：

CREATE OR REPLACE FUNCTION WORKDAYS_BETWEEN(START_DATE DATE, END_DATE DATE, FACTORY_IDENT VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS $$
    WITH DATE_RANGE AS (
        SELECT
            LEAST(START_DATE, END_DATE) AS LOWER_BOUND,
            GREATEST(START_DATE, END_DATE) AS UPPER_BOUND,
            SIGN(END_DATE - START_DATE) AS DATE_SIGN
    )
    SELECT COUNT(*) * DATE_SIGN
    FROM FLATTENED_TFACS
    JOIN DATE_RANGE ON CAL_DATE BETWEEN DATE_RANGE.LOWER_BOUND AND DATE_RANGE.UPPER_BOUND
    WHERE IDENT = FACTORY_IDENT AND IS_WORKDAY = TRUE;
$$;

方案优势 #

• 逻辑清晰，易于维护和调试
• 适合频繁查询的场景，查询性能稳定

方案二：直接计算（无需额外视图） #

如果不想创建扁平化视图，可以直接在UDF中按年份、月份拆分计算工作日数，适合TFACS数据量较小的场景：

CREATE OR REPLACE FUNCTION WORKDAYS_BETWEEN(START_DATE DATE, END_DATE DATE, FACTORY_IDENT VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS $$
    WITH DATE_BOUNDS AS (
        SELECT
            LEAST(START_DATE, END_DATE) AS LOWER_DATE,
            GREATEST(START_DATE, END_DATE) AS UPPER_DATE,
            SIGN(END_DATE - START_DATE) AS SIGN
    ),
    -- 统计完整年份的工作日数
    FULL_YEARS_WORKDAYS AS (
        SELECT SUM(LENGTH(REPLACE(MON01||MON02||MON03||MON04||MON05||MON06||MON07||MON08||MON09||MON10||MON11||MON12, '0', ''))) AS TOTAL
        FROM TFACS
        JOIN DATE_BOUNDS ON JAHR BETWEEN YEAR(LOWER_DATE)+1 AND YEAR(UPPER_DATE)-1
        WHERE IDENT = FACTORY_IDENT
    ),
    -- 统计起始年份的剩余工作日数
    START_YEAR_WORKDAYS AS (
        SELECT
            SUM(
                CASE MONTH_NUM
                    WHEN MONTH(LOWER_DATE) THEN LENGTH(REPLACE(SUBSTR(MON_COL, DAY(LOWER_DATE)), '0', ''))
                    ELSE LENGTH(REPLACE(MON_COL, '0', ''))
                END
            ) AS TOTAL
        FROM TFACS
        JOIN DATE_BOUNDS ON JAHR = YEAR(LOWER_DATE)
        LATERAL FLATTEN(
            INPUT => [MON01, MON02, MON03, MON04, MON05, MON06, MON07, MON08, MON09, MON10, MON11, MON12],
            INDEX => MONTH_NUM
        ) AS MONTHS
        WHERE IDENT = FACTORY_IDENT AND MONTH_NUM >= MONTH(LOWER_DATE)
    ),
    -- 统计结束年份的前期工作日数
    END_YEAR_WORKDAYS AS (
        SELECT
            SUM(
                CASE MONTH_NUM
                    WHEN MONTH(UPPER_DATE) THEN LENGTH(REPLACE(SUBSTR(MON_COL, 1, DAY(UPPER_DATE)), '0', ''))
                    ELSE LENGTH(REPLACE(MON_COL, '0', ''))
                END
            ) AS TOTAL
        FROM TFACS
        JOIN DATE_BOUNDS ON JAHR = YEAR(UPPER_DATE)
        LATERAL FLATTEN(
            INPUT => [MON01, MON02, MON03, MON04, MON05, MON06, MON07, MON08, MON09, MON10, MON11, MON12],
            INDEX => MONTH_NUM
        ) AS MONTHS
        WHERE IDENT = FACTORY_IDENT AND MONTH_NUM <= MONTH(UPPER_DATE)
    )
    SELECT
        COALESCE(FULL_YEARS_WORKDAYS.TOTAL, 0) +
        COALESCE(START_YEAR_WORKDAYS.TOTAL, 0) +
        COALESCE(END_YEAR_WORKDAYS.TOTAL, 0) * SIGN
    FROM FULL_YEARS_WORKDAYS, START_YEAR_WORKDAYS, END_YEAR_WORKDAYS;
$$;

方案优势 #

• 无需创建额外视图，减少对象维护
• 适合TFACS数据量小、查询频率低的场景

注意事项 #

1. 确保TFACS的MON01-MON12列是字符串格式，每个字符对应当月的一天；如果是数字类型，需先转为字符串（如TO_VARCHAR(MON01)）。
2. 若TFACS中存在缺失年份/月份的记录，可根据业务需求调整COALESCE的默认值（比如返回NULL或假设缺失日期为工作日）。

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