寻求AWS Redshift中MySQL WEEK()函数的完全等效替代方案

Redshift没有原生函数和MySQL默认的WEEK()行为完全一致，但可以通过自定义函数或表达式实现匹配效果。

先明确MySQL默认WEEK()的规则 #

MySQL默认的WEEK(date)（对应模式0）遵循：

• 周起始日为周日
• 一年的第一周是包含该年第一个周日的周；1月1日到第一个周日之前的日期，周数返回0

比如WEEK('2022-01-01')返回0，因为2022年1月1日是周六，在当年第一个周日（1月2日）之前。

自定义函数实现（推荐） #

创建一个PL/pgSQL函数，完全模拟MySQL默认WEEK()的逻辑：

CREATE OR REPLACE FUNCTION mysql_week(date_val DATE)
RETURNS INTEGER
IMMUTABLE
AS $$
DECLARE
    year_start DATE := DATE_TRUNC('year', date_val)::DATE;
    first_sunday DATE;
BEGIN
    -- 计算当年第一个周日的日期
    first_sunday := year_start + (6 - EXTRACT(DOW FROM year_start)::INTEGER) * INTERVAL '1 day';
    
    -- 日期在第一个周日之前，返回0；否则计算周数
    IF date_val < first_sunday THEN
        RETURN 0;
    ELSE
        RETURN FLOOR((date_val - first_sunday) / 7) + 1;
    END IF;
END;
$$ LANGUAGE plpgsql;

使用示例：

SELECT mysql_week('2022-01-01'); -- 返回0，和MySQL一致
SELECT mysql_week('2022-01-02'); -- 返回1
SELECT mysql_week('2022-12-31'); -- 返回52

纯表达式实现（无需自定义函数） #

如果不想创建函数，可以用以下表达式直接计算：

SELECT
    CASE
        WHEN date_val < year_start + days_to_first_sunday THEN 0
        ELSE FLOOR((date_val - (year_start + days_to_first_sunday)) / 7) + 1
    END AS mysql_week
FROM (
    SELECT
        '2022-01-01'::DATE AS date_val,
        DATE_TRUNC('year', '2022-01-01'::DATE)::DATE AS year_start,
        (6 - EXTRACT(DOW FROM DATE_TRUNC('year', '2022-01-01'::DATE)::DATE)::INTEGER) * INTERVAL '1 day' AS days_to_first_sunday
) AS calc;

关键逻辑说明 #

• EXTRACT(DOW FROM date)：返回日期对应的星期几，0=周日，6=周六
• 计算当年第一个周日：通过year_start + (6 - DOW值)天，得到当年第一个周日的日期
• 日期早于第一个周日时返回0，否则从第一个周日开始按7天为一周计算周数

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