1. Aggregated tables#
1.1. Synopsis#
CREATE AGGREGATED TABLE [ IF NOT EXISTS ] <table_name> (
<name> <type> [, ... ]
)
[ SAMPLE_SIZE [ = ] <positive_integer> ]
[ WATERMARK [ = ] <duration> ]
[ WITH TAGS = [ 'tag_name' [, ... ] ] ]
[ TTL [ = ] <duration> ]
{
TUMBLINGWINDOW (
[ PARTITION BY <column_name> [, ... ], ] { <duration> | <row_count> }
)
| HOPPINGWINDOW (
[ PARTITION BY <column_name> [, ... ], ]
{ <duration> | <row_count> },
{ <duration> | <row_count> }
)
| INFINITEWINDOW ( [ PARTITION BY <column_name> [, ... ] ] )
}
AS
SELECT $timestamp,
{ <column_name> | <aggregation> OVER $window AS <name> } [, ... ]
1.2. Definition#
An aggregated table stores calculated results instead of raw rows. QuasarDB calculates each result for a specified window. For example, it can store a count, sum, minimum, maximum, or average.
Use an aggregated table when the aggregation rules are stable. This method can reduce storage use and query time. It is suitable for monitoring systems and resource-limited systems.
Do not use an aggregated table for exploratory analysis. Use a standard table if the query or aggregation rules change frequently.
1.3. WATERMARK#
Use WATERMARK to accept data that arrives out of timestamp order.
The duration sets the maximum permitted delay from the latest timestamp.
QuasarDB ignores data that is older than this limit.
1.4. Row-count based windows#
Use an integer instead of a duration to create a row-count window.
For example, TUMBLINGWINDOW(1000) aggregates each group of 1000 rows.
Row-count windows are useful for high-frequency data that arrives at irregular intervals.
1.5. Parameters#
table_nameThe name of the aggregated table to create.
nameThe name of a source or result column. Each column name must be unique in the table.
typeThe data type of a source column.
durationA time interval. For more information, refer to durations.
aggregationA function that calculates a result for each window. Examples include
AVG,COUNT,MAX,MIN, andSUM.SAMPLE_SIZESets the sample size that QuasarDB uses for the aggregated table. If you omit this option, QuasarDB uses the default sample size.
WITH TAGSAdds one or more tags when QuasarDB creates the table. Put each tag in single quotation marks.
PARTITION BYCreates an independent window for each unique combination of the specified column values.
INFINITEWINDOWUses one window for all input rows. You can use
PARTITION BYto create one infinite window for each partition.TUMBLINGWINDOWCreates consecutive windows that do not overlap.
TUMBLINGWINDOW(5min)creates one window for each five-minute interval.HOPPINGWINDOWCreates windows that can overlap. The first value sets the window size. The second value sets the hop size.
HOPPINGWINDOW(5min, 1min)creates five-minute windows at one-minute intervals.TTLSets how long QuasarDB keeps the aggregated data.
TTL 1dkeeps the data for one day.WATERMARKSets the maximum permitted delay for out-of-order data.
WATERMARK 10minaccepts data that is not more than 10 minutes behind the latest timestamp.
1.6. Use OVER in aggregated tables#
Use OVER $window to calculate an aggregation for each window.
For more information, refer to Differentiating “OVER” for Window Functions and Aggregated Tables.
1.7. Examples#
Create five-minute averages in table t.
Keep the results for one day.
CREATE AGGREGATED TABLE t(col DOUBLE) TTL 1d TUMBLINGWINDOW(5min) AS
SELECT $timestamp, avg(col) OVER $window AS avg
Create five-minute averages at one-minute intervals.
CREATE AGGREGATED TABLE t (col DOUBLE) HOPPINGWINDOW(5min, 1min) AS
SELECT $timestamp, avg(col) OVER $window AS avg
1.7.1. Tumbling window with sum#
Create an aggregated table with a one-hour tumbling window. Calculate the sum of the values.
CREATE AGGREGATED TABLE simple_sum_table (col DOUBLE) TUMBLINGWINDOW(1 hour)
AS SELECT $timestamp, sum(col) OVER $window AS sum
Insert some sample data into the aggregated table.
INSERT INTO simple_sum_table ($timestamp, col) VALUES
(2023-01-01T00:00:00, 10),
(2023-01-01T00:30:00, 20),
(2023-01-01T01:00:00, 15)
Query data from the aggregated table.
SELECT $timestamp, sum FROM simple_sum_table
$timestamp sum
------------------------------------------------
2023-01-01T00:00:00.000000000Z 30
2023-01-01T01:00:00.000000000Z 15
Returned 2 rows in 2,026 us
Scanned 2 points in 2,026 us (986 rows/sec)
1.7.2. Hopping window with multiple aggregations#
Create an aggregated table with a 15-minute window and a five-minute hop. Calculate multiple aggregations.
CREATE AGGREGATED TABLE complex_aggregation_table (col DOUBLE) HOPPINGWINDOW(15min, 5min)
AS SELECT $timestamp,
count(col) OVER $window AS count_values,
sum(col) OVER $window AS total_sum,
avg(col) OVER $window AS average_value,
max(col) OVER $window AS max_value,
min(col) OVER $window AS min_value
Insert some sample data into the aggregated table.
INSERT INTO complex_aggregation_table ($timestamp, col) VALUES
(2023-01-01T00:00:00, 10),
(2023-01-01T00:05:00, 15),
(2023-01-01T00:10:00, 5),
(2023-01-01T00:15:00, 25)
Query data from the aggregated table.
SELECT $timestamp, count_values, total_sum, average_value, max_value, min_value FROM complex_aggregation_table
$timestamp count_values total_sum average_value max_value min_value
------------------------------------------------------------------------------------------------------------------
2022-12-31T23:50:00.000000000Z 1 10 10 10 10
2022-12-31T23:55:00.000000000Z 2 25 12.5 15 10
2023-01-01T00:00:00.000000000Z 3 30 10 15 5
2023-01-01T00:05:00.000000000Z 3 45 15 25 5
2023-01-01T00:10:00.000000000Z 2 30 15 25 5
2023-01-01T00:15:00.000000000Z 1 25 25 25 25
Returned 6 rows in 2,192 us
Scanned 30 points in 2,192 us (13,683 rows/sec)
1.7.3. Watermark#
Create an aggregated table with a 10-minute watermark. Use a one-hour tumbling window.
CREATE AGGREGATED TABLE table_example_watermark (col DOUBLE) WATERMARK 10min TUMBLINGWINDOW(1hour)
AS SELECT $timestamp,
count(col) OVER $window AS count,
sum(col) OVER $window AS sum
Insert data points into the aggregated table.
INSERT INTO table_example_watermark ($timestamp, col) VALUES (2023-01-01T00:12:00, 2);
INSERT INTO table_example_watermark ($timestamp, col) VALUES
(2023-01-01T00:01:00, 1),
(2023-01-01T00:04:00, 4);
Query data from the aggregated table.
SELECT $timestamp, count, sum FROM table_example_watermark
$timestamp count sum
--------------------------------------------------------
2023-01-01T00:00:00.000000000Z 2 6
Returned 1 row in 14,691 us
Scanned 2 points in 14,691 us (136 rows/sec)
1.7.4. Row-based window#
Create an aggregated table with a window size of three rows and a hop size of two rows.
CREATE AGGREGATED TABLE table_row_based_example (col DOUBLE) HOPPINGWINDOW(3, 2)
AS SELECT $timestamp,
count(col) OVER $window AS count,
sum(col) OVER $window AS sum,
min(col) OVER $window AS min,
max(col) OVER $window AS max,
avg(col) OVER $window AS avg
Each complete window contains three rows. Each new window starts two rows after the preceding window. Thus, the windows overlap by one row.
Insert data into table_row_based_example.
INSERT INTO table_row_based_example ($timestamp, col) VALUES
(2023-01-01T00:01:00, 1),
(2023-01-01T00:12:00, 2),
(2023-01-01T00:40:00, 3),
(2023-01-01T01:02:00, 4),
(2023-01-01T01:10:00, 5)
Query the aggregated results.
SELECT $timestamp, count, sum, min, max, avg FROM table_row_based_example;
$timestamp count sum min max avg
-----------------------------------------------------------------------------------------------------------
2023-01-01T00:01:00.000000000Z 3 6 1 3 2
2023-01-01T00:40:00.000000000Z 3 12 3 5 4
2023-01-01T01:10:00.000000000Z 1 5 5 5 5
Returned 3 rows in 2,408 us
Scanned 15 points in 2,408 us (6,226 rows/sec)
The first result contains rows 1 through 3. The second result contains rows 3 through 5. The final result contains the remaining row.