Relix

Language reference

Operators by task (the operator for a task SQL has no operator for)

Syntax

<the task, in a question's words>  →  <the page of the operator that does it>

Description

Many questions have a Relix operator made for them, which SQL answers only with a workaround: a self-join, a correlated subquery, a recursive CTE or a frame clause. This page lists those tasks, each in the words a question puts it in, and links the page of the operator that does it. Before writing a script, check whether its task is one of these; if it is, read the page.

Each link is a page's path in this reference, the path a tool that serves the reference reads it by.

Rows and their neighbours

  • A value from another row of the same group: the previous or next row, the first or last in order → Window offset
  • Number the rows of each group in order, or rank them, ties and all → Window ranking
  • The top N rows of each group: the latest per customer, the three biggest per category → Top-K
  • Running totals, cumulative sums, moving averages over an ordered sequence → Rolling
  • A centred moving window: the average of each row with the one before and the one after it → Rolling
  • Split each group's ordered rows into sessions wherever the gap between two rows exceeds a limit: gaps and islands → Sessionize
  • One row per time bucket: per hour, per day, fewer points over a long series → Downsample

Groups

  • A value from a related relation beside each row: each customer's order count, total or latest order → Subquery, or the rows themselves, keeping a row with none → LATERAL
  • Keep the rows that have, or have no, related row → EXISTS
  • Subtotals and a grand total in one result, and which level each row is → GROUPING
  • The value from the row with the highest or lowest of something, per group: the name of each team's top scorer → ARGMAX, ARGMIN
  • Count or sum only the rows that meet a condition, beside the total → FILTER
  • The most common value of each group: each customer's usual status → MODE
  • Whether every row of each group meets a condition, or any does, as a column → BOOL_AND, BOOL_OR
  • Gather a group's values into a list → COLLECT, or into one string → STRING_AGG
  • Turn the values of a column into columns of their own → Pivot; turn columns into rows → Unpivot
  • One row per element of a list held in a column → Unnest

Hierarchies and graphs

  • Every pair linked through a chain of any length: ancestors, descendants, everyone under a manager, everything reachable → Closure
  • Routes between nodes: the shortest path, how many steps apart two nodes are, what is reachable within N steps → Path
  • A hierarchy as nested documents, each item with its children inside it → Tree
  • A recursion the two above do not fit, built up until nothing new appears → Fixpoint
  • Repeat a computation until it settles: PageRank, a state machine, a value that converges → Iterate
  • Group rows that are linked, directly or through others → Cluster; rows that probably describe the same thing → Record linkage

Matching and comparing

  • Match each row to the nearest earlier row in time: the price in force when an order was placed → AS-OF join
  • Rows whose time ranges overlap, or one inside another → Interval join
  • Keep the rows that have a match in another relation → Semi join, or that have none → Anti join
  • Keep the rows that match every row of another relation: the products affordable within every department's budget → Universal semi join
  • Keep the rows of both sides, matched or not → Full outer join
  • Rows related to every one of something: students who took all the required courses → Division, Universal
  • Rows in one of two relations but not both → Symmetric difference
  • Put two relations' rows together, keeping the repeats → Union all

Dates

  • The time between two dates as an age, in years, months and days → AGE

Choosing and explaining

  • Values that satisfy a set of constraints: a puzzle, an assignment → Solve
  • The best choice or quantities under limits: maximise or minimise an objective → Optimize
  • The fewest combinations that still cover every pair of values → Cover
  • Which input rows produced each result row → Why
  • A random sample of the rows → Sample, Reservoir

Examples

"Each order's amount beside the customer's previous one" is a value from another row of the same group, so it is Window offset's:

WINDOW LAG(amount) SORT order_date ASC PER customer_id AS prev_amount (Orders)

"The three biggest orders of each customer" is the top N rows of each group:

TOP 3 amount DESC PER customer_id (Orders)

"Customers who placed no order" keeps the rows that have no match:

Customers ▷ Customers.customer_id = Orders.customer_id Orders

See Also

spellings, glossary, getting started