sql - 如何根据 SQL（最好是 Bigquery）中的有效和到期日期检测更改的行？

Question

我遇到了 sql 的情况。我在 BigQuery 中有这样的表：

row - supplier_id - cargo_provider  - rate - effective_date - expiry date

1   - 1001        - cp1             - 30   - 2021.01.01     - 2021.01.15
2   - 1001        - cp2             - 70   - 2021.01.01     - 2021.01.15
3   - 1001        - cp1             - 30   - 2021.01.15     - 2021.01.20
4   - 1001        - cp2             - 70   - 2021.01.15     - 2021.01.20
5   - 1001        - cp1             - 50   - 2021.01.20     - 9999.01.01
6   - 1001        - cp3             - 50   - 2021.01.20     - 9999.01.01

然后，我的目标是只保留供应商货物供应商的变更日志或货物供应商的费率。如果第 3 行和第 4 行没有变化（2021.01.15 和 2021.01.20 之间）与之前的情况（2021.01.01 和 2021.01.15）进行比较，我需要一个控制机制来删除并从表中删除。而且这个机制还应该检测到即使货物提供者是相同的费率在第5排也不同，并且新的货物提供者（cp3）用cp2改变，所以在2021.01.20和9999.01.01之间发生了变化（这意味着这费率和货运供应商仍然有效）。最后，我的目标表应该是：

row - supplier_id - cargo_provider  - rate - effective_date - expiry date

1   - 1001        - cp1             - 30   - 2021.01.01     - 2021.01.20 (not 2021.01.15)
2   - 1001        - cp2             - 70   - 2021.01.01     - 2021.01.20 (not 2021.01.15)
3   - 1001        - cp1             - 50   - 2021.01.20     - 9999.01.01
4   - 1001        - cp3             - 50   - 2021.01.20     - 9999.01.01

我找不到在 sql 中执行此操作的方法，但有没有人可以执行此操作或提供方法。

我正在使用 Bigquery，但任何 SQL 语言对我来说都可以。我可以将其更改为 bigquery。

标签： sqlgoogle-bigquery

Answer 1

这是一种差距和孤岛问题。您正在寻找相邻行的费率相同的时间段，其中相邻似乎由与下一个生效日期匹配的到期日期定义。

对于此版本，您可以使用lag()来查看“孤岛”何时开始。也就是说，率没有差距。“岛屿起点”的累积总和定义了每个“岛屿”。剩下的就是聚合。

select supplier_id, cargo_provider, rate,
       min(effective_date), max(expiry_date)
from (select t.*,
             sum(case when prev_expiry_date = effective_date then 0 else 1 end) over (partition by supplier_id, cargo_provider, rate order by effective_date) as island
      from (select t.*,
                   lag(expiry_date) over (partition by supplier_id, cargo_provider, rate order by effective_date) as prev_expiry_date
            from t
           ) t
     ) t
group by supplier_id, cargo_provider, rate, island