Window functions are used to perform calculations across rows of a query result. Common SQL aggregate functions calculate the results of only one row or aggregate all rows into one row for calculation. Window functions support cross-row calculation and fill the calculation results in each row.

Syntax of window functions:
SELECT key1, key2, value,
       rank() OVER (PARTITION BY key2
                    ORDER BY value DESC) AS rnk
FROM orders
ORDER BY key1,rnk
Key part:
rank() OVER (PARTITION BY  KEY1 ORDER BY KEY2 DESC)

rank() is an aggregate function. You can use any function in analysis syntax or the function listed in this topic. PARTITION BY indicates the buckets based on which values are calculated.

Special aggregate functions used in windows

Function Description
rank() Returns the rank of a value within a group of values. The rank is one plus the number of preceding rows that are not peers of the current row.
row_number() Returns a unique, sequential number for each row.
first_value(x) Returns the first value of the window. Typically, the function is used to obtain the maximum value after values of the window are sorted.
last_value(x) Returns the last value of the window.
nth_value(x, offset) Returns the value at the specified offset from the beginning of the window.
lead(x,offset,defaut_value) Returns the value at offset rows after the current row in the window. If the target row does not exist, the default_value is returned.
lag(x,offset,defaut_value) Returns the value at offset rows before the current row in the window. If the target row does not exist, the default_value is returned.

Example

  • Rank the salaries of employees in their respective departments
    * | select department, personId, salary , rank() over(PARTITION BY department order by salary desc) as salary_rank  order by department,salary_rank

    Results

    department personId salary salary_rank
    dev john 9000 1
    dev Smith 8000 2
    dev Snow 7000 3
    dev Achilles 6000 4
    Marketing Blan Stark 9000 1
    Marketing Rob Stark 8000 2
    Marketing Sansa Stark 7000 3
  • Calculate the salaries of employees as percentages in their respective departments
    * | select department, personId, salary *1.0 / sum(salary) over(PARTITION BY department ) as salary_percentage

    Results

    department personId salary salary_percentage
    dev john 9000 0.3
    dev Smith 8000 0.26
    dev Snow 7000 0.23
    dev Achilles 6000 0.2
    Marketing Blan Stark 9000 0.375
    Marketing Rob Stark 8000 0.333
    Marketing Sansa Stark 7000 0.29
  • Calculate the daily UV increase over the previous day
    * | select day ,uv, uv *1.0 /(lag(uv,1,0) over() ) as diff_percentage from
    (
    select approx_distinct(ip) as uv, date_trunc('day',__time__) as day from log group by day order by day asc
    )

    Results

    day uv diff_percentage
    2017-12-01 00:00:00 100 null
    2017-12-02 00:00:00 125 1.25
    2017-12-03 00:00:00 150 1.2
    2017-12-04 00:00:00 175 1.16
    2017-12-05 00:00:00 200 1.14
    2017-12-06 00:00:00 225 1.125
    2017-12-07 00:00:00 250 1.11