3 ms·
SQL pipe syntax is a great step in the right direction but what I think sets PRQL apart (in my very biased opinion, contributor) is the ability to define functi
by snthpy 2y ago
SQL pipe syntax is a great step in the right direction but what I think sets PRQL apart (in my very biased opinion, contributor) is the ability to define functions.
Here's a simple example:
# define the "take_smallest" function
let take_smallest = func n col tbl<relation> -> (
from tbl
sort col
take n
)
# find smallest 3 tracks by milliseconds
from tracks
take_smallest 3 milliseconds
You can try this now in the online playground: https://prql-lang.org/playground/ https://prql-lang.org/playground/
That's simple enough and there's not that much gained there but say you now want to find the 3 smallest tracks per album by bytes?
That's really simple in PRQL and you can just reuse the "take_smallest" function and pass a different column name as an argument:
from tracks
group album_id (
take_smallest 3 bytes
)
- snthpy 2y agoHere'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/ https://prql-lang.org/playground/
- jshute00 2y agoSQL 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/examples/pipe_queries/walkthrough_7day.sql https://github.com/google/zetasql/blob/master/zetasql/exampl...
- snthpy 2y agoOh nice! Thanks for setting that straight. Apologies, I must have either missed that or forgotten about it.
- aoeusnth1 2y agoBigQuery has table-valued functions already, which can be used with pipes with a CALL clause.