Applying Multiple Window Functions in 1 SELECT using different partition

Question

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

• Andriy M: A couple of things don't seem to make sense. 1. salary is part of the grouping criteria and yet you are aggregating it (SUM(salary) / SUM(SUM(salary)) OVER (...)). I think doing it like this would give you the same results: salary / SUM(salary) OVER (...) 2. What do you mean by PARTITION BY fiscal_year ORDER BY fiscal_year? There'll be just one fiscal_year per partition of fiscal_year. Perhaps you could elaborate a little on the logic you are trying to implement?

Answer 1

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?