timechart¶
Type of operation: Aggregate
Description¶
Bin (in time) and aggregate point or interval table columns through time, based on (optional) grouping columns.
An optional window frame can be specified to compute sliding window aggregation.
When options(bins: <N>) is specified, Observe will pick a user friendly bin size that produces at most N bins across the query window. And when it is set to 1, Observe will produce one single time bin that matches the query window exactly. This option is not accelerable as it is query window dependent.
When a bin_duration is specified, the bins are anchored from Unix epoch. With options(offset: <duration>), the bins are offset from the epoch by duration.
If groupby is not specified, the default grouping will be used. The default grouping for timechart is the set of primary key columns.
Usage¶
timechart [ options ], [ bin_duration ], [ frame ], [ groupby_1, groupby_2, ... ], groupOrAggregateFunction_1, groupOrAggregateFunction_2, ...
Argument |
Type |
Optional |
Repeatable |
Restrictions |
|---|---|---|---|---|
options |
options |
yes |
no |
constant |
bin_duration |
duration |
yes |
no |
none |
frame |
frame |
yes |
no |
constant |
groupby |
storable |
yes |
yes |
column |
groupOrAggregateFunction |
expression |
no |
yes |
none |
Options¶
Option |
Type |
Meaning |
|---|---|---|
bins |
int64 |
Sets the maximum number of bins to produce (not accelerable) |
empty_bins |
bool |
Generate output even for bins in the time window that have no data (not accelerable) |
offset |
duration |
Offset the bin’s start and end times by this amount from Unix epoch. This option can be used only when bin_duration is specified |
Accelerable¶
timechart is sometimes accelerable, depending on options used. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.
Examples¶
timechart 1h, count: count(1), group_by(server_name)
Group input point table by server name, calculating a count of rows through time per server name per hour, returning a dataset with the 5 columns ‘valid_from’, ‘valid_to’, ‘bucket’, ‘server_name’, and ‘Count’.
timechart 1h, frame(back:24h), count: count(1), group_by(server_name)
Group input point table by server name, calculating a moving count of rows through time per server name per hour, with each count covering the 24 hour window ending at the hour.
timechart options(empty_bins:true), 1h, count: count(1), group_by(server_name)
Similar to the first example, but generate a row with NULL value for each time bin in the query window with no matching input rows. Because of empty_bins, the query may run slowly, especially if the input data points are sparse.
timechart count: count(1), group_by(server_name)
Group the input point table by server name, calculating a count of rows through time per server name per time bin. The time bin size will be determined dynamically to make the chart human-readable. It’s determined based on the query window and a number of other parameters such as chart resolution. This formulation is not accelerable.
timechart options(bins: 100), memory_used: avg(memory_used), group_by(server_name)
Group input table by server name and calculate the average memory usage across time.
The time bin size will be determined dynamically to make the chart human-readable, and it will produce no more than 100 points for each server in the query window.
For example:
when query window is 1 hour, 1 minute bins will be produced
when query window is 4 hours, 5 minutes bins will be produced
when query window is 1 day, 30 minutes bins will be produced
This formulation is not accelerable.
timechart options(bins: 1), count: count(1), group_by(server_name)
Group input point table by server name, calculating the number of rows for each server during the query window.
This formulation is not accelerable.
Aliases¶
bucketize