postgres to grab the first N most populous groups and put the rest in an "Other" group

user_13176386 0
Viewed 142

I have a table with the columns status, operator, and cost, where the columns status and operator are categorical and I want to sum up the cost per (status, operator) pair. Usually I would do this with a simple statement like

SELECT SUM(cost), status, operator FROM my_table GROUP BY status, operator;

but the hard part is there could be 100s of unique operators which I can't visualize for the client in a meaningful way. What I want to be able to do is only explicitly show the top N many operator categories (meaning to top N operators that have the highest SUM(cost) across the entire dataset) and then group all of the remaining rows in an "Other" operator. An inefficient way to do this would be the following:

-- letting N = 12
SELECT
  SUM(cost),
  status,
  CASE
    WHEN operator IN (
      SELECT t.operator
      FROM my_table AS t
      GROUP BY t.operator ORDER BY SUM(t.cost) DESC
      LIMIT 12
    ) THEN operator
    ELSE 'Other'
  END AS operator
FROM my_table
GROUP BY
  status,
  CASE 
    WHEN operator IN (
      SELECT t.operator
      FROM my_table AS t
      GROUP BY t.operator ORDER BY SUM(t.cost) DESC
      LIMIT 12
    ) THEN operator 
    ELSE 'Other'
  END;

While the inefficient way works, in production it is far too slow. In actuality, the cost is not a simple column in a table but is computed by a subquery that is very slow to compute and the table is large, so I can't afford to use the CASE statement with an IN clause. What I would rather do is somehow have the full table where I use the GROUP BY statement I listed first in a FROM-clause subquery, then aggregate that to get the top N operator categories and an "Other" category. I tried to do this with window functions but I don't really understand how those work and I could not find something that got the right answer. If somebody could help it would be greatly appreciated.

EDIT: The cost column is not an actual column. I should have been more clear. It's computed by a very expensive subquery so I want to compute the cost for each row of the original table as few times as possible.

Example:

Say we have a table that looks like:

pk  |  status   |     operator      |        cost         
----+-----------+-------------------+----------------------
 1  |    A      |       op_1        |         1
 2  |    A      |       op_1        |         5
 3  |    A      |       op_1        |         3
 4  |    A      |       op_1        |         7
 5  |    B      |       op_2        |         10
 6  |    B      |       op_2        |         15
 7  |    A      |       op_3        |         100
 8  |    A      |       op_4        |         1000
 9  |    B      |       op_5        |         12000
 10 |    A      |       op_5        |         10200
 11 |    B      |       op_5        |         10020

If I only want the top 3 operators (meaning the three operators with the highest SUM(cost) - in this case operators 3, 4, 5), the query should return:

  status   |     operator      |        cost         
-----------+-------------------+----------------------
    B      |       op_5        |         32220
    A      |       op_4        |         1000
    A      |       op_3        |         100
    B      |       Other       |         25
    A      |       Other       |         16

In this example, operators 1-2 get rolled up into the "Other" operator, since we only want the top 3 given explicitly. So the first "Other" row in the result table sums all rows where status=B and operator is not one of the top three operators. The second "Other" row sums up all the rows where status=A and operator is not one of the top three operators.

postgresql sql
1 Answers

I have converted your query from sub-query to join. You can use Left join as follows:

SELECT
  SUM(cost),
  status,
  CASE
    WHEN tt.operator is not null 
    THEN tt.operator
    ELSE 'Other'
  END AS operator
FROM my_table t
LEFT JOIN (
      SELECT t.operator FROM my_table AS t 
      GROUP BY t.operator
      ORDER BY SUM(t.cost) DESC LIMIT 12 ) tt
On t.operator = tt.operator
GROUP BY
  status,
  CASE
    WHEN tt.operator is not null 
    THEN tt.operator
    ELSE 'Other'
  END;

Now, comming to what I understood from description. You want total 13 or less rows(12 operator and 1 other) for particular status if there is N=12. You can use row_number window function as follows

SELECT SUM(cost), 
       status,
       Operator
From
(SELECT SUM(cost), 
       status, 
       Case when Row_number() over (partition by stqtus order by sum(cost) desc) <= 12 
           then operator
           else 'Others'
       end as operator
FROM my_table 
GROUP BY status, operator) t
GROUP BY status, operator;
user_115656290
Related