PostgreSQL中如何以周六为起始日给日期添加对应周数？

你的问题出在date_part('week', dates)默认采用ISO周标准（周一为周起始，且每年第一周需包含至少4天），和你要求的周六起始规则不匹配，所以结果不符合预期。

以下是适配周六为周起始的SQL代码（基于PostgreSQL）：

WITH weekly_data AS (
    SELECT
        dates,
        -- 计算当前日期所在周的起始日（周六）
        dates - ((EXTRACT(DOW FROM dates) + 1) % 7) * INTERVAL '1 day' AS week_start_saturday
    FROM finaldata
)
SELECT
    EXTRACT(YEAR FROM week_start_saturday) AS year,
    -- 计算当年的第几个周（以周六为起始）
    FLOOR((EXTRACT(DOY FROM week_start_saturday) - 1) / 7) + 1 AS weekly,
    COUNT(dates) AS date_count
FROM weekly_data
GROUP BY year, weekly
ORDER BY year, weekly;

代码说明： #

• 周起始日计算：EXTRACT(DOW FROM dates)返回日期对应的星期几（0=周日，1=周一，…，6=周六）。通过((DOW +1) %7)计算需要回溯的天数，确保任何日期都能得到对应的周六起始日（周六当天回溯0天，周日回溯1天，周五回溯6天）。
• 周数计算：基于周六起始日的年内天数（DOY），计算它是当年的第7天周期，得到以周六为起始的周数。
• 分组统计：按周起始日的年份和周数分组，统计每个周的日期数量。

如果追求更简洁的写法，可以合并CTE的逻辑到主查询中：

SELECT
    EXTRACT(YEAR FROM (dates - ((EXTRACT(DOW FROM dates) + 1) % 7) * INTERVAL '1 day')) AS year,
    FLOOR((EXTRACT(DOY FROM (dates - ((EXTRACT(DOW FROM dates) + 1) % 7) * INTERVAL '1 day')) - 1) / 7) + 1 AS weekly,
    COUNT(dates) AS date_count
FROM finaldata
GROUP BY year, weekly
ORDER BY year, weekly;

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