Functions and Expressions
This tutorial covers computed columns, string interpolation, case expressions, transforms, and user-defined functions. Each query is editable -- press Run (or Ctrl+Enter) to execute.
Arithmetic
Arithmetic in a projection creates computed columns. Use as to name them.
String Interpolation
Prefix a string with : to interpolate column values with {column}.
Use +( ) after a pipe to append the new column without dropping existing ones.
Simple Case Expression
_:(column @ value -> result; ...) is a simple CASE -- it matches a
column against literal values.
Searched Case Expression
Omit the column @ part and write predicates directly -- this is a
searched CASE, useful for range checks.
Filtered Aggregates
Add a predicate after | inside an aggregate to filter which rows are
counted -- no subquery needed.
Defining Functions (CFEs)
Define a reusable function with name:(args) : body. Once defined, call it
like any built-in.
CFE with Scalar Subquery
A CFE can encapsulate a correlated subquery, turning it into a clean function call.
Lambdas
:(expression) creates an anonymous function. Use @ as the placeholder
for the input value.
Function Piping
The /-> operator pipes a value through a function. This lets you
chain transformations left-to-right, including with higher-order CFEs
that accept functions as arguments.
Here apply_twice takes a function f:() and a value x, then pipes
x through f twice. The lambda :(@ * 2) doubles its input, so
piping through it twice quadruples the value.
Window Functions
Window functions use <~ to introduce the OVER clause. %(column) is
PARTITION BY, #(column desc) is ORDER BY.