在基于时间的关联中,如何在任何给定时间获得有效的关联?

一个解决方案涉及构建 间隔 指定处于活动状态的时间,然后筛选覆盖所需时间的间隔 t 。

例如,我们可以使用window函数轻松地做到这一点 lead() :

with assignment_intervals as (
  select
    user_id,
    assignment_time as assignment_start,
    lead(assignment_time) over (
      partition by user_id
      order by assignment_time
    ) as assignment_end,
    group_id
  from players_teams_assignments
)
select group_id
where
  assignment_start <= t and
  coalesce(assignment_end > t, true)

我相信 引线() 是 一些SQL标准的一部分(尽管我不知道是哪一个)。如果您的数据库不支持以下内容 引线() ,您将需要以其他方式构造这些区间。

注意使用 coalesce 在这里在雪花中 引线() 退货 NULL 在窗口的最后一行。将比较与 true 相当于将赋值的结束视为在未来无限期地结束,表示数据中最后一个已知的赋值。

还要注意的是,在这种情况下,我使用了左侧闭合、右侧打开的区间。也就是说,每个间隔从当前分配开始,在下一个分配之前结束。对于大多数用例来说,这是有意义的默认值,从而导致连续、不相交的间隔覆盖。