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

Alias scope: a column alias only names an output column; it cannot be referenced in a WHERE / GROUP BY / HAVING clause (consistent with eKuiper: column_alias cannot be used in a WHERE, GROUP BY, or HAVING clause). GROUP BY must use the original column name or expression.

-- ❌ the alias `dev` cannot be used in GROUP BY
SELECT deviceId AS dev, COUNT(*) FROM stream GROUP BY dev

-- ✅ use the original column name in GROUP BY; the alias only names the output column
SELECT deviceId AS dev, COUNT(*) FROM stream GROUP BY deviceId

1
2
3
4
5

# 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.

Note: GROUP BY cannot reference SELECT aliases — use the original column name or expression (see "Aliases (AS)" above).

# 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

The HAVING clause filters aggregation results (window/group output) — like WHERE, but applied after GROUP BY.

HAVING references SELECT aliases

StreamSQL's HAVING references the alias from SELECT; you cannot restate the aggregate function. The restated form (e.g. HAVING AVG(temperature) > 25) is not parsed and silently produces no output.

-- ✅ Correct: HAVING references the alias avg_temp
SELECT deviceId, AVG(temperature) AS avg_temp
FROM stream
GROUP BY deviceId, TumblingWindow('5s')
HAVING avg_temp > 25

-- ❌ Wrong: restating the aggregate function (does not work)
HAVING AVG(temperature) > 25

1
2
3
4
5
6
7
8

# Usage Examples

-- Filter by count
SELECT location, COUNT(*) AS event_count
FROM stream
GROUP BY location, TumblingWindow('1m')
HAVING event_count >= 10

-- Multiple aggregate conditions (all use aliases)
SELECT deviceId, AVG(temperature) AS avg_temp, MAX(humidity) AS max_hum
FROM stream
GROUP BY deviceId, TumblingWindow('5s')
HAVING avg_temp > 20 AND max_hum < 80

-- Compound conditions
SELECT deviceType, COUNT(*) AS cnt, AVG(value) AS avg_value, MAX(value) AS max_value
FROM stream
GROUP BY deviceType, TumblingWindow('1m')
HAVING cnt > 5 AND (avg_value > 100 OR max_value > 500)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# Sustained Over-Threshold Detection (Typical IoT Scenario)

"Sustained over-threshold for N seconds" uses windowed aggregation + HAVING: the window aggregates all events in the period (including dips), and HAVING uses min > threshold to drop windows where a dip occurred.

-- Alert only when current stays above 200A for 10 seconds straight
SELECT min(concurrency) AS mn, count(*) AS c
FROM stream
GROUP BY SlidingWindow(ss, 10)
HAVING mn > 200

1
2
3
4
5

# WHERE vs HAVING

-- WHERE filters raw data (before aggregation); HAVING filters aggregation results (after, referencing aliases)
SELECT deviceId, AVG(temperature) AS avg_temp
FROM stream
WHERE temperature > 0              -- filter raw data before aggregation
GROUP BY deviceId, TumblingWindow('5s')
HAVING avg_temp > 25               -- filter after aggregation

-- ❌ Wrong: raw fields belong in WHERE, not HAVING
HAVING deviceId = 'sensor001'

1
2
3
4
5
6
7
8
9

# 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

The WITH clause defines query options, mainly to enable event-time windows by specifying the timestamp field and (for integer timestamps) the time unit.

# Basic Syntax

SELECT columns FROM stream
[WHERE condition]
[GROUP BY grouping]
WITH (option = value [, ...])

1
2
3
4

# Core Effect

No WITH (TIMESTAMP=...): processing time. Windows use arrival time; record time fields are ignored.
With WITH (TIMESTAMP='field'): event time. Windows use the given field; late arrivals are counted into the correct window.

# Options

TIMESTAMP — event time field
Syntax:

WITH (TIMESTAMP='field_name')

Examples:

SELECT COUNT(*) FROM stream
GROUP BY TumblingWindow('5m')
WITH (TIMESTAMP='order_time')

SELECT deviceId, AVG(temperature)
FROM stream
GROUP BY deviceId, TumblingWindow('1m')
WITH (TIMESTAMP='event_time')

1
2
3
4
5
6
7
8

Notes: If the field is missing/empty, current time is used as a fallback.

TIMEUNIT — unit for integer timestamps
Syntax:

WITH (TIMEUNIT='ns'|'ms'|'ss'|'mi'|'hh'|'dd')

Examples:

WITH (TIMESTAMP='event_time', TIMEUNIT='ms')
WITH (TIMESTAMP='timestamp', TIMEUNIT='ss')

1
2

Notes: Not needed for time.Time; required for integer (int64). Default: ms.

MAXOUTOFORDERNESS — max out-of-order tolerance
Purpose: watermark = max(event_time) - MaxOutOfOrderness.
Syntax:

