Выберите несколько столбцов (неагрегатная функция) с помощью GROUP BY

Я пытаюсь выбрать максимальное значение из одного столбца, тогда как группировка с помощью другого не уникального столбца id, который имеет несколько повторяющихся значений. Оригинальная база данных выглядит примерно так:

mukey    | comppct_r | name | type
65789    | 20        | a    | 7n
65789    | 15        | b    | 8m
65789    | 1         | c    | 1o
65790    | 10        | a    | 7n
65790    | 26        | b    | 8m
65790    | 5         | c    | 1o
...

Это работает отлично, используя:

SELECT c.mukey, Max(c.comppct_r) AS ComponentPercent
FROM c
GROUP BY c.mukey;

Возвращает таблицу типа:

mukey    | ComponentPercent
65789    | 20
65790    | 26
65791    | 50
65792    | 90

Я хочу иметь возможность добавлять другие столбцы, не затрагивая функцию GROUP BY, для включения столбцов типа name и type в таблицу вывода, например:

mukey    | comppct_r | name | type
65789    | 20        | a    | 7n
65790    | 26        | b    | 8m
65791    | 50        | c    | 7n
65792    | 90        | d    | 7n

но он всегда выводит ошибку, говоря, что мне нужно использовать агрегированную функцию с помощью оператора select. Как мне это сделать?

Ответ 1

У вас есть greatest-n-per-group, Это одно из возможных решений:

select c.mukey, c.comppct_r, c.name, c.type
from c yt
inner join(
    select c.mukey, max(c.comppct_r) comppct_r
    from c
    group by c.mukey
) ss on c.mukey = ss.mukey and c.comppct_r= ss.comppct_r

Другой возможный подход, тот же вывод:

select c1.*
from c c1
left outer join c c2
on (c1.mukey = c2.mukey and c1.comppct_r < c2.comppct_r)
where c2.mukey is null;

Здесь содержится исчерпывающий и пояснительный ответ на этот вопрос: SQL Выберите только строки с максимальным значением в столбце

Ответ 2

В столбце Group By должен содержаться любой неагрегатный столбец. Почему?

                  t1
x1           y1           z1
1            2             5
2            2             7

Теперь вы пытаетесь написать запрос типа:

select x1,y1,max(z1) from t1 group by y1;

Теперь этот запрос приведет только к одной строке, но каково должно быть значение x1?? Это в основном поведение undefined. Чтобы преодолеть это, SQL будет обходить этот запрос.

Теперь, приблизившись к точке, вы можете либо выбрать агрегатную функцию для x1, либо добавить x1 в группу. Обратите внимание, что все это зависит от вашего требования.

Если вы хотите, чтобы все строки с агрегацией в группе z1 группировались по y1, вы можете использовать подход SubQ.

Select x1,y1,(select max(z1) from t1  where tt.y1=y1 group by y1)
 from t1 tt;

Это приведет к результату, например:

                  t1
x1           y1           max(z1)
1            2             7
2            2             7

Ответ 3

Попробуйте использовать виртуальную таблицу следующим образом:

SELECT vt.*,c.name FROM(
SELECT c.mukey, Max(c.comppct_r) AS ComponentPercent
FROM c
GROUP BY c.muke;
) as VT, c 
WHERE VT.mukey = c.mukey

Ответ 4

Вы не можете просто добавлять дополнительные столбцы, не добавляя их в GROUP BY или применяя функцию агрегации. Причиной этого является то, что значения столбца могут быть разными внутри одной группы. Например, у вас может быть две строки:

mukey    | comppct_r | name | type
65789    | 20        | a    | 7n
65789    | 20        | b    | 9f

Как должна выглядеть агрегированная группа для столбцов name и type?

Если имя и тип всегда одинаковы внутри группы, просто добавьте его в предложение GROUP BY:

SELECT c.mukey, Max(c.comppct_r) AS ComponentPercent
FROM c
GROUP BY c.muke, c.name, c.type;