Skip to main content

Window functions

info

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

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.

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.

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 changed value.

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)
View results
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.

Window function syntax​

You can write a window function in Druid using either syntax below. The second syntax shows a window alias to reference a window that you can reuse.

window_function() OVER (
  [PARTITION BY partitioning expression]
  [ORDER BY order expression]
  [[ROWS, RANGE] BETWEEN range start AND range end])
FROM table
GROUP BY dimensions
window_function() OVER w
FROM table
WINDOW w AS ([PARTITION BY partitioning expression] [ORDER BY order expression]
  [[ROWS, RANGE] BETWEEN range start AND range end])
GROUP BY dimensions

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

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

An empty OVER clause or the absence of a PARTITION BY clause indicates that all data belongs to a single window.

In the following example, 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 frames, set in ROWS and RANGE expressions, limit the set of rows used for the windowed aggregation.

ROWS and RANGE accept the following values for range start and range end:

  • UNBOUNDED PRECEDING: from the beginning of the window as ordered by the order expression
  • N ROWS PRECEDING: N rows before the current row as ordered by the order expression
  • CURRENT ROW: the current row
  • N ROWS FOLLOWING: N rows after the current row as ordered by the order expression
  • UNBOUNDED FOLLOWING: to the end of the window as ordered by the order expression

See Example with window frames for more detail.

Druid applies the GROUP BY dimensions before calculating all non-window aggregation functions. Then it applies the window function over the aggregated results.

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.

ORDER BY windows​

When the window definition only specifies ORDER BY and not PARTITION BY, it sorts the aggregate data set and applies the function in that order.

The following query uses ORDER BY SUM(delta) DESC to rank user hourly activity from the most changed the least changed within an hour:

SELECT
    TIME_FLOOR(__time, 'PT1H') as time_hour, 
    channel, 
    user,
    SUM(delta) net_user_changes,
    RANK() OVER (ORDER BY SUM(delta) DESC) AS editing_rank
FROM "wikipedia"
WHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')
  AND __time BETWEEN '2016-06-27' AND '2016-06-28'
GROUP BY TIME_FLOOR(__time, 'PT1H'), channel, user
ORDER BY 5
View results
time_hourchannelusernet_user_changesediting_rank
2016-06-27T15:00:00.000Z#kk.wikipediaNurkhan69001
2016-06-27T19:00:00.000Z#lt.wikipedia77.221.66.4143582
2016-06-27T09:00:00.000Z#kk.wikipediaСалиха27023
2016-06-27T04:00:00.000Z#kk.wikipediaNurkhan24404
2016-06-27T09:00:00.000Z#lt.wikipedia80.4.147.2228945
2016-06-27T09:00:00.000Z#lt.wikipedia178.11.203.2124476
2016-06-27T11:00:00.000Z#kk.wikipediaНұрлан Рахымжанов1267
2016-06-27T06:00:00.000Z#kk.wikipediaШокай918
2016-06-27T11:00:00.000Z#lt.wikipediaMaryroseB54599
2016-06-27T04:00:00.000Z#kk.wikipediaНұрлан Рахымжанов5610
2016-06-27T12:00:00.000Z#lt.wikipediaKaroliuk5311
2016-06-27T12:00:00.000Z#lt.wikipediaPowermelon2812
2016-06-27T07:00:00.000Z#lt.wikipediaPowermelon1313
2016-06-27T10:00:00.000Z#lt.wikipedia80.4.147.222114
2016-06-27T07:00:00.000Z#kk.wikipediaСалиха-115
2016-06-27T06:00:00.000Z#lt.wikipediaPowermelon-216

PARTITION BY windows​

When a window only specifies PARTITION BY partition expression, Druid calculates the aggregate window function over all the rows that share a value within the selected dataset.

The following example demonstrates a query that uses two different windows—PARTITION BY channel and PARTITION BY user—to calculate the total activity in the channel and total activity by the user so that they can be compared to individual hourly activity:

SELECT
    TIME_FLOOR(__time, 'PT1H') as time_hour,
    channel,
    user,
    SUM(delta) AS hourly_user_changes,
    SUM(SUM(delta)) OVER (PARTITION BY user) AS total_user_changes,
    SUM(SUM(delta)) OVER (PARTITION BY channel) AS total_channel_changes
FROM "wikipedia"
WHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')
  AND __time BETWEEN '2016-06-27' AND '2016-06-28'
GROUP BY TIME_FLOOR(__time, 'PT1H'), 2, 3
ORDER BY channel, TIME_FLOOR(__time, 'PT1H'), user
View results
time_hourchanneluserhourly_user_changestotal_user_changestotal_channel_changes
2016-06-27T04:00:00.000Z#kk.wikipediaNurkhan2440934012314
2016-06-27T04:00:00.000Z#kk.wikipediaНұрлан Рахымжанов5618212314
2016-06-27T06:00:00.000Z#kk.wikipediaШокай919112314
2016-06-27T07:00:00.000Z#kk.wikipediaСалиха-1270112314
2016-06-27T09:00:00.000Z#kk.wikipediaСалиха2702270112314
2016-06-27T11:00:00.000Z#kk.wikipediaНұрлан Рахымжанов12618212314
2016-06-27T15:00:00.000Z#kk.wikipediaNurkhan6900934012314
2016-06-27T06:00:00.000Z#lt.wikipediaPowermelon-2395851
2016-06-27T07:00:00.000Z#lt.wikipediaPowermelon13395851
2016-06-27T09:00:00.000Z#lt.wikipedia178.11.203.2124474475851
2016-06-27T09:00:00.000Z#lt.wikipedia80.4.147.2228948955851
2016-06-27T10:00:00.000Z#lt.wikipedia80.4.147.22218955851
2016-06-27T11:00:00.000Z#lt.wikipediaMaryroseB5459595851
2016-06-27T12:00:00.000Z#lt.wikipediaKaroliuk53535851
2016-06-27T12:00:00.000Z#lt.wikipediaPowermelon28395851
2016-06-27T19:00:00.000Z#lt.wikipedia77.221.66.41435843585851

