SQL pipe syntax available in public preview in BigQuery

Re:SQL pipe syntax available in public preview in BigQuery by snthpy on Thursday February 13, 2025 @11:23AM (#43037622)

Here's a more complex example for sessionizing user events:

    # specify the target SQL dialect
    prql target:sql.duckdb
    
    # define the "sessionize" function
    let sessionize = func user_col date_col max_gap:365 tbl<relation> -> (  
      from tbl
      group user_col (
        window rows:-1..0 (
          sort date_col
          derive prev_date=(lag 1 (date_col|as date))
          )
      )
      derive {
        date_diff = (date_col|as date) - prev_date,
        is_new_session = case [date_diff > max_gap  || prev_date==null => 1, true => 0],
        }
      window rows:..0 (
        group user_col (
          sort {date_col}
          derive user_session_id = (sum is_new_session)
        )
        sort {user_col, date_col}
        derive global_session_id = (sum is_new_session)
      )
      select !{prev_date, date_diff, is_new_session}
    )
    
    # main query
    from invoices
    select {customer_id, invoice_date}
    sessionize customer_id invoice_date max_gap:365
    sort {customer_id, invoice_date}

You can also try that in the playground: https://prql-lang.org/playground/

[ Parent | on HN ]

Re:SQL pipe syntax available in public preview in BigQuery by jshute00 on Friday February 14, 2025 @12:43AM (#43045246)

SQL with pipe syntax can also do functions like that. An sequence of pipe operators can be saved as a table-valued function, and then reused by invoking in other queries with CALL.

Example:

  CREATE TEMP TABLE FUNCTION ExtendDates(input ANY TABLE, num_days INT64)
  AS
  FROM input
  |> EXTEND date AS original_date
  |> EXTEND max(date) OVER () AS max_date
  |> JOIN UNNEST(generate_array(0, num_days - 1)) diff_days
  |> SET date = date_add(date, INTERVAL diff_days DAY)
  |> WHERE date <= max_date
  |> SELECT * EXCEPT (max_date, diff_days);

  FROM Orders
  |> RENAME o_orderdate AS date, o_custkey AS user_id
  |> CALL ExtendDates(7)
  |> LIMIT 10;

from the script here: https://github.com/google/zetasql/blob/master/zetasql/exampl...

[ Parent | on HN ]

Re:SQL pipe syntax available in public preview in BigQuery by snthpy on Friday February 14, 2025 @02:23AM (#43045778)

Oh nice! Thanks for setting that straight. Apologies, I must have either missed that or forgotten about it.

[ Parent | on HN ]