Search code examples
sqlpostgresqlwindow-functions

last_value() sql window function using RANGE

I am running this sql query below:

select e.first_name ,
    e.last_name,
    ed.department_name ,
    e.salary,
    last_value(concat(e.first_name, ' ', e.last_name)) over(partition by ed.department_name order by salary range between unbounded preceding and unbounded following) UnboundedCol,
    last_value(concat(first_name, ' ', last_name)) over(partition by ed.department_name order by salary range between 0 preceding and 0 following) range0,
    last_value(concat(first_name, ' ', last_name)) over(partition by ed.department_name order by salary range between 1 preceding and 1 following) range1,
    last_value(concat(first_name, ' ', last_name)) over(partition by ed.department_name order by salary range between 2 preceding and 2 following) range2,
    last_value(concat(first_name, ' ', last_name)) over(partition by ed.department_name order by salary range between 3 preceding and 3 following) range3
from employees e 
join employee_departments ed on e.department_id = ed.department_id
order by ed.department_name, salary;

And here is the output of the query:

enter image description here

My question is, for example if you focus on the value Finance in column department_name , why the values of columns range0, range1, range2, range3 are coming out to be same?

If I take example of row 9 with first_name as Jose Manuel:

  • for column range1, the defined window range on the last_value() functions is 1 preceding and 1 following, which means that 1 row above it and 1 below it.
  • And since we are using last_value(), the value of range1 for row 9 should be the name from row 10 which is John Chen.
  • However, it is not the case as it is still Jose Manuel Urman.
  • And this is happening to all the other range (range1, range2, range3) columns as well.

Can someone please explain why is this happening? It works fine if I use row, but not with range.

Solution

  • Just change windowing from "range" to "rows" and adjust Concat() (for Oracle) as in the code below...

    The ROWS clause does that quite literally. It specifies a fixed number of rows that precede or follow the current row regardless of their value. These rows are used in the window function.

    On the other hand, the RANGE clause logically limits the rows. That means it considers the rows based on their value compared to the current row.
    From the Docs:
    ROWS | RANGE These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom. ROWS specifies the window in physical units (rows). RANGE specifies the window as a logical offset

    last_value(concat(FIRST_NAME || ' ', LAST_NAME)) over(partition by ed.DEPT_NAME order by SALARY rows between 0 preceding and 0 following) rows0

    this part above doesn't make sense it is current row - in fact it is:

    concat(FIRST_NAME || ' ', LAST_NAME) rows0

    WITH    --  S a m p l e    D a t a :
    employees (EMP_ID, DEPT_ID, FIRST_NAME, LAST_NAME, SALARY)  AS
        ( Select 1, 10, 'Luis', 'Popp', 6900 From Dual Union All 
          Select 2, 10, 'Ismael', 'Sciarra', 7700 From Dual Union All 
          Select 3, 10, 'Jose Manuel', 'Urman', 7800 From Dual Union All 
          Select 4, 10, 'John', 'Chen', 8200 From Dual Union All 
          Select 5, 10, 'Daniel', 'Faviet', 9000 From Dual Union All 
          Select 6, 10, 'Nancy', 'Greenberg', 12000 From Dual 
        ),
    departments (DEPT_ID, DEPT_NAME) AS
        ( Select 10, 'Finance' From Dual Union All
          Select 20, 'Accounting' From Dual Union All
          Select 30, 'Administration' From Dual 
        )

    --  S Q L
select e.FIRST_NAME ,
    e.LAST_NAME,
    ed.DEPT_NAME ,
    e.SALARY,
    last_value(concat(e.FIRST_NAME || ' ', e.LAST_NAME)) over(partition by ed.DEPT_NAME order by SALARY rows between unbounded preceding and unbounded following) UnboundedCol,
    concat(FIRST_NAME || ' ', LAST_NAME) rows0,
    last_value(concat(FIRST_NAME || ' ', LAST_NAME)) over(partition by ed.DEPT_NAME order by SALARY rows between 1 preceding and 1 following) rows1,
    last_value(concat(FIRST_NAME || ' ', LAST_NAME)) over(partition by ed.DEPT_NAME order by SALARY rows between 2 preceding and 2 following) rows2,
    last_value(concat(FIRST_NAME || ' ', LAST_NAME)) over(partition by ed.DEPT_NAME order by SALARY rows between 3 preceding and 3 following) rows3
from employees e 
join departments ed on e.DEPT_ID = ed.DEPT_ID

    /*     R e s u l t :
FIRST_NAME  LAST_NAME DEPT_NAME          SALARY UNBOUNDEDCOL          ROWS0                 ROWS1                 ROWS2                 ROWS3                
----------- --------- -------------- ---------- --------------------- --------------------- --------------------- --------------------- ---------------------
Luis        Popp      Finance              6900 Nancy Greenberg       Luis Popp             Ismael Sciarra        Jose Manuel Urman     John Chen            
Ismael      Sciarra   Finance              7700 Nancy Greenberg       Ismael Sciarra        Jose Manuel Urman     John Chen             Daniel Faviet        
Jose Manuel Urman     Finance              7800 Nancy Greenberg       Jose Manuel Urman     John Chen             Daniel Faviet         Nancy Greenberg      
John        Chen      Finance              8200 Nancy Greenberg       John Chen             Daniel Faviet         Nancy Greenberg       Nancy Greenberg      
Daniel      Faviet    Finance              9000 Nancy Greenberg       Daniel Faviet         Nancy Greenberg       Nancy Greenberg       Nancy Greenberg      
Nancy       Greenberg Finance             12000 Nancy Greenberg       Nancy Greenberg       Nancy Greenberg       Nancy Greenberg       Nancy Greenberg      
*/

    NOTE: I took just the Finance department as a sample - for multiple departments take care when making adjustments to your context.