刚好之前帮同事处理过类似的周维度聚合需求——把每周统计范围从「周一至周日」改成「周五至下周四」，你的CASE语句思路方向是对的，不过可以结合日期函数优化，避免踩一些数据库日期格式的坑，同时让逻辑更清晰。

核心逻辑 #

我们需要把：

• 周五、周六、周日的日期，归属到当前周五作为起始的周
• 周一、周二、周三、周四的日期，归属到上一个周五作为起始的周

这样聚合时，这七天就会被归为同一个统计周期。

方法一：CASE语句直观实现 #

用CASE判断星期几，直接映射到对应的周起始日。这里建议用DY（星期缩写）而非DAY（全拼），因为部分数据库（比如Oracle）的DAY会返回带空格的字符串（比如'FRIDAY '），容易导致判断失效：

SELECT 
  TRANSACTIONDATE,
  -- 计算该日期所属的「周五起始周」的起始日期
  CASE 
    WHEN TO_CHAR(TRANSACTIONDATE, 'DY') IN ('MON', 'TUE', 'WED', 'THU') THEN
      TRANSACTIONDATE - INTERVAL '4' DAY  -- 周一到周四，减4天得到上周五
    ELSE
      TRUNC(TRANSACTIONDATE)  -- 周五、六、日，直接取当天日期作为周起始
  END AS WEEK_START_FRIDAY
FROM YOUR_TABLE;

方法二：日期函数简化计算（更高效） #

如果用数据库自带的周截断函数，可以更简洁地实现，不同数据库的写法略有不同：

Oracle

SELECT
  TRANSACTIONDATE,
  -- 逻辑：先把日期偏移3天，让周五对齐到周一的位置，截断到周（周一）后再偏移回去
  TRUNC(TRANSACTIONDATE + 3, 'IW') - INTERVAL '3' DAY AS WEEK_START_FRIDAY
FROM YOUR_TABLE;

PostgreSQL

SELECT
  TRANSACTIONDATE,
  DATE_TRUNC('week', TRANSACTIONDATE + INTERVAL '3 days') - INTERVAL '3 days' AS WEEK_START_FRIDAY
FROM YOUR_TABLE;

MySQL

SELECT
  TRANSACTIONDATE,
  -- MySQL中DAYOFWEEK()返回1=周日，2=周一...6=周五，7=周六
  DATE_SUB(TRANSACTIONDATE, INTERVAL (CASE 
    WHEN DAYOFWEEK(TRANSACTIONDATE) IN (2,3,4,5) THEN 4  -- 周一到周四减4天
    ELSE (DAYOFWEEK(TRANSACTIONDATE) - 6)  -- 周五减0，周六减1，周日减2
  END) DAY) AS WEEK_START_FRIDAY
FROM YOUR_TABLE;

聚合统计示例 #

拿到周起始日后，就可以直接按这个字段做聚合了：

SELECT
  WEEK_START_FRIDAY,
  COUNT(*) AS total_transactions,
  SUM(transaction_amount) AS total_revenue
FROM (
  SELECT
    TRANSACTIONDATE,
    transaction_amount,
    -- 这里嵌入上面的周起始日计算逻辑
    CASE 
      WHEN TO_CHAR(TRANSACTIONDATE, 'DY') IN ('MON', 'TUE', 'WED', 'THU') THEN
        TRANSACTIONDATE - INTERVAL '4' DAY
      ELSE
        TRUNC(TRANSACTIONDATE)
    END AS WEEK_START_FRIDAY
  FROM YOUR_TRANSACTION_TABLE
) aggregated_data
GROUP BY WEEK_START_FRIDAY
ORDER BY WEEK_START_FRIDAY DESC;

避坑提示 #

• 不同数据库的星期数值定义不同，比如Oracle的TO_CHAR(date, 'D')返回1=周日，2=周一；而MySQL的DAYOFWEEK()返回1=周日，2=周一，一定要根据自己用的数据库调整判断条件。
• 测试时拿几个关键日期验证：比如周五（2024-09-06）、周日（2024-09-08）、周一（2024-09-09）、周四（2024-09-12），确认它们都归属到同一个周起始日（2024-09-06）。

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