Window - Kinetica Docs

Window functions are available through the use of the OVER clause, which can partition rows into frames. Different types of functions can be used to aggregate data over a sliding window.

Window Function Syntax

SELECT
    <window function> OVER (
        [PARTITION BY <column expression list>]
        [ORDER BY <ordering expression list>]
        [
            <RANGE | ROWS>
            <
                <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
                |
                BETWEEN <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
                    AND <UNBOUNDED FOLLOWING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
            >
        ]
    ) [AS <alias>]

The default frame type is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Parameters

One of the supported window functions listed below

PARTITION BY

Optional list of columns and/or column expressions to use in partitioning the data. The window function will be applied separately to each partition, defined by the set of records containing the same value(s) for the specified partition-by column(s). If no PARTITION BY clause is given, the window function will be calculated over the entire data set.

ORDER BY

Clause defining the ordering of records within each partition before applying the window function; optional when using FIRST_VALUE() or LAST_VALUE().The ordering expression syntax is:

<column name/alias/expression/position> [ASC | DESC] [NULLS FIRST | NULLS LAST]

The default sort order is ascending (ASC). The default null ordering is NULLS FIRST when using ascending order and NULLS LAST when using descending order.

Only one column can be specified in the ordering expression list when using RANGE. When using ROWS, the frame is applied after any ordering; so, while several columns may appear in the order expression list, there will be only one ROWS clause following the list.

RANGE

Range-based frame used in applying the window function to each record. The frame is based on the values in the column or column expression used in the ORDER BY clause. All records with this value within the specified relation to the current record’s value will be used in calculating its window function result.A range-based frame can be specified for any aggregate function or the FIRST_VALUE() or LAST_VALUE() ranking function.For a frame between a specified lower bound and the current row:

RANGE <lower frame bound>

For a frame between a specified lower and upper bound:

RANGE BETWEEN <lower frame bound> AND <upper frame bound>

The frame bound can be one of the following:

Frame Bound	Description
`UNBOUNDED PRECEDING`	All records in the partition before the one at the upper bound; cannot be used as an upper bound
`<number> PRECEDING`	All records in the partition with an `ORDER BY` value between the current record’s value and `<number>` less than the current record’s value, inclusive
`CURRENT ROW`	The current record being processed by the window function, as well as all peer rows (rows with the same ordering value)
`<number> FOLLOWING`	All records in the partition with an `ORDER BY` value between the current record’s value and `<number>` more than the current record’s value, inclusive
`UNBOUNDED FOLLOWING`	All records in the partition after the one at the lower bound; cannot be used as a lower bound

ROWS

Row-based frame used in applying the window function to each record. The frame is based on the position of records, as determined by the ORDER BY clause. All records within the specified ordered distance from a given record will be used in calculating its window function value.A row-based frame can be specified for any aggregate function or the FIRST_VALUE() or LAST_VALUE() ranking function.For a frame between a specified lower bound and the current row:

ROWS <lower frame bound>

For a frame between a specified lower and upper bound:

ROWS BETWEEN <lower frame bound> AND <upper frame bound>

The frame bound can be one of the following:

Frame Bound	Description
`UNBOUNDED PRECEDING`	All records in the partition before the one at the upper bound; cannot be used as an upper bound
`<number> PRECEDING`	All records in the partition from the one `<number>` records before the current record through the record at the upper bound
`CURRENT ROW`	The current record being processed by the window function
`<number> FOLLOWING`	All records in the partition from the record at the lower bound through the one `<number>` records after the current record
`UNBOUNDED FOLLOWING`	All records in the partition after the one at the lower bound; cannot be used as a lower bound

<alias>

Column alias to apply to the window function result column

Aggregate Functions

AVG(expr)

Calculates the average of the given expression expr over the specified window frame

COUNT(expr)

Calculates the count of the given expression expr over the specified window frame

MAX(expr)

Calculates the maximum value of the given expression expr over the specified window frame

MEAN(expr)

Alias for AVG(). Calculates the average of the given expression expr over the specified window frame

