DB2 顶级分组

Question

我已经尝试了几个小时，但无法让查询使用 DB2 执行我想要的操作。 从表“公司和用户”中，我有以下每个公司/用户的门票数量信息，

user         company      quantity
------------ ------------ ------------
mark         nissan       300
tom          toyota       50
steve        krysler      80
mark         ford         20
tom          toyota       120
jose         toyota       230
tom          nissan       145
steve        toyota       10
jose         krysler      35
steve        ford         100

这是由查询生成的：

SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY FROM TICKET T
INNER JOIN COMPANY P ON P.COMPANY = T.COMPANY 
GROUP BY (T.USER, T.COMPANY) -- ORDER BY QUANTITY DESC

我想看到的是每个公司的顶级用户，因此鉴于上面的数据，查询应该向我显示：

user         company      quantity (Top user per company)
------------ ------------ --------------------------------
mark         nissan       300
jose         toyota       230
steve        ford         100
steve        krysler      80

如何我写SQL来返回这个结果？

---

最终答案（在评论中注明）：

SELECT user, quantity, company
  FROM (SELECT user, quantity, company,
               RANK () OVER (PARTITION BY company ORDER BY quantity DESC) AS r
          FROM (SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY
                  FROM TICKET T JOIN COMPANY P ON P.COMPANY = T.COMPANY
                 GROUP BY (T.USER, T.COMPANY) ) s ) t
 WHERE r = 1;

Answer 1

一步步把它建立起来。

查找每个公司的最大数量，假设问题中显示的第一个数据表称为“门票”：

SELECT Company, MAX(Quantity) AS MaxQuantity
  FROM Tickets
 GROUP BY Company;

现在，查找该公司具有该最大数量的用户的数据：

SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

如果特定公司的最大数量比如说，200，并且两个用户对该公司的得分都为 200，那么此查询会列出这两个用户。

现在，如果您的意思是问题中显示的查询生成第一个结果表，那么我上面所说的门票需要是派生表：

SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
  FROM Ticket AS T
 INNER JOIN Company AS P ON P.Company = T.Company 
 GROUP BY (T.User, T.Company)
 ORDER BY QUANTITY DESC 

在这种情况下，我们可以使用WITH子句（语法未检查，但我认为根据 SQL 标准，它是正确的）：

WITH Tickets AS
    (SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
       FROM Ticket AS T
       JOIN Company AS P ON P.Company = T.Company 
      GROUP BY (T.User, T.Company)
    )
SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

显然，如果您愿意，您也可以将 WITH 子查询写出两次。

Answer 2

这应该有效。创建派生视图来计算每个用户和每个公司的数量。然后获取数量的最大值，然后将最大值加入到数量的计算中。

SELECT p.company, 
       t.user, 
       t.quantity 
FROM   (SELECT MAX(t.quantity) max_quantity, 
               t.company 
        FROM   (SELECT  
                       COUNT(t.user) quantity, 
                       t.company 
                FROM   ticket t 
                GROUP  BY t.company) t) maxq 
       INNER JOIN (SELECT t.user, 
                          t.company, 
                          COUNT(t.user) quantity 
                   FROM   ticket t 
                   GROUP  BY t.company, 
                             t.user) t 
         ON maxq.max_quantity = t.quantity 
            AND maxq.company = t.company 
       INNER JOIN company p 
         ON p.company = t.company 
ORDER  BY t.quantity DESC 

可以在此处找到按 StackOverflow 数据标签显示排名靠前的用户的工作示例。