In this example, the dataset is filtered for a single day. Therefore the window function results represent the total activity for the day, for the user and for the channel dimensions respectively.

This type of result helps you analyze the impact of an individual user's hourly activity:

  • the impact to the channel by comparing hourly_user_changes to total_channel_changes
  • the impact of each user over the channel by total_user_changes to total_channel_changes
  • the progress of each user's individual activity by comparing hourly_user_changes to total_user_changes

Window frame guardrails​

Druid has guardrail logic to prevent you from executing window function queries with window frame expressions that might return unexpected results.

For example:

  • You cannot set expressions as bounds for window frames.
  • You can only use a RANGE frames when both endpoints are unbounded or current row.

Window function reference​

FunctionNotes
ROW_NUMBER()Returns the number of the row within the window starting from 1
RANK()Returns the rank with gaps for a row within a window. For example, if two rows tie for rank 1, the next rank is 3
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 relative rank of the row calculated as a percentage according to the formula: RANK() OVER (window) / COUNT(1) OVER (window)
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 divided by total window rows. The return value ranges between 1/number of rows and 1
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])If you do not supply an offset, returns the value evaluated at the row preceding the current row. Specify an offset number, n, to return the value evaluated at n rows preceding the current one
LEAD(expr[, offset])If you do not supply an offset, returns the value evaluated at the row following the current row. Specify an offset number n to return the value evaluated at n rows following the current one; if there is no such row, returns the given default value
FIRST_VALUE(expr)Returns the value evaluated for the expression for the first row within the window
LAST_VALUE(expr)Returns the value evaluated 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)
View results
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
View results
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

Example with window frames​

The following query uses a few different window frames to calculate overall activity by channel:

SELECT
    channel, 
    TIME_FLOOR(__time, 'PT1H')      AS time_hour, 
    SUM(delta)                      AS hourly_channel_changes,
    SUM(SUM(delta)) OVER cumulative AS cumulative_activity_in_channel,
    SUM(SUM(delta)) OVER moving5    AS csum5,
    COUNT(1) OVER moving5           AS count5
FROM "wikipedia"
WHERE channel = '#en.wikipedia'
  AND __time BETWEEN '2016-06-27' AND '2016-06-28'
GROUP BY 1, TIME_FLOOR(__time, 'PT1H')
WINDOW cumulative AS (   
                         PARTITION BY channel 
                         ORDER BY TIME_FLOOR(__time, 'PT1H') 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                     )
                     ,
        moving5 AS ( 
                    PARTITION BY channel 
                    ORDER BY TIME_FLOOR(__time, 'PT1H') 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                  )
View results
channeltime_hourhourly_channel_changescumulative_activity_in_channelcsum5count5
#en.wikipedia2016-06-27T00:00:00.000Z7499674996749961
#en.wikipedia2016-06-27T01:00:00.000Z2415099146991462
#en.wikipedia2016-06-27T02:00:00.000Z1023722015182015183
#en.wikipedia2016-06-27T03:00:00.000Z613622628802628804
#en.wikipedia2016-06-27T04:00:00.000Z616663245463245465
#en.wikipedia2016-06-27T05:00:00.000Z1441994687453937495
#en.wikipedia2016-06-27T06:00:00.000Z334145021594030135
#en.wikipedia2016-06-27T07:00:00.000Z793975815563800385
#en.wikipedia2016-06-27T08:00:00.000Z1044366859924231125
#en.wikipedia2016-06-27T09:00:00.000Z580207440124194665
#en.wikipedia2016-06-27T10:00:00.000Z939048379163691715
#en.wikipedia2016-06-27T11:00:00.000Z744369123524101935
#en.wikipedia2016-06-27T12:00:00.000Z834919958434142875
#en.wikipedia2016-06-27T13:00:00.000Z10305110988944129025
#en.wikipedia2016-06-27T14:00:00.000Z21141113103055662935
#en.wikipedia2016-06-27T15:00:00.000Z10124714115525736365
#en.wikipedia2016-06-27T16:00:00.000Z18976516013176889655
#en.wikipedia2016-06-27T17:00:00.000Z7440416757216798785
#en.wikipedia2016-06-27T18:00:00.000Z10482417805456816515
#en.wikipedia2016-06-27T19:00:00.000Z7126818518135415085
#en.wikipedia2016-06-27T20:00:00.000Z8818519399985284465
#en.wikipedia2016-06-27T21:00:00.000Z4258419825823812655

The example defines multiple window specifications in the WINDOW clause that you can use for various window function calculations.

The query uses two windows:

  • cumulative is partitioned by channel and includes all rows from the beginning of partition up to the current row as ordered by __time to enable cumulative aggregation
  • moving5 is also partitioned by channel but only includes up to the last four rows and the current row as ordered by time

The number of rows considered for the moving5 window for the count5 column:

  • starts at a single row because there are no rows before the current one
  • grows up to five rows as defined by ROWS BETWEEN 4 ROWS PRECEDING AND CURRENT ROW

Known issues​

The following are known issues with window functions:

  • SELECT * queries without a WHERE clause are not supported. If you want to retrieve all columns in this case, specify the column names.