Двойная агрегатная функция Mysql

Question

Я хочу взять максимальное значение из серии возвращаемых значений, но я не могу найти простой способ сделать это. Мой запрос возвращает все строки, так что 1/2 пути туда. Я могу отфильтровать это с помощью PHP, но я бы хотел сделать все это на SQL. Я пытался с max подзапрос, но это вернуло все результаты до сих пор.

DDL:

create table matrix(
   count int(4), 
   date date, 
   product int(4)
);
create table products(
   id int(4), 
   section int(4)
);

DML:

select max(magic_count), section, id
from (
    select sum(count) as magic_count, p.section, p.id
    from matrix as m
    join products as p on m.product = p.id
    group by m.product
) as faketable
group by id, section

Демо с моей текущей попытки.

Только идентификаторы 1 а также 3 должны быть возвращены из выборочных данных, потому что они имеют самый высокий совокупный count для каждого из sections.

Вот вторая скрипта SQL, которая демонстрирует ту же проблему.

Answer 1

Ну вот:

select a.id, 
       a.section,
       a.magic_count
from (
    select p.id,
           p.section,
           magic_count
    from (
        select m.product, sum(count) as magic_count
        from matrix m
        group by m.product
    ) sm
    join products p on sm.product = p.id
) a
left join (
    select p.id,
           p.section,
           magic_count
    from (
        select m.product, sum(count) as magic_count
        from matrix m
        group by m.product
    ) sm
    join products p on sm.product = p.id
) b on a.section = b.section and a.magic_count < b.magic_count
where b.id is null

• см. упрощенный пример (и другие методы) в ручном вводе строк, содержащих групповой максимум определенного столбца.

• увидеть это работает вживую здесь

Answer 2

Здесь у вас есть решение без использования JOINс, он имеет лучшую производительность, чем другой ответ, который использует много JOINs:

select @rn := 1, @sectionLag := 0;

select id, section, count from (
    select id,
           case when @sectionLag = section then @rn := @rn + 1 else @rn := 1 end rn,
           @sectionLag := section,
           section, 
           count
    from (
        select id, section, sum(count) count
        from matrix m
        join products p on m.product = p.id
        group by id, section
    ) a order by section, count desc
) a where rn = 1

Переменные в начале используются для имитации оконных функций (LAG а также ROW_NUMBER), которые доступны в MySQL 8.0 или выше (если вы используете такую ​​версию, дайте мне знать, поэтому я дам вам решение также с оконными функциями).

DEMO

Еще одна демонстрация, где вы можете сравнить производительность моего и другого запроса. Он содержит ~20 тыс. Строк, и мой запрос работает почти в 2 раза быстрее.

Answer 3

Как насчет удаления идентификатора из второй группы?

select max(magic_count), section, id
from
(SELECT sum(count) as magic_count, p.section, p.id
FROM matrix as m
join products as p
on m.product = p.id
group by m.product) as faketable
group by section

Я получил его для запуска в вашей SQL Fiddle.

В качестве альтернативы, вы могли бы сделать сортировку desc для вашего первого запроса, а затем выполнить SELECT TOP 1 или эквивалент MySQL?