我不知道怎么做，甚至不知道这是否可能。。。。我正在尝试以第二高的值联接表。我试过rownumber、lag、lead和rank，但没能让他们中的任何一个做我需要的。总而言之，我只是尝试将activitydate表下移一行，以便在rolldate减1时加入（但不能使用-1，因为它们的日期不一致，缺少天）
有人知道这样做的好方法吗？如有任何建议，我们将不胜感激！

Select 
         ds.activitydate
        ,sum(ws.weeklyTotals / ds.daysBetween) as newRunRates -- getting an average of daily activity from weekly totals 
    from 
        (select 
             fsc.activitydate
            ,fsc.weekstart
            ,max(fsc.activitydate) OVER (partition by fsc.weekstart) as rollUpDate
            ,datediff(to_date(max(fsc.activitydate) OVER (partition by fsc.weekstart)), to_date(fsc.weekstart)) + 1 as daysBetween
        from fiscalcalendar fsc
        ) ds  -- used this to get a week-ending date bc that is what I need to join on. I only have a week start in this table 
    left join 
        (select 
             activitydate_iso 
            ,count(distinct assignedmaincomponentid) as weeklyTotals 
        from activityTable 
        group by 1
        ) ws  -- weeklySplits -- this gives me my weekly totals by a week ending date 
    on ds.rollUpDate = ws.activitydate_iso 
    -- need this join logic to actually be
    -- on ds.rollUpDate = (max(ws.activitydate_iso) where activitydate_iso < rollUpDate) 
    where activitydate between '2020-05-22' and '2020-06-15'
    group by 1,2
    order by 1,2 ```