Groupy window function help


1 comment

  • Avatar
    Candido Dessanti

    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,
                    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


    Comment actions Permalink

Please sign in to leave a comment.