PRql

Jun 27, 2022

https://prql-lang.org/ https://prql-lang.org/examples/

A re-imagining of SQL in a sensible order. Compiles to SQL. Would love to play around with it more, I've wanted something like this for a long time.

ex query:

from employees                                # Each line transforms the previous result.
filter start_date > @2021-01-01               # Clear date syntax.
derive [                                      # `derive` adds columns / variables.
  gross_salary = salary + payroll_tax,
  gross_cost = gross_salary + benefits_cost   # Variables can use other variables.
]
filter gross_cost > 0
group [title, country] (                      # `group` runs a pipeline over each group.
  aggregate [                                 # `aggregate` reduces a column to a row.
    average salary,
    sum     salary,
    average gross_salary,
    sum     gross_salary,
    average gross_cost,
    sum_gross_cost = sum gross_cost,          # `=` sets a column name.
    ct = count,
  ]
)
sort [sum_gross_cost, -country]               # `-country` means descending order.
filter ct > 200
take 20

which compiles to the equivalent of this SQL query:

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 ct
FROM
  employees
WHERE
  start_date > DATE('2021-01-01')
  AND salary + payroll_tax + benefits_cost > 0
GROUP BY
  title,
  country
HAVING
  COUNT(*) > 200
ORDER BY
  sum_gross_cost,
  country DESC

derive means, given the previous results, create these new columns. Being able to do multiple derive/filter pairs would be so nice!

In golang, I think the best way to integrate this tool would be to use it at code generation time? Looks like the work has not been done there yet, this project is v new.

↑ up