mysql - with - sql select only one row per group




SQL selecionar apenas linhas com valor máximo em uma coluna (20)

Eu tenho esta tabela para documentos (versão simplificada aqui):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Como faço para selecionar uma linha por id e apenas a maior rev?
Com os dados acima, o resultado deve conter duas linhas: [1, 3, ...] e [2, 1, ..] . Eu estou usando o MySQL .

Atualmente eu uso cheques no loop while para detectar e sobrescrever rotações antigas do conjunto de resultados. Mas este é o único método para alcançar o resultado? Não há uma solução SQL ?

Atualizar
Como as respostas sugerem, há uma solução SQL e aqui uma demo sqlfiddle .

Atualização 2
Notei que depois de adicionar o sqlfiddle acima, a taxa em que a pergunta é votada ultrapassou a taxa de votos das respostas. Essa não foi a intenção! O violino é baseado nas respostas, especialmente na resposta aceita.


À primeira vista...

Tudo o que você precisa é de uma cláusula GROUP BY com a função de agregação MAX :

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Nunca é tão simples assim, é?

Eu só notei que você precisa da coluna de content também.

Essa é uma pergunta muito comum no SQL: encontre todos os dados para a linha com algum valor máximo em uma coluna por algum identificador de grupo. Eu ouvi muito isso durante a minha carreira. Na verdade, foi uma das perguntas que eu respondi na entrevista técnica do meu trabalho atual.

Na verdade, é tão comum que a comunidade tenha criado uma única tag apenas para lidar com questões como: greatest-n-per-group .

Basicamente, você tem duas abordagens para resolver esse problema:

Juntando group-identifier, max-value-in-group simples group-identifier, max-value-in-group

Nessa abordagem, você primeiro encontra o group-identifier, max-value-in-group (já resolvido acima) em uma subconsulta. Em seguida, você une sua tabela à subconsulta com igualdade no group-identifier e max-value-in-group :

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Esquerda Unindo-se a si mesmo, aprimorando as condições e os filtros da junção

Nesta abordagem, você saiu da mesa com ela mesma. A igualdade, claro, entra no group-identifier . Então, 2 movimentos inteligentes:

  1. A segunda condição de junção é ter valor do lado esquerdo menor que o valor correto
  2. Quando você faz o passo 1, a (s) linha (s) que realmente tem o valor máximo terá NULL no lado direito (é um LEFT JOIN , lembra?). Em seguida, filtramos o resultado associado, mostrando apenas as linhas em que o lado direito é NULL .

Então você acaba com:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusão

Ambas as abordagens trazem exatamente o mesmo resultado.

Se você tiver duas linhas com max-value-in-group para o group-identifier , ambas as linhas estarão no resultado nas duas abordagens.

Ambas as abordagens são compatíveis com SQL ANSI, portanto, funcionarão com seu RDBMS favorito, independentemente de seu "sabor".

Ambas as abordagens também são compatíveis com o desempenho, mas sua quilometragem pode variar (RDBMS, Estrutura de BD, Índices, etc.). Então, quando você escolhe uma abordagem sobre a outra, benchmark . E certifique-se de escolher o que faz mais sentido para você.


Ainda outra solução é usar uma subconsulta correlacionada:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Ter um índice em (id, rev) renderiza a subconsulta quase como uma simples pesquisa ...

A seguir estão as comparações com as soluções na resposta do @AdrianCarneiro (subquery, leftjoin), baseadas em medições do MySQL com tabela InnoDB de ~ 1 milhão de registros, sendo o tamanho do grupo: 1-3.

Enquanto que para varreduras de tabelas completas, os tempos de subconsulta / leftjoin / correlated se relacionam como 6/8/9, quando se trata de pesquisas diretas ou batch ( id in (1,2,3) ), a subconsulta é muito mais lenta que as outras ( Devido a executar novamente a subconsulta). No entanto, não consegui diferenciar entre soluções esquerdas e correlacionadas em velocidade.

Uma nota final, como leftjoin cria n * (n + 1) / 2 joins em grupos, seu desempenho pode ser fortemente afetado pelo tamanho dos grupos ...


Aqui está outra solução para recuperar os registros somente com um campo que tenha o valor máximo para esse campo. Isso funciona para o SQL400, que é a plataforma em que trabalho. Neste exemplo, os registros com o valor máximo no campo FIELD5 serão recuperados pela seguinte instrução SQL.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

Aqui está uma boa maneira de fazer isso

Use o seguinte código:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

Esta solução faz apenas uma seleção da YourTable, portanto, é mais rápida. Ele funciona apenas para MySQL e SQLite (para SQLite remove DESC) de acordo com o teste em sqlfiddle.com. Talvez isso possa ser ajustado para trabalhar em outros idiomas com os quais não estou familiarizado.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

