如何在PostgreSQL中实现自定义周起始日的可配置周数计算？

现有PostgreSQL查询返回ISO标准周数（周一为周起始），现在需要实现两个可配置参数：自定义周起始日、查询起止日期，以便Java代码按自定义周分组数据（示例：起止日期2022-11-21至2022-12-05、周起始日为周三时，分组为2022-11-21至2022-11-22、2022-11-23至2022-11-29、2022-11-30至2022-12-05）。需要修改以下原查询的哪些部分？

原查询代码：

select m.tool_id, m.module_location, alarm_count,  recipe_id, alarm_alias, alarm_issuer_name,
DATE_PART('week',alarm_date) AS week, 
DATE_PART('year',alarm_date) AS yearNo
from alarms.alarm_count a, alarms.alarm_module m, alarms.alarm_issuer ai 
where a.module_uuid = m.module_uuid and a.alarm_issuer_uuid = ai.alarm_issuer_uuid 
and m.module_uuid in (
'027909d4-12dd-4b7d-a391-847f88ee97ab',
 '212277f4-9d05-4465-95f7-a99fcb936451')
and (a.alarm_date) BETWEEN '2022-11-21' and '2022-12-05' 
and severity in ('Critical','Error','Fatal')

需要修改的核心部分 #

1. 替换硬编码的查询起止日期为参数 #

把原查询中固定的日期范围替换为参数化写法，方便Java代码传入动态值：

and a.alarm_date BETWEEN :start_date and :end_date

（Java中可通过PreparedStatement设置start_date和end_date两个参数，类型为java.sql.Date或LocalDate）

2. 重构周分组逻辑，替换ISO周为自定义周起始日 #

原查询用DATE_PART('week',alarm_date)获取ISO标准周，需要改为基于自定义起始日的分组逻辑：

• 新增自定义周起始日参数（比如:week_start_day，取值规则：1=周一，2=周二...7=周日）
• 计算每条记录所属的自定义周起始/结束日期，以此作为分组依据：

-- 计算当前记录所属自定义周的起始日期
DATE_TRUNC('day', alarm_date - INTERVAL (:week_start_day - EXTRACT(DOW FROM alarm_date)) || ' days') AS custom_week_start,
-- 计算自定义周的结束日期（如果需要展示，且不超过查询的结束日期）
LEAST(
  DATE_TRUNC('day', alarm_date - INTERVAL (:week_start_day - EXTRACT(DOW FROM alarm_date)) || ' days') + INTERVAL '6 days',
  :end_date
) AS custom_week_end

• 移除原有的DATE_PART('week',alarm_date)和DATE_PART('year',alarm_date)字段，改用上述自定义周字段作为分组标识。

3. 补充分组聚合逻辑（若需按周统计） #

如果原查询需要按自定义周聚合alarm_count，必须添加GROUP BY子句，将所有非聚合字段（包括自定义周字段）纳入分组：

GROUP BY m.tool_id, m.module_location, recipe_id, alarm_alias, alarm_issuer_name, custom_week_start, custom_week_end

同时将alarm_count改为聚合函数（比如SUM(alarm_count)），避免分组报错。

完整修改后的示例查询 #

select 
  m.tool_id, 
  m.module_location, 
  SUM(alarm_count) as alarm_count,
  recipe_id, 
  alarm_alias, 
  alarm_issuer_name,
  DATE_TRUNC('day', alarm_date - INTERVAL (:week_start_day - EXTRACT(DOW FROM alarm_date)) || ' days') AS custom_week_start,
  LEAST(
    DATE_TRUNC('day', alarm_date - INTERVAL (:week_start_day - EXTRACT(DOW FROM alarm_date)) || ' days') + INTERVAL '6 days',
    :end_date
  ) AS custom_week_end
from alarms.alarm_count a
join alarms.alarm_module m on a.module_uuid = m.module_uuid
join alarms.alarm_issuer ai on a.alarm_issuer_uuid = ai.alarm_issuer_uuid
where 
  m.module_uuid in (
    '027909d4-12dd-4b7d-a391-847f88ee97ab',
    '212277f4-9d05-4465-95f7-a99fcb936451'
  )
  and a.alarm_date BETWEEN :start_date and :end_date
  and severity in ('Critical','Error','Fatal')
GROUP BY m.tool_id, m.module_location, recipe_id, alarm_alias, alarm_issuer_name, custom_week_start, custom_week_end

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