CODEWITHSUNDEEP
google-bigquery
I.m.rich
I.m.rich

Reputation: 55

Adjusting table based on previous values in BigQuery

I have a table that looks like below:

ID|Date |X| Flag |
1 |1/1/16|2| 0
2 |1/1/16|0| 0
3 |1/1/16|0| 0
1 |2/1/16|0| 0
2 |2/1/16|1| 0
3 |2/1/16|2| 0
1 |3/1/16|2| 0
2 |3/1/16|1| 0
3 |3/1/16|2| 0

I'm trying to make it so that flag is populated if X=2 in the PREVIOUS month. As such, it should look like this:

ID|Date |X| Flag |
1 |1/1/16|2| 0
2 |1/1/16|0| 0
3 |1/1/16|0| 0
1 |2/1/16|2| 1
2 |2/1/16|1| 0
3 |2/1/16|2| 0
1 |3/1/16|2| 1
2 |3/1/16|1| 0
3 |3/1/16|2| 1

I use this in 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`

However I cannot do this in Bigquery. Any ideas?

Upvotes: 0

Views: 396

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard 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`

You can test / play with it using dummy data from your question as 

#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

with result as 

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

Upvotes: 2

Related Questions