SQL Functions Reference

SQL Functions Reference

This document provides a comprehensive reference for the SQL functions supported by Stoolap, categorized by function type.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

AVG

Calculates the average of a numeric column.

SELECT AVG(price) FROM products;

COUNT

Counts the number of rows or non-NULL values.

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-NULL values in a column
SELECT COUNT(email) FROM users;

FIRST

Returns the first value in a group.

SELECT category, FIRST(name) FROM products GROUP BY category;

LAST

Returns the last value in a group.

SELECT category, LAST(name) FROM products GROUP BY category;

MAX

Returns the maximum value from a column.

SELECT MAX(price) FROM products;

MIN

Returns the minimum value from a column.

SELECT MIN(price) FROM products;

SUM

Calculates the sum of values in a numeric column.

SELECT SUM(quantity * price) FROM order_items;

Scalar Functions

Scalar functions operate on a single value and return a single value.

String Functions

CONCAT

Concatenates two or more strings.

SELECT CONCAT(first_name, ' ', last_name) FROM users;

LENGTH

Returns the length of a string.

SELECT name, LENGTH(name) FROM products;

LOWER

Converts a string to lowercase.

SELECT LOWER(email) FROM users;

UPPER

Converts a string to uppercase.

SELECT UPPER(country_code) FROM locations;

SUBSTRING

Extracts a portion of a string.

-- Syntax: SUBSTRING(string, start_position, length)
SELECT SUBSTRING(description, 1, 100) FROM products;

COLLATE

Compares strings using specific collation rules.

SELECT * FROM users ORDER BY name COLLATE NOCASE;

Numeric Functions

ABS

Returns the absolute value of a number.

SELECT ABS(temperature) FROM weather_data;

CEILING

Rounds a number up to the nearest integer.

SELECT CEILING(price) FROM products;

FLOOR

Rounds a number down to the nearest integer.

SELECT FLOOR(price) FROM products;

ROUND

Rounds a number to a specified number of decimal places.

-- Round to nearest integer
SELECT ROUND(price) FROM products;

-- Round to 2 decimal places
SELECT ROUND(price, 2) FROM products;

Date and Time Functions

NOW

Returns the current date and time.

SELECT NOW();

DATE_TRUNC

Truncates a timestamp to a specified precision.

-- Truncate to day (removes time component)
SELECT DATE_TRUNC('day', timestamp) FROM events;

-- Truncate to month
SELECT DATE_TRUNC('month', timestamp) FROM events;

TIME_TRUNC

Truncates a time or timestamp to a specified precision.

-- Truncate to hour
SELECT TIME_TRUNC('hour', timestamp) FROM events;

-- Truncate to minute
SELECT TIME_TRUNC('minute', timestamp) FROM events;

Type Conversion Functions

CAST

Converts a value from one data type to another.

-- Convert string to integer
SELECT CAST(value AS INT) FROM data;

-- Convert string to timestamp
SELECT CAST(date_string AS TIMESTAMP) FROM events;

Conditional Functions

COALESCE

Returns the first non-NULL value from a list of expressions.

SELECT COALESCE(preferred_name, first_name, 'Unknown') FROM users;

Window Functions

Window functions perform calculations across a set of rows related to the current row.

ROW_NUMBER

Assigns a unique sequential integer to each row within a partition.

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

Advanced Usage

Function Chaining

Functions can be nested to perform complex operations:

SELECT ROUND(AVG(price), 2) FROM products;

Functions in WHERE Clauses

Functions can be used in WHERE clauses to filter data:

SELECT * FROM products WHERE LOWER(name) LIKE '%organic%';

Functions in GROUP BY and HAVING

Functions can be used in GROUP BY and HAVING clauses:

SELECT DATE_TRUNC('month', order_date) as month, SUM(total) as monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
HAVING SUM(total) > 10000;

Implementation Details

Stoolap’s function implementation is modular and extensible:

  • Function Registry - Central registry of all available functions
  • Type Checking - Functions validate argument types at parse time
  • Function Categories - Organized into scalar, aggregate, and window functions
  • Custom Implementations - Each function has a specialized implementation for performance

Functions are defined in:

  • /internal/functions/aggregate/ - Aggregate function implementations
  • /internal/functions/scalar/ - Scalar function implementations
  • /internal/functions/window/ - Window function implementations
  • /internal/functions/registry/ - Function registration system

Performance Considerations

  • Avoid using functions on indexed columns in WHERE clauses, as this may prevent index usage
  • Some functions can be pushed down to the storage layer for better performance
  • Window functions may require multiple passes over the data
  • Complex function chains may impact query performance