PRQL – a modern language for transforming data ↦
PRQL (pronounced “Prequel”) aims to be “a simpler and more powerful SQL”
Like SQL, it’s readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL.
To get an idea on PRQL’s design, they provide this SQL statement as an example:
SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(salary + payroll_tax) AS average_gross_salary, SUM(salary + payroll_tax) AS sum_gross_salary, AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost, SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost, COUNT(*) as count FROM employees WHERE salary + payroll_tax + benefits_cost > 0 AND country = 'USA' GROUP BY title, country ORDER BY sum_gross_cost HAVING count > 200
And then translate it to PRQL, which looks like:
from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / variable. let gross_cost = gross_salary + benefits_cost # Variables can use other variables. filter gross_cost > 0 aggregate by:[title, country] [ # `by` are the columns to group by. average salary, # These are the calcs to run on the groups. sum salary, average gross_salary, sum gross_salary, average gross_cost, sum gross_cost, count, ] sort sum_gross_cost # Uses the auto-generated column name. filter count > 200 take 20
Sign in or Join to comment or subscribe