運算子

表達式可以由 函式呼叫 和 運算 組成，例如 2 + 3 或 ((1 + x) * -y)。在下方的範例中，請注意使用表達式計算別名 circumference 和在 filter 轉換中的用法。

from foo
select {
  circumference = diameter * 3.14159,
  area = (diameter / 2) ** 2,
  color,
}
filter circumference > 10 && color != "red"

WITH table_0 AS (
  SELECT
    diameter * 3.14159 AS circumference,
    POW(diameter / 2, 2) AS area,
    color
  FROM
    foo
)
SELECT
  circumference,
  area,
  color
FROM
  table_0
WHERE
  circumference > 10
  AND color <> 'red'

運算子優先級

此表顯示運算子優先級。使用括號 () 優先順序排列運算和函式呼叫（參閱下面的討論。）

除法和整數除法

/ 運算子執行總是返回浮點值的除法，而 // 運算子執行整數除法（截斷除法），總是返回整數值。

prql target:sql.sqlite

from [
  {a = 5, b = 2},
  {a = 5, b = -2},
]
select {
  div_out = a / b,
  int_div_out = a // b,
}

WITH table_0 AS (
  SELECT
    5 AS a,
    2 AS b
  UNION
  ALL
  SELECT
    5 AS a,
    -2 AS b
)
SELECT
  (a * 1.0 / b) AS div_out,
  ROUND(ABS(a / b) - 0.5) * SIGN(a) * SIGN(b) AS int_div_out
FROM
  table_0

合併

我們可以使用 ?? 運算子合併值。合併會取第一個值，或者如果該值為空，則取第二個值。

from orders
derive amount ?? 0

SELECT
  *,
  COALESCE(amount, 0)
FROM
  orders

常規表達式

[!注意] 這目前是實驗性的

要執行區分大小寫的常規表達式搜索，請使用 ~= 運算子。這通常編譯為 REGEXP，儘管因方言而異。常規表達式搜索意味著要匹配確切的值，開始和結束需要用 ^foo$ 錨定。

from tracks
filter (name ~= "Love")

SELECT
  *
FROM
  tracks
WHERE
  REGEXP(name, 'Love')

prql target:sql.duckdb

from artists
filter (name ~= "Love.*You")

SELECT
  *
FROM
  artists
WHERE
  REGEXP_MATCHES(name, 'Love.*You')

prql target:sql.bigquery

from tracks
filter (name ~= "\\bLove\\b")

SELECT
  *
FROM
  tracks
WHERE
  REGEXP_CONTAINS(name, '\bLove\b')

prql target:sql.postgres

from tracks
filter (name ~= "\\(I Can't Help\\) Falling")

SELECT
  *
FROM
  tracks
WHERE
  name ~ '\(I Can''t Help\) Falling'

prql target:sql.mysql

from tracks
filter (name ~= "With You")

SELECT
  *
FROM
  tracks
WHERE
  REGEXP_LIKE(name, 'With You', 'c')

prql target:sql.sqlite

from tracks
filter (name ~= "But Why Isn't Your Syntax More Similar\\?")

SELECT
  *
FROM
  tracks
WHERE
  name REGEXP 'But Why Isn''t Your Syntax More Similar\?'

括號

PRQL 使用括號 () 有幾個目的：

• 括號對操作數進行分組以控制評估順序，例如：((1 + x) * y)

• 括號用於分隔函式引數的減號，例如：add (-1) (-3)

• ““括號用於分隔包含管道的嵌套函式呼叫，可以是 | 符號或新行。“嵌套“是指在轉換中；即不僅僅是主管道，例如：(column-name | in 0..20)

• 括號包裹作為較大表達式一部分的函式呼叫，例如：math.round 0 (sum distance)

括號對於不包含函式呼叫的表達式不是必需的，例如：foo + bar。

Here’s a set of examples of these rules:

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 = (math.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),
  # _Technically_, this doesn't require parentheses, because it's
  # the RHS of an assignment in a tuple
  # (this is especially confusing)
  average_distance = average distance,
}
# Requires parentheses because of the minus sign
sort (-distance)
# A tuple is fine too
sort {-distance}

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, 1 + 1) 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

例如，下面的代碼片段產生錯誤，因為 sum 函式呼叫不在元組中。

from employees
derive total_distance = sum distance

Error:
   ╭─[ :2:29 ]
   │
 2 │ derive total_distance = sum distance
   │                             ────┬───
   │                                 ╰───── Unknown name `distance`
───╯

…while with parentheses, it works at expected:

from employees
derive other_distance = (sum distance)

SELECT
  *,
  SUM(distance) OVER () AS other_distance
FROM
  employees

[!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.

換行

PRQL 中的換行有語義含義，所以要將單個邏輯行換行為多個物理行，我們可以在後續物理行的開始處使用 \：

from artists
select is_europe =
\ country == "DE"
\ || country == "FR"
\ || country == "ES"

SELECT
  country = 'DE'
  OR country = 'FR'
  OR country = 'ES' AS is_europe
FROM
  artists

Wrapping will “jump over” empty lines or lines with comments. For example, the select here is only one logical line:

from tracks
# This would be a really long line without being able to split it:
select listening_time_years = (spotify_plays + apple_music_plays + pandora_plays)
# We can toggle between lines when developing:
# \ * length_seconds
\ * length_s
#   min  hour day  year
\ / 60 / 60 / 24 / 365

SELECT
  (
    spotify_plays + apple_music_plays + pandora_plays
  ) * length_s / 60 / 60 / 24 / 365 AS listening_time_years
FROM
  tracks

[!注意] 注意 PRQL 與大多數語言不同，大多數語言在前一行的末尾使用 \。由於 PRQL 旨在友好地進行數據探索，我們希望可以註解掉任何行，包括最後一行，而不破壞查詢。這要求第一行之後的所有行的結構相似，並且字符位於每個後續行的開始。

有關換行的更多詳細資訊，請參閱 Pipes。