BigQuery Function to Persist Values

Question

What is the best way to persist a value in BigQuery? For example, if the value in the cell is (none) then it should get the last known value that is not (none) but if the value then changes, begin persisting that value. See below for an example of what I'd like. I tried using the LAG function but it only works for one row.

Row      Value      Persisted
1        Apple      Apple
2        (none)     Apple
3        (none)     Apple
4        (none)     Apple
5        Orange     Orange
6        (none)     Orange

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-09-04T19:48:58

Below is for BigQuery Standard SQL

To achieve your goal you need some extra field in your data that will define the order of your values - usually it is column with timestamp,date,etc. data type, or number reflecting position. In below example I am using ts as a such column

#standardSQL
SELECT ts, value, 
  LAST_VALUE(IF(value = '(none)', NULL, value) IGNORE NULLS) OVER(ORDER BY ts) Persisted
FROM `project.dataset.table`

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 ts, 'Apple' Value UNION ALL
  SELECT 2, '(none)' UNION ALL
  SELECT 3, '(none)' UNION ALL
  SELECT 4, '(none)' UNION ALL
  SELECT 5, 'Orange' UNION ALL
  SELECT 6, '(none)'  
)
SELECT ts, value, 
  LAST_VALUE(IF(value = '(none)', NULL, value) IGNORE NULLS) OVER(ORDER BY ts) Persisted
FROM `project.dataset.table`

with output

Row ts  value   Persisted    
1   1   Apple   Apple    
2   2   (none)  Apple    
3   3   (none)  Apple    
4   4   (none)  Apple    
5   5   Orange  Orange   
6   6   (none)  Orange

BigQuery Function to Persist Values

1 Answers