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