PRQL 語言手冊

Pipelined Relational Query Language, pronounced “Prequel”.

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. 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 compiles to SQL.

本書作為語言和更廣泛專案的教學和參考指南。目前有三個部分，通過左側連結進行導航：

教學 — 一份友善且易於接近的 PRQL 學習指南。難度逐步遞增，只需要基本的編程語言理解。SQL 知識是有益的，因為有很多與 SQL 的比較，但不是必需的。
參考 — 關於 PRQL 語言的深入資訊。包含語言設計決策的理據，以及語言各部分的正式規範。
專案 — 關於專案、工具與開發的一般資訊。

PRQL 示例與生成的 SQL 的比較。PRQL 查詢可以非常簡單，例如：

PRQL

from tracks
filter artist == "Bob Marley"  # 每行都會變換前面的結果
aggregate {                    # `aggregate` 將每列縮減為一個值
  plays    = sum plays,
  longest  = max length,
  shortest = min length,       # 允許尾部逗號
}

SQL

SELECT
  COALESCE(SUM(plays), 0) AS plays,
  MAX(length) AS longest,
  MIN(length) AS shortest
FROM
  tracks
WHERE
  artist = 'Bob Marley'

…and here’s a larger example:

PRQL

from employees
filter start_date > @2021-01-01            # 清晰的日期語法
derive {                                   # `derive` 新增列/變數
  gross_salary = salary + (tax ?? 0),      # 簡潔的合併
  gross_cost = gross_salary + benefits,    # 變數可以使用其他變數
}
filter gross_cost > 0
group {title, country} (                   # `group` 在每個群組上執行管道
  aggregate {                              # `aggregate` 將每個群組縮減為一個值
    average gross_salary,
    sum_gross_cost = sum gross_cost,       # `=` 設定列名
  }
)
filter sum_gross_cost > 100_000            # `filter` 取代 SQL 的 `WHERE` 和 `HAVING`
derive id = f"{title}_{country}"           # 像 Python 一樣的 F-字串
derive country_code = s"LEFT(country, 2)"  # S-字串允許 SQL 作為逃脫艙口
sort {sum_gross_cost, -country}            # `-country` 表示遞減順序
take 1..20                                 # 範圍表達式（也可以寫成 `take 20`）

SQL

WITH table_0 AS (
  SELECT
    title,
    country,
    AVG(salary + COALESCE(tax, 0)) AS _expr_0,
    COALESCE(SUM(salary + COALESCE(tax, 0) + benefits), 0) AS sum_gross_cost
  FROM
    employees
  WHERE
    start_date > DATE '2021-01-01'
    AND salary + COALESCE(tax, 0) + benefits > 0
  GROUP BY
    title,
    country
)
SELECT
  title,
  country,
  _expr_0,
  sum_gross_cost,
  CONCAT(title, '_', country) AS id,
  LEFT(country, 2) AS country_code
FROM
  table_0
WHERE
  sum_gross_cost > 100000
ORDER BY
  sum_gross_cost,
  country DESC
LIMIT
  20

Keyboard shortcuts

PRQL language book

PRQL 語言手冊

PRQL

SQL

PRQL

SQL