RuleGo
目录

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 [, ...])]
1
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)]
1
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
1
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
1
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
1
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
1
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
1
2
3
4
5

# DISTINCT Deduplication

-- Deduplication query
SELECT DISTINCT deviceType FROM stream

-- Multi-field deduplication
SELECT DISTINCT deviceId, location FROM stream
1
2
3
4
5

# Basic Syntax

SELECT column1, column2, ...
SELECT expression AS alias
SELECT *
SELECT DISTINCT column1
1
2
3
4

# Field Selection

# 1. Direct Field Reference

-- Select specific fields
SELECT deviceId, temperature, humidity FROM stream

-- Select all fields 
SELECT * FROM stream
1
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
1
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
1
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
1
2
3
4
5
6
7
8

# DISTINCT

-- Deduplication (used in window aggregation)
SELECT DISTINCT deviceId, location 
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
1
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
1
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
1
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
1
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)
1
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')
1
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_%'
1
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
1
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')
1
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')
1
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)
1
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')
1
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')
1
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
1
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
1
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
1
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
1
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
1
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
1
2
3
4

# LIMIT Clause

LIMIT clause restricts the number of returned results.

# Basic Syntax

SELECT deviceId, temperature
FROM stream
LIMIT 10
1
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
1
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
1
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')
1
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')
1
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')
1
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)
1
2
3
4
5

# Complete Query Examples

# Basic Queries

# 1. Simple Filtering

SELECT deviceId, temperature, humidity
FROM stream
WHERE temperature > 25 AND humidity < 80
1
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')
1
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
1
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')
1
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')
1
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
1
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')
1
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
1
2
3
4
5

# 3. Configuration Errors

-- Error: Invalid option
SELECT * FROM stream WITH (INVALID_OPTION='value')
1
2

# Best Practices

  1. Always test queries with sample data
  2. Use proper data types for comparisons
  3. Validate window sizes and time units
  4. Check for NULL values in critical fields
  5. Use meaningful aliases for complex expressions
Edit this page on GitHub (opens new window)
Last Updated: 2025/08/07, 08:57:47
Core Concepts
API Reference

← API Reference→

Theme by Vdoing | Copyright © 2023-2025 RuleGo Team | Apache 2.0 License