sql - 如何通过 BigQuery 中最近的时间戳加入？

Question

假设我有一张类(class)开始时间表和一张学生表以及他们想要的开始时间。我想通过将最接近的 Class.StartTime 与 Student.DesiredStartTime 匹配来加入这两个表(请参见下面的示例)。你会怎么做？
我看到这个问题经常被问到和回答，但仅适用于其他数据库(不是 BigQuery)。由于 BigQuery 有一些独特的属性，我想知道 BigQuery 是否有任何特殊功能来帮助实现这一点？谢谢!

Class
+-----------------------------------+------------+
|               StartTime           |    Class   |
+-----------------------------------+------------+
| 07/01/19 08:00                    | English    |
| 07/01/19 09:00                    | Chemistry  |
| 07/01/19 10:30                    | Math       |
+-----------------------------------+------------+

Student
+-----------------------------------+------------+
|               DesiredStartTime    |    Student |
+-----------------------------------+------------+
| 07/01/19 08:45                    | Jimmy      |
| 07/01/19 09:15                    | Bobby      |
| 07/01/19 10:00                    | Buddy      |
+-----------------------------------+------------+

[Query Results]
+-----------------------------------+------------+------------+
|               StartTime           |    Class   |  Student   |
+-----------------------------------+------------+------------+
| 07/01/19 09:00                    | Chemistry  | Jimmy      |
| 07/01/19 09:00                    | Chemistry  | Bobby      |
| 07/01/19 10:30                    | Math       | Buddy      |
+-----------------------------------+------------+------------+

Answer 1

与许多其他数据库不同，这是在 BQ 中交叉连接的好时机。以下查询查找学生所需的开始时间与所有类(class)开始时间之间的绝对差异(以分钟为单位)，对它们进行排名，然后选择最接近的那个。

with joined as (
  select 
    Student, 
    Class,
    StartTime,
    DesiredStartTime, 
    ABS(TIMESTAMP_DIFF(StartTime,DesiredStartTime, MINUTE)) as abs_difference_mins
  from <dataset>.Class
  cross join <dataset>.Student
),
ranked as (
  select
    StartTime,
    Class,
    Student,
    row_number() over(partition by Student order by abs_difference_mins asc) as ranked_by_mins_diff
  from joined
)
select * except(ranked_by_mins_diff)
from ranked
where ranked_by_mins_diff = 1