SQL Reference

문서 > 로그 관리 > Log Workspaces > 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:

Syntax compatible with PostgreSQL
SQL functions
Window functions

Syntax

The following SQL syntax is supported:

Syntax	Description	Example
`SELECT (DISTINCT)` DISTINCT: Optional	Retrieves rows from a database, with `DISTINCT` filtering out duplicate records.	`SELECT DISTINCT customer_id FROM orders`
`JOIN`	Combines rows from two or more tables based on a related column between them.	`SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id`
`GROUP BY`	Groups 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` filters on strings for pattern matching.	Filters records that meet a specified condition.	`SELECT * FROM employees WHERE department = 'Sales' AND name LIKE 'J%'`
`CASE`	Provides 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`
`WINDOW`	Performs 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`
Arithmetic Operations	Performs basic calculations using operators like `+`, `-`, `*`, `/`.	`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 unit`	interval	Represents a time duration specified in a given unit.

Examples

`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

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
`OVER`	N/A	Defines a window for a set of rows for other window functions to operate on.
`PARTITION BY`	N/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.