Groupy window function help

Follow

Arun Soman

• October 13, 2020 05:16

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

0

• 

  Candido Dessanti

  • October 14, 2020 14:50

  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

  0

  Comment actions Permalink

Please sign in to leave a comment.