MIN(expr)

Calculates the minimum value of the given expression expr over the specified window frame

PRODUCT(expr)

Calculates the product of the given expression expr over the specified window frame

RATIO_TO_REPORT(expr)

Calculates the ratio of the value of expr to the sum of expr over the specified window frame. Note that ORDER BY is not supported for this function.

STDDEV(expr)

Alias for STDDEV_POP(). Calculates the population standard deviation of the given expression expr over the specified window frame

STDDEV_POP(expr)

Calculates the population standard deviation of the given expression expr over the specified window frame

STDDEV_SAMP(expr)

Calculates the sample standard deviation of the given expression expr over the specified window frame

SUM(expr)

Calculates the sum of the given expression expr over the specified window frame

VAR(expr)

Alias for VAR_POP(). Calculates the population variance of the given expression expr over the specified window frame

VAR_POP(expr)

Calculates the population variance of the given expression expr over the specified window frame

VAR_SAMP(expr)

Calculates the sample variance of the given expression expr over the specified window frame

Ranking Functions

CUME_DIST()

The relative position of the current row within the cumulative distribution of the selected partition, expressed as a percentage from 0 (exclusive) to 1 (inclusive). The formula for this calculation is as follows:

(count of <= records within partition) / (partition row count)

This function is shorthand for using the COUNT() function in separate partition statements to arrive at the same result:

