Databases github.com

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

Discussion

Sign in or Join to comment or subscribe

0:00 / 0:00