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

from foo
select [
  circumference = diameter * 3.14159,
  color,
]
filter circumference > 10 && color != "red"

SQL

WITH table_1 AS (
  SELECT
    diameter * 3.14159 AS circumference,
    color
  FROM
    foo
)
SELECT
  circumference,
  color
FROM
  table_1 AS table_0
WHERE
  circumference > 10
  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

from employees
# Requires parentheses, because it contains a pipe
derive is_proximate = (distance | in 0..20)
# Requires parentheses, because it's a function call
derive total_distance = (sum distance)
# `??` doesn't require parentheses, as it's not a function call
derive min_capped_distance = (min distance ?? 5)
# No parentheses needed, because no function call
derive travel_time = distance / 40
# No inner parentheses needed around `1+1` because no function call
derive distance_rounded_2_dp = (round 1+1 distance)
derive [
  # Requires parentheses, because it contains a pipe
  is_far = (distance | in 100..),
  # The left value of the range requires parentheses,
  # because of the minus sign
  is_negative = (distance | in (-100..0)),
  # ...this is equivalent
  is_negative = (distance | in (-100)..0),
  # Doesn't require parentheses, because it's in a list (confusing, see footnote)!
  average_distance = average distance,
]
# Requires parentheses because of the minus sign
sort (-distance)
# A list is fine too
sort [-distance]

SQL

SELECT
  *,
  distance BETWEEN 0 AND 20 AS is_proximate,
  SUM(distance) OVER () AS total_distance,
  MIN(COALESCE(distance, 5)) OVER () AS min_capped_distance,
  distance / 40 AS travel_time,
  ROUND(distance, 2) AS distance_rounded_2_dp,
  distance >= 100 AS is_far,
  distance BETWEEN -100 AND 0,
  distance BETWEEN -100 AND 0 AS is_negative,
  AVG(distance) OVER () AS average_distance
FROM
  employees
ORDER BY
  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

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

Error

Error:
   ╭─[:2:29]
   │
 2 │ derive total_distance = sum distance # generates the error shown below
   │                             ────┬───
   │                                 ╰───── Unknown name distance
───╯

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.