PostgreSQL生成带周数、年份的周序列出现重复数据如何修复？

问题根因 #

• 原始SQL中generate_series的步长设置为6天，每6天生成一个候选起始日，会出现两个日期落在同一ISO周的情况，造成同一周返回多条记录
• 直接提取日期的自然年作为周对应年份，会出现跨年周的周数和年份不匹配问题，例如每年1月1日可能归属上一年的最后一周，12月31日可能归属下一年的第一周

推荐修复方案（从生成逻辑优化） #

PostgreSQL内置的ISO周规则天然以周一作为周首日，直接对齐周起始日+修改步长即可从源头避免重复：

with weeks as (
    select generate_series(
        -- date_trunc('week', date)会自动返回输入日期所在周的周一，符合周首日要求
        date_trunc('week', '2020-01-01'::date)::date,
        current_date,
        -- 步长改为1周，确保每次生成的都是下一周的周一
        '1 week'::interval
    ) as week_starting_date
)
select 
    row_number() over (order by week_starting_date) as id,
    extract(week from week_starting_date) as week_number,
    -- 提取ISO周对应的年份，解决跨年周匹配问题
    extract(isoyear from week_starting_date) as week_year,
    week_starting_date::date as week_start_date,
    (week_starting_date + interval '6 days')::date as week_end_date
from weeks;

兼容原有逻辑的修复方案（仅去重） #

如果不希望修改原始的生成逻辑，可以通过distinct on按周分组取最早的起始日：

with weeks as (
    select generate_series('2020-01-01'::date, current_date, '6 day') as week_starting_date
),
week_with_attr as (
    select 
        week_starting_date,
        extract(week from week_starting_date) as week_number,
        extract(isoyear from week_starting_date) as week_year,
        (week_starting_date + interval '6 day')::date as week_end_date
    from weeks
)
select distinct on (week_year, week_number)
    row_number() over (order by week_starting_date) as id,
    week_number,
    week_year,
    week_starting_date::date as week_start_date,
    week_end_date
from week_with_attr
order by week_year, week_number, week_starting_date asc;

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