Language guide
A program is an ordered list of stages; each stage transforms the relation produced by the one before it. Write one stage per line, and read the file top to bottom in the order the data is processed.
# source orders: examples/spec/orders.csv from orders derive line_total = quantity * unit_price group by category aggregate orders = COUNT(*), revenue = SUM(line_total) having revenue > 100 sort -revenue take 3
Stage model
| Stage | Purpose |
|---|---|
with | Define a named CTE (nested program) before from. |
from | Start from a table, a CTE name, or an aliased nested program. |
join | Combine the current relation with another table/CTE/subquery. |
filter | Keep rows matching a boolean condition. |
derive | Add computed columns; later stages see them. |
group by + aggregate | Group rows and compute named aggregates; one lowering unit. |
having | Filter the grouped relation (like filter, after aggregation). |
window | Add a window-function column over the current relation. |
select | Project and alias columns; select * plus named expressions is allowed. |
sort / skip / take | Order and paginate; repeatable anywhere. |
distinct | Deduplicate the current relation. |
union / union all / intersect / except | Combine the current relation with a nested program. |
sql | Escape hatch: one raw SQL query; the input relation is __input__. |
Unlike SQL, there is no fixed clause order: stages can repeat and interleave (join after grouping, filter after select, a second sort after take) as long as each stage's inputs exist at that point. Two pairing rules apply: with definitions come before from, and every group by is immediately followed by its aggregate.
with — named CTEs
with recent = (from orders | filter quantity > 1 | select customer, quantity) from recent as r join customers as c on r.customer = c.customer select r.customer, c.segment, r.quantity
Nested programs are written inside parentheses, either inline with | or across multiple lines. Later definitions may reference earlier ones, and from/join resolve CTE names before schema tables.
from — sources
from orders -- schema table from recent -- CTE defined with `with` from (from orders | take 2) as top -- aliased subquery source
A subquery source always requires an alias; the alias becomes the label used in qualified references (top.order_id).
join — combining relations
join products on orders.product_id = products.product_id left join members on orders.customer = members.customer right join regions on orders.country = regions.country full join targets on orders.customer = targets.customer cross join calendar
All five join kinds are supported. The ON expression runs in the join's own scope: bare names resolve against the incoming relation, and qualified names use source labels. A bare name that exists on both sides is an error — qualify it. When a right column name collides with a live column, select * exposes it as <label>_<column>.
filter and derive
filter quantity >= 5 and country in ('United Kingdom', 'Netherlands')
derive line_total = quantity * unit_price, tax = line_total * 0.2
derive items resolve left to right inside one stage, so an item may reference an earlier item from the same stage. A derived name must not collide with a live column.
group by + aggregate
group by country aggregate orders = COUNT(*), revenue = SUM(quantity * unit_price), best = MAX(quantity)
Aggregates are COUNT (including COUNT(*) and COUNT(DISTINCT x)), SUM, AVG, MIN, and MAX. After the unit, only the group keys, the aggregate names, and later group-phase derive values are addressable. Nesting aggregates is rejected. The pair may repeat later in the pipeline to aggregate an aggregate.
having
having revenue > 100
Filters the grouped relation. It runs before a later select, so a select alias is not in scope yet — reference the aggregate name instead.
window
window rank = ROW_NUMBER() over (partition by customer order by quantity desc, order_id) window total = SUM(line_total) over ()
Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG, COUNT, MIN, MAX. over () is valid for whole-relation aggregates such as a share of total. Frames (rows between ...) are not implemented yet.
select
select customer, revenue select *, margin = revenue - cost
A projection: later stages see the projected columns, and a name introduced here must not duplicate a live column. select x = y means "output column x from expression y", never a comparison.
sort, skip, take, distinct
sort -revenue, customer skip 5 take 10 distinct
A leading - (or trailing desc) sorts descending; the formatter canonicalizes to -. Sort order is preserved through later row-preserving stages and top-N via an internal ordinal and removed from the visible output; group by, distinct, set operators, and sql reset the ordering boundary, so add a final sort when output order matters.
Set operators
from orders select customer, country union (from customers | select customer, country) intersect (from orders | filter quantity > 1 | select customer, country) sort customer, country
union all keeps duplicates, plain union deduplicates. Both sides must return the same number of columns (checked unless a side is sql-opaque); names and order follow the left side.
sql — escape hatch
from orders sql "SELECT *, line_total / (SELECT SUM(line_total) FROM __input__) AS share FROM __input__"
One query per stage, starting with SELECT or WITH. The previous relation is __input__; named tables stay visible. Columns after the hatch are treated as opaque, so joins after a sql stage are rejected. Use it for anything the stages cannot express yet.
Comments and separators
# full-line comment -- SQL-style comment from orders | take 1 -- `|` separates stages on one line
# and -- comments run to end of line. A newline or | at the top level ends a stage; inside parentheses both separate the stages of a nested program.