How to use SQL Functions

---

Table of Contents

1. Function Types
   1. Aggregate Functions
   2. Scalar Functions
   3. Window Functions
2. Usage Examples
   1. Basic Function Usage
   2. Function Chaining
   3. Functions in Queries
3. Additional Resources
4. Implementation Notes

---

This document provides an overview of the SQL functions supported by Oxibase. For detailed documentation on each function type, see the dedicated guides below.

Function Types

Aggregate Functions

Aggregate functions perform calculations on sets of values to return a single result, typically used with GROUP BY clauses for data summarization.

View Aggregate Functions Guide →

Scalar Functions

Scalar functions operate on individual values and return a single result. They include string manipulation, numeric operations, date/time processing, type conversion, and more.

View Scalar Functions Guide →

Window Functions

Window functions perform calculations across related rows without grouping them into a single output row, enabling advanced analytical queries.

View Window Functions Guide →

Usage Examples

Basic Function Usage

-- Scalar function
SELECT UPPER(name), LENGTH(description) FROM products;

-- Aggregate function
SELECT category, COUNT(*), AVG(price) FROM products GROUP BY category;

-- Window function
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

Function Chaining

Functions can be nested for complex operations:

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

Functions in Queries

Functions can be used in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses:

SELECT DATE_TRUNC('month', order_date) as month, SUM(total) as sales
FROM orders
WHERE order_date >= DATE_SUB(NOW(), 1, 'year')
GROUP BY DATE_TRUNC('month', order_date)
HAVING SUM(total) > 10000
ORDER BY month;

Additional Resources

• User-Defined Functions: Creating custom functions with scripting backends
• Stored Procedures: Multi-statement procedures (planned feature)
• SQL Features: Advanced SQL capabilities including ROLLUP/CUBE operations

Implementation Notes

OxiBase’s function system is designed for performance and extensibility:

• Modular Architecture: Functions are organized by type for easy maintenance
• Type Safety: Arguments are validated at parse time
• Multiple Backends: Support for different scripting languages for UDFs
• Optimization: Many functions can be pushed down to storage layer

For detailed implementation information, see the individual function guides.