For each row the window function is computed across the rows that fall into the same partition as the current row.Window functions are permitted only in the SELECT list and the ORDER BY clause of the query
Over, Partition By
def createWindowOnRows(ByWhat): DataWithWindows
OVER (PARTITION BY depname)
SELECT x,y,count(z), OVER (PARTITION BY x) FROM MyTable
- We have a standard SQL SELECT statement
- One of the fields in our case in SELECT was in window we said OVER (PARTITION BY x)
- One of the fields in our case had an aggregation we had count(z)
- One of the fields (we had a total of 3) in select did not have any aggregation nor any window therefore we get an actual row for each such value in our case that was employee number, so we did get a row for each such employee number it was not aggregated but for each such employee number we did get the average salary for his department.
performed a calculation across a set of rows that are somehow related to the current row. The rows retain their separate identity.
With rank you control the order of the rows that fall into the window add a new column with name rank according to the order by
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;