mysql - 大查询 : Join single latest row from second table

Question

我有两张 table 。一个是订单列表，一个是事件列表。

对于每个订单，我想加入created_at之前发生的最后一个事件(使用clicked_at) 订单的code>。

我尝试了多种方法来使其正常工作，并在 Stack Overflow 上尝试了其他几个答案，但我很难返回正确的数据。

在我看来，子查询的 sudo 逻辑是这样的:

SELECT campaign, user_id, created_at 
FROM `Events`
WHERE order.user_id = user_id AND clicked_at < order.created_at
ORDER created_at DESC
LIMIT 1

请参阅下面的示例数据:

# Orders

| order_id | user_id | created_at |
-----------------------------------
| 123      | abc     | 2020-07-04 |
| 456      | abc     | 2020-05-01 |


# Events

| campaign | keyword  | user_id | clicked_at |
----------------------------------------------
| facebook | shoes    | abc     | 2020-07-03 |
| google   | hair     | abc     | 2020-07-01 |

我想要的结果

# Orders with campaign attribution

| order_id | user_id | created_at | campaign | keyword  |
---------------------------------------------------------
| 123      | abc     | 2020-07-04 | facebook | shoes    |
| 456      | abc     | 2020-06-04 | null     | null     | 

谢谢!亚历克斯

Answer 1

以下适用于 BigQuery 标准 SQL

#standardSQL
SELECT a.*, campaign, keyword
FROM  `project.dataset.orders` a
LEFT JOIN (
  SELECT  
    ANY_VALUE(o).*, 
    ARRAY_AGG(STRUCT(campaign, keyword) ORDER BY clicked_at DESC LIMIT 1)[OFFSET(0)].*
  FROM `project.dataset.orders` o
  JOIN `project.dataset.events` e
  ON o.user_id = e.user_id
  AND clicked_at < created_at
  GROUP BY FORMAT('%t', o)
)
USING(order_id)   

如果应用于我们问题中的样本数据 - 结果是

Row order_id    user_id created_at  campaign    keyword  
1   123         abc     2020-07-04  facebook    shoes    
2   456         abc     2020-05-01  null        null