google-bigquery - 将总和加入到最近的时间戳一次直到间隔上限

Question

我正在尝试将一张site_interactions桌子与一张store_transactions桌子连接起来。为此，我希望store_transactions.sales_amount给定的变量username附加到最接近的site_interactions.timestamp匹配项，最多一次，最多 7 天。site_interactions.timestamp

site_interaction桌子：

username    timestamp
John        01.01.2020 15:00:00
John        02.01.2020 11:30:00
Sarah       03.01.2020 12:00:00

store_transactions桌子：

username    timestamp             sales_amount
John        02.01.2020 16:00:00   45
John        03.01.2020 16:00:00   70
John        09.01.2020 16:00:00   15
Sarah       02.01.2020 09:00:00   35
Tim         02.01.2020 10:00:00   60

期望的输出：

 username   timestamp             sales_amount
 John       01.01.2020 15:00:00   NULL
 John       02.01.2020 11:30:00   115
 Sarah      03.01.2020 12:00:00   NULL

解释：

John 在表中有 3 个条目/事务store_transactions。第一次和第二次购买是在 7 天的时间间隔/限制内实现的，并且这两次交易的总和 (45 + 70 = 115) 仅附加/加入到最接近和最近的匹配一次 - 即约翰的第二次交互（时间戳= 02.01.2020 11:30:00)。John 的第三次交易没有附加到任何站点交互，因为它超过了 7 天的间隔（包括时间）。

莎拉在与网站交互之前实现了一笔交易。因此sales_amount，35 岁的她并没有依附在site_interaction桌子上。

最后，Tim 的交易没有附加到任何地方——因为username它没有显示在site_interaction表格中。

这里是表格的链接：https ://rextester.com/RKSUK73038

提前致谢！

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select i.username, i.timestamp, 
  sum(sales_amount) as sales_amount
from (
  select username, timestamp, 
    ifnull(lead(timestamp) over(partition by username order by timestamp), timestamp_add(timestamp, interval 7 day)) next_timestamp
  from `project.dataset.site_interaction`
) i
left join `project.dataset.store_transactions` t 
on i.username = t.username
and t.timestamp >= i.timestamp 
and t.timestamp < least(next_timestamp, timestamp_add(i.timestamp, interval 7 day))
group by username, timestamp

如果适用于您的问题的样本数据 - 输出是