Expressions and operators

PRQL allows expressions, like 2 + 3 or ((1 + x) * y) made up of various operators. In the example below, note the use of expressions to calculate the alias circumference and in the filter transform.

PRQL

1. from foo
2. select [
3. circumference = diameter * 3.14159,
4. color,
5. ]
6. filter circumference > 10 && color != "red"

SQL

1. WITH table_1 AS (
2. SELECT
3. diameter * 3.14159 AS circumference,
4. color
5. FROM
6. foo
7. )
8. SELECT
9. circumference,
10. color
11. FROM
12. table_1 AS table_0
13. WHERE
14. circumference > 10
15. AND color <> 'red'

Operator precedence

This table shows operator precedence. Use parentheses () to prioritize operations and for function calls (see the discussion below.)

Parentheses

PRQL uses parentheses () for several purposes:

• Parentheses group operands to control the order of evaluation, for example: ((1 + x) * y)
• Parentheses delimit an inner transform for the group () and window () transforms.
• Parentheses delimit a minus sign of a function argument, for example: add (-1) (-3)
• Parentheses delimit nested function calls that contain a pipe, either the | symbol or a new line. “Nested” means within a transform; i.e. not just the main pipeline, for example: (column-name | in 0..20)
• Parentheses wrap a function call that is part of a larger expression on the right-hand side of an assignment, for example: round 0 (sum distance)
• Parentheses are not required for expressions that do not contain function calls, for example: foo + bar.

Here’s a set of examples of these rules:

PRQL

1. from employees
2. # Requires parentheses, because it contains a pipe
3. derive is_proximate = (distance | in 0..20)
4. # Requires parentheses, because it's a function call
5. derive total_distance = (sum distance)
6. # `??` doesn't require parentheses, as it's not a function call
7. derive min_capped_distance = (min distance ?? 5)
8. # No parentheses needed, because no function call
9. derive travel_time = distance / 40
10. # No inner parentheses needed around `1+1` because no function call
11. derive distance_rounded_2_dp = (round 1+1 distance)
12. derive [
13. # Requires parentheses, because it contains a pipe
14. is_far = (distance | in 100..),
15. # The left value of the range requires parentheses,
16. # because of the minus sign
17. is_negative = (distance | in (-100..0)),
18. # ...this is equivalent
19. is_negative = (distance | in (-100)..0),
20. # Doesn't require parentheses, because it's in a list (confusing, see footnote)!
21. average_distance = average distance,
22. ]
23. # Requires parentheses because of the minus sign
24. sort (-distance)
25. # A list is fine too
26. sort [-distance]

SQL

1. SELECT
2. *,
3. distance BETWEEN 0 AND 20 AS is_proximate,
4. SUM(distance) OVER () AS total_distance,
5. MIN(COALESCE(distance, 5)) OVER () AS min_capped_distance,
6. distance / 40 AS travel_time,
7. ROUND(distance, 2) AS distance_rounded_2_dp,
8. distance >= 100 AS is_far,
9. distance BETWEEN -100 AND 0,
10. distance BETWEEN -100 AND 0 AS is_negative,
11. AVG(distance) OVER () AS average_distance
12. FROM
13. employees
14. ORDER BY
15. distance DESC

Note: The total_distance statement below generates an error because the function is not in a list. (The PRQL compiler should display a better error message.)

PRQL

1. from employees
2. derive total_distance = sum distance # generates the error shown below
3. derive other_distance = (sum distance) # works as expected

Error

1. Error:
2. ╭─[:2:29]
3. │
4. 2 │ derive total_distance = sum distance # generates the error shown below
5. │                             ────┬───
6. │                                 ╰───── Unknown name distance
7. ───╯

Note

We’re continuing to think whether these rules can be more intuitive. We’re also planning to make the error messages much better, so the compiler can help out.