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.

yaml

window:
    order_by:
        - order_column (desc)
    frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    partition_by:
        - partition_column

Field	Description
`order_by`	Column to order rows by, for example, add `desc` for descending order.
`frame_clause`	Specify the set of rows (or window) the calculation is applied to, see the specific warehouse documentation for more details.
`partition_by`	Column to partition the rows by. Applies only to `input_vars`.

Window function references

Example

yaml

# 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