嗯。你可以用铅和一个累积的总和来做这个。逻辑基本上是：
如果上一个日期在同一个月，中间有一个星期六，则加1
从1开始，开始一个月的第一天。
逻辑如下：

select t.*,
         sum(case when extract(year_month from date) <> extract(year_month from prev_date) or prev_date is null
                  then 1
                  when datediff(date, prev_date) >= 7
                  then 1
                  when day = 'Fri' or
                       day = 'Thu' and prev_day not in ('Fri') or
                       day = 'Wed' and prev_day not in ('Fri', 'Thu') or
                       day = 'Tue' and prev_day not in ('Fri', 'Thu', 'Wed') or
                       day = 'Mon' and prev_day not in ('Fri', 'Thu', 'Wed', 'Tue') or
                       day = 'Sun' and prev_day not in ('Fri', 'Thu', 'Wed', 'Tue', 'Mon') 
                  then 0
                  else 1
             end) over (order by date) as week_num

  from (select t.*,
               lag(date) over (order by date) as prev_date,
               lag(day) over (order by day) as prev_day
        from t
       ) t