Top Group по 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 

Рабочий образец, который показывает топ пользователей по тегу для данных Stackru, можно найти здесь.