sql - 根据行 SQL 的过去值添加计算字段

Question

我有一个包含日期字段和列“​​X”的数据集，如下所示。X 是一个二进制字段 - 我想在末尾添加一个字段（“所需字段（计算）”），它将为我提供字段“X”的 1 字符串开始的第一个日期。有任何想法吗？在 Excel 中，我会根据以下内容查看之前的行以检查它是否为空白：

逻辑：

Case when X=0 then Null 
     when X=1 and 'desired field value for the row above' = Null, then Date
     when X=1 and 'desired field value for the row above' != Null, then value of 'desired field value for the row above'
) end as desired field

---

Date (dd/mm/yy)|X|desired field (calculated)
01/01/00       | |
02/01/00       |1|02/01/00
04/01/00       |1|02/01/00
10/01/00       |1|02/01/00
20/01/00       | |
20/02/00       | |
20/03/00       | |
04/04/00       |1|04/04/00
06/04/00       |1|04/04/00
10/04/00       |1|04/04/00
20/05/00       | |

标签： sqlgoogle-bigquerycasecommon-table-expression

Answer 1

考虑以下方法

select * except(new_grp, grp),
  case 
    when x = 1 then first_value(date) over(partition by grp order by cast(date as date format 'DD/MM/YY'))
    else null
  end desired_field
from (
  select *, countif(new_grp) over(order by cast(date as date format 'DD/MM/YY')) grp
  from (
    select *, x != lag(x) over(order by cast(date as date format 'DD/MM/YY')) new_grp
    from `project.dataset.table`
  )
)
# order by cast(date as date format 'DD/MM/YY')    

如果应用于样本数据 - 输出是