WITH (MAXOUTOFORDERNESS='500ms'|'5s'|'2m'|'1h')

Example:

WITH (TIMESTAMP='event_time', MAXOUTOFORDERNESS='5s')

Notes: Event-time only. Larger values delay window triggering but tolerate disorder.

ALLOWEDLATENESS — allowed lateness after trigger
Purpose: keep window open to accept late data until watermark >= window_end + AllowedLateness.
Syntax:

WITH (ALLOWEDLATENESS='2s'|'1m'|...)

Examples:

WITH (TIMESTAMP='event_time', ALLOWEDLATENESS='2s')
WITH (TIMESTAMP='timestamp', ALLOWEDLATENESS='1m')

1
2

Notes: Event-time only. Default 0 closes immediately.

IDLETIMEOUT — idle source timeout Purpose: when the source is idle longer than this, advance watermark by processing time so windows can close. Syntax:

WITH (IDLETIMEOUT='5s'|'2m'|...)

Examples:

WITH (TIMESTAMP='event_time', IDLETIMEOUT='5s')
WITH (TIMESTAMP='timestamp', IDLETIMEOUT='2m')

1
2

Notes: Event-time only. Default 0 (disabled).

STATETTL — counting-window state TTL Purpose: set a time-to-live for CountingWindow per-group state so inactive "dead keys" are reaped periodically, preventing unbounded memory growth under high-cardinality GROUP BY. Syntax:

WITH (STATETTL='24h'|'10m'|'30s'|...)

Examples:

-- Counting window: fire every 10 rows, reap keys idle for 24h
SELECT deviceId, COUNT(*) AS cnt
FROM stream
GROUP BY deviceId, CountingWindow(10)
WITH (STATETTL='24h')

-- Shorter TTL for short-lived keys
SELECT userId, AVG(score) AS avg_score
FROM stream
GROUP BY userId, CountingWindow(100)
WITH (STATETTL='1h')

1
2
3
4
5
6
7
8
9
10
11

Notes: Applies to CountingWindow only (it is count-triggered, so state is never naturally reaped). Default 0 (disabled), Flink-aligned — opt in explicitly. A background scan runs every TTL/2 (min 1s) and drops keys whose last arrival is older than TTL. Keys that already fired are removed from the idle set immediately, so they are never reaped by mistake. Time windows (Tumbling/Sliding/Session) trigger and clean up by time, so they do not need STATETTL.

When to configure:

Scenario	STATETTL needed?
Low-cardinality `GROUP BY` (e.g. a few dozen deviceId)	No
High-cardinality `GROUP BY` (huge, ever-growing userId/deviceId)	Recommended
Sparse keys (never reach the count threshold before going quiet)	Recommended
Time windows (Tumbling/Sliding/Session)	N/A (CountingWindow only)

Note

Enabling STATETTL discards buffered rows for keys that never reached the count threshold. If every row must be aggregated and emitted, make sure each key receives enough rows within the TTL, or switch to a time-based window.

# WITH Options Summary

Option	Type	Description	Default	Required
`TIMESTAMP`	string	Event-time field name	none (processing time)	No
`TIMEUNIT`	string	Unit for integer timestamps	`'ms'`	No
`MAXOUTOFORDERNESS`	string	Max out-of-orderness (event-time windows)	`'0s'`	No
`ALLOWEDLATENESS`	string	Allowed lateness (event-time windows)	`'0s'`	No
`IDLETIMEOUT`	string	Idle source timeout (event-time windows)	`'0s'` (disabled)	No
`STATETTL`	string	Counting-window state TTL (CountingWindow only)	`'0s'` (disabled)	No

# Examples

Event time:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM stream
GROUP BY TumblingWindow('5m')
WITH (TIMESTAMP='order_time')

1
2
3
4
5
6

Processing time:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM stream
GROUP BY TumblingWindow('5m')

1
2
3
4

# Watermark Workflow

On arrival, update watermark: max(event_time) - MaxOutOfOrderness
Trigger when watermark >= window_end
Keep open until watermark >= window_end + AllowedLateness
If idle for IDLETIMEOUT, use currentProcessingTime - MaxOutOfOrderness to advance watermark

# Built-in Functions

# Aggregate Functions

Function	Description	Example
`COUNT(*)`	Count	`COUNT(*)`
`SUM(expr)`	Sum	`SUM(temperature)`
`AVG(expr)`	Average	`AVG(temperature)`
`MIN(expr)`	Minimum	`MIN(temperature)`
`MAX(expr)`	Maximum	`MAX(temperature)`
`STDDEV(expr)`	Standard deviation	`STDDEV(temperature)`
`MEDIAN(expr)`	Median	`MEDIAN(temperature)`

# Mathematical Functions

