google-bigquerygoogle-cloud-dataprep

Is there a Dataprep rolling list equivalent function in BigQuery?

I'm looking for functionality similar to this in BigQuery: https://cloud.google.com/dataprep/docs/html/ROLLINGLIST-Function_118228853

Does anyone know of a suitable function?

Solution

  • Below example for BigQuery Standard SQL 

    #standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' col1, 1 col2 UNION ALL
  SELECT 'b', 2 UNION ALL
  SELECT 'c', 3 UNION ALL
  SELECT 'd', 4 UNION ALL
  SELECT 'e', 5 UNION ALL
  SELECT 'f', 6 UNION ALL
  SELECT 'g', 7 UNION ALL
  SELECT 'h', 8 
)
SELECT *, 
  STRING_AGG(col1) 
    OVER(ORDER BY col2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) rolling_list
FROM `project.dataset.table`

    with output 

    Row col1    col2    rolling_list     
1   a       1       a    
2   b       2       a,b  
3   c       3       a,b,c    
4   d       4       b,c,d    
5   e       5       c,d,e    
6   f       6       d,e,f    
7   g       7       e,f,g    
8   h       8       f,g,h