如何在Amazon Redshift设置DATEFIRST为周三并实现自定义周数规则

1. 如何在Amazon Redshift中将DATEFIRST设为周三？ #

Redshift支持兼容SQL Server的DATEFIRST会话级设置，你可以直接执行以下命令：

SET DATEFIRST 3;

这里的数值对应关系是：1=周一，2=周二，3=周三，...，7=周日。需要注意的是，这个设置只对当前会话有效——如果断开重连或者开启新会话，得重新执行这条命令。要是想让所有新会话默认应用这个设置，可以修改Redshift集群的参数组（通过AWS控制台或CLI调整datefirst参数），不过这种全局变更可能影响其他依赖默认周起始的查询，建议谨慎操作。

2. 自定义周范围（周三至周二）生成周数：是否必须设置DATEFIRST？ #

完全不需要依赖DATEFIRST设置，反而直接通过日期计算函数实现会更灵活，能避免全局或会话级设置带来的副作用。下面结合你的示例需求，给出具体的实现方案：

你的需求规则：

默认DATEFIRST为周日时，2017-01-01是周日，但在我的需求中该日期的daynumber=4、week=1；2017-01-02的daynumber=5；2017-01-03的daynumber=6且week=1；下一个周三2017-01-04的daynumber=0、week=2。

方案：通过日期偏移和CASE计算实现 #

我们可以直接基于Redshift内置的日期函数（EXTRACT、DATE_TRUNC等）来计算每个日期对应的daynumber和week：

计算daynumber（周三=0，周二=6）

daynumber本质是当前日期与本周起始周三的天数差，我们先算出每个日期所在周的起始周三，再计算差值：

SELECT
    date_column,
    -- 计算本周起始的周三
    CASE 
        WHEN EXTRACT(DOW FROM date_column) >= 3 THEN date_column - (EXTRACT(DOW FROM date_column) - 3) * INTERVAL '1 day'
        ELSE date_column - (EXTRACT(DOW FROM date_column) + 4) * INTERVAL '1 day'
    END AS week_start_wednesday,
    -- 计算daynumber
    EXTRACT(DAY FROM (date_column - (
        CASE 
            WHEN EXTRACT(DOW FROM date_column) >= 3 THEN date_column - (EXTRACT(DOW FROM date_column) - 3) * INTERVAL '1 day'
            ELSE date_column - (EXTRACT(DOW FROM date_column) + 4) * INTERVAL '1 day'
        END
    ))) AS daynumber
FROM your_table;

计算week（周数）

要让2017-01-01（周日）属于第1周，2017-01-04（周三）属于第2周，我们可以用日期偏移的技巧：把日期往后推4天，这样原本以周三为起始的周，就会对齐到Redshift默认的周日起始周，直接用DATE_PART('week', ...)就能得到正确的周数：

SELECT
    date_column,
    DATE_PART('week', date_column + INTERVAL '4 days') AS week_number
FROM your_table;

合并完整查询（针对DimDate表生成）

如果是生成DimDate表，可以把这两个计算逻辑整合到一起，比如：

WITH date_range AS (
    -- 生成你需要的日期范围，这里以2017年全年为例
    SELECT '2017-01-01'::DATE + (n || ' days')::INTERVAL AS date_column
    FROM generate_series(0, 364) n
)
SELECT
    date_column,
    -- 计算week_start_wednesday
    CASE 
        WHEN EXTRACT(DOW FROM date_column) >= 3 THEN date_column - (EXTRACT(DOW FROM date_column) - 3) * INTERVAL '1 day'
        ELSE date_column - (EXTRACT(DOW FROM date_column) + 4) * INTERVAL '1 day'
    END AS week_start_wednesday,
    -- 计算daynumber
    EXTRACT(DAY FROM (date_column - (
        CASE 
            WHEN EXTRACT(DOW FROM date_column) >= 3 THEN date_column - (EXTRACT(DOW FROM date_column) - 3) * INTERVAL '1 day'
            ELSE date_column - (EXTRACT(DOW FROM date_column) + 4) * INTERVAL '1 day'
        END
    ))) AS daynumber,
    -- 计算week_number
    DATE_PART('week', date_column + INTERVAL '4 days') AS week_number
FROM date_range
ORDER BY date_column;

测试你的示例日期：

• 2017-01-01：daynumber=4，week_number=1
• 2017-01-02：daynumber=5，week_number=1
• 2017-01-03：daynumber=6，week_number=1
• 2017-01-04：daynumber=0，week_number=2

完全符合你的需求。

为什么不推荐依赖DATEFIRST？ #

虽然设置DATEFIRST 3后，部分函数（比如DATEPART(week, ...)）会以周三为周起始，但这种方式存在两个问题：

1. 会话级限制：每次新会话都要重新设置，容易遗漏导致查询结果错误；
2. 全局风险：如果修改集群参数组全局设置，会影响所有依赖默认周起始的其他查询和报表，引发不必要的问题。

所以直接用日期计算的方案更可靠、灵活。

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