google-bigquery - bigquery - 左连接右表中的最新记录而不超过左表的时间戳

Question

所以我有一张购买表:
用户编号
购买时间
数量

我有一张网站上的用户事件表:
用户编号
位置
浏览时间

如何在不超过 purchase_time 的情况下将 purchases 表与 activities 表连接起来以获得最近的 browse_time 事件？

例如，如果我有采购表:

user_id     Purchase_time          amount
-------     -------------------    ------
1           2012-12-13 12:30:00    $20
2           2012-12-14 23:00:00    $50

我有事件表:

user_id     browse_time            location
-------     -----------            ---------
1           2012-12-14 23:00:00    Product 3
1           2012-12-13 12:00:00    Product 1
1           2012-12-13 11:30:00    Product 2
2           2012-12-15 00:00:00    Product 5
2           2012-12-14 22:30:00    Product 7
2           2012-12-14 20:00:00    Product 6

我想要以下输出:

user_id    purchase_time           browse_time           location     amount
-------    -----------------       ---------------       ----------   -------
1           2012-12-13 12:30:00    2012-12-13 12:00:00   Product 1    $20
2           2012-12-14 23:00:00    2012-12-14 22:30:00   Product 7    $50   

我尝试了 mysql 语法，但没有成功。我知道 bigquery 不允许“<”或“>”用于“on”上的连接语句。那么，甚至可以在 Big Query 中做到这一点吗？

Answer 1

所以如果你这样做，你会得到比你想要的多得多的结果:

SELECT
    user_id
    purchase_time
    browse_time
    location
    amount
FROM
    purchases pur
JOIN
    user_activities uav
ON
    pur.user_id = uav.user_id

您需要最新的 user_activities，所以让我们对连接的右侧进行子查询:

SELECT
    user_id
    purchase_time
    browse_time
    location
    amount
FROM
    (SELECT 
         user_id AS user_id 
         location AS location
         MAX(browse_time) AS browse_time 
     FROM
         purchases 
     GROUP BY user_id,location) pur
JOIN
    user_activities uav
ON
    pur.user_id = uav.user_id

希望本文能帮助您解决问题。