Impala SQL如何按Agent分组取task_count排序的Top20任务？

这问题我之前也碰到过，LIMIT全局生效确实没法满足按组取Top N的需求，现在主流的解决办法是用窗口函数，不同数据库的实现大同小异，我给你分情况举例：

1. 支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等） #

核心思路是用ROW_NUMBER()函数，按agent分区（也就是每个Agent单独编号），然后按task_count排序，最后只保留行号≤20的记录。

假设你原来的查询是这样的：

SELECT 
  supervisor, 
  agent, 
  task, 
  COUNT(*) AS task_count
FROM your_table
-- 这里是你的WHERE条件
GROUP BY supervisor, agent, task
ORDER BY supervisor, agent, task_count DESC;

现在修改成按Agent取前20条：

WITH ranked_tasks AS (
  SELECT 
    supervisor, 
    agent, 
    task, 
    COUNT(*) AS task_count,
    -- 按agent分区，task_count降序排序，给每条记录编号
    ROW_NUMBER() OVER (PARTITION BY agent ORDER BY COUNT(*) DESC) AS rn
  FROM your_table
  -- 你的WHERE条件
  GROUP BY supervisor, agent, task
)
SELECT supervisor, agent, task, task_count
FROM ranked_tasks
WHERE rn <= 20
ORDER BY supervisor, agent, task_count DESC;

如果你的数据库不支持CTE（比如老版本MySQL），可以用子查询代替：

SELECT supervisor, agent, task, task_count
FROM (
  SELECT 
    supervisor, 
    agent, 
    task, 
    COUNT(*) AS task_count,
    ROW_NUMBER() OVER (PARTITION BY agent ORDER BY COUNT(*) DESC) AS rn
  FROM your_table
  -- 你的WHERE条件
  GROUP BY supervisor, agent, task
) AS ranked_tasks
WHERE rn <= 20
ORDER BY supervisor, agent, task_count DESC;

2. 老版本MySQL（低于8.0，不支持窗口函数） #

如果还在使用不支持窗口函数的老版本MySQL，可以用变量来实现分组编号：

SELECT 
  supervisor, 
  agent, 
  task, 
  task_count
FROM (
  SELECT 
    supervisor, 
    agent, 
    task, 
    task_count,
    @rn := IF(@current_agent = agent, @rn + 1, 1) AS rn,
    @current_agent := agent
  FROM (
    SELECT 
      supervisor, 
      agent, 
      task, 
      COUNT(*) AS task_count
    FROM your_table
    -- 你的WHERE条件
    GROUP BY supervisor, agent, task
    ORDER BY agent, task_count DESC
  ) AS task_counts,
  (SELECT @current_agent := '', @rn := 0) AS vars
) AS ranked_tasks
WHERE rn <= 20
ORDER BY supervisor, agent, task_count DESC;

额外注意点 #

• 如果存在task_count相同的情况，ROW_NUMBER()会随机给它们分配不同的编号；如果希望相同task_count的记录都被保留，可以改用RANK()或者DENSE_RANK()函数（比如两个task的count都是100，用RANK()的话它们的编号都是1，这样取前20可能会超过20条，根据你的实际需求选择）。
• 排序方向：示例里用的是ORDER BY COUNT(*) DESC，也就是取task_count最多的前20条；如果要取最少的，改成ASC即可。

内容的提问来源于stack exchange，提问作者chris