DOUBLE
(
    COUNT(*) OVER
    (
        PARTITION BY <partition_column>
        ORDER BY <sort_column>
    )
) /
(
    COUNT(*) OVER
    (
        PARTITION BY <partition_column>
        ORDER BY <sort_column>
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)

DENSE_RANK()

Number of the current row within the selected partition except rows with identical values evaluate to different ranks. Starts at 1

FIRST_VALUE(<column>) [<IGNORE | RESPECT> NULLS]

The value found in the first row within a frame of the given expression. Optionally, add IGNORE NULLS or RESPECT NULLS to the function syntax to ignore or respect nulls, respectively.

LAG(<column>[, <num>]) [<IGNORE | RESPECT> NULLS]

The value of the row before the given expression’s value. Provide an additional comma-separated value to specify which row to select, e.g., LAG(vendor_id, 3) would list the value in the vendor_id column from three rows prior to the current row. Optionally, add IGNORE NULLS or RESPECT NULLS to the function syntax to ignore or respect nulls respectively.

LAST_VALUE(<column>) [<IGNORE | RESPECT> NULLS]

The value found in the last row within a frame of the given expression. Optionally, add IGNORE NULLS or RESPECT NULLS to the function syntax to ignore or respect nulls respectively.

LEAD(<column>[, <num>]) [<IGNORE | RESPECT> NULLS]

The value of the row after the given expression’s value. Provide an additional comma-separated value to specify which row to select, e.g., LEAD(vendor_id, 3) would list the value in the vendor_id column from three rows after the current row. Optionally, add IGNORE NULLS or RESPECT NULLS to the function syntax to ignore or respect nulls respectively.

NTILE(<num of groups>)

The group number of the row after partitioning the rows into num of groups groups. For example, NTILE(4) will partition data by quartiles and return the associated group number, 1 to 4.

PERCENT_RANK()

The rank of the current row within the selected partition, expressed as a percentage from 0 to 1, inclusive. The formula for this calculation is as follows:

(rank within partition - 1) / (partition row count - 1)

This function is shorthand for using the RANK() & COUNT() functions in separate partition statements to arrive at the same result:

DOUBLE
(
    RANK() OVER
    (
        PARTITION BY <partition_column>
        ORDER BY <sort_column>
    ) - 1
) /
(
    COUNT(*) OVER
    (
        PARTITION BY <partition_column>
        ORDER BY <sort_column>
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - 1
)

RANK()

Number of the current row within the selected partition. However, rows with identical values evaluate to the same rank. Starts at 1

ROW_NUMBER()

Number of the current row within the selected partition. Starts at 1

Examples

To calculate the rolling sum of total amounts collected by each taxi vendor over the course of a given day, as well as the number of other trips that occurred within 5 minutes of each trip:

Window Rolling Sum Example

SELECT
    vendor_id,
    pickup_datetime,
    total_amount,
    passenger_count,
    DECIMAL
    (
        SUM(total_amount) OVER
            (
                PARTITION BY vendor_id
                ORDER BY pickup_datetime
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
    ) AS growing_sum,
    COUNT(*) OVER
        (
            PARTITION BY vendor_id
            ORDER BY LONG(pickup_datetime)
            RANGE BETWEEN 300000 PRECEDING AND 300000 FOLLOWING
        ) AS trip_demand
FROM demo.nyctaxi
WHERE pickup_datetime >= '2015-01-01' AND pickup_datetime < '2015-01-01 02:00:00'
ORDER BY
    vendor_id,
    pickup_datetime

To calculate a 5-before and 10-after moving average of 4-passenger trip distances per vendor over the course of a given day:

Window Moving Average Example

SELECT
    vendor_id,
    pickup_datetime,
    trip_distance,
    AVG(trip_distance) OVER
        (
            PARTITION BY vendor_id
            ORDER BY pickup_datetime
            ROWS BETWEEN 5 PRECEDING AND 10 FOLLOWING
        ) AS local_avg_dist
FROM demo.nyctaxi
WHERE
    passenger_count = 4 AND
    pickup_datetime >= '2015-01-01' AND pickup_datetime < '2015-01-02'
ORDER BY
    vendor_id,
    pickup_datetime

To rank, by vendor, the total amounts collected from 3-passenger trips on a given day:

Window Ranking Example

SELECT
    vendor_id,
    pickup_datetime,
    dropoff_datetime,
    total_amount AS fare,
    RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount) AS ranked_fare,
    DECIMAL(PERCENT_RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount)) * 100 AS percent_ranked_fare
FROM demo.nyctaxi
WHERE
    passenger_count = 3 AND
    pickup_datetime >= '2015-01-11' AND pickup_datetime < '2015-01-12'
ORDER BY
    vendor_id,
    pickup_datetime

To compare each trip’s total amount to the lowest (ignoring nulls), highest (ignoring nulls), & average total amount for 5-passenger trips for each vendor over the course of a given day:

Window FIRST_VALUE Example

SELECT
    vendor_id,
    pickup_datetime,
    tip_amount,
    tip_amount -
        FIRST_VALUE(tip_amount) IGNORE NULLS OVER
            (PARTITION BY vendor_id ORDER BY tip_amount) AS vs_lowest_tip,
    tip_amount -
        DECIMAL
        (
            AVG(tip_amount) OVER
                (
                    PARTITION BY vendor_id
                    ORDER BY tip_amount
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                )
        ) AS vs_average_tip,
    tip_amount -
        FIRST_VALUE(tip_amount) IGNORE NULLS OVER
            (PARTITION BY vendor_id ORDER BY tip_amount DESC) AS vs_highest_tip
FROM demo.nyctaxi
WHERE
    passenger_count = 5 AND trip_distance > 0 AND tip_amount > 0 AND
    pickup_datetime >= '2015-04-17' AND pickup_datetime < '2015-04-18'
ORDER BY
    vendor_id,
    pickup_datetime

To compare each vendor’s average total amount to their average total amount within the interquartile range:

Window N-Tile Example

SELECT
    vendor_id,
    DECIMAL(AVG(total_amount)) AS average_total_amount,
    DECIMAL(AVG(IF(quartile IN (2,3), total_amount, null))) AS average_interq_range_total_amount
FROM
(
    SELECT
        vendor_id,
        total_amount,
        NTILE(4) OVER (PARTITION BY vendor_id ORDER BY total_amount) quartile
    FROM
        demo.nyctaxi
)
GROUP BY vendor_id
ORDER BY vendor_id

​Parameters

​Aggregate Functions

​Ranking Functions

​Examples

Parameters

Aggregate Functions

Ranking Functions

Examples