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, from_string s1, to_string s2)stringReplaces occurrences of a substring within a string with another substring.
substring(string s, start_position_int i, length_int l)stringExtracts a substring from a string, starting at a given position and for a specified length.
extract(field from timestamp/interval)numericExtracts a part of a date or time field (such as year or month) from a timestamp or interval.
to_timestamp(numeric n)timestamp with time zoneConverts a numeric value to a timestamp with time zone.
to_char(timestamp t / interval i / numeric n, format f)stringConverts a timestamp, interval, or numeric value to a string using a format.
date_trunc(field f, source [, time_zone])timestamp [with time zone] / intervalTruncates a timestamp or interval to a specified precision.
regexp_like(string s, pattern p [flags])booleanEvaluates if a string matches a regular expression pattern.

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

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

EXTRACT

SELECT
  extract(year FROM purchase_date) AS purchase_year
FROM
  sales

TO_TIMESTAMP

SELECT
  to_timestamp(epoch_time) AS formatted_time
FROM
  event_logs

TO_CHAR

SELECT
  to_char(order_date, 'MM-DD-YYYY') AS formatted_date
FROM
  orders

DATE_TRUNC

SELECT
  date_trunc('month', event_time) AS month_start
FROM
  events

REGEXP_LIKE

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

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