google-bigquery - 将一组变量和值与最近的日期时间匹配

Question

我有一个transaction看起来像这样的表：

transaction_start    store_no    item_no    amount    post_voided
2021-03-01 10:00:00       001        101        45         N
2021-03-01 10:00:00       001        105        25         N
2021-03-01 10:00:00       001        109        40         N
2021-03-01 10:05:00       002        103        35         N
2021-03-01 10:05:00       002        135        20         N
2021-03-01 10:08:00       001        140        2          N
2021-03-01 10:11:00       001        101       -45         Y
2021-03-01 10:11:00       001        105       -25         Y
2021-03-01 10:11:00       001        109       -40         Y

该表没有 id 列；给transaction_start定store_no的永远不会相同。

每当交易被取消后，交易就会以相同的 重复store_no，item_no但带有负/负amount和等于或更高的transaction_start。此外，该列post_voided等于“Y”。

在上面的示例中，行 1-3 具有相同的transaction_start和store_no，因此属于同一收据，包含三个不同的项目（101、105、109）。相同的逻辑适用于其他行：第 4-5 行属于同一收据，依此类推。在示例中，可以看到 4 个不同的收据。最后三行给出的最后一个收据是与第一个收据（第 1-3 行）无效的帖子。

我想要做的是transaction_start将post_voided= 'Y' 交易（在我的示例中，只有一个收据 - 由最后三行表示 - 有它）更改为具有变量的类似收据的下一个/最近的日期时间store_no，item_no和 (negative) amount(but post_voided= 'N') （在我的示例中，前三行给出了类似的票证 - store_no，所有item_no和（正）amount匹配）。transaction_start邮局作废收据总是等于或高于“原始”收据。

期望的输出：

transaction_start    store_no    item_no    amount    post_voided
2021-03-01 10:00:00       001        101        45         N
2021-03-01 10:00:00       001        105        25         N
2021-03-01 10:00:00       001        109        40         N
2021-03-01 10:05:00       002        103        35         N
2021-03-01 10:05:00       002        135        20         N
2021-03-01 10:08:00       001        140        2          N
2021-03-01 10:00:00       001        101       -45         Y
2021-03-01 10:00:00       001        105       -25         Y
2021-03-01 10:00:00       001        109       -40         Y

这里是表格的链接：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=26142fa24e46acb4213b96c86f4eb94b

提前致谢！

标签： google-bigquery

Answer 1

考虑下面

select a.* replace(ifnull(b.transaction_start, a.transaction_start) as transaction_start)
from `project.dataset.table` a 
left join (
  select * replace(-amount as amount)
  from `project.dataset.table`
  where post_voided = 'N'
) b
using (store_no, item_no)  

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

考虑下面的新/扩展示例（https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=91f9f180fd672e7c357aa48d18ced5fd）

select x.* replace(ifnull(y.original_transaction_start, x.transaction_start) as transaction_start)
from `project.dataset.table` x 
left join (
  select b.transaction_start, b.store_no, b.item_no, b.amount amount, 
    max(a.transaction_start) original_transaction_start
  from `project.dataset.table` a 
  join `project.dataset.table` b
  on a.store_no = b.store_no
  and a.item_no = b.item_no
  and a.amount = -b.amount
  and a.post_voided = 'N'
  and b.post_voided = 'Y'
  and a.transaction_start < b.transaction_start
  group by b.transaction_start, b.store_no, b.item_no, b.amount
) y
using (store_no, item_no, amount, transaction_start)  

带输出