我有一个购物表:
user_id
purchase_time
量
我在网站上有一个用户活动表:
user_id
位置
browse_time
我如何将购买表与活动表连接起来以获得最近的browse_time活动而不经过purchase_time?
例如,如果我有For Purchase Table:
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"上的连接语句。那么,在大查询中是否可能做到这一点呢?
如果你这样做,你会得到比你想要的更多的结果:
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,所以让我们在join的右边做一个子查询:
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
我希望这能帮助你解决这个问题。