danger

You are viewing documentation for an older version.

Click here to view the latest documentation.

Window Functions

Learn how to compute complex features using window functions.

  •   less than a minute  

Window functions are SQL functions that you can use with input_vars and entity_vars.

SQL window functions perform calculations across rows related to the current row, enabling complex analytics like rankings, running totals, and running averages.

window:
    order_by:
        - order_column (desc)
    frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    partition_by:
        - partition_column
FieldDescription
order_byColumn to order rows by, for example, add desc for descending order.
frame_clauseSpecify the set of rows (or window) the calculation is applied to, see the specific warehouse documentation for more details.
partition_byColumn to partition the rows by.

Window function references

Example

# Order by
- entity_var:
    name: order_number
    select: rank() # DO NOT specify frame clause when a ranking window function is used
    window:
        order_by:
            - order_date

# Partition by and Input var
- input_var:
    name: session_start_time
    from: models/rsTracksUnionPages
    select: min(timestamp)
    window:
        partition_by:
            - context_session_id
            - rudder_id
    description: Describes the start time of session of a specific context_id

# Using frame clause

- entity_var:
    name: first_num_b_order_num_b
    select: first_value(tbl_c.num_b) # Specify frame clause as aggregate window function is used
    from: inputs/tbl_c
    default_value: -1
    where: tbl_c.num_b >= 10
    window:
        order_by:
            - tbl_c.num_b desc
        frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Questions? Contact us by email or on Slack