SQL Reference
# SQL Reference
This chapter provides complete SQL syntax reference supported by StreamSQL, including all supported clauses, functions, and operators.
# 📋 SQL Syntax Overview
StreamSQL supports a subset of standard SQL syntax, specifically optimized for stream processing.
SELECT [DISTINCT] select_list
FROM stream
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]
[LIMIT count]
[WITH (option = value [, ...])]
2
3
4
5
6
7
StreamSQL supports a subset of standard SQL syntax, specifically optimized for stream processing scenarios. This chapter provides complete SQL syntax reference.
# SQL Syntax Overview
# Basic Query Structure
SELECT [DISTINCT] select_list
FROM stream_name
[WHERE condition]
[GROUP BY grouping_list]
[HAVING condition]
[ORDER BY ordering_list]
[LIMIT number]
[WITH (option_list)]
2
3
4
5
6
7
8
# Supported Clauses
Clause | Required | Description |
---|---|---|
SELECT | Yes | Specify output fields |
FROM | Yes | Specify data source |
WHERE | No | Filter conditions |
GROUP BY | No | Grouping and windows |
HAVING | No | Aggregate result filtering |
ORDER BY | No | Sorting (limited support) |
LIMIT | No | Limit result count |
WITH | No | Configuration options |
# SELECT Clause
SELECT clause defines query output fields and calculation expressions.
# Basic Syntax
SELECT column1, column2, expression AS alias
FROM stream
2
# Supported Selection Types
# 1. Field Selection
-- Select all fields
SELECT * FROM stream
-- Select specific fields
SELECT deviceId, temperature FROM stream
-- Field alias
SELECT deviceId AS device, temperature AS temp FROM stream
2
3
4
5
6
7
8
# 2. Nested Field Access
-- Dot notation for nested fields
SELECT device.info.name, device.location.building FROM stream
-- Deep nesting
SELECT sensor.data.temperature.value FROM stream
2
3
4
5
# 3. Expression Calculation
-- Arithmetic expressions
SELECT temperature * 1.8 + 32 AS fahrenheit FROM stream
-- String concatenation
SELECT CONCAT(deviceId, '-', location) AS full_id FROM stream
-- Conditional expressions
SELECT CASE
WHEN temperature > 30 THEN 'hot'
WHEN temperature < 10 THEN 'cold'
ELSE 'normal'
END AS temp_level FROM stream
2
3
4
5
6
7
8
9
10
11
12
# 4. Aggregate Functions
-- Basic aggregation
SELECT COUNT(*), AVG(temperature), MAX(humidity) FROM stream
-- Aggregation with grouping
SELECT deviceId, AVG(temperature) FROM stream GROUP BY deviceId
2
3
4
5
# DISTINCT Deduplication
-- Deduplication query
SELECT DISTINCT deviceType FROM stream
-- Multi-field deduplication
SELECT DISTINCT deviceId, location FROM stream
2
3
4
5
# Basic Syntax
SELECT column1, column2, ...
SELECT expression AS alias
SELECT *
SELECT DISTINCT column1
2
3
4
# Field Selection
# 1. Direct Field Reference
-- Select specific fields
SELECT deviceId, temperature, humidity FROM stream
-- Select all fields
SELECT * FROM stream
2
3
4
5
# 2. Expression Calculation
-- Arithmetic expressions
SELECT deviceId, temperature * 1.8 + 32 as fahrenheit FROM stream
-- String concatenation
SELECT CONCAT(deviceId, '_', status) as device_status FROM stream
-- Conditional expressions
SELECT deviceId,
CASE
WHEN temperature > 30 THEN 'HIGH'
WHEN temperature > 20 THEN 'NORMAL'
ELSE 'LOW'
END as temp_level
FROM stream
2
3
4
5
6
7
8
9
10
11
12
13
14
# 3. Function Calls
-- Built-in functions
SELECT deviceId, UPPER(status), ABS(temperature) FROM stream
-- Aggregate functions
SELECT deviceId, AVG(temperature), COUNT(*) FROM stream
GROUP BY deviceId, TumblingWindow('1m')
-- Custom functions
SELECT deviceId, custom_function(temperature) FROM stream
2
3
4
5
6
7
8
9
# Aliases (AS)
-- Field alias
SELECT temperature AS temp, humidity AS hum FROM stream
-- Expression alias
SELECT temperature * 1.8 + 32 AS fahrenheit FROM stream
-- AS keyword can be omitted
SELECT temperature temp, humidity hum FROM stream
2
3
4
5
6
7
8
# DISTINCT
-- Deduplication (used in window aggregation)
SELECT DISTINCT deviceId, location
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
2
3
4
# FROM Clause
FROM clause specifies the data source, in StreamSQL it is always stream
.
-- Standard FROM clause
SELECT * FROM stream
-- FROM clause is always required
SELECT deviceId FROM stream
2
3
4
5
# WHERE Clause
WHERE clause filters data before window processing and aggregation.
# Basic Syntax
SELECT deviceId, temperature
FROM stream
WHERE temperature > 25 AND humidity < 80
2
3
# Supported Conditions
# 1. Comparison Operators
-- Numeric comparison
SELECT * FROM stream WHERE temperature > 25
SELECT * FROM stream WHERE humidity <= 60
SELECT * FROM stream WHERE pressure != 1013.25
-- String comparison
SELECT * FROM stream WHERE deviceType = 'sensor'
SELECT * FROM stream WHERE location LIKE 'building_%'
-- NULL check
SELECT * FROM stream WHERE temperature IS NOT NULL
SELECT * FROM stream WHERE humidity IS NULL
2
3
4
5
6
7
8
9
10
11
12
# 2. Logical Operators
-- AND, OR, NOT
SELECT * FROM stream
WHERE temperature > 25 AND humidity < 80
SELECT * FROM stream
WHERE deviceType = 'sensor' OR deviceType = 'actuator'
SELECT * FROM stream
WHERE NOT (temperature < 0)
2
3
4
5
6
7
8
9
# 3. Range Conditions
-- BETWEEN...AND
SELECT * FROM stream
WHERE temperature BETWEEN 20 AND 30
-- IN operator
SELECT * FROM stream
WHERE deviceType IN ('sensor', 'thermostat', 'humidity')
2
3
4
5
6
7
# 4. String Pattern Matching
-- LIKE pattern matching
SELECT * FROM stream WHERE deviceId LIKE 'sensor_%'
SELECT * FROM stream WHERE location LIKE '%building%'
-- NOT LIKE
SELECT * FROM stream WHERE deviceId NOT LIKE 'test_%'
2
3
4
5
6
# 5. Complex Expressions
-- Nested field filtering
SELECT * FROM stream
WHERE device.info.status = 'active'
AND sensor.temperature > 25
-- Mathematical expressions
SELECT * FROM stream
WHERE ABS(temperature - 25) > 5
2
3
4
5
6
7
8
# GROUP BY Clause
GROUP BY clause groups data for aggregation operations and defines window types.
# Window Functions
# 1. Tumbling Window
-- Basic tumbling window
SELECT AVG(temperature)
FROM stream
GROUP BY TumblingWindow('5m')
-- With grouping
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('5m')
2
3
4
5
6
7
8
9
# 2. Sliding Window
-- Basic sliding window
SELECT AVG(temperature)
FROM stream
GROUP BY SlidingWindow('10m', '2m')
-- With grouping
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, SlidingWindow('10m', '2m')
2
3
4
5
6
7
8
9
# 3. Counting Window
-- Basic counting window
SELECT AVG(temperature)
FROM stream
GROUP BY CountingWindow(100)
-- With grouping
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, CountingWindow(100)
2
3
4
5
6
7
8
9
# 4. Session Window
-- Basic session window
SELECT AVG(temperature)
FROM stream
GROUP BY SessionWindow('5m')
-- With grouping
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, SessionWindow('5m')
2
3
4
5
6
7
8
9
# Grouping Fields
-- Single field grouping
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
-- Multiple field grouping
SELECT deviceId, location, AVG(temperature)
FROM stream
GROUP BY deviceId, location, TumblingWindow('1m')
2
3
4
5
6
7
8
9
# HAVING Clause
HAVING clause filters results after aggregation calculations.
# Basic Syntax
SELECT deviceId, AVG(temperature) as avg_temp
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
HAVING AVG(temperature) > 25
2
3
4
# Usage Examples
# 1. Aggregate Value Filtering
-- Filter by average temperature
SELECT deviceId, AVG(temperature) as avg_temp
FROM stream
GROUP BY deviceId, TumblingWindow('5m')
HAVING avg_temp > 30
-- Filter by data count
SELECT deviceId, COUNT(*) as data_count
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
HAVING data_count > 100
2
3
4
5
6
7
8
9
10
11
# 2. Complex Conditions
-- Multiple aggregate conditions
SELECT deviceId,
AVG(temperature) as avg_temp,
MAX(temperature) as max_temp,
MIN(temperature) as min_temp
FROM stream
GROUP BY deviceId, TumblingWindow('10m')
HAVING avg_temp > 25 AND max_temp - min_temp > 10
2
3
4
5
6
7
8
# ORDER BY Clause
ORDER BY clause sorts query results. In stream processing, sorting is only supported within the same window.
# Basic Syntax
SELECT deviceId, temperature
FROM stream
ORDER BY temperature DESC
LIMIT 10
2
3
4
# Usage Examples
# 1. Single Field Sorting
-- Sort by temperature descending
SELECT deviceId, temperature
FROM stream
ORDER BY temperature DESC
-- Sort by timestamp ascending
SELECT deviceId, temperature, timestamp
FROM stream
ORDER BY timestamp ASC
2
3
4
5
6
7
8
9
# 2. Multi-field Sorting
-- Sort by device first, then by temperature
SELECT deviceId, temperature
FROM stream
ORDER BY deviceId ASC, temperature DESC
2
3
4
# LIMIT Clause
LIMIT clause restricts the number of returned results.
# Basic Syntax
SELECT deviceId, temperature
FROM stream
LIMIT 10
2
3
# Usage Examples
# 1. Limit Result Count
-- Return top 5 results
SELECT deviceId, temperature
FROM stream
ORDER BY temperature DESC
LIMIT 5
-- Return first 100 records
SELECT * FROM stream
LIMIT 100
2
3
4
5
6
7
8
9
# 2. Combined with WHERE
-- Limit filtered results
SELECT deviceId, temperature
FROM stream
WHERE temperature > 30
LIMIT 20
2
3
4
5
# WITH Clause
WITH clause configures query options and parameters.
# Basic Syntax
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMESTAMP='event_time', TIMEUNIT='ms')
2
3
4
# Configuration Options
# 1. Time Configuration
-- Specify event time field
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMESTAMP='event_time')
-- Specify time unit
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMEUNIT='ms')
2
3
4
5
6
7
8
9
10
11
# 2. Performance Configuration
-- Configure buffer size
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (BUFFER_SIZE=1000)
-- Configure timeout
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMEOUT='30s')
2
3
4
5
6
7
8
9
10
11
# 3. Advanced Options
-- Multiple options
SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMESTAMP='event_time', TIMEUNIT='ms', BUFFER_SIZE=1000)
2
3
4
5
# Complete Query Examples
# Basic Queries
# 1. Simple Filtering
SELECT deviceId, temperature, humidity
FROM stream
WHERE temperature > 25 AND humidity < 80
2
3
# 2. Aggregation with Window
SELECT deviceId,
AVG(temperature) as avg_temp,
MAX(temperature) as max_temp,
COUNT(*) as data_count
FROM stream
GROUP BY deviceId, TumblingWindow('5m')
2
3
4
5
6
# 3. Complex Aggregation
SELECT deviceId, location,
AVG(temperature) as avg_temp,
STDDEV(temperature) as temp_std,
PERCENTILE(temperature, 0.95) as temp_95p
FROM stream
GROUP BY deviceId, location, SlidingWindow('10m', '2m')
HAVING avg_temp > 25
ORDER BY avg_temp DESC
LIMIT 10
2
3
4
5
6
7
8
9
# Advanced Queries
# 1. Nested Field Processing
SELECT device.info.name as device_name,
device.location.building as building,
AVG(sensor.temperature.value) as avg_temp,
MAX(sensor.humidity.value) as max_humidity
FROM stream
WHERE device.info.status = 'active'
GROUP BY device.info.name, device.location.building, TumblingWindow('1m')
2
3
4
5
6
7
# 2. Conditional Aggregation
SELECT deviceId,
COUNT(CASE WHEN temperature > 30 THEN 1 END) as high_temp_count,
COUNT(CASE WHEN temperature BETWEEN 20 AND 30 THEN 1 END) as normal_temp_count,
COUNT(CASE WHEN temperature < 20 THEN 1 END) as low_temp_count
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
2
3
4
5
6
# 3. Time-based Analysis
SELECT deviceId,
DATE_FORMAT(window_start(), 'HH:mm') as time_slot,
AVG(temperature) as avg_temp,
COUNT(*) as data_count
FROM stream
GROUP BY deviceId, TumblingWindow('1h')
ORDER BY time_slot ASC
2
3
4
5
6
7
# Error Handling
# Common Error Types
# 1. Syntax Errors
-- Error: Missing FROM clause
SELECT deviceId, temperature
-- Error: Invalid window function
SELECT deviceId FROM stream GROUP BY InvalidWindow('5m')
2
3
4
5
# 2. Type Errors
-- Error: Type mismatch in WHERE clause
SELECT * FROM stream WHERE temperature = 'invalid'
-- Error: Invalid aggregation
SELECT deviceId, temperature FROM stream GROUP BY deviceId
2
3
4
5
# 3. Configuration Errors
-- Error: Invalid option
SELECT * FROM stream WITH (INVALID_OPTION='value')
2
# Best Practices
- Always test queries with sample data
- Use proper data types for comparisons
- Validate window sizes and time units
- Check for NULL values in critical fields
- Use meaningful aliases for complex expressions