Below given is my table schema
CREATE TABLE ticks (
instrument_token BIGINT,
lastPrice BIGINT,
lastQuantity BIGINT,
avgPrice BIGINT,
volume BIGINT,
buy_quantity BIGINT,
sell_quantity BIGINT,
o BIGINT,
h BIGINT,
l BIGINT,
c BIGINT,
ltt TIMESTAMP,
oi BIGINT,
oi_day_high BIGINT,
oi_day_low BIGINT,
ts TIMESTAMP
);
for a given instrument_token I want to fetch first and last value min, max value of lastPrice for every 5 minute window for the period defined by where condition
ltt >= ‘2020-10-12 10:16:00’ and ltt < ‘2020-10-12 11:18:00’
llt contains datetime till minute, second is truncated
expected output is
first, last, min, max,timestamp
75,80,10,90,'2020-10-12 10:16:00
75,82,150,290,'2020-10-12 10:17:00
75,85,190,190,'2020-10-12 10:18:00
Hi @arunsoman,
nice to have you in the community forum,
The window functions are a fairly new feature, and it comes with some limitations; one of them is that you can’t use a derived field to create a computed bucket of 5 mins on the partition clause of first, last, min, max, and so on.
To overcome this limitation, you can create a TEMPORARY TABLE using a CTAS and then querying the temporary table.
As an example, using the provided schema:
CREATE TABLE ticks_2021012101699_20201012111800_temp AS
SELECT instrument_token,
ts,
lastPrice,
FLOOR(EXTRACT(EPOCH FROM ttl) / (5 * 60)) * (5 * 60) as ttl_five_minutes_bin
FROM ticks
WHERE ltt BETWEEN ‘2020-10-12 10:16:00’
AND ‘2020-10-12 11:18:00’;
SELECT FIRST_VALUE(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS first,
LAST_VALUE(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS last,
MIN(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS min,
MAX(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS max,
ts AS timestamp
FROM ticks_2021012101699_20201012111800_temp;
DROP TABLE ticks_2021012101699_20201012111800_temp;
I’m not sure if I guessed what you asked when you said “for every 5 mins”; I choosed to create a serie of buckets of 5 mins on Time To Live field
Regards