原文
from employees
select {id, first_name, age}
sort age
take 10
SELECT
id,
first_name,
age
FROM
employees
ORDER BY
age
LIMIT
10
from track_plays
filter plays > 10_000 # Readable numbers
filter (length | in 60..240) # Ranges with `..`
filter recorded > @2008-01-01 # Simple date literals
filter released - recorded < 180days # Nice interval literals
sort {-length} # Concise order direction
SELECT
*
FROM
track_plays
WHERE
plays > 10000
AND length BETWEEN 60 AND 240
AND recorded > DATE '2008-01-01'
AND released - recorded < INTERVAL 180 DAY
ORDER BY
length DESC
from employees
# `filter` before aggregations...
filter start_date > @2021-01-01
group country (
aggregate {max_salary = max salary}
)
# ...and `filter` after aggregations!
filter max_salary > 100_000
SELECT
country,
MAX(salary) AS max_salary
FROM
employees
WHERE
start_date > DATE '2021-01-01'
GROUP BY
country
HAVING
MAX(salary) > 100000
from track_plays
derive {
finished = started - unfinished,
fin_share = finished / started, # Use previous definitions
fin_ratio = fin_share / (1-fin_share), # BTW, hanging commas are optional!
}
SELECT
*,
started - unfinished AS finished,
(started - unfinished) / started AS fin_share,
(started - unfinished) / started / (1 - (started - unfinished) / started)
AS fin_ratio
FROM
track_plays
from web
# Just like Python
select url = f"https://www.{domain}.{tld}/{page}"
SELECT
CONCAT('https://www.', domain, '.', tld, '/', page) AS url
FROM
web
from employees
group employee_id (
sort month
window rolling:12 (
derive {trail_12_m_comp = sum paycheck}
)
)
SELECT
*,
SUM(paycheck) OVER (
PARTITION BY employee_id
ORDER BY
month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS trail_12_m_comp
FROM
employees
let celsius_to_fahrenheit = temp -> temp * 9/5 + 32
from weather
select temp_f = (celsius_to_fahrenheit temp_c)
SELECT
temp_c * 9 / 5 + 32 AS temp_f
FROM
weather
# Most recent employee in each role
# Quite difficult in SQL...
from employees
group role (
sort join_date
take 1
)
WITH table_0 AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY role
ORDER BY
join_date
) AS _expr_0
FROM
employees
)
SELECT
*
FROM
table_0
WHERE
_expr_0 <= 1
# There's no `version` in PRQL, but s-strings
# let us embed SQL as an escape hatch:
from x
derive db_version = s"version()"
SELECT
*,
version() AS db_version
FROM x
from employees
join b=benefits (==employee_id)
join side:left p=positions (p.id==employees.employee_id)
select {employees.employee_id, p.role, b.vision_coverage}
SELECT
employees.employee_id,
p.role,
b.vision_coverage
FROM
employees
INNER JOIN benefits AS b ON employees.employee_id = b.employee_id
LEFT OUTER JOIN positions AS p ON p.id = employees.employee_id
from users
filter last_login != null
filter deleted_at == null
derive channel = channel ?? "unknown"
SELECT
*,
COALESCE(channel, 'unknown') AS channel
FROM
users
WHERE
last_login IS NOT NULL
AND deleted_at IS NULL
prql target:sql.mssql # Will generate TOP rather than LIMIT
from employees
sort age
take 10
SELECT
*
FROM
employees
ORDER BY
age OFFSET 0 ROWS
FETCH
FIRST 10 ROWS ONLY