目前还不清楚这4天的时间应该是以块为单位，还是像每个日期的4天滑动窗口一样。
将尝试解决这两个问题。首先，使用一些示例数据：

WITH        --  S a m p l e   D a t a :
    tbl AS
        (   Select  To_Date(TRUNC(SYSDATE) - LEVEL) "A_DATE", CEIL(2 * LEVEL /  7) "A_VALUE"
            From    Dual
            Connect By LEVEL <= 10
        )
/*  sample data
A_DATE       A_VALUE
--------- ----------
18-OCT-23          3
19-OCT-23          3
20-OCT-23          3
21-OCT-23          2
22-OCT-23          2
23-OCT-23          2
24-OCT-23          2
25-OCT-23          1
26-OCT-23          1
27-OCT-23          1 */

字符串

• 如果每个日期的滑动窗口为4天
• 你可以使用Sum（）Over（）解析函数和窗口子句（Between.）

Select      A_DATE "PERIOD_FROM", A_DATE + 3 "PERIOD_TO",  
            Sum(A_VALUE) Over(Order By A_DATE Rows Between Current Row And 3 Following) "TOTAL_PERIOD"
From        tbl
Order By    A_DATE
/*      R e s u l t :
PERIOD_FROM PERIOD_TO TOTAL_PERIOD
----------- --------- ------------
18-OCT-23   21-OCT-23           11
19-OCT-23   22-OCT-23           10
20-OCT-23   23-OCT-23            9
21-OCT-23   24-OCT-23            8
22-OCT-23   25-OCT-23            7
23-OCT-23   26-OCT-23            6
24-OCT-23   27-OCT-23            5
25-OCT-23   28-OCT-23            3
26-OCT-23   29-OCT-23            2
27-OCT-23   30-OCT-23            1 */

型
1.在4天的时间段内

• 创建一个cte（命名为periods）来获取起始日期和区块数

periods AS
    ( Select      Min(A_DATE) Over() "MIN_DATE", 
                  Ceil((Max(A_DATE) Over() - Min(A_DATE) Over()) / 4) "PERIODS"
      From        tbl
      Order By    A_DATE
      FETCH FIRST ROW ONLY
    )
/*
MIN_DATE     PERIODS
--------- ----------
18-OCT-23          3 */

型

• 将其与您的数据连接为ON（1 = 1），生成期间的开始和结束日期，并对每个期间进行求和

SELECT   START_DATE, END_DATE, Sum(A_VALUE) "TOTAL_FOR_PERIOD"
FROM    ( Select      ROW_NUMBER() Over(PARTITION BY t.A_DATE Order By t.A_DATE) "RN_DATE",
                      p.MIN_DATE + (LEVEL- 1) * 4 "START_DATE", 
                      (p.MIN_DATE + (LEVEL- 1) * 4) + 3 "END_DATE", 
                      Case When t.A_DATE Between p.MIN_DATE + (LEVEL- 1) * 4 And (p.MIN_DATE + (LEVEL- 1) * 4) + 3 Then t.A_VALUE Else 0 End "A_VALUE"
          From        periods p
          Inner Join  tbl t ON(1 = 1)
          Where       t.A_DATE Between p.MIN_DATE + (LEVEL- 1) * 4 And (p.MIN_DATE + (LEVEL- 1) * 4) + 3
          Connect By  LEVEL <= p.PERIODS
          Order By    p.MIN_DATE + (LEVEL- 1) * 4
        ) 
WHERE RN_DATE = 1
GROUP BY  START_DATE, END_DATE
ORDER BY  START_DATE, END_DATE
/*    R e s u l t :
START_DATE END_DATE  TOTAL_FOR_PERIOD
---------- --------- ----------------
18-OCT-23  21-OCT-23               11
22-OCT-23  25-OCT-23                7
26-OCT-23  29-OCT-23                2 */

型
如果您要查找总计最大的时段，则可以从结果数据集中选择（1或2）

你发布的查询语法是PostgreSQL，但你在Oracle下标记了你的问题，所以你应该改变它。
下面的查询将对4个连续的日期进行分组，并计算每个组的值的总和，然后根据总和以降序排列各组（测试数据在WITH块中）：

WITH tab as (
    select now() - interval '10 days' as date, 5 as value
    union all
    select now() - interval '9 days' as date, 5 as value
    union all
    select now() - interval '8 days' as date, 5 as value
    union all
    select now() - interval '7 days' as date, 5 as value
    union all
    select now() - interval '6 days' as date, 5 as value
    union all
    select now() - interval '5 days' as date, 5 as value
    union all
    select now() - interval '4 days' as date, 5 as value
    union all
    select now() - interval '3 days' as date, 5 as value
    union all
    select now() - interval '2 days' as date, 5 as value
)
SELECT grp, MIN(date) grp_min_date, MAX(date) grp_max_date, SUM(value) grp_sum, DENSE_RANK() over(order by SUM(value) desc) as rank 
FROM (
    SELECT
        t.*, CEIL(ROW_NUMBER() over(order by date) / 4::float) as grp
    FROM tab as t
    WHERE date >= NOW() - INTERVAL '20 years'
    ORDER BY date
) as t1
GROUP BY GRP
ORDER BY rank

字符串