sql - 在 BigQuery SQL 中：在满足日期约束时检查和更新表列值

Question

有一个结构如下的 bigquery 表：

Store       Product     Weekbegindate   Weekenddate     Datevalidfrom   Datevalidto     IP      Unit
11          AA          2021-02-24      2021-03-02      null            null            null    7.7
11          BB          2021-02-10      2021-02-16      2021-04-17      2021-12-01      44      59.9
11          CC          2021-06-09      2021-06-15      2021-06-03      2021-06-24      03      6.5

我需要Unit在满足两个条件时更新 ' ' 列，1. 检查 ' IP' IS NOT NULL 并验证从 ' Weekbegindate' 开始并以 ' ' 结尾Weekenddate的日期 - 位于日期范围 ' Datevalidfrom' 和 ' Datevalidto' 之间 2. 如果 ' 是'然后将“ Unit”列更新为值“ 0”，否则保留相同的值。

预期输出：

Store       Product     Weekbegindate   Weekenddate     Datevalidfrom   Datevalidto     IP      Unit
11          AA          2021-02-24      2021-03-02      null            null            null    7.7
11          BB          2021-02-10      2021-02-16      2021-04-17      2021-12-01      44      59.9
11          CC          2021-06-09      2021-06-15      2021-06-03      2021-06-24      03      0

尝试查询：

SELECT t1.*,
      CASE WHEN (tt1.Store AND tt1.Product AND tt1.Weekbegindate AND tt1.Weekenddate) IS NOT NULL
      THEN 0 ELSE t1.Unit END as ip_Unit
FROM table1 t1
LEFT JOIN
    (SELECT * FROM table1 
      where IP is not null 
        and Datevalidfrom between Weekbegindate and Weekenddate
        or Datevalidto between Weekbegindate and Weekenddate
        or Weekbegindate between Datevalidfrom and Datevalidto
        or Weekenddate between Datevalidfrom and Datevalidto) tt1 
ON t1.Store = tt1.Store AND t1.Product = tt1.Product AND t1.Weekbegindate = tt1.Weekbegindate AND t1.Weekenddate = tt1.Weekenddate

但这不锻炼。任何帮助将不胜感激。提前致谢！

标签： sqldategoogle-bigqueryleft-join

Answer 1

这听起来像一个case表达式：

select t1.* (except unit),
       (case when ip is not null and
                  Weekbegindate >= Datevalidfrom and
                  Weekenddate <= Datevalidto
             then 0 else unit
        end) as unit
from t1;

此逻辑也可以合并到update.