PostgreSQL如何查询去年前后6个月及当前日期前5周的数据

假设业务表名为data_records，可根据实际表名替换后直接执行以下SQL：

• 查询去年前6个月（1-26周）数据
  直接匹配去年年份 + 周数1-26范围即可：

  SELECT * FROM data_records
  WHERE 
    current_year = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 year')::INTEGER
    AND current_week_number BETWEEN 1 AND 26;
  

• 查询去年后6个月（26-52周）数据
  若不需要和前6个月的26周数据重复，可将周数范围调整为27-52：

  SELECT * FROM data_records
  WHERE 
    current_year = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 year')::INTEGER
    AND current_week_number BETWEEN 26 AND 52;
  

• 查询当前日期往前最近5周的数据
  内置跨年场景处理逻辑，无需额外手动调整年份判断：

  WITH current_time_params AS (
    SELECT
      EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER AS curr_year,
      EXTRACT(WEEK FROM CURRENT_DATE)::INTEGER AS curr_week
  )
  SELECT dr.* 
  FROM data_records dr, current_time_params ctp
  WHERE 
    (dr.current_year * 100 + dr.current_week_number) >= 
    CASE
      WHEN ctp.curr_week >= 5 THEN ctp.curr_year * 100 + (ctp.curr_week - 4)
      ELSE (ctp.curr_year - 1) * 100 + (52 - (5 - ctp.curr_week) + 1)
    END
    AND (dr.current_year * 100 + dr.current_week_number) <= ctp.curr_year * 100 + ctp.curr_week;
  

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