基于BigQuery中先前值的调整表

Question

我有一张桌子，看上去如下：

ID，日期，x，x，国旗

1/1/16\x{e76f}2

1/16\x{e76f}\x{e76f}0

1/1/16\x{e76f}0

1/1/16\x{e 010}0

2/1/16\x{e76f}1

2/1/16\x{e76f}2

1/1/16\x{e76f}2

2-3/1/16

3/1/16\x{e76f}2

我正试图使它在上个月的X=2中填充这个标志。因此，它应该是这样的：

ID，日期，x，x，国旗

1/1/16\x{e76f}2

1/16\x{e76f}\x{e76f}0

1/1/16\x{e76f}0

1/1/16\x{e76f}2

2/1/16\x{e76f}1

2/1/16\x{e76f}2

1/1/16\x{e76f}2

2-3/1/16

3/1/16\x{e76f}2

我在SQL中使用这个：

  `select ID, date, X, flag into Work_Table from t

(
    Select  ID, date, X, flag,
            Lag(X) Over (Partition By ID Order By date Asc) As Prev into Flag_table
    From    Work_Table
) 

Update  [dbo].[Flag_table]
Set flag = 1
where prev = '2' 


UPDATE   t
Set t.flag = [dbo].[Flag_table].flag FROM T 
JOIN [dbo].[Flag_table]
 ON t.ID= [dbo].[Flag_table].ID where T.date = [dbo].[Flag_table].date`

但是，我不能在Bigquery中这样做。有什么想法吗？

Answer 1

下面是用于BigQuery标准SQL的

#standardSQL
SELECT id, dt, x, 
  IF(LAG(x = 2) OVER(PARTITION BY id ORDER BY dt), 1, 0) flag
FROM `project.dataset.work_table`

您可以使用问题中的虚拟数据测试/播放它，如下所示

#standardSQL
WITH `project.dataset.work_table` AS (
  SELECT 1 id, '1/1/16' dt, 2 x, 0 flag UNION ALL
  SELECT 2, '1/1/16', 0, 0 UNION ALL
  SELECT 3, '1/1/16', 0, 0 UNION ALL
  SELECT 1, '2/1/16', 0, 0 UNION ALL
  SELECT 2, '2/1/16', 1, 0 UNION ALL
  SELECT 3, '2/1/16', 2, 0 UNION ALL
  SELECT 1, '3/1/16', 2, 0 UNION ALL
  SELECT 2, '3/1/16', 1, 0 UNION ALL
  SELECT 3, '3/1/16', 2, 0 
)
SELECT id, dt, x, 
  IF(LAG(x = 2) OVER(PARTITION BY id ORDER BY dt), 1, 0) flag
FROM `project.dataset.work_table`
ORDER BY dt, id

结果如

Row id  dt      x   flag     
1   1   1/1/16  2   0    
2   2   1/1/16  0   0    
3   3   1/1/16  0   0    
4   1   2/1/16  0   1    
5   2   2/1/16  1   0    
6   3   2/1/16  2   0    
7   1   3/1/16  2   0    
8   2   3/1/16  1   0    
9   3   3/1/16  2   1