从sql中的week列派生4周的period列

你可以用 row_number() min() s和 max()

select
    date,
    periodId,
    concat(
        cast(min(date) over(partition by periodId) as varchar),
        ':',
        cast(max(date) over(partition by periodId) as varchar)
    ) period
from (
    select date, (row_number() over(order by date desc) + 3) / 4 periodId       
    from mytable 
) t 

这假设 date 属于 数据类型。如果是一根绳子 yyymmdd 格式,无需 cast()

select
    date,
    periodId,
    concat(
        min(date) over(partition by periodId),
        ':',
        max(date) over(partition by periodId)
    ) period    
from (
    select date, (row_number() over(order by date desc) + 3) / 4 periodId       
    from mytable 
) t 

Demo on DB Fiddle :

date       | periodId | period               
:--------- | :------- | :--------------------
2019-11-25 | 1        | 2019-11-04:2019-11-25
2019-11-18 | 1        | 2019-11-04:2019-11-25
2019-11-11 | 1        | 2019-11-04:2019-11-25
2019-11-04 | 1        | 2019-11-04:2019-11-25
2019-10-28 | 2        | 2019-10-07:2019-10-28
2019-10-21 | 2        | 2019-10-07:2019-10-28
2019-10-14 | 2        | 2019-10-07:2019-10-28
2019-10-07 | 2        | 2019-10-07:2019-10-28
2019-09-30 | 3        | 2019-09-30:2019-09-30

select date,
       row_number() over (order by date) + 3 as period,
       concat(convert(varchar(10), 
                      dateadd(week, row_number() over (order by date) - 1, date)
                     ),
              ':',
              convert(varchar(10), 
                      dateadd(week, row_number() over (order by date) + 3, date)
                     )
             ) as dates
from t;