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:
Syntax The following SQL syntax is supported:
Syntax Description Example SELECT (DISTINCT)Retrieves rows from a database, with DISTINCT filtering out duplicate records.
Copy
SELECT DISTINCT customer_id
FROM orders JOINCombines rows from two or more tables based on a related column between them.
Copy
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.
Copy
SELECT product_id , SUM ( quantity )
FROM sales
GROUP BY product_id WHERELIKE filters on strings for pattern matching.Filters records that meet a specified condition.
Copy
SELECT *
FROM employees
WHERE department = 'Sales' AND name LIKE 'J%' CASEProvides conditional logic to return different values based on specified conditions.
Copy
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.
Copy
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 Arithmetic Operations Performs basic calculations using operators like +, -, *, /.
Copy
SELECT price , tax , ( price * tax ) AS total_cost
FROM products
Functions The following SQL functions are supported. For Window function, see the separate Window function section in this documentation.
Function Return Type Description min(variable v)typeof v Returns the smallest value in a set of data. max(variable v)typeof v Returns the maximum value across all input values. count(any a)numeric Returns the number of input values that are not null. sum(numeric n)numeric Returns the summation across all input values. avg(numeric n)numeric Returns the average value (arithmetic mean) across all input values. ceil(numeric n)numeric Returns the value rounded up to the nearest integer. floor(numeric n)numeric Returns the value rounded down to the nearest integer. round(numeric n)numeric Returns the value rounded to the nearest integer. lower(string s)string Returns the string as lowercase. upper(string s)string Returns the string as uppercase. abs(numeric n)numeric Returns the absolute value. coalesce(args a)typeof first non-null a OR null Returns the first non-null value or null if all are null. cast(value AS type)type Converts the given value to the specified data type. length(string s)integer Returns the number of characters in the string. INTERVAL value unitinterval Represents a time duration specified in a given unit.
MIN
Copy
SELECT MIN ( response_time ) AS min_response_time
FROM logs
WHERE status_code = 200 MAX
Copy
SELECT MAX ( response_time ) AS max_response_time
FROM logs
WHERE status_code = 200 COUNT
Copy
SELECT COUNT ( request_id ) AS total_requests
FROM logs
WHERE status_code = 200 SUM
Copy
SELECT SUM ( bytes_transferred ) AS total_bytes
FROM logs
GROUP BY service_name AVG
Copy
SELECT AVG ( response_time )
AS avg_response_time
FROM logs
WHERE status_code = 200
GROUP BY service_name CEIL
Copy
SELECT CEIL ( price ) AS rounded_price
FROM products FLOOR
Copy
SELECT FLOOR ( price ) AS floored_price
FROM products ROUND
Copy
SELECT ROUND ( price ) AS rounded_price
FROM products LOWER
Copy
SELECT LOWER ( customer_name ) AS lowercase_name
FROM customers UPPER
Copy
SELECT UPPER ( customer_name ) AS uppercase_name
FROM customers ABS
Copy
SELECT ABS ( balance ) AS absolute_balance
FROM accounts COALESCE
Copy
SELECT COALESCE ( phone_number , email ) AS contact_info
FROM users CAST
Copy
SELECT
CAST ( order_id AS VARCHAR ) AS order_id_string ,
'Order-' || CAST ( order_id AS VARCHAR ) AS order_label
FROM
orders LENGTH
Copy
SELECT
customer_name ,
LENGTH ( customer_name ) AS name_length
FROM
customers INTERVAL
Copy
SELECT
TIMESTAMP '2023-10-01 10:00:00' + INTERVAL '30 days' AS future_date Window functions This table provides an overview of the supprted window functions. For comprehensive details and examples, see to the PostgreSQL documentation .
Function Return Type Description OVERN/A Defines a window for a set of rows for other window functions to operate on. PARTITION BYN/A Divides the result set into partitions, specifically for applying window functions. RANK()integer Assigns a rank to each row within a partition, with gaps for ties. ROW_NUMBER()integer Assigns a unique sequential number to each row within a partition. LEAD(column n)typeof column Returns the value from the next row in the partition. LAG(column n)typeof column Returns the value from the previous row in the partition. FIRST_VALUE(column n)typeof column Returns the first value in an ordered set of values. LAST_VALUE(column n)typeof column Returns the last value in an ordered set of values. NTH_VALUE(column n, offset)typeof column Returns the value at the specified offset in an ordered set of values.
Further reading Additional helpful documentation, links, and articles:
