SQL Commands
For security and stability reasons, we only allow a carefully selected collection of SQL functions. This ensures that queries cannot modify data, access system resources, or interfere with other users' data. The functions listed below have been tested and approved for use in the Polyteia platform.
If you need additional SQL functions for your use case, please contact us through our help and support page and we'll be happy to review your request.
Mathematical Functions
ABS - Absolute value
Returns the absolute (positive) value of a number.
Syntax: ABS(number)
Example:
SELECT ABS(-25) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 25
CEIL - Round up to nearest integer
Rounds a number up to the nearest integer.
Syntax: CEIL(number)
Example:
SELECT CEIL(4.3) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 5
FLOOR - Round down to nearest integer
Rounds a number down to the nearest integer.
Syntax: FLOOR(number)
Example:
SELECT FLOOR(4.8) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 4
ROUND - Round to nearest integer or decimal places
Rounds a number to the nearest integer or specified decimal places.
Syntax: ROUND(number, [decimal_places])
Example:
SELECT ROUND(4.567, 2) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 4.57
SQRT - Square root
Returns the square root of a number.
Syntax: SQRT(number)
Example:
SELECT SQRT(16) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 4
EXP - Exponential function
Returns e raised to the power of the given number.
Syntax: EXP(number)
Example:
SELECT EXP(1) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 2.718281828459045
LN - Natural logarithm
Returns the natural logarithm of a number.
Syntax: LN(number)
Example:
SELECT LN(2.718281828459045) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 1
LOG - Logarithm
Returns the logarithm of a number to a specified base.
Syntax: LOG(base, number)
Example:
SELECT LOG(10, 100) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 2
POWER - Raise to power
Returns a number raised to the power of another number.
Syntax: POWER(base, exponent)
Example:
SELECT POWER(2, 3) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 8
MOD - Modulo operation
Returns the remainder of a division operation.
Syntax: MOD(dividend, divisor)
Example:
SELECT MOD(10, 3) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 1
Aggregate Functions
COUNT - Count rows
Counts the number of rows or non-null values.
Syntax: COUNT(*)
or COUNT(column)
Example:
SELECT COUNT(*) AS total_rows
FROM '{{ds_your_dataset}}'
LIMIT 10
COUNT_DISTINCT - Count unique values
Counts the number of distinct (unique) values in a column.
Syntax: COUNT_DISTINCT(column)
Example:
SELECT COUNT_DISTINCT(department) AS unique_departments
FROM '{{ds_your_dataset}}'
LIMIT 10
COUNT_IF - Conditional count
Counts rows that meet a specific condition.
Syntax: COUNT_IF(condition)
Example:
SELECT COUNT_IF(age > 30) AS adults_over_30
FROM '{{ds_your_dataset}}'
LIMIT 10
SUM - Sum values
Calculates the sum of numeric values.
Syntax: SUM(column)
Example:
SELECT SUM(salary) AS total_salary
FROM '{{ds_your_dataset}}'
LIMIT 10
AVG - Average value
Calculates the average of numeric values.
Syntax: AVG(column)
Example:
SELECT AVG(age) AS average_age
FROM '{{ds_your_dataset}}'
LIMIT 10
MIN - Minimum value
Returns the smallest value in a column.
Syntax: MIN(column)
Example:
SELECT MIN(salary) AS lowest_salary
FROM '{{ds_your_dataset}}'
LIMIT 10
MAX - Maximum value
Returns the largest value in a column.
Syntax: MAX(column)
Example:
SELECT MAX(salary) AS highest_salary
FROM '{{ds_your_dataset}}'
LIMIT 10
STRING_AGG - Concatenate strings
Concatenates string values with a specified separator.
Syntax: STRING_AGG(column, separator)
Example:
SELECT STRING_AGG(name, ', ') AS all_names
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "John, Jane, Bob"
ANY_VALUE - Get any value
Returns any value from a group (useful with GROUP BY).
Syntax: ANY_VALUE(column)
Example:
SELECT department, ANY_VALUE(manager) AS sample_manager
FROM '{{ds_your_dataset}}'
GROUP BY department
LIMIT 10
Type Conversion Functions
CAST - Convert data types
Converts a value from one data type to another.
Syntax: CAST(value AS data_type)
Example:
SELECT CAST('123' AS INTEGER) AS number
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 123
String Functions
SUBSTR - Extract substring from text
Extracts a portion of a string starting at a specific position.
Syntax: SUBSTR(string, start, length)
Example:
SELECT SUBSTR("Hello World", 1, 5) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "Hello"
LEFT - Get leftmost characters
Returns the leftmost characters from a string.
Syntax: LEFT(string, length)
Example:
SELECT LEFT("Hello World", 5) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "Hello"
RIGHT - Get rightmost characters
Returns the rightmost characters from a string.
Syntax: RIGHT(string, length)
Example:
SELECT RIGHT("Hello World", 5) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "World"
REPLACE - Replace text in string
Replaces all occurrences of a substring with another substring.
Syntax: REPLACE(string, old_text, new_text)
Example:
SELECT REPLACE("Hello World", "World", "DuckDB") AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "Hello DuckDB"
REGEXP_REPLACE - Replace using regular expressions
Replaces text using regular expression patterns for advanced text manipulation.
Syntax: REGEXP_REPLACE(string, pattern, replacement)
Example:
-- Convert DD.MM.YYYY to YYYY-MM-DD format
SELECT REGEXP_REPLACE("19.02.2025", '(\d{2})\.(\d{2})\.(\d{4})', '\3-\2-\1') AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "2025-02-19"
Date and Time Functions
DATE_FORMAT - Format date as string
Formats a date value as a string with a specific format.
Syntax: DATE_FORMAT(date, format)
Example:
SELECT DATE_FORMAT(CURRENT_DATE, '%d/%m/%Y') AS formatted_date
FROM '{{ds_your_dataset}}'
LIMIT 10
DATE_PART - Extract part of date
Extracts a specific part from a date (year, month, day, etc.).
Syntax: DATE_PART(part, date)
Example:
SELECT DATE_PART('year', CURRENT_DATE) AS current_year
FROM '{{ds_your_dataset}}'
LIMIT 10
DATE_TRUNC - Truncate date to specific precision
Truncates a date to a specified level of precision (year, month, day, hour, etc.).
Syntax: DATE_TRUNC(precision, date)
Example:
SELECT DATE_TRUNC('day', STRPTIME("2025-02-19 14:30:00", '%Y-%m-%d %H:%M:%S')) AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: "2025-02-19 00:00:00"
STRPTIME - Parse string to datetime
Converts a string to a datetime using a specified format pattern.
Syntax: STRPTIME(string, format)
Common format codes:
%Y
- 4-digit year%m
- Month (01-12)%d
- Day (01-31)%H
- Hour (00-23)%M
- Minute (00-59)
Example:
-- Parse German date format
SELECT STRPTIME("19.02.2025 10:44", '%d.%m.%Y %H:%M') AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 2025-02-19 10:44:00
STRFTIME - Format datetime to string
Converts a datetime to a string using a specified format pattern.
Syntax: STRFTIME(date, format)
Example:
SELECT STRFTIME(CURRENT_DATE, '%d.%m.%Y') AS result
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns current date in DD.MM.YYYY format
SPLIT - Split string into array
Splits a string into an array using a delimiter.
Syntax: SPLIT(string, delimiter)
Example:
SELECT SPLIT('apple,banana,cherry', ',') AS fruit_array
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: ['apple', 'banana', 'cherry']
YEAR - Extract year from date
Extracts the year from a date value.
Syntax: YEAR(date)
Example:
SELECT YEAR(CURRENT_DATE) AS current_year
FROM '{{ds_your_dataset}}'
LIMIT 10
Array Functions
UNNEST - Expand array into rows
Converts an array into individual rows, with one row per array element.
Syntax: UNNEST(array)
Example:
SELECT UNNEST(['apple', 'banana', 'cherry']) AS fruit
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns three rows: "apple", "banana", "cherry"
ARRAY_LENGTH - Get array size
Returns the number of elements in an array.
Syntax: ARRAY_LENGTH(array)
Example:
SELECT ARRAY_LENGTH(['apple', 'banana', 'cherry']) AS array_size
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: 3
CONTAINS - Check if array contains value
Checks if an array contains a specific value.
Syntax: CONTAINS(array, value)
Example:
SELECT CONTAINS(['apple', 'banana', 'cherry'], 'banana') AS has_banana
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns: true
Utility Functions
NULLIF - Return NULL if values are equal
Returns NULL if two values are equal, otherwise returns the first value.
Syntax: NULLIF(value1, value2)
Example:
SELECT NULLIF(salary, 0) AS non_zero_salary
FROM '{{ds_your_dataset}}'
LIMIT 10
-- Returns NULL if salary is 0, otherwise returns salary
Operators
|| - String concatenation
Concatenates two or more strings together.
Syntax: string1 || string2
Example:
SELECT first_name || ' ' || last_name AS full_name
FROM '{{ds_your_dataset}}'
LIMIT 10
IN - Check if value exists in list
Checks if a value exists in a list of values.
Syntax: column IN (value1, value2, value3)
Example:
SELECT *
FROM '{{ds_your_dataset}}'
WHERE department IN ('Sales', 'Marketing', 'IT')
LIMIT 10
Spatial Functions (Geospatial)
Note: These functions are available for working with geographic data and spatial operations.
ST_AREA - Calculate area of geometry
Calculates the area of a polygon or other 2D geometry.
Syntax: ST_AREA(geometry)
Example:
SELECT ST_AREA(polygon_column) AS area_sqm
FROM '{{ds_your_dataset}}'
LIMIT 10
ST_DISTANCE - Calculate distance between geometries
Calculates the distance between two geometries.
Syntax: ST_DISTANCE(geometry1, geometry2)
Example:
SELECT ST_DISTANCE(point1, point2) AS distance_m
FROM '{{ds_your_dataset}}'
LIMIT 10
ST_CONTAINS - Check if geometry contains another
Checks if one geometry completely contains another geometry.
Syntax: ST_CONTAINS(geometry1, geometry2)
Example:
SELECT ST_CONTAINS(polygon, point) AS point_inside_polygon
FROM '{{ds_your_dataset}}'
LIMIT 10
ST_INTERSECTS - Check if geometries intersect
Checks if two geometries intersect (overlap or share space).
Syntax: ST_INTERSECTS(geometry1, geometry2)
Example:
SELECT ST_INTERSECTS(polygon1, polygon2) AS polygons_overlap
FROM '{{ds_your_dataset}}'
LIMIT 10
ST_POINT - Create point geometry
Creates a point geometry from X and Y coordinates.
Syntax: ST_POINT(x, y)
Example:
SELECT ST_POINT(longitude, latitude) AS location_point
FROM '{{ds_your_dataset}}'
LIMIT 10
For a complete list of spatial functions, see the DuckDB Spatial Extension documentation.
Zuletzt aktualisiert
War das hilfreich?