id: sql-window-functions title: Window functions

:::info

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.

Window functions are an experimental feature. Development and testing are still at early stage. Feel free to try window functions and provide your feedback. Windows functions are not currently supported by multi-stage-query engine so you cannot use them in SQL-based ingestion.

Set the context parameter enableWindowing: true to use window functions.

:::

Window functions in Apache Druid produce values based upon the relationship of one row within a window of rows to the other rows within the same window. A window is a group of related rows within a result set. For example, rows with the same value for a specific dimension.

The following example organizes results with the same channel value into windows. For each window, the query returns the rank of each row in ascending order based upon its delta value.

Window functions in Druid require a GROUP BY statement. Druid performs the row-level aggregations for the GROUP BY before performing the window function calculations.

SELECT FLOOR(__time TO DAY) AS event_time,
    channel,
    ABS(delta) AS change,
    RANK() OVER w AS rank_value
FROM wikipedia
WHERE channel in ('#kk.wikipedia', '#lt.wikipedia')
AND '2016-06-28' > FLOOR(__time TO DAY) > '2016-06-26'
GROUP BY channel, ABS(delta), __time
WINDOW w AS (PARTITION BY channel ORDER BY ABS(delta) ASC)
event_timechannelchangerank_value
2016-06-27T00:00:00.000Z#kk.wikipedia11
2016-06-27T00:00:00.000Z#kk.wikipedia11
2016-06-27T00:00:00.000Z#kk.wikipedia73
2016-06-27T00:00:00.000Z#kk.wikipedia564
2016-06-27T00:00:00.000Z#kk.wikipedia564
2016-06-27T00:00:00.000Z#kk.wikipedia636
2016-06-27T00:00:00.000Z#kk.wikipedia917
2016-06-27T00:00:00.000Z#kk.wikipedia24408
2016-06-27T00:00:00.000Z#kk.wikipedia27039
2016-06-27T00:00:00.000Z#kk.wikipedia690010
2016-06-27T00:00:00.000Z#lt.wikipedia11
2016-06-27T00:00:00.000Z#lt.wikipedia22
2016-06-27T00:00:00.000Z#lt.wikipedia133
2016-06-27T00:00:00.000Z#lt.wikipedia284
2016-06-27T00:00:00.000Z#lt.wikipedia535
2016-06-27T00:00:00.000Z#lt.wikipedia566
2016-06-27T00:00:00.000Z#lt.wikipedia597
2016-06-27T00:00:00.000Z#lt.wikipedia3918
2016-06-27T00:00:00.000Z#lt.wikipedia8949
2016-06-27T00:00:00.000Z#lt.wikipedia435810

Window functions are similar to aggregation functions.

You can use the OVER clause to treat other Druid aggregation functions as window functions. For example, the sum of a value for rows within a window.

Window functions support aliasing.

Define a window with the OVER clause

The OVER clause defines the query windows for window functions as follows:

  • PARTITION BY indicates the dimension that defines the rows within the window
  • ORDER BY specifies the order of the rows within the windows.

:::note

Sometimes windows are called partitions. However, the partitioning for window functions are a shuffle (partition) of the result set created at query time and is not to be confused with Druid's segment partitioning feature which partitions data at ingest time.

:::

The following OVER clause example sets the window dimension to channel and orders the results by the absolute value of delta ascending:

...
RANK() OVER (PARTITION BY channel ORDER BY ABS(delta) ASC)
...

Window function reference

FunctionNotes
ROW_NUMBER()Returns the number of the row within the window
RANK()Returns the rank for a row within a window
DENSE_RANK()Returns the rank for a row within a window without gaps. For example, if two rows tie for rank of 1, the subsequent row is ranked 2.
PERCENT_RANK()Returns the rank of the row calculated as a percentage according to the formula: (rank - 1) / (total window rows - 1)
CUME_DIST()Returns the cumulative distribution of the current row within the window calculated as number of window rows at the same rank or higher than current row / total window rows
NTILE(tiles)Divides the rows within a window as evenly as possible into the number of tiles, also called buckets, and returns the value of the tile that the row falls into
LAG(expr[, offset])Returns the value evaluated at the row that precedes the current row by the offset number within the window. offset defaults to 1 if not provided
LEAD(expr[, offset])Returns the value evaluated at the row that follows the current row by the offset number within the window; if there is no such row, returns the given default value. offset defaults to 1 if not provided
FIRST_VALUE(expr)Returns the value for the expression for the first row within the window
LAST_VALUE(expr)Returns the value for the expression for the last row within the window

Examples

The following example illustrates all of the built-in window functions to compare the number of characters changed per event for a channel in the Wikipedia data set.

