Window functions are used for cross-row calculation. 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 enter 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
Core part is:
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 document.  PARTITION BY indicates the buckets based on which values are calculated.

Special aggregate functions used in windows

Function name Meaning
rank() Sorts data based on a specific column in a window and returns the serial numbers in the window.
row_number() Returns the row numbers in the window.
first_value(x) Returns the first value in the window. Generally used to obtain the maximum value after values are sorted in the window.
last_value(x) Opposite to first_value.
nth_value(x, offset) Value of the No. offset row in xth column in the window.
lead(x,offset,defaut_value) Value of the No. offset row after a certain row in xth column in the window. If that row does not exist, use the default_value.
lag(x,offset,defaut_value) Value of the No. offset row before a certain row in xth column in the window. If that row does not exist, use the default_value.

Example

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

    Response results:

    department persionId sallary sallary_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, persionId, sallary *1.0 / sum(sallary) over(PARTITION BY department ) as sallary_percentage

    Response results:

    department persionId sallary sallary_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
    

    Response 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