12. Select#
12.1. Synopsis#
SELECT { <expression> | * } [, ... ]
FROM { <table_name> | <find_expression> } [, ... ]
[ <asof_type> ASOF JOIN { <table_name> | <find_expression> } [, ... ] [ <interpolation> ] [ <lookback> ] [ PREWHERE <condition> ]]
[ ASOF JOIN <range_generator> [ <interpolation> ] [ PREWHERE <condition> ] ]
[ IN { RANGE <range_spec> | '[' RANGE <range_spec> [, ...] ']' }
[ WITH MONTHS IN ( <month>, <month> ) ]
[ WITH DAYS IN ( <day>, <day> ) ]
[ WITH TIME IN ( <time>, <time> ) ] ]
[ WHERE <condition> ]
[ RESTRICT TO <restriction> ]
[ GROUP BY <group> [, ... ] ]
[ PIVOT <column> ]
[ HAVING <condition> ]
[ ORDER BY { <expression> [ { ASC | DESC } ] } [, ... ] ]
[ LIMIT <limit> ]
[ OFFSET <offset> ]
asof_type ::=
FULL | LEFT | RIGHT
expression ::=
<column_name>
| <function> ( <column_name> )
| ( <sub_query> )
interpolation ::=
INTERPOLATE (method=<interpolation_method> [, limit_direction=<interpolation_limit_direction>])
interpolation_limit_direction ::=
FORWARD | BACKWARD | BOTH | NONE
interpolation_method ::=
CONSTANT | LINEAR | CUBIC | BARYCENTRIC | LOGARITHMIC
lookback ::=
WITH LOOKBACK <duration>
range_spec ::=
( <timestamp>, <timestamp> )
( <timestamp>, <time_offset> )
range_generator ::=
( <timestamp>, <timestamp>, <time_offset> )
( <timestamp>, <time_offset>, <time_offset> )
restriction ::=
<function> ( <column_name> )
group ::=
<expression>
| <duration>
find_expression ::=
FIND ( { <tag_expression> | NOT <tag_expression> } [ AND ... ] )
tag_expression ::=
TAG = 'tag_name'
sub_query ::=
SELECT <function> ( <column_name> ) FROM <table_name> IN RANGE <sub_range_spec>
sub_range_spec ::=
( <timestamp> | <column_name>, <timestamp> | <column_name> )
( <timestamp> | <column_name>, <time_offset> )
12.2. Description#
SELECT retrieves rows from one or more tables. A SELECT statement can perform calculations on rows prior to returning the result which are performed server-side and distributed over the entire cluster.
12.3. Parameters#
table_nameThe name of the table to retrieve rows from.
find_expressionWhen your tables are tagged, you can use inline key/value lookups to perform your query over multiple tables. To match all tables that have the tag “stocks”, you can use
FIND(tag='stocks' AND type=ts).asof_typeThe type of ASOF JOIN to perform, it can be FULL, LEFT, or RIGHT.
column_nameA column name to read data from. Must be part of the table’s schema or will throw an error otherwise.
functionAn aggregate function to apply over. Can only be used in combination with
GROUP BY. For valid functions, please refer to the function reference.conditionA WHERE or HAVING condition is any expression that evaluates to a boolean. Any row that does not match this predicate will be filtered from the results. For an overview of valid operators, please refer to the comparison operators reference. The WHERE condition is applied before the SELECT, whereas the HAVING condition is applied on the SELECT results.
restrictionA RESTRICT TO restriction selects only one row from each set of rows with duplicated $timestamp values. The restriction function can be one of
first,last,min,max,abs_min,abs_max. For a description of these functions, please refer to the function reference.range_generatorAn expression to generate range intervals.
limitAccepts a non-negative integer to limit the maximum number of rows returned by the query. Typical use cases include pagination and returning the top-X results.
offsetAccepts a non-negative integer to skip rows of the result. Most common use cases include pagination in combination with
limit.timestampAn absolute timestamp. This can be either a date or a date + time. Supports precision for days, seconds or nanoseconds. For more information, please refer to the documentation for timestamps.
time_offsetA relative offset, can only be used in combination with an
absolute_timestamp. For more information, please refer to the documentation for timestamps.monthA month. Can be one of
jan,feb,mar,apr,may,jun,jul,aug,sep,oct,novordec. When defining a subrange usingWITH MONTHS IN, is both left and right inclusive:WITH MONTHS IN (jan, apr)will match January to April.dayA weekday. Can be one of
sun,mon,tue,wed,thu,friorsat. When defining a subrange usingWITH DAYS IN, is both left and right inclusive:WITH DAYS IN (mon, wed)will match Monday to Wednesday.timeTime of day, having a precision of either minutes, seconds or nanoseconds. When defining a subrange using
WITH TIME IN, is left inclusive and right exclusive:WITH TIME IN (09:22:00, 09:22:01)will not match a row whose time is exactly09:22:01AM.durationA time duration to group aggregates by. Valid values are any durations as specified in the documentation for durations.
interpolation
Specifies the mathematical technique used to estimate values between two known data points.
12.4. Examples#
Select all columns from all rows:
SELECT * FROM example
Note
The examples below assume typical open, high, low, close, volume stocks time series.
Get everything between January 1st 2007 and January 1st 2008 (left inclusive) for the time series “stocks.apple”:
SELECT * FROM stocks.apple IN RANGE(2007, 2008)
Get everything between November 3rd 2017, 20:01:22 and December 2nd, 2017, 06:20:10 (left inclusive) for the time series “stocks.apple”:
SELECT * FROM stocks.apple IN RANGE(2017-11-03T20:01:22, 2017-12-02T06:20:10)
Get the first 10 days of 2007 for “stocks.apple”:
SELECT * FROM stocks.apple IN RANGE(2007, +10d)
Get the last second of 2016 for “stocks.apple”:
SELECT * FROM stocks.apple IN RANGE(2017, -1s)
Get the close and open values that are greater than 3 of “stocks.apple” for the first 10 days of 2016:
SELECT close, open FROM stocks.apple IN RANGE(2016, +10d) WHERE (close > 3) AND (open > 3)
Get the last close value for March 28th 2016:
SELECT LAST(close) FROM stocks.apple IN RANGE(2016-03-28, +1d)
12.5. Advanced examples#
Note
The examples below assume typical open, high, low, close, volume stocks time series.
Get the open and close value when volume is greater than 0 for the first 5 days of 2016 and 2017 for “stocks.apple”:
SELECT open, close FROM stocks.apple IN [range(2016, +5d), range(2017, +5d)] WHERE volume > 0
Get the hourly arithmetic mean of volume exchanged for all nasdaq stocks for yesterday:
SELECT arithmetic_mean(volume) FROM find(tag='nasdaq' AND type=ts) IN RANGE(yesterday, +1d) GROUP BY 1hour
Get the sum of volumes for every Friday of January 2008 between 16:00 and 17:00 for “stocks.apple”:
SELECT sum(volume) FROM stocks.apple IN RANGE(2008, +1month) WITH DAYS IN (fri, fri) WITH TIME IN (16:00, 17:00)
Get the daily open, high, low, close, volume for “stocks.apple” for the last 30 days:
SELECT first(open), max(high), min(low), last(close), sum(volume) FROM stocks.apple IN RANGE(today, -30d) GROUP BY 1day
Get the sum of volume and the number of lines for the last hour by 10 seconds group:
SELECT sum(volume), count(volume) FROM stocks.apple IN RANGE(now, -1h) GROUP BY 10s
Get the sum of volumes for “stocks.apple” the year 2008 and 2010, grouped by month (gregorian calendar):
SELECT sum(volume) FROM stocks.apple IN [RANGE(2008, +1y), RANGE(2010, +1y)] GROUP BY 1month
If we assume we have an additional ‘deal_timestamp’ column on our timeseries, we apply a filter on it:
SELECT sum(volume) FROM stocks.apple IN RANGE(now, -1h) WHERE deal_timestamp=datetime(2009-11-23T09:30)
12.6. IN FIND example#
Example: tagging stock tables by exchange:
CREATE TABLE stocks (symbol INT64, open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
timestamp symbol open close high low volume
-----------------------------------------------------------------------------------------------------------------------------
2018-01-01T00:00:00.000000000Z 1 10.240000 10.170000 10.360000 10.070000 1027389
2018-01-01T00:00:00.000000000Z 2 902.750000 909.510000 925.000000 890.120000 8437
2018-01-01T00:00:00.000000000Z 3 45.770000 49.750000 53.000000 42.820000 9820
And with the following int keys (added through the key/value API):
Key
Value
Tags
‘symbol.apple’
1
‘nasdaq’
‘symbol.facebook’
2
‘nasdaq’
‘symbol.tesco’
3
‘lse’
The following query:
select * from stocks where symbol in find(tag='nasdaq')
Will output:
timestamp symbol open close high low volume
-----------------------------------------------------------------------------------------------------------------------------
2018-01-01T00:00:00.000000000Z 1 10.240000 10.170000 10.360000 10.070000 1027389
2018-01-01T00:00:00.000000000Z 2 902.750000 909.510000 925.000000 890.120000 8437
12.7. SUBQUERY example#
A subquery performs an aggregation for each resulting row of the outer query.
Assuming these tables
parent
$timestamp
contract_end
2021-01-01T00:00:00
2021-01-02T00:00:00
2021-01-02T00:00:00
2021-01-04T00:00:00
child
$timestamp
c
2021-01-01T00:00:00
1
2021-01-02T00:00:00
2
2021-01-03T00:00:00
3
2021-01-04T00:00:00
4
2021-01-05T00:00:00
5
The following query:
SELECT $timestamp, contract_end, (SELECT SUM(c) FROM child IN RANGE (parent.$timestamp, parent.contract_end)) AS sum FROM parent;
Will output:
timestamp contract_end sum
-------------------------------------------------------------------------------
2021-01-01T00:00:00.000000000Z 2021-01-02T00:00:00.000000000Z 1
2021-01-02T00:00:00.000000000Z 2021-01-04T00:00:00.000000000Z 5
12.8. ASOF joins#
An ASOF joins several tables based on their timestamps. ASOF joins use the last value for the merge, based in the range of the query. If values exist before the range of the select, they will be ignored.
There are four types of ASOF joins:
Left: The left table will be used as a reference. The timestamps for the right table will be using the last value for each timestamps of all columns of the left table.
Right: The right table will be used as a reference. The timestamps for the left table will be using the last value for each timestamps of all columns of the right table.
Full: Both table will be used as references.
Range: Joins the table on the left against a generated range.
Currently, ASOF joins support joining against exactly one table. However, the number of tables to join is not limited. This means, for example, that if you are doing a LEFT ASOF JOIN, only one table may be specified on the left, but several can be specified on the right.
This is correct:
SELECT * FROM table_left LEFT ASOF JOIN table_right1, table_right2;
This is not supported:
SELECT * FROM table_left1, table_left2 LEFT ASOF JOIN table_right;
It is possible to join the result of a find. However, the result of the FIND must yield exactly one value for the table to join against. The number of results for the tables being joined is not limited.
This is correct if FIND(tag='left_tag') returns exactly one table, and FIND(tag='right_tag') returns at least one table:
SELECT * FROM FIND(tag='left_tag') LEFT ASOF JOIN FIND(tag='right_tag');
12.8.1. Filtering ASOF join results#
The WHERE clause is applied after the ASOF JOIN is executed. What if you want to filter the data before you join the two tables? QuasarDB has a specific PREWHERE clause to solve that problem.
This query will filter out negative values before joining the two tables:
SELECT * FROM table_left LEFT ASOF JOIN table_right PREWHERE table_left.value >= 0
This query will filter out negative values after joining the two tables:
SELECT * FROM table_left LEFT ASOF JOIN table_right WHERE table_left.value >= 0
This query will filter out negative value before joining the two tables and filter out values greater than 10 after joining the two tables:
SELECT * FROM table_left LEFT ASOF JOIN table_right PREWHERE table_left.value >= 0 WHERE table_left.value < 10
12.8.2. Examples#
Assuming these tables
table_left
Timestamp
Pressure
2019-11-23T13:02:01
100
2019-11-23T13:03:03
110
2019-11-23T13:03:59
105
2019-11-23T13:05:00
115
table_right
Timestamp
Temperature
2019-11-23T13:01:58
56
2019-11-23T13:03:03
59
2019-11-23T13:04:02
58
2019-11-23T13:05:02
56
2019-11-23T13:05:22
57
Here are the results for the three possible ASOF joins:
SELECT $timestamp, pressure, temperature FROM table_left LEFT ASOF JOIN table_right;
Timestamp
Pressure
Temperature
2019-11-23T13:02:01
100
56
2019-11-23T13:03:03
110
59
2019-11-23T13:03:59
105
59
2019-11-23T13:05:00
115
58
SELECT $timestamp, pressure, temperature FROM table_left RIGHT ASOF JOIN table_right;
Timestamp
Pressure
Temperature
2019-11-23T13:01:58
(void)
56
2019-11-23T13:03:03
110
59
2019-11-23T13:04:02
105
58
2019-11-23T13:05:02
115
56
2019-11-23T13:05:22
115
57
SELECT $timestamp, pressure, temperature FROM table_left FULL ASOF JOIN table_right;
Timestamp
Pressure
Temperature
2019-11-23T13:01:58
(void)
56
2019-11-23T13:02:01
100
56
2019-11-23T13:03:03
110
59
2019-11-23T13:03:59
105
59
2019-11-23T13:04:02
105
58
2019-11-23T13:05:00
115
58
2019-11-23T13:05:02
115
56
2019-11-23T13:05:22
115
57
SELECT $timestamp, pressure FROM table_left ASOF JOIN RANGE(2019-11-23T13:02:00, +5min, +1min);
Timestamp
Pressure
2019-11-23T13:02:00
(void)
2019-11-23T13:03:00
100
2019-11-23T13:04:00
105
2019-11-23T13:05:00
115
2019-11-23T13:06:00
115
Using interpolation, we could obtain the following results:
SELECT $timestamp, temperature FROM table_left FULL ASOF JOIN table_right INTERPOLATE(method=linear);
Timestamp
Temperature
2019-11-23T13:01:58
56
2019-11-23T13:02:01
56.13846154
2019-11-23T13:03:03
59
2019-11-23T13:03:59
58.05084746
2019-11-23T13:04:02
58
2019-11-23T13:05:00
56.06666667
2019-11-23T13:05:02
56
2019-11-23T13:05:22
57
SELECT $timestamp, pressure FROM %{:1} FULL ASOF JOIN %{:2} INTERPOLATE(method=linear, limit_direction=none);
Timestamp
Pressure
2019-11-23T13:01:58
(void)
2019-11-23T13:02:01
100
2019-11-23T13:03:03
110
2019-11-23T13:03:59
105
2019-11-23T13:04:02
105.4918033
2019-11-23T13:05:00
115
2019-11-23T13:05:02
(void)
2019-11-23T13:05:22
(void)
12.9. PIVOT#
A PIVOT creates a new table whose columns are selected timestamps and the product (pivot column value) X (selected columns). It’s often combined with ASOF, to treat pivot each column value as a table and align their content to the ASOF reference table. PIVOT queries supports lookup for pivot and pivoted columns. Usual filters (PREWHERE, WHERE and HAVING) and GROUP BY clauses are supported.
12.9.1. Examples#
Assuming this table
sells
Timestamp
Country
Id
2010-01-01
France
14
2011-01-01
Italy
42
2012-01-01
Italy
30
2013-01-01
France
28
2014-01-01
France
65
A first simple query can be done:
SELECT $timestamp, Country, Id FROM sells PIVOT ON Country ORDER BY Id;
Timestamp
France.Id
Italy.Id
2010-01-01
14
(void)
2013-01-01
28
(void)
2012-01-01
(void)
30
2011-01-01
(void)
42
2014-01-01
65
(void)
Now, assuming these three tables
ref_table
Timestamp
Label
2017-01-01
A
2018-01-01
B
2019-01-01
C
north_sells
Timestamp
Vendor
Benefits
2017-01-01
Bob
10
2018-01-01
Alice
20
2019-01-01
Bob
15
2020-01-01
Alice
40
east_sells
Timestamp
Vendor
Benefits
2017-01-01
Carol
30
2018-01-01
Dave
5
2019-01-01
Dave
10
A PIVOT ASOF query can be done:
SELECT $timestamp, label, vendor, benefits FROM ref_table LEFT ASOF JOIN north_sells, east_sells PIVOT ON vendor;
Timestamp
Label
Alice.benefits
Bob.benefits
Carol.benefits
Dave.benefits
2017-01-01
A
(void)
10
30
(void)
2018-01-01
B
20
10
30
5
2019-01-01
C
20
15
30
10
With a PREWHERE and GROUP BY clause, we can get average benefits on several years of some vendors:
SELECT $timestamp, vendor, avg(benefits) FROM ref_table LEFT ASOF JOIN north_sells, east_sells IN RANGE(2016, +4y) PREWHERE vendor != 'Alice' GROUP BY 2y, vendor PIVOT ON vendor;
Timestamp
arithmetic_mean(Bob.benefits)
arithmetic_mean(Carol.benefits)
arithmetic_mean(Dave.benefits)
2016-01-01
10
30
(void)
2018-01-01
12.5
30
7.5
12.10. Interpolation#
Interpolation is a mathematical technique used to estimate values between two known data points. In QuasarDB, it is primarily employed to make predictions or fill in gaps in time-series data. Interpolation helps smooth time-series visualizations and makes predictions between known data points.
12.10.1. Why Use Interpolation?#
Interpolation is useful when you need to estimate values between known data points. It’s particularly valuable when data changes discretely meaning that the data points are distinct and separate, and there are no values between those points, and you want to approximate values between two points. The choice of interpolation method depends on your understanding of how the underlying data behaves over time. Different interpolation methods are available, including constant, linear, cubic, barycentric, and logarithmic.
It is typically applied to arithmetic values, such as temperature, pressure, or other numeric measurements. For non-numeric columns, the previous row’s value is usually copied. Any null values are subject to interpolation.
12.10.2. Interpolation Methods#
QuasarDB supports various interpolation methods:
Constant (default): Takes the previous value.
Linear: Estimates values by drawing an imaginary line between two data points.
Cubic: Draws a 3rd-degree curve based on up to four data points.
Barycentric: Uses all selected points to draw a smoother, higher-degree curve. It can produce “stray” oscillations.
Logarithmic: Performs linear interpolation on logarithms of the values, ideal for data changing exponentially with time.
All these methods require at least two data points, except for constant, which requires only one.
So, which method to choose? It depends on the nature of your data and how accurately you know it.
Constant: If you’re okay with values staying constant until you have new data, this is the method for you. No predictions, just consistency.
Linear: Use this when you have a clear idea that your data is steadily changing over time. It’s like connecting the dots.
Cubic and Barycentric: When you want super-smooth curves through your data, choose one of these methods. They’re great for data that has a gentle flow but might have some small “bumps” in it.
Logarithmic: If your data behaves exponentially, this method is your friend. It’s like understanding the exponential growth.
12.10.3. Interpolation Limit Directions#
When using interpolation, you can specify a limit direction to guide how the estimation is made. This direction sets the boundaries for data estimation between known points. Let’s explore these directions more profoundly:
Forward Direction:
In the forward direction, interpolation predicts future values based on the data you already have. It’s like peering ahead to anticipate what might come next. Use this when your objective is to forecast future data points.
Backward Direction:
In contrast, the backward direction examines past data to infer what might have occurred earlier. Choose this when you’re interested in understanding the events that led to the present state of your data. It helps you reconstruct the past based on the available information.
Both Directions:
The “both” direction is a comprehensive approach that involves looking into both the past and the future. This direction is valuable when you want a complete view, covering both past events and future predictions.
None Direction:
When you select “none,” you’re keeping it simple. It strictly relies on the data points you already have and doesn’t make any predictions.
12.10.4. Examples#
- Insert some sample data into the left table
INSERT INTO table_left ($timestamp, a) VALUES (2019-01-01T00:00, 10), (2019-01-01T01:00, 11), (2019-01-01T05:00, 15)
- Insert some sample data into the right table
INSERT INTO table_right ($timestamp, b) VALUES (2019-01-01T00:00, 110), (2019-01-01T02:00, 112), (2019-01-01T03:00, 113), (2019-01-01T04:00, 114)
- Linear: It provides a straightforward way to estimate values between two data points assuming a constant rate of change.
SELECT $timestamp, table_left.a, table_right.b FROM table_left LEFT ASOF JOIN table_right INTERPOLATE(method=linear); $timestamp table_left.a table_right.b ------------------------------------------------------------------ 2019-01-01T00:00:00.000000000Z 10 110 2019-01-01T01:00:00.000000000Z 11 111 2019-01-01T05:00:00.000000000Z 15 114 Returned 3 rows in 3,165 us Scanned 7 points in 3,165 us (2,211 rows/sec)
- Logarithmic:
SELECT $timestamp, table_left.a, table_right.b FROM table_left LEFT ASOF JOIN table_right INTERPOLATE(method=logarithmic); $timestamp table_left.a table_right.b ------------------------------------------------------------------ 2019-01-01T00:00:00.000000000Z 10 110 2019-01-01T01:00:00.000000000Z 11 110.995 2019-01-01T05:00:00.000000000Z 15 114 Returned 3 rows in 2,562 us Scanned 7 points in 2,562 us (2,731 rows/sec)
Let’s dessect the example:
Timestamp 2019-01-01T00:00:00.000000000Z:
For “a” (from “table_left”), the value is 10. For “b” (from “table_right”), the value is 110. This is the starting point of your data. At this timestamp, “a” is 10, and “b” is 110.
Timestamp 2019-01-01T01:00:00.000000000Z:
For “a” (from “table_left”), the value is interpolated to be 11. For “b” (from “table_right”), the value is interpolated to be approximately 110.995. Here, you can see a non-linear change in the data. While “a” increases by 1, “b” increases only slightly, which is characteristic of logarithmic data changes. The interpolation method accurately captures this non-linear growth.
Timestamp 2019-01-01T05:00:00.000000000Z:
For “a” (from “table_left”), the value is interpolated to be 15. For “b” (from “table_right”), the value is 114. By the final timestamp, you observe that “a” has increased to 15, which is consistent with the previous non-linear trend. Meanwhile, “b” has also increased, but the exact value at this point is 114.
The key takeaway here is that the data changes in a non-linear, exponential fashion. As time progresses, small changes in “a” lead to relatively larger changes in “b.” Logarithmic interpolation is well-suited to capture these non-linear relationships in the data.
- Cubic:
SELECT $timestamp, table_left.a, table_right.b FROM table_left LEFT ASOF JOIN table_right INTERPOLATE(method=cubic); $timestamp table_left.a table_right.b ------------------------------------------------------------------ 2019-01-01T00:00:00.000000000Z 10 110 2019-01-01T01:00:00.000000000Z 11 111 2019-01-01T05:00:00.000000000Z 15 114 Returned 3 rows in 2,880 us Scanned 7 points in 2,880 us (2,429 rows/sec)
- ASOF JOIN RANGE interpolation within a single table
CREATE TABLE single_table (a DOUBLE)
INSERT INTO single_table ($timestamp, a) VALUES (2019-01-01T01:00, 11), (2019-01-01T05:00, 17);
SELECT * FROM single_table ASOF JOIN RANGE(2019-01-01T00:00:00, +7h, 1h) INTERPOLATE(method=linear); $timestamp $table single_table.a ------------------------------------------------------------- 2019-01-01T00:00:00.000000000Z $multitables (void) 2019-01-01T01:00:00.000000000Z $multitables 11 2019-01-01T02:00:00.000000000Z $multitables 12.5 2019-01-01T03:00:00.000000000Z $multitables 14 2019-01-01T04:00:00.000000000Z $multitables 15.5 2019-01-01T05:00:00.000000000Z $multitables 17 2019-01-01T06:00:00.000000000Z $multitables 17 Returned 7 rows in 11,604 us Scanned 2 points in 11,604 us (172 rows/sec)
Interpolation can work on a single table, and it can be used to estimate values within that table. It’s particularly useful when you want to fill in missing or interpolated values within the same dataset, often due to gaps in the data.
12.10.5. Automatic Handling of Data Types#
In most cases, interpolation methods can only be applied to numeric columns, such as DOUBLE or INT64, while non-numeric columns, like BLOB, STRING, or SYMBOL, are typically not interpolated. For these non-numeric columns, our system applies the “constant” method, which essentially copies the previous available value.
This approach ensures that interpolation is automatically applied to numeric values, without the need for users to specify which columns should be interpolated. For non-numeric columns, the previous value is retained to maintain data consistency.
Example:
- Consider the following example with a table named test containing various data types:
CREATE TABLE test (cb BLOB, cd DOUBLE, ci INT64, cs STRING, csy SYMBOL(sym_csy2), ct TIMESTAMP)
INSERT INTO test ($timestamp, cb, cd, ci, cs, csy, ct) VALUES (2019-01-01T00:00, 'd', 2.0, 2, 'D', 'ddd', 2001-01-02), (2019-01-01T04:00, 'e', 5.0, 20, 'E', 'eee', 2001-01-04), (2019-01-01T06:00, 'f', 6.0, 200, 'F', 'fff', 2001-01-07)
SELECT $timestamp, test.* FROM test ASOF JOIN RANGE(2019-01-01T00:00, +5h, 1h) INTERPOLATE(method=linear) $timestamp test.cb test.cd test.ci test.cs test.csy test.ct ------------------------------------------------------------------------------------------------------------------------ 2019-01-01T00:00:00.000000000Z d 2 2 D ddd 2001-01-02T00:00:00.000000000Z 2019-01-01T01:00:00.000000000Z d 2.75 7 D ddd 2001-01-02T00:00:00.000000000Z 2019-01-01T02:00:00.000000000Z d 3.5 11 D ddd 2001-01-02T00:00:00.000000000Z 2019-01-01T03:00:00.000000000Z d 4.25 16 D ddd 2001-01-02T00:00:00.000000000Z 2019-01-01T04:00:00.000000000Z e 5 20 E eee 2001-01-04T00:00:00.000000000Z
In this example, the query interpolates values for all columns within the specified time range. Numeric columns like “cd” and “ci” are interpolated using the “linear” method, while non-numeric columns like “cb,” “cs,” and “csy” receive constant values, copying the previous available value.
This approach ensures that interpolation is performed on all columns automatically, simplifying the experience. There’s no need to specify which columns should be interpolated, making the process more accessible for those who are new to interpolation methods.