Function	Description	Example
`ABS(x)`	Absolute value	`ABS(temperature)`
`ROUND(x, d)`	Round	`ROUND(temperature, 2)`
`FLOOR(x)`	Floor	`FLOOR(temperature)`
`CEIL(x)`	Ceiling	`CEIL(temperature)`
`SQRT(x)`	Square root	`SQRT(area)`
`POWER(x, y)`	Power	`POWER(distance, 2)`

# String Functions

Function	Description	Example
`CONCAT(s1, s2, ...)`	String concatenation	`CONCAT(first, '_', last)`
`UPPER(s)`	Uppercase	`UPPER(status)`
`LOWER(s)`	Lowercase	`LOWER(deviceId)`
`LENGTH(s)`	String length	`LENGTH(message)`
`SUBSTRING(s, start, len)`	Substring	`SUBSTRING(deviceId, 1, 5)`
`TRIM(s)`	Trim whitespace	`TRIM(name)`

# Time Functions

Function	Description	Example
`window_start()`	Window start time	`window_start()`
`window_end()`	Window end time	`window_end()`
`NOW()`	Current time	`NOW()`

# Type Conversion Functions

Function	Description	Example
`CAST(expr AS type)`	Type conversion	`CAST(temperature AS STRING)`

# Analytic Functions

Analytic functions perform cross-event state computation on a continuous event stream (change detection, context backtracking, accumulation); each event is evaluated on arrival and they are usable in SELECT and WHERE. See Analytic Functions.

Function	Description	Example
`lag(field [,offset [,default [,ignoreNull]]])`	The value offset rows before	`lag(temperature) OVER (PARTITION BY deviceId)`
`latest(field [,default])`	Latest non-null value	`latest(temperature)`
`had_changed(ignoreNull, field...)`	Whether it changed (boolean)	`had_changed(true, status)`
`changed_col(ignoreNull, field)`	The changed column value (scalar)	`changed_col(true, temperature)`
`changed_cols(prefix, ignoreNull, field...)`	Multiple changed column values (dynamic columns)	`changed_cols("c_", true, temperature, humidity)`
`acc_sum/acc_max/acc_min/acc_count/acc_avg(field)`	Lifecycle accumulation	`acc_sum(power)`

Analytic functions take an OVER ([PARTITION BY ...] [WHEN ...]) clause to control partitioning and conditional state. They can also be used inside windowed queries (see below), where they evaluate on window output rows with state kept across windows. They cannot appear in HAVING.

-- CDC change detection: current is over threshold but the previous value was not
SELECT current, deviceId, ts FROM stream
WHERE current > 300 AND lag(current) OVER (PARTITION BY deviceId) < 300

1
2
3

Conditional accumulation (start/reset points)

acc_* accepts two extra boolean expressions: a start point (begin accumulating once true, then continue) and a reset point (reset to zero and stop until the start point fires again). acc_count(a, a > 1, a < 0) over a: 1,2,1,3,-1,1 yields 0,1,2,3,0,0.

Analytic functions inside windows

In a windowed query, analytic functions evaluate on window output rows with state preserved across windows — useful for change detection / accumulation on aggregates. PARTITION defaults to the GROUP BY keys. Arguments may be aggregate functions (inline form):

-- Emit windows whose average temperature changed vs the previous window
SELECT changed_cols("t", true, avg(temperature)) FROM stream GROUP BY CountingWindow(2)
-- Cross-window running sum of the per-window average
SELECT acc_sum(avg(temperature)) AS total FROM stream GROUP BY CountingWindow(2)

1
2
3
4

The alias form is equivalent: SELECT avg(temperature) AS a, changed_col(true, a) AS chg ... GROUP BY window. Arguments must reference window-output fields (aggregates or GROUP BY keys), not raw columns.

acc_* vs SUM

SUM is a windowed aggregate: it sums the rows inside each GROUP BY window and resets when the window closes. acc_sum (and acc_count/acc_max/acc_min/acc_avg) accumulates across the whole rule lifetime and never resets — it runs on the per-event state machine (works with EmitSync). Mainstream engines (Flink/Spark) express the latter as SUM(v) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); because streamsql's OVER intentionally does not support ORDER BY/frames (edge simplification), a separate acc_* function name is used instead.

PARTITION cardinality cap

Each analytic field caps the number of PARTITION states (default 10000); above the cap the least-recently-used partition is evicted. An evicted partition resets on its next event: lag returns nil, had_changed/changed_col treat it as the first row, and acc_* accumulators silently restart from 0. The default covers a typical single edge node (hundreds to low-thousands of devices; a few MB at most). Raise it only when the partition key is genuinely high-cardinality (e.g. tens of thousands of devices on one node) and memory allows:

ssql := streamsql.New(streamsql.WithAnalyticMaxPartitions(50000))

# 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

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

Edit this page on GitHub

Last Updated: 2026/07/11, 16:40:34

← Core Concepts API Reference→