SQL - 返回分区中的最大值

Question

我有以下表格 -

我的目标是按ID分组，返回具有最高“count”的公司/ID行。

因此，期望的输出应该像这样：

我的当前代码返回所有ID分区的计数，我只希望它返回具有最高计数的那个。

当前代码 -

select distinct Company, Id, count(*) over (partition by ID)
from table1
where company in ("Facebook","Apple")

我的输出：

- Void S

---

你可以将你所写的内容作为子查询使用，然后按公司进行分区以获取最大值。 - Barmar

你好，请问您能否发布一下这个的代码是什么样子的？@Barmar - Void S

嗨，@ThorstenKettner，不是的，应该考虑每个公司/ID。换句话说，它适用于每个公司/ID组合，然后返回出现次数更多的ID。 - Void S

顺便提一下：在SQL中，双引号用于表示名称/别名。对于字符串字面值，请使用单引号：where company in ('Facebook'，'Apple')。 - Thorsten Kettner

下次请不要在提问时上传代码/错误的图片。 - Thorsten Kettner

Answer 1

您的基本查询有误。您按ID进行分区，而不考虑公司，但在您的请求注释中，您澄清希望按ID和公司计数。这将需要：

select distinct company, id, count(*) over (partition by company, id)
from table1
where company in ('Facebook','Apple');

但是这个查询归结为一个简单的聚合操作，并不需要使用窗口函数。它只是对每一行进行计数，然后用DISTINCT去除重复项。由于DISTINCT是一个昂贵的操作，所以为什么不直接在第一次聚合时就将行聚合起来呢？

select company, id, count(*)
from table1
where company in ('Facebook','Apple')
group by company, id;

现在，您只想保留每个公司数量最高的行，这就是窗口函数发挥作用的地方：

select  company, id, total
from
(
  select
    company,
    id,
    count(*) as total,
    max(count(*)) over (partition by company) as max_total
  from table1
  where company in ('Facebook','Apple')
  group by company, id
) aggregated
where total = max_total;

- Thorsten Kettner

Answer 2

我们可以在聚合查询中使用ROW_NUMBER：

WITH cte AS (
    SELECT Company, ID, COUNT(*) AS Count,
           ROW_NUMBER() OVER (PARTITION BY Company ORDER BY COUNT(*) DESC) rn
    FROM table1
    GROUP BY Company, ID
)

SELECT Company, ID, Count
FROM cte
WHERE rn = 1;

这是一个关于MySQL的运行演示。

- Tim Biegeleisen

---

是的，令人印象深刻。这甚至可以算是心灵读取，因为您要求获取具有最高总计数的ID，并添加了一个查询来支持此语句，而实际上您想要的是每个公司计数最高的ID，这一点在Tim编写此查询之后在请求注释中得到了澄清 :-) - Thorsten Kettner

Answer 3

SELECT company, id, COUNT(*)
  FROM table1
 GROUP BY EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
HAVING COUNT(*) > 1;

- PRIYABRATA BEHERA

---

你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community

此查询未报告具有最高计数的公司/ID，但包括所有计数大于一的公司/ID。这不是所期望的结果。 - Thorsten Kettner