Snowflake PostgreSQL多日期列间工作日天数计算报错求助

方法1：修正日期序列生成 + 过滤计算 #

Snowflake中不能直接在generate_series中引用表列，需通过LATERAL横向连接关联每行数据的日期范围，同时调整过滤逻辑避免语法报错：

WITH dates_to_omit (omit_date) AS (
    SELECT * FROM VALUES
        (DATE '2023-07-01'), (DATE '2023-07-02'), (DATE '2023-07-04'),
        (DATE '2023-07-08'), (DATE '2023-07-09'), (DATE '2023-07-15'),
        (DATE '2023-07-16'), (DATE '2023-07-22'), (DATE '2023-07-23'),
        (DATE '2023-07-29'), (DATE '2023-07-30'), (DATE '2023-08-05'),
        (DATE '2023-08-06'), (DATE '2023-08-12'), (DATE '2023-08-13'),
        (DATE '2023-08-19'), (DATE '2023-08-20'), (DATE '2023-08-26'),
        (DATE '2023-08-27'), (DATE '2023-09-02'), (DATE '2023-09-03'),
        (DATE '2023-09-04'), (DATE '2023-09-09'), (DATE '2023-09-10'),
        (DATE '2023-09-16'), (DATE '2023-09-17')
)
SELECT
    t1.client,
    t1.category,
    COUNT(CASE WHEN EXTRACT(DOW FROM s.date_val) NOT IN (0, 6) AND s.date_val NOT IN (SELECT omit_date FROM dates_to_omit) THEN 1 END) - 1 AS order_to_invoice,
    COUNT(CASE WHEN EXTRACT(DOW FROM s2.date_val) NOT IN (0, 6) AND s2.date_val NOT IN (SELECT omit_date FROM dates_to_omit) THEN 1 END) - 1 AS invoice_to_fill,
    COUNT(CASE WHEN EXTRACT(DOW FROM s3.date_val) NOT IN (0, 6) AND s3.date_val NOT IN (SELECT omit_date FROM dates_to_omit) THEN 1 END) - 1 AS fill_to_ship
FROM t1
LEFT JOIN LATERAL (SELECT DATEADD(DAY, seq4(), t1.order_date) AS date_val FROM TABLE(GENERATOR(ROWCOUNT => 1000)) WHERE DATEADD(DAY, seq4(), t1.order_date) <= t1.invoice_date) s ON TRUE
LEFT JOIN LATERAL (SELECT DATEADD(DAY, seq4(), t1.invoice_date) AS date_val FROM TABLE(GENERATOR(ROWCOUNT => 1000)) WHERE DATEADD(DAY, seq4(), t1.invoice_date) <= t1.date_fulfilled) s2 ON TRUE
LEFT JOIN LATERAL (SELECT DATEADD(DAY, seq4(), t1.date_fulfilled) AS date_val FROM TABLE(GENERATOR(ROWCOUNT => 1000)) WHERE DATEADD(DAY, seq4(), t1.date_fulfilled) <= t1.date_shipped) s3 ON TRUE
GROUP BY t1.client, t1.category;

关键修正点： #

• Snowflake用TABLE(GENERATOR(ROWCOUNT => N))结合DATEADD生成日期序列，替代PostgreSQL的generate_series
• 通过LATERAL连接让每行数据生成对应的日期范围
• 用CASE WHEN替代FILTER解决语法兼容问题，同时减1（日期序列包含起始和结束日期，间隔天数为计数减1）
• Snowflake的DOW中0代表周日、6代表周六，需排除这两个值

方法2：使用Snowflake内置函数NETWORKDAYS（更高效） #

Snowflake自带NETWORKDAYS函数计算工作日间隔（默认排除周末），可通过参数指定自定义节假日，性能远优于生成日期序列：

WITH dates_to_omit (omit_date) AS (
    SELECT * FROM VALUES
        (DATE '2023-07-01'), (DATE '2023-07-02'), (DATE '2023-07-04'),
        (DATE '2023-07-08'), (DATE '2023-07-09'), (DATE '2023-07-15'),
        (DATE '2023-07-16'), (DATE '2023-07-22'), (DATE '2023-07-23'),
        (DATE '2023-07-29'), (DATE '2023-07-30'), (DATE '2023-08-05'),
        (DATE '2023-08-06'), (DATE '2023-08-12'), (DATE '2023-08-13'),
        (DATE '2023-08-19'), (DATE '2023-08-20'), (DATE '2023-08-26'),
        (DATE '2023-08-27'), (DATE '2023-09-02'), (DATE '2023-09-03'),
        (DATE '2023-09-04'), (DATE '2023-09-09'), (DATE '2023-09-10'),
        (DATE '2023-09-16'), (DATE '2023-09-17')
),
holidays AS (SELECT ARRAY_AGG(omit_date) AS holiday_list FROM dates_to_omit)
SELECT
    t1.client,
    t1.category,
    NETWORKDAYS(t1.order_date, t1.invoice_date, holidays.holiday_list) - 1 AS order_to_invoice,
    NETWORKDAYS(t1.invoice_date, t1.date_fulfilled, holidays.holiday_list) - 1 AS invoice_to_fill,
    NETWORKDAYS(t1.date_fulfilled, t1.date_shipped, holidays.holiday_list) - 1 AS fill_to_ship
FROM t1, holidays;

优势： #

• 无需生成大量日期序列，性能更优，适合大数据量场景
• 内置函数逻辑可靠，减少自定义代码的出错概率

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