Простой запрос для получения максимального значения для каждого идентификатора

ОК, у меня есть такая таблица:

ID     Signal    Station    OwnerID
111     -120      Home       1
111     -130      Car        1
111     -135      Work       2
222     -98       Home       2
222     -95       Work       1
222     -103      Work       2

Это все на один и тот же день. Мне просто нужно запрос, чтобы вернуть максимальный сигнал для каждого идентификатора:

ID    Signal    Station    OwnerID
111   -120      Home        1
222   -95       Work        1

Я попытался использовать MAX(), и агрегация испортилась, когда Station и OwnerID были разными для каждой записи. Нужно ли мне присоединиться?

Источник

8 ответов

Решение

Что-то вроде этого? Соедините свою таблицу с самим собой и исключите строки, для которых был найден более высокий сигнал.

select cur.id, cur.signal, cur.station, cur.ownerid
from yourtable cur
where not exists (
    select * 
    from yourtable high 
    where high.id = cur.id 
    and high.signal > cur.signal
)

Это будет список одной строки для каждого самого высокого сигнала, так что может быть несколько строк на идентификатор.

Источник

Вы делаете групповую максимальную / минимальную операцию. Это обычная ловушка: кажется, что это должно быть легко сделать, но в SQL это не отягощает.

Существует несколько подходов (как стандартных ANSI, так и конкретных поставщиков) к этой проблеме, большинство из которых являются неоптимальными во многих ситуациях. Некоторые выдадут вам несколько строк, если несколько строк имеют одинаковое максимальное / минимальное значение; некоторые не будут. Некоторые хорошо работают на столах с небольшим количеством групп; другие более эффективны для большего числа групп с меньшими рядами на группу.

Вот обсуждение некоторых из них (MySQL-смещено, но обычно применимо). Лично, если я знаю, что нет множественных максимумов (или мне все равно, как их получить), я часто склоняюсь к методу null-left-self-join, который я опубликую, как никто другой еще:

SELECT reading.ID, reading.Signal, reading.Station, reading.OwnerID
FROM readings AS reading
LEFT JOIN readings AS highersignal
    ON highersignal.ID=reading.ID AND highersignal.Signal>reading.Signal
WHERE highersignal.ID IS NULL;
Источник

В классическом SQL-92 (без использования операций OLAP, используемых Quassnoi), вы можете использовать:

SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
  FROM (SELECT id, MAX(Signal) AS MaxSignal
          FROM t
          GROUP BY id) AS g
       JOIN t ON g.id = t.id AND g.MaxSignal = t.Signal;

(Непроверенный синтаксис; предполагается, что ваша таблица 't'.)

Подзапрос в предложении FROM определяет максимальное значение сигнала для каждого идентификатора; объединение объединяет это с соответствующей строкой данных из основной таблицы.

Примечание: если есть несколько записей для определенного идентификатора, которые имеют одинаковую мощность сигнала, и эта сила равна MAX(), то вы получите несколько выходных строк для этого идентификатора.

Протестировано на IBM Informix Dynamic Server 11.50.FC3, работающем на Solaris 10:

+ CREATE TEMP TABLE signal_info
(
    id      INTEGER NOT NULL,
    signal  INTEGER NOT NULL,
    station CHAR(5) NOT NULL,
    ownerid INTEGER NOT NULL
);
+ INSERT INTO signal_info VALUES(111, -120, 'Home', 1);
+ INSERT INTO signal_info VALUES(111, -130, 'Car' , 1);
+ INSERT INTO signal_info VALUES(111, -135, 'Work', 2);
+ INSERT INTO signal_info VALUES(222, -98 , 'Home', 2);
+ INSERT INTO signal_info VALUES(222, -95 , 'Work', 1);
+ INSERT INTO signal_info VALUES(222, -103, 'Work', 2);
+ SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
  FROM (SELECT id, MAX(Signal) AS MaxSignal
            FROM signal_info
            GROUP BY id) AS g
      JOIN signal_info AS t  ON g.id = t.id AND g.MaxSignal = t.Signal;

111     -120    Home    1
222     -95     Work    1

Я назвал таблицу Signal_Info для этого теста - но, похоже, дает правильный ответ. Это только показывает, что существует хотя бы одна СУБД, которая поддерживает обозначения. Тем не менее, я немного удивлен, что MS SQL Server нет - какую версию вы используете?

Меня не перестает удивлять, как часто вопросы SQL отправляются без имен таблиц.

Источник 
WITH q AS
         (
         SELECT  c.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signal DESC) rn
         FROM    mytable
         )
SELECT   *
FROM     q
WHERE    rn = 1

Это вернет одну строку, даже если есть дубликаты MAX(signal) для данного ID,

Наличие индекса на (id, signal) значительно улучшит этот запрос.

Источник 

with tab(id, sig, sta, oid) as
(
select 111 as id, -120 as signal, 'Home' as station, 1 as ownerId union all
select 111, -130, 'Car',  1 union all
select 111, -135, 'Work', 2 union all
select 222, -98, 'Home',  2 union all
select 222, -95, 'Work',  1 union all
select 222, -103, 'Work', 2
) ,
tabG(id, maxS) as
(
   select id, max(sig) as sig from tab group by id
)
select g.*, p.* from tabG g
cross apply ( select  top(1) * from tab t where t.id=g.id order by t.sig desc ) p
Источник

Мы можем сделать с помощью самостоятельного соединения

SELECT  T1.ID,T1.Signal,T2.Station,T2.OwnerID
FROM (select ID,max(Signal) as Signal from mytable group by ID) T1
LEFT JOIN mytable T2
ON T1.ID=T2.ID and T1.Signal=T2.Signal;

Или вы также можете использовать следующий запрос

SELECT t0.ID,t0.Signal,t0.Station,t0.OwnerID 
FROM mytable t0 
LEFT JOIN mytable t1 ON t0.ID=t1.ID AND t1.Signal>t0.Signal 
WHERE t1.ID IS NULL;
Источник

Выберите id, max_signal, owner, ownerId FROM (выберите *, rank() более (разделить по порядку идентификатора по сигналу desc) как max_signal из таблицы), где max_signal = 1;

Источник 
select a.id, b.signal, a.station, a.owner from 
mytable a
join 
(SELECT ID, MAX(Signal) as Signal FROM mytable GROUP BY ID) b
on a.id = b.id AND a.Signal = b.Signal
Источник 
SELECT * FROM StatusTable
ГДЕ сигнал ВХОД (ВЫБЕРИТЕ A.maxSignal ОТ
    (
        SELECT ID, MAX(Сигнал) AS maxSignal
        FROM StatusTable
        GROUP BY ID) КАК);
Источник
Другие вопросы по тегам