SQL Reference

Overview

SQL in Analysis cells allows you to analyze and manipulate data within Log Workspaces. This documentation covers the SQL support available in Log Workspaces and includes:

Example workspace cell with SQL syntax

Syntax

The following SQL syntax is supported:

SyntaxDescriptionExample
SELECT (DISTINCT)
DISTINCT: Optional		Retrieves rows from a database, with DISTINCT filtering out duplicate records.
SELECT DISTINCT customer_id 
FROM orders
JOINCombines rows from two or more tables based on a related column between them. Supports FULL JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN.
SELECT orders.order_id, customers.customer_name 
FROM orders 
JOIN customers 
ON orders.customer_id = customers.customer_id
GROUP BYGroups rows that have the same values in specified columns into summary rows.
SELECT product_id, SUM(quantity) 
FROM sales 
GROUP BY product_id
WHERE
Includes support for LIKE, IN, ON, OR filters.		Filters records that meet a specified condition.
SELECT * 
FROM employees 
WHERE department = 'Sales' AND name LIKE 'J%'
CASEProvides conditional logic to return different values based on specified conditions.
SELECT order_id, 
  CASE 
    WHEN quantity > 10 THEN 'Bulk Order' 
    ELSE 'Standard Order' 
  END AS order_type 
FROM orders
WINDOWPerforms a calculation across a set of table rows that are related to the current row.
SELECT
  timestamp,
  service_name,
  cpu_usage_percent,
  AVG(cpu_usage_percent) OVER (PARTITION BY service_name ORDER BY timestamp ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_cpu
FROM
  cpu_usage_data
IS NULL / IS NOT NULLChecks if a value is null or not null.
SELECT * 
FROM orders 
WHERE delivery_date IS NULL
LIMITSpecifies the maximum number of records to return.
SELECT * 
FROM customers 
LIMIT 10
ORDER BYSorts the result set of a query by one or more columns. Includes ASC, DESC for sorting order.
SELECT * 
FROM sales 
ORDER BY sale_date DESC
HAVINGFilters records that meet a specified condition after grouping.
SELECT product_id, SUM(quantity) 
FROM sales 
GROUP BY product_id 
HAVING SUM(quantity) > 10
IN, ON, ORUsed for specified conditions in queries. Available in WHERE, JOIN clauses.
SELECT * 
FROM orders 
WHERE order_status IN ('Shipped', 'Pending')
ASRenames a column or table with an alias.
SELECT first_name AS name 
FROM employees
Arithmetic OperationsPerforms basic calculations using operators like +, -, *, /.
SELECT price, tax, (price * tax) AS total_cost 
FROM products
INTERVAL value unitintervalRepresents a time duration specified in a given unit.

Functions

The following SQL functions are supported. For Window function, see the separate Window function section in this documentation.

FunctionReturn TypeDescription
min(variable v)typeof vReturns the smallest value in a set of data.
max(variable v)typeof vReturns the maximum value across all input values.
count(any a)numericReturns the number of input values that are not null.
sum(numeric n)numericReturns the summation across all input values.
avg(numeric n)numericReturns the average value (arithmetic mean) across all input values.
ceil(numeric n)numericReturns the value rounded up to the nearest integer.
floor(numeric n)numericReturns the value rounded down to the nearest integer.
round(numeric n)numericReturns the value rounded to the nearest integer.
lower(string s)stringReturns the string as lowercase.
upper(string s)stringReturns the string as uppercase.
abs(numeric n)numericReturns the absolute value.
coalesce(args a)typeof first non-null a OR nullReturns the first non-null value or null if all are null.
cast(value AS type)typeConverts the given value to the specified data type.
length(string s)integerReturns the number of characters in the string.
trim(string s)stringRemoves leading and trailing whitespace from the string.
replace(string s, string from, string to)stringReplaces occurrences of a substring within a string with another substring.
substring(string s, int start, int length)stringExtracts a substring from a string, starting at a given position and for a specified length.
strpos(string s, string substring)integerReturns the first index position of the substring in a given string, or 0 if there is no match.
split_part(string s, string delimiter, integer index)stringSplits the string on the given delimiter and returns the string at the given position counting from one.
extract(unit from timestamp/interval)numericExtracts a part of a date or time field (such as year or month) from a timestamp or interval.
to_timestamp(string timestamp, string format)timestampConverts a string to a timestamp according to the given format.
to_char(timestamp t, string format)stringConverts a timestamp to a string according to the given format.
date_trunc(string unit, timestamp t)timestampTruncates a timestamp to a specified precision based on the provided unit.
regexp_like(string s, pattern p)booleanEvaluates whether a string matches a regular expression pattern.
cardinality(array a)integerReturns the number of elements in the array.
array_position(array a, typeof_array value)integerReturns the index of the first occurrence of the value found in the array, or null if value is not found.
string_to_array(string s, string delimiter)array of stringsSplits the given string into an array of strings using the given delimiter.
array_agg(expression e)array of input typeCreates an array by collecting all the input values.
unnest(array a [, array b...])rows of a [, b…]Expands arrays into a set of rows. This form is only allowed in a FROM clause.

MIN

SELECT MIN(response_time) AS min_response_time 
FROM logs 
WHERE status_code = 200

MAX

SELECT MAX(response_time) AS max_response_time 
FROM logs 
WHERE status_code = 200

COUNT

SELECT COUNT(request_id) AS total_requests 
FROM logs 
WHERE status_code = 200

SUM

SELECT SUM(bytes_transferred) AS total_bytes 
FROM logs 
GROUP BY service_name

AVG

SELECT AVG(response_time) 
AS avg_response_time 
FROM logs 
WHERE status_code = 200 
GROUP BY service_name

CEIL

 
SELECT CEIL(price) AS rounded_price 
FROM products

FLOOR

SELECT FLOOR(price) AS floored_price 
FROM products

ROUND

SELECT ROUND(price) AS rounded_price 
FROM products

LOWER

SELECT LOWER(customer_name) AS lowercase_name 
FROM customers

UPPER

SELECT UPPER(customer_name) AS uppercase_name 
FROM customers

ABS

SELECT ABS(balance) AS absolute_balance 
FROM accounts

COALESCE

SELECT COALESCE(phone_number, email) AS contact_info 
FROM users

CAST

Supported cast target types:

  • BIGINT
  • DECIMAL
  • TIMESTAMP
  • VARCHAR
SELECT
  CAST(order_id AS VARCHAR) AS order_id_string,
  'Order-' || CAST(order_id AS VARCHAR) AS order_label
FROM
  orders

LENGTH

SELECT
  customer_name,
  LENGTH(customer_name) AS name_length
FROM
  customers

INTERVAL

SELECT
  TIMESTAMP '2023-10-01 10:00:00' + INTERVAL '30 days' AS future_date

TRIM

SELECT
  trim(name) AS trimmed_name
FROM
  users

REPLACE

SELECT
  replace(description, 'old', 'new') AS updated_description
FROM
  products

SUBSTRING

SELECT
  substring(title, 1, 10) AS short_title
FROM
  books

STRPOS

SELECT
  STRPOS('foobar', 'bar')

SPLIT_PART

SELECT
  SPLIT_PART('aaa-bbb-ccc', '-', 2)

EXTRACT

Supported extraction units:

LiteralInput TypeDescription
daytimestamp / intervalday of the month
dowtimestampday of the week 1 (Monday) to 7 (Sunday)
doytimestampday of the year (1 - 366)
hourtimestamp / intervalhour of the day (0 - 23)
minutetimestamp / intervalminute of the hour (0 - 59)
secondtimestamp / intervalsecond of the minute (0 - 59)
weektimestampweek of the year (1 - 53)
monthtimestampmonth of the year (1 - 12)
quartertimestampquarter of the year (1 - 4)
yeartimestampyear
timezone_hourtimestamphour of the time zone offset
timezone_minutetimestampminute of the time zone offset
SELECT
  extract(year FROM purchase_date) AS purchase_year
FROM
  sales

TO_TIMESTAMP

Supported patterns for date/time formatting:

PatternDescription
YYYYyear (4 digits)
YYyear (2 digits)
MMmonth number (01 - 12)
DDday of month (01 - 31)
HH24hour of day (00 - 23)
HH12hour of day (01 - 12)
HHhour of day (01 - 12)
MIminute (00 - 59)
SSsecond (00 - 59)
MSmillisecond (000 - 999)
TZtime-zone abbreviation
OFtime-zone offset from UTC
AM / ammeridiem indicator (without periods)
PM / pmmeridiem indicator (without periods)
SELECT
  to_timestamp('25/12/2025 04:23 pm', 'DD/MM/YYYY HH:MI am') AS ts

TO_CHAR

Supported patterns for date/time formatting:

PatternDescription
YYYYyear (4 digits)
YYyear (2 digits)
MMmonth number (01 - 12)
DDday of month (01 - 31)
HH24hour of day (00 - 23)
HH12hour of day (01 - 12)
HHhour of day (01 - 12)
MIminute (00 - 59)
SSsecond (00 - 59)
MSmillisecond (000 - 999)
TZtime-zone abbreviation
OFtime-zone offset from UTC
AM / ammeridiem indicator (without periods)
PM / pmmeridiem indicator (without periods)
SELECT
  to_char(order_date, 'MM-DD-YYYY') AS formatted_date
FROM
  orders

DATE_TRUNC

Supported truncations:

  • milliseconds
  • seconds / second
  • minutes / minute
  • hours / hour
  • days / day
  • weeks / week
  • months / month
  • quarters / quarter
  • years / year
SELECT
  date_trunc('month', event_time) AS month_start
FROM
  events

REGEXP_LIKE

SELECT
  *
FROM
  emails
WHERE
  regexp_like(email_address, '@example\.com$')

CARDINALITY

SELECT
  CARDINALITY(recipients)
FROM
  emails

ARRAY_POSITION

SELECT
  ARRAY_POSITION(recipients, 'hello@example.com')
FROM
  emails

STRING_TO_ARRAY

SELECT 
  STRING_TO_ARRAY('a,b,c,d,e,f', ',')

ARRAY_AGG

SELECT 
  sender,
  ARRAY_AGG(subject) subjects, 
  ARRAY_AGG(ALL subject) all_subjects, 
  ARRAY_AGG(DISTINCT subject) distinct_subjects
FROM 
  emails
GROUP BY 
  sender

UNNEST

SELECT 
  sender,
  recipient 
FROM 
  emails,
  UNNEST(recipients) AS recipient

Window functions

This table provides an overview of the supprted window functions. For comprehensive details and examples, see to the PostgreSQL documentation.

FunctionReturn TypeDescription
OVERN/ADefines a window for a set of rows for other window functions to operate on.
PARTITION BYN/ADivides the result set into partitions, specifically for applying window functions.
RANK()integerAssigns a rank to each row within a partition, with gaps for ties.
ROW_NUMBER()integerAssigns a unique sequential number to each row within a partition.
LEAD(column n)typeof columnReturns the value from the next row in the partition.
LAG(column n)typeof columnReturns the value from the previous row in the partition.
FIRST_VALUE(column n)typeof columnReturns the first value in an ordered set of values.
LAST_VALUE(column n)typeof columnReturns the last value in an ordered set of values.
NTH_VALUE(column n, offset)typeof columnReturns the value at the specified offset in an ordered set of values.

Further reading

Additional helpful documentation, links, and articles:

Learn more about Log WorkspacesDOCUMENTATION more more