Functions Reference¶

Complete reference of ASQL built-in functions.

Aggregate Functions¶

count¶

Count rows or non-null values.

count(*)                    -- Count all rows
count(column)               -- Count non-null values
count(distinct column)      -- Count distinct values
--                           -- Shorthand for count(*)
#(column)                   -- Shorthand for count(column)

sum¶

Sum numeric values.

sum(amount)                 -- Sum of amount
sum_amount                  -- Shorthand (same as above)
total(amount)               -- Alias for sum
total amount                -- Natural language style

avg / average¶

Calculate average.

avg(price)                  -- Average price
average(price)              -- Alias
avg_price                   -- Shorthand
average of price            -- Natural language

min / minimum¶

Find minimum value.

min(date)                   -- Earliest date
minimum(amount)             -- Smallest amount
min_created_at              -- Shorthand

max / maximum¶

Find maximum value.

max(amount)                 -- Largest amount
maximum(amount)             -- Alias
max_created_at              -- Shorthand

Date Truncation Functions¶

Truncate dates to a specific precision.

year¶

year(created_at)            -- Truncate to year start: 2025-01-01
year_created_at             -- Shorthand
year of created_at          -- Natural language

month¶

month(created_at)           -- Truncate to month start: 2025-03-01

week¶

week(created_at)            -- Truncate to week start (Monday)
week_monday(created_at)     -- Explicit Monday start
week_sunday(created_at)     -- Sunday start

day¶

day(created_at)             -- Truncate to day: 2025-03-15

hour¶

hour(created_at)            -- Truncate to hour: 2025-03-15 14:00:00

quarter¶

quarter(created_at)         -- Truncate to quarter start

Date Part Extraction¶

Extract specific parts from dates (returns integers).

day of week¶

day of week created_at      -- 1-7 (Monday = 1)
day_of_week(created_at)     -- Function style

day of month¶

day of month created_at     -- 1-31
day_of_month(created_at)

day of year¶

day of year created_at      -- 1-366
day_of_year(created_at)

week of year¶

week of year created_at     -- 1-52
week_of_year(created_at)

month of year¶

month of year created_at    -- 1-12
month_of_year(created_at)

quarter of year¶

quarter of year created_at  -- 1-4
quarter_of_year(created_at)

Date Difference Functions¶

Calculate time between dates.

days¶

days(end_date - start_date)     -- Integer days between
days_between(start, end)        -- Alternative syntax

weeks¶

weeks(end_date - start_date)    -- Integer weeks between
weeks_between(start, end)

months¶

months(end_date - start_date)   -- Integer months between
months_between(start, end)

years¶

years(end_date - start_date)    -- Integer years between
years_between(start, end)

hours¶

hours(end_date - start_date)    -- Integer hours between

Time Since/Until Functions¶

Calculate time elapsed since a date, or time remaining until a future date.

days_since / days_until¶

-- Three equivalent syntaxes:
days_since(created_at)          -- Function call (recommended)
days since created_at           -- Space notation
days_since_created_at           -- Underscore alias

days_until(due_date)            -- Time remaining
days until due_date
days_until_due_date

weeks_since / weeks_until¶

weeks_since(signup_date)
weeks since signup_date
weeks_since_signup_date

weeks_until(deadline)
weeks until deadline

months_since / months_until¶

months_since(last_login)
months since last_login

months_until(renewal_date)
months until renewal_date

years_since / years_until¶

years_since(birth_date)         -- Calculate age
years since birth_date

years_until(contract_end)

hours_since / hours_until¶

hours_since(event_time)
hours since event_time

minutes_since / minutes_until¶

minutes_since(last_update)
minutes since last_update

seconds_since / seconds_until¶

seconds_since(request_time)
seconds since request_time

Window Functions¶

prior¶

Access previous row value (LAG).

prior(revenue)              -- Previous row's revenue
prior(revenue, 3)           -- 3 rows back

next¶

Access next row value (LEAD).

next(revenue)               -- Next row's revenue
next(revenue, 2)            -- 2 rows ahead

running_sum¶

Cumulative sum from start.

running_sum(amount)         -- Cumulative total
running sum amount          -- Space style

running_avg¶

Cumulative average from start.

running_avg(amount)         -- Average to date

running_count¶

Cumulative count from start.

running_count(*)            -- Row number (effectively)

rolling_avg¶

Moving average with window size.

rolling_avg(price, 7)       -- 7-day moving average
rolling_avg(price, 30)      -- 30-day moving average

rolling_sum¶

Moving sum with window size.

rolling_sum(amount, 7)      -- 7-day rolling sum

first¶

Get first value with ordering.

first(order_id order by -order_date)    -- Latest order
first(order_id order by order_date)     -- Earliest order

last¶

Get last value with ordering.

last(order_id order by order_date)      -- Latest order

arg_max¶

Value where another column is maximum.

arg_max(order_id, order_date)           -- order_id at max order_date

arg_min¶

Value where another column is minimum.

arg_min(order_id, order_date)           -- order_id at min order_date

row_number¶

Standard SQL window function.

row_number() over (partition by customer_id order by -order_date)

rank¶

Standard SQL window function.

rank() over (partition by department order by -salary)

dense_rank¶

Standard SQL window function (no gaps).

dense_rank() over (partition by category order by -revenue)

String Functions¶

concat¶

Concatenate strings.

concat(first_name, " ", last_name)

string_agg¶

Aggregate strings with separator.

string_agg(product_name, ", ")

upper / lower¶

Change case.

upper(name)
lower(email)

trim / ltrim / rtrim¶

Remove whitespace.

trim(input)
ltrim(input)
rtrim(input)

length¶

String length.

length(name)

substring¶

Extract substring.

substring(email, 1, 5)
email[1:5]                  -- Slice syntax

replace¶

Replace text.

replace(text, "old", "new")

Null Handling Functions¶

coalesce¶

Return first non-null value.

value ?? default            -- Operator form (preferred)
primary_email ?? secondary_email ?? "unknown"  -- Chained fallbacks

-- Function form (also accepted)
coalesce(primary_email, secondary_email, "unknown")

Type Functions¶

cast¶

Explicit type conversion.

value::INTEGER              -- Cast to integer
value::DATE                 -- Cast to date
value::TIMESTAMP            -- Cast to timestamp
value::INTEGER?             -- Safe cast (NULL on failure)

Special Functions¶

guarantee¶

Specify explicit values for group by spine.

guarantee(status, ['pending', 'active', 'completed'])

Function Aliases¶

Natural Language	SQL Function
`total`	`SUM`
`average`	`AVG`
`maximum`	`MAX`
`minimum`	`MIN`

Auto-Generated Column Names¶

When functions are used without explicit AS aliases, ASQL automatically generates meaningful column names:

Function	Auto-Alias
`sum(amount)`	`sum_amount`
`avg(price)`	`avg_price`
`count(*)` / `#`	`num`
`month(created_at)`	`month_created_at`
`upper(name)`	`upper_name`

See the Auto-Aliasing Reference for the complete mapping table and configuration.