SELECT FLOOR(__time TO DAY) AS event_time,
    channel,
    ABS(delta) AS change,
    ROW_NUMBER() OVER w AS row_no,
    RANK() OVER w AS rank_no,
    DENSE_RANK() OVER w AS dense_rank_no,
    PERCENT_RANK() OVER w AS pct_rank,
    CUME_DIST() OVER w AS cumulative_dist,
    NTILE(4) OVER w AS ntile_val,
    LAG(ABS(delta), 1, 0) OVER w AS lag_val,
    LEAD(ABS(delta), 1, 0) OVER w AS lead_val,
    FIRST_VALUE(ABS(delta)) OVER w AS first_val,
    LAST_VALUE(ABS(delta)) OVER w AS last_val
FROM wikipedia
WHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')
GROUP BY channel, ABS(delta), FLOOR(__time TO DAY) 
WINDOW w AS (PARTITION BY channel ORDER BY ABS(delta) ASC)
event_timechannelchangerow_norank_nodense_rank_nopct_rankcumulative_distntile_vallag_vallead_valfirst_vallast_val
2016-06-27T00:00:00.000Z#kk.wikipedia11110.00.1251null716900
2016-06-27T00:00:00.000Z#kk.wikipedia72220.142857142857142850.25115616900
2016-06-27T00:00:00.000Z#kk.wikipedia563330.28571428571428570.375276316900
2016-06-27T00:00:00.000Z#kk.wikipedia634440.428571428571428550.52569116900
2016-06-27T00:00:00.000Z#kk.wikipedia915550.57142857142857140.625363244016900
2016-06-27T00:00:00.000Z#kk.wikipedia24406660.71428571428571430.75391270316900
2016-06-27T00:00:00.000Z#kk.wikipedia27037770.85714285714285710.87542440690016900
2016-06-27T00:00:00.000Z#kk.wikipedia69008881142703null16900
2016-06-27T00:00:00.000Z#lt.wikipedia111100.11null214358
2016-06-27T00:00:00.000Z#lt.wikipedia22220.11111111111111110.2111314358
2016-06-27T00:00:00.000Z#lt.wikipedia133330.22222222222222220.3122814358
2016-06-27T00:00:00.000Z#lt.wikipedia284440.33333333333333330.42135314358
2016-06-27T00:00:00.000Z#lt.wikipedia535550.44444444444444440.52285614358
2016-06-27T00:00:00.000Z#lt.wikipedia566660.55555555555555560.62535914358
2016-06-27T00:00:00.000Z#lt.wikipedia597770.66666666666666660.735639114358
2016-06-27T00:00:00.000Z#lt.wikipedia3918880.77777777777777780.835989414358
2016-06-27T00:00:00.000Z#lt.wikipedia8949990.88888888888888880.94391435814358
2016-06-27T00:00:00.000Z#lt.wikipedia4358101010114894null14358

The following example demonstrates applying the SUM() function over the values in a window to calculate the cumulative changes to a channel over time:

SELECT
    FLOOR(__time TO MINUTE) as "time",
    channel,
    ABS(delta) AS changes,
    sum(ABS(delta)) OVER (PARTITION BY channel ORDER BY FLOOR(__time TO MINUTE) ASC) AS cum_changes
FROM wikipedia
WHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')
GROUP BY channel, __time, delta
timechannelchangescum_changes
2016-06-27T04:20:00.000Z#kk.wikipedia5656
2016-06-27T04:35:00.000Z#kk.wikipedia24402496
2016-06-27T06:15:00.000Z#kk.wikipedia912587
2016-06-27T07:32:00.000Z#kk.wikipedia12588
2016-06-27T09:00:00.000Z#kk.wikipedia27035291
2016-06-27T09:24:00.000Z#kk.wikipedia15292
2016-06-27T11:00:00.000Z#kk.wikipedia635355
2016-06-27T11:05:00.000Z#kk.wikipedia75362
2016-06-27T11:32:00.000Z#kk.wikipedia565418
2016-06-27T15:21:00.000Z#kk.wikipedia690012318
2016-06-27T06:17:00.000Z#lt.wikipedia22
2016-06-27T07:55:00.000Z#lt.wikipedia1315
2016-06-27T09:05:00.000Z#lt.wikipedia894909
2016-06-27T09:12:00.000Z#lt.wikipedia3911300
2016-06-27T09:23:00.000Z#lt.wikipedia561356
2016-06-27T10:59:00.000Z#lt.wikipedia11357
2016-06-27T11:49:00.000Z#lt.wikipedia591416
2016-06-27T12:41:00.000Z#lt.wikipedia531469
2016-06-27T12:58:00.000Z#lt.wikipedia281497
2016-06-27T19:03:00.000Z#lt.wikipedia43585855

Known issues

The following are known issues with window functions:

  • Aggregates with ORDER BY specified are processed in the window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    This behavior differs from other databases that use the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
    In cases where the order column is unique there is no difference between RANGE / ROWS; windows with RANGE specifications are handled as ROWS.
  • LEAD/LAG ignores the default value
  • LAST_VALUE returns the last value of the window even when you include an ORDER BY clause