Sql-server – Applying Multiple Window Functions in 1 SELECT using different partition

running-totalssql serverwindow functions

I was wondering if there is anything wrong with using this method.

I have a working demo of what i'm trying to do below

In the SELECT statement I have used 2 OVER clauses.

Is there any risk that the data could get out of sequence?

CREATE TABLE basic_pays (
    employee_id int,
    fiscal_year INT,
    salary DECIMAL(10 , 2 ),
    PRIMARY KEY (employee_id, fiscal_year)
);

INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2017,24000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2017,17000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2017,18000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2017,9000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2018,25920);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2018,18190);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2018,18360);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2020,26179.2);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2020,19463.3);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2020,19278);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2020,10206);

SELECT 
    employee_id, 
    fiscal_year,
    salary, 
    LAG(salary) 
        OVER (PARTITION BY employee_id ORDER BY fiscal_year) previous_salary,
    SUM(salary) /SUM(SUM(salary))
        OVER (PARTITION BY fiscal_year ORDER BY fiscal_year) salary_percentage
FROM
    basic_pays
GROUP BY 
    employee_id, fiscal_year, salary
ORDER BY fiscal_year, employee_id

DROP TABLE basic_pays

Best Answer

That should be fine.

Your first column (LAG(salary)) will be calculated over the partition you specified, which the second column will be calculated over the second partition.

Note that each partition specifies its own ORDER BY clause - so your windowed functions are calculated based on your row's position within the partition and its own order by clause.

I can't see any issues with this.

When you run it, do you get the results you expect?

Related Question