PostgreSQL按周按候选人统计total数据的实现方法问询

要实现按周分组并按候选人统计total的需求，你可以通过日期截断、区间格式化、分组聚合三步完成，以下是具体SQL语句及说明：

完整SQL示例 #

假设你的表名为candidate_stats，请替换成实际表名：

SELECT
  -- 生成指定格式的周区间字符串
  CASE
    WHEN EXTRACT(MONTH FROM week_start) = EXTRACT(MONTH FROM week_end)
    THEN TO_CHAR(week_start, 'Mon DD', 'en_US') || '-' || TO_CHAR(week_end, 'DD')
    ELSE TO_CHAR(week_start, 'Mon DD', 'en_US') || '-' || TO_CHAR(week_end, 'Mon DD', 'en_US')
  END AS "Date",
  SUM(total) AS total,
  name
FROM (
  -- 先计算每条记录所属周的起始和结束日期
  SELECT
    name,
    total,
    "timestamp",
    -- 按周日作为周起始（若要周一起始，看下方调整说明）
    date_trunc('week', "timestamp") AS week_start,
    date_trunc('week', "timestamp") + INTERVAL '6 days' AS week_end
  FROM candidate_stats
) t
-- 按候选人和周区间分组
GROUP BY name, week_start, week_end
-- 按周排序，保证结果顺序符合示例
ORDER BY week_start, name;

关键部分说明 #

1. 周区间计算

   • date_trunc('week', "timestamp")：将时间戳截断到所在周的起始日期（PostgreSQL默认以周日为一周开始）。
   • 若需要以周一作为周起始，把date_trunc('week', "timestamp")替换为：

     date_trunc('week', "timestamp" + INTERVAL '1 day') - INTERVAL '1 day'
     

2. 日期格式处理

   • 用CASE判断周起始和结束是否在同一个月：同月份只显示一次月份缩写，跨月份则分别显示起止的月份和日期。
   • TO_CHAR(..., 'Mon DD', 'en_US')：强制生成英文月份缩写（如Jan、Feb），避免受数据库locale影响。

3. 分组聚合

   • 按name（候选人）和week_start（周起始，唯一标识一周）分组，用SUM(total)统计每周每个候选人的总数据。

注意事项 #

• 确保你的timestamp字段是timestamp或timestamptz类型，若为字符串需先转换为日期类型（用TO_TIMESTAMP("timestamp", 'YYYY-MM-DD HH24:MI:SS')）。
• 示例中的Jan 23-39应为输入笔误，实际会生成符合日期逻辑的区间（如Jan 23-29）。

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