generate_series function让您的问题变得简单：
在下面的示例中，100天前到未来100天之间的所有记录将按周分组，星期一是一周的第一天。
如果您想硬编码一个日期范围，只需将generate_series中的参数替换为timestamp s（或转换为timestamp的日期）。

WITH Weeks(Day) AS (    
SELECT generate_series(current_timestamp - interval '100 days', current_timestamp + interval '100 days', '1 week')::date 
    + 1 - date_part('dow', current_date)::integer /* Sets Monday as first day of the week */
)
SELECT from_date, to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (SELECT DAY, LEAD(Day) OVER (ORDER BY Day) - 1 FROM Weeks) W(from_date, to_date)
  ON run_date BETWEEN from_date and to_date
GROUP BY from_date, to_date
ORDER BY from_date, to_date

字符串
您也可以不根据Monday-Sunday逻辑对记录进行分组，而是使用runs表所包含的内容。

WITH Weeks(Day) AS (    
SELECT generate_series((SELECT MIN(run_date) FROM runs) , (SELECT MAX(run_date) + 6 FROM runs), '1 week')::date 
)
SELECT from_date, to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (SELECT DAY, LEAD(Day) OVER (ORDER BY Day) - 1 FROM Weeks) W(from_date, to_date)
  ON run_date BETWEEN from_date and to_date
GROUP BY from_date, to_date
ORDER BY from_date

型

附加说明：

首先，如果你没有注意到，我会在一周的每个上限上减去1。这是因为BETWEEN包括下限和上限。所以对于BETWEEN，在一周的最后一天发生的运行也会被认为是在下周的第一天发生的。
你可以从相同的结果中使用run_date >= from_date and run_date < to_date来代替run_date BETWEEN from_date and to_date - 1。
此外，查询计算W的部分只是在这里获取generate_series返回的连续日期中的2个。有一种稍微更紧凑的方式来表达同样的想法：

SELECT from_date, (from_date - 1 + interval '1 week')::date AS to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (
   SELECT generate_series(current_timestamp - interval '100 days', current_timestamp + interval '100 days', '1 week')::date
) W(from_date)
ON run_date BETWEEN from_date and from_date - 1 + interval '1 week'
GROUP BY from_date
ORDER BY from_date

型
然而，我觉得这不太方便：如果你想改变分组，例如从1到2周，你必须确保在查询的3个地方（在SELECT，子查询和ON内部）而不是一个地方。

使用以下查询计算平均每英里配速：

SELECT
   CASE
     WHEN run_date BETWEEN current_date - INTERVAL '7 days' AND current_date THEN 'This Week'
     WHEN run_date BETWEEN current_date - INTERVAL '14 days' AND current_date - INTERVAL '8 days' THEN 'Last week'
     WHEN run_date BETWEEN current_date + INTERVAL '7 days' AND current_date + INTERVAL '14 days' THEN 'Next Week 1'
     WHEN run_date BETWEEN current_date + INTERVAL '14 days' AND current_date + INTERVAL '21 days' THEN 'Next Week 2'
     WHEN run_date BETWEEN current_date + INTERVAL '21 days' AND current_date + INTERVAL '28 days' THEN 'Next Week 3'
     WHEN run_date BETWEEN current_date + INTERVAL '28 days' AND current_date + INTERVAL '35 days' THEN 'Next Week 4'
   END US week,
   TO_CHAR(SUM(run_time) / SUM(distance), 'MI:SS') AS average_pace
FROM runs
WHERE run_date BETWEEN current_date - INTERVAL '21 days' AND current_date + INTERVAL '28 days'
GROUP BY week;

字符串
此查询返回本周、上周和未来4周的平均每英里配速。根据需要调整间隔。

参见递归构造日历表的例子。同样，你可以使用generate_series代替递归。我引用了递归的使用，以使解决方法完整。
需要设置报告的end date和报告数据集包含的number of weeks。
它们可以是请求参数。

