时间戳

首页 > 解决方案 > postgres 抓取前 N 个人口最多的组，并将其余的放在“其他”组中

sql - postgres 抓取前 N 个人口最多的组，并将其余的放在“其他”组中

问题描述

我有一个包含 、 和 列的表status，operator其中cost列status和operator是分类的，我想总结cost每个 ( status, operator) 对。通常我会用一个简单的语句来做到这一点，比如

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

但困难的部分是可能有 100 多个独特的运算符，我无法以有意义的方式为客户可视化。我想要做的只是显式显示前N许多operator类别（意味着在整个数据集中N具有最高的顶级运算符），然后将所有剩余的行分组到“其他”运算符中。SUM(cost)一种低效的方法如下：

-- 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;

虽然效率低下的方式有效，但在生产中它太慢了。实际上，这cost不是表中的简单列，而是由计算速度非常慢且表很大的子查询计算的，所以我不能使用CASE带有IN子句的语句。我宁愿以某种方式拥有完整的表，我在其中使用我在 FROM 子句子查询中首先列出的 GROUP BY 语句，然后将其聚合以获得前 Noperator个类别和一个“其他”类别。我试图用窗口函数来做到这一点，但我真的不明白它们是如何工作的，而且我找不到能得到正确答案的东西。如果有人可以提供帮助，将不胜感激。

编辑：该cost列不是实际的列。我应该更清楚。它是由一个非常昂贵的子查询计算的，所以我想尽可能cost少地计算原始表的每一行。

例子：

假设我们有一个看起来像这样的表：

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

如果我只想要前 3 个运算符（即 SUM(cost) 最高的三个运算符 - 在本例中为运算符 3、4、5），则查询应返回：

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

在此示例中，运算符 1-2 被汇总到“其他”运算符中，因为我们只希望明确给出前 3 个。因此，结果表中的第一个“其他”行将所有行求和，status=B并且operator不是前三个运算符之一。第二个“Other”行总结了所有行，status=A并且operator不是前三个运算符之一。

标签： sqlpostgresql

解决方案

我已将您的查询从子查询转换为加入。您可以Left join按如下方式使用：

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;

现在，回到我从描述中理解的内容。status如果有 N=12，您需要总共 13 行或更少的行（12 个运算符和 1 个其他） 。您可以row_number按如下方式使用窗口功能

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;

推荐阅读