sql - 具有两个不同分区的 LAG 函数
问题描述
我有一张里面有多个日历的表格,我需要获取表格中每一行的前一个工作日。
Calendar ID Date Business Day Previous Business Day
AC1 24/12/2030 Y -
AC1 25/12/2030 N 24/12/2030
AC1 26/12/2030 N 24/12/2030
AC1 27/12/2030 Y 24/12/2030
AC1 28/12/2030 N 27/12/2030
AC1 29/12/2030 N 27/12/2030
AC1 30/12/2030 Y 27/12/2030
AC1 31/12/2030 Y 30/12/2030
AC2 01/01/2010 Y -
AC2 02/01/2010 N 01/01/2010
AC2 03/01/2010 N 01/01/2010
AC2 04/01/2010 Y 01/01/2010
AC2 05/01/2010 Y 04/01/2010
我一直在尝试使用 LAG 函数来实现这一点,但我无法为每个项目获得正确的回报。我最终把它分成两个查询。当一天是工作日时,我可以使用:
CASE WHEN BUSINESS_DAY = 'Y' THEN LAG(CALENDAR_DATE,1,null) OVER(PARTITION BY CALENDAR_ID Order By CALENDAR_ID asc, CALENDAR_DATE asc) FROM CALENDAR Where BUSINESS_DAY = 'Y'
当它是非工作日时,我不得不将它放入表函数并交叉应用(有数百万行,这非常慢)。有没有更好的办法?
解决方案
这不是差距和孤岛问题。这只是一个累积最大值问题:
select t.*,
max(case when businessday = 'Y' then date end) over
(partition by calendarid
order by date
rows between unbounded preceding and 1 preceding
) as prev_businessday
from t;
编辑:
下一个工作日的等效逻辑是:
min(case when businessday = 'Y' then date end) over
(partition by calendarid
order by date
rows between 1 following and unbounded following
) as next_businessday
您还可以反转排序:
min(case when businessday = 'Y' then date end) over
(partition by calendarid
order by date desc
rows between unbounded preceding and 1 preceding
) as next_businessday
就个人而言,我发现这个版本有点难以想象。