Use window functions to select the value from a column based on the sum of another column, in an aggregate query

user_5141196 0
Viewed 43

Consider this data (View on DB Fiddle):

id dept value
1 A 5
1 A 5
1 B 7
1 C 5
2 A 5
2 A 5
2 B 15
2 A 2

The base query I am running is pretty simple. Just get the total value by id and the most frequent dept.

SELECT 
  id,
  MODE() WITHIN GROUP(ORDER BY dept) AS dept_freq,
  SUM(value) AS value
FROM test
GROUP BY id
;
id dept_freq value
1 A 22
2 A 27

But I also need to get, for each id, the dept that concentrates the greatest value (so the greatest sum of value by id and dept, not the highest individual value in the original table).

Is there any way to use window functions to achieve that and do it directly in the base query above?

The expected output for this particular example would be:

id dept_freq dept_value value
1 A A 22
2 A B 27

I could achieve that with the query below and then joining that with the results of the base query above

SELECT * FROM(
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY value DESC) as row
  FROM (
    SELECT id, dept, SUM(value) AS value
    FROM test
    GROUP BY id, dept
  ) AS alias1
) AS alias2
WHERE alias2.row = 1
;
id dept value row
1 A 10 1
2 B 15 1

But it is not easy to read/maintain and seems also pretty inefficient. So I thought it should be possible to achieve this using window functions directly in the base query, and that also may also help Postgres to come up with a better query plan that does less passes over the data. But none of my attempts using over partition and filter worked.

postgresql sql
1 Answers

step-by-step demo:db<>fiddle

You can fetch the dept for the highest values using the first_value() partition function. Adding this before your mode() grouping should do it:

SELECT
    id,
    highest_value_dept,
    MODE() WITHIN GROUP(ORDER BY dept) AS dept_freq,
    SUM(value) as value
FROM (
    SELECT
        id,
        dept,
        value,
        FIRST_VALUE(dept) OVER (PARTITION BY id ORDER BY value DESC) as highest_value_dept
    FROM test
) s
GROUP BY 1,2
user_39842210
Related