with RECURSIVE report_days as(
 select date_part('ISOYEAR',endDate) yy
  ,date_part('week',endDate) wn
  ,endDate dt
  ,date_add( date_add(endDate,(-weeks ||' week')::interval)
            ,(8-date_part('ISODOW',endDate) ||' days')::interval
    ) first_date
       -- there is parameters endDate and week count
 from ( select '2024-01-14'::date endDate,7::int weeks) myParameters
union all
 select date_part('ISOYEAR',date_add(r.dt,(-1||' day')::interval)::date) yy
  ,date_part('week',date_add(r.dt,(-1||' day')::interval)::date) wn
  ,date_add(r.dt,(-1||' day')::interval)::date dt 
  ,first_date
 from report_days r
 where date_add(r.dt,(-1||' day')::interval)::date>=first_date
)
,allRunsByDate as(
select *
from report_days c
left join runs r on c.dt=r.run_date
)
select yy as year,wn as weekNum
  ,case when sum(run_time)is not null then sum(run_time)/sum(distance) end avg_pace_per_mile
  ,min(dt) from_dt,max(dt) to_dt
  ,count(id) week_qty,sum(distance) week_distance
  ,sum(run_time) week_run_time
from allRunsByDate c
group by yy,wn
order by yy,wn

字符串
计算

date_add( date_add(endDate,(-weeks ||' week')::interval)
            ,(8-date_part('ISODOW',endDate) ||' days')::interval
    ) first_date

型
需要在first_date附近度过整整一周。
例如，如果end_date的day_of_week（DOW）为星期三（date_part（'ISODOW'，endDate）=3）
所以
date_add（endDate，（-weeks）的day_of_week|| 'week'）：：interval）
也是星期三，
我们在first_date上加上（8-3）=5天，所以first_date的day_of_week always is monday。有3+（8-3）=3+5=8->8-8/7*7=1。
所以，前几周是整周，最后一周是一周。
date_part函数需要使用ISOYEAR键。当一周的开始与一年的开始不一致时，这很重要。另请参阅@atmo的评论
如果你不需要花整整一周的时间，这可以简化。
测试数据的

insert into runs values
  (1, 10, '01:35:00', '2023-12-10'),
  (2, 10, '01:48:00', '2023-12-16'),
  (3, 10, '01:25:00', '2023-12-17'),
  (4, 10, '01:41:00', '2023-12-23'),
  (5, 20, '02:41:00', '2023-12-23'),
  (6, 15, '02:01:00', '2023-12-25'),
  (7, 15, '02:10:00', '2024-01-05')
  ;

型
要求7周的结果是
| 年|weeknum|每英里平均速度|从DT|到dt|周数量|周距|周运行时间|
| --|--|--|--|--|--|--|--|
| 2023 | 48 |null| 2023-11-27 2023-11-27 2023-11-27| 2023-12-03 - 2023 -03 - 01| 0 |null| null|
| 2023 | 49 |时间00：09：30| 2023-12-04 2023-12-04 2023-12-04| 2023-12-10| 1 | 10 |时间01：35：00|
| 2023 | 50 |时间00：09：39| 2023-12-11 - 02 - 02 - 2023-12-11 - 02 - 02| 2023-12-17 2023-12-17| 2 | 20 |三点十三分|
| 2023 | 51 |时间00：08：44| 2023-12-18 2023-12-18| 2023-12-24 - 2023 - 2024 - 2025| 2 | 30 |四点二十二分|
| 2023 | 52 |时间00：08：04| 2023-12-25 - 2023-12-25| 2023-12-31 - 2023| 1 | 15 |02：01：00|
| 2024 | 1 |时间00：08：40| 2024-01-01 2024-01-01| 2024-01-07 2024-01-07| 1 | 15 |两点十分|
| 2024 | 2 |null| 2024-01-08 2024-01-08| 2024-01-14 2024-01-14 2024-01-14| 0 |null| null|

你可以使用带有@符号的变量并调用它。Getdate（）获取你的系统日期，它可以被修改。作为你的数据结构没有时间作为日期的一部分，你可以使用CAST（）来改变它的数据类型。

Declare @Now Date
set @Now = getdate()

Declare @LastWeek Date
set @LastWeek = CAST(GETDATE()-7 as date )

Declare @WeekThree
set @WeekThree = CAST(GETDATE()-14 as date)

Declare @WeekFour
set @Weekfour = CAST(GETDATE() -21 as date)

select ID, run_date, sum(run_time) / sum(distance) AS average_pace
from runs
Between @Lastweek AND @Now

UNION

select ID, run_date, sum(run_time) / sum(distance) AS average_pace
from runs

字符串