Eu acho que esta é a solução mais fácil:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: retorna todos os campos.
  • FROM Employee: Tabela pesquisada.
  • (SELECT * ...) subconsulta: Retorna todas as pessoas, classificadas por Salário.
  • GROUP BY employeesub.Salary:: Força a linha Salary top-sorted, de cada funcionário a ser o resultado retornado.

Se você precisar de apenas uma linha, é ainda mais fácil:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Eu também acho que é o mais fácil de quebrar, entender e modificar para outros fins:

  • ORDER BY Employee.Salary DESC: Ordene os resultados pelo salário, com os salários mais altos primeiro.
  • LIMITE 1: Retorna apenas um resultado.

Entendendo essa abordagem, resolver qualquer um desses problemas semelhantes torna-se trivial: contratar funcionários com salários mais baixos (mudar DESC para ASC), obter funcionários com mais de 10 anos (alterar LIMITE 1 para LIMIT 10), classificar por outro campo (alterar ORDER BY Employee.Salary para ORDER BY Employee.Commission), etc.


Eu gosto de fazer isso, classificando os registros por alguma coluna. Nesse caso, classifique os valores de rev agrupados por id . Aqueles com maior rev terão rankings mais baixos. Então, a maior rev terá classificação de 1.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Não tenho certeza se a introdução de variáveis ​​torna a coisa toda mais lenta. Mas pelo menos eu não estou consultando o YOURTABLE duas vezes.


Eu gosto de usar uma solução baseada em NOT EXIST para este problema:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

Eu usaria isso:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Subconsulta SELECT não é muito eficiente talvez, mas na cláusula JOIN parece ser utilizável. Eu não sou um especialista em otimizar consultas, mas eu tentei no MySQL, PostgreSQL, FireBird e funciona muito bem.

Você pode usar esse esquema em várias associações e com a cláusula WHERE. É o meu exemplo de trabalho (resolvendo idêntico ao seu problema com a tabela "firmy"):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

É perguntado sobre tabelas tendo teens e registros, e leva menos de 0,01 segundo na máquina não muito forte.

Eu não usaria a cláusula IN (como é mencionado em algum lugar acima). IN é dado para usar com listas curtas de constantes, e não como sendo o filtro de consulta construído na subconsulta. É porque a subconsulta em IN é executada para cada registro varrido, o que pode fazer com que a consulta demore muito tempo.


Eu usei o abaixo para resolver um problema meu. Primeiro, criei uma tabela temporária e inseri o valor máximo de rev por ID exclusivo.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

Em seguida, juntei esses valores máximos (# temp1) a todas as possíveis combinações de ID / conteúdo. Ao fazer isso, eu naturalmente filtro as combinações de ID / conteúdo não máximo, e fico com os únicos valores máximos de rev para cada um.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

Nenhuma dessas respostas funcionou para mim.

Isto é o que funcionou para mim.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Ordenou o campo rev em ordem inversa e depois agrupou por id, o que deu a primeira linha de cada agrupamento que é aquele com o maior valor de rev.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Testado em http://sqlfiddle.com/ com os seguintes dados

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

Isso deu o seguinte resultado no MySQL 5.5 e 5.6

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

Que tal agora:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

SELECT * FROM Employee onde Employee.Salary in (selecione max (salário) do grupo Employee por Employe_id) ORDER BY Employee.Salary


Se você tiver muitos campos na instrução select e desejar o valor mais recente para todos esses campos por meio do código otimizado:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

Uma terceira solução que dificilmente vejo mencionada é específica do MySQL e se parece com isso:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Sim, parece horrível (convertendo para string e para trás, etc.), mas na minha experiência, geralmente é mais rápido que as outras soluções. Talvez isso apenas para os meus casos de uso, mas eu usei em tabelas com milhões de registros e muitos ids exclusivos. Talvez seja porque o MySQL é muito ruim em otimizar as outras soluções (pelo menos nos 5.0 dias em que surgiu essa solução).

Uma coisa importante é que o GROUP_CONCAT tem um tamanho máximo para a string que pode ser criada. Você provavelmente desejará aumentar esse limite configurando a variável group_concat_max_len . E tenha em mente que isso será um limite no dimensionamento se você tiver um grande número de linhas.

De qualquer forma, o acima não funciona diretamente se o seu campo de conteúdo já é texto. Nesse caso, você provavelmente desejará usar um separador diferente, como \ 0 talvez. Você também vai correr no limite group_concat_max_len mais rápido.


aqui está outra solução espero que ajude alguém

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

NÃO é mySQL , mas para outras pessoas que estão encontrando esta questão e usando SQL, outra maneira de resolver o greatest-n-per-group é usando o Cross Apply no MS SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Aqui está um exemplo no SqlFiddle


SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);






greatest-n-per-group