Usando LIMIT dentro do GROUP BY para obter N resultados por grupo?

A consulta a seguir:

SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC 

rendimentos:

 year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 3.9 2004 p01 3.5 2005 p01 2.1 2000 p01 0.8 2001 p02 12.5 2004 p02 12.4 2002 p02 12.2 2003 p02 10.3 2000 p02 8.7 2006 p02 4.6 2007 p02 3.3 

O que eu gostaria é apenas os 5 melhores resultados para cada id:

 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2001 p02 12.5 2004 p02 12.4 2002 p02 12.2 2003 p02 10.3 2000 p02 8.7 

Existe uma maneira de fazer isso usando algum tipo de modificador LIMIT que funcione dentro do GROUP BY?

Você poderia usar a function agregada GROUP_CONCAT para obter todos os anos em uma única coluna, agrupados por id e ordenados por rate :

 SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id 

Resultado:

 ----------------------------------------------------------- | ID | GROUPED_YEAR | ----------------------------------------------------------- | p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 | | p02 | 2001,2004,2002,2003,2000,2006,2007 | ----------------------------------------------------------- 

E então você poderia usar FIND_IN_SET , que retorna a posição do primeiro argumento dentro do segundo, por exemplo.

 SELECT FIND_IN_SET('2006', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000'); 1 SELECT FIND_IN_SET('2009', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000'); 6 

Usando uma combinação de GROUP_CONCAT e FIND_IN_SET e filtrando pela posição retornada por find_in_set, você poderia usar essa consulta que retorna apenas os primeiros 5 anos para cada id:

 SELECT yourtable.* FROM yourtable INNER JOIN ( SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id) group_max ON yourtable.id = group_max.id AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5 ORDER BY yourtable.id, yourtable.year DESC; 

Por favor, veja o violino aqui .

Por favor, note que se mais de uma linha pode ter a mesma taxa, você deve considerar o uso de GROUP_CONCAT (taxa DISTINCT ORDER BY) na coluna de taxa em vez da coluna de ano.

O comprimento máximo da string retornada por GROUP_CONCAT é limitado, portanto, isso funciona bem se você precisar selecionar alguns registros para cada grupo.

A consulta original usava variables ​​de usuário e ORDER BY em tabelas derivadas; o comportamento de ambas as peculiaridades não é garantido. Resposta revisada da seguinte forma.

Você pode usar a sorting do pobre homem sobre a partição para obter o resultado desejado. Apenas outer associa a tabela a si mesma e, para cada linha, conta o número de linhas abaixo dela:

 SELECT testdata.id, testdata.rate, testdata.year, COUNT(lesser.rate) AS rank FROM testdata LEFT JOIN testdata AS lesser ON testdata.id = lesser.id AND testdata.rate < lesser.rate GROUP BY testdata.id, testdata.rate, testdata.year HAVING COUNT(lesser.rate) < 5 ORDER BY testdata.id, testdata.rate DESC 

Observe que:

  1. COUNT é baseado em zero
  2. Para classificar descendente, a linha menor é aquela com taxa mais alta
  3. Todas as linhas que ligam para o último lugar são retornadas

Resultado:

 +------+-------+------+------+ | id | rate | year | rank | +------+-------+------+------+ | p01 | 8.00 | 2006 | 0 | | p01 | 7.40 | 2003 | 1 | | p01 | 6.80 | 2008 | 2 | | p01 | 5.90 | 2001 | 3 | | p01 | 5.30 | 2007 | 4 | | p02 | 12.50 | 2001 | 0 | | p02 | 12.40 | 2004 | 1 | | p02 | 12.20 | 2002 | 2 | | p02 | 10.30 | 2003 | 3 | | p02 | 8.70 | 2000 | 4 | +------+-------+------+------+ 

Para mim algo como

 SUBSTRING_INDEX(group_concat(col_name order by desired_col_order_name), ',', N) 

funciona perfeitamente. Nenhuma consulta complicada.


por exemplo: consiga o top 1 para cada grupo

 SELECT * FROM yourtable WHERE id IN (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY rate DESC), ',', 1) id FROM yourtable GROUP BY year) ORDER BY rate DESC; 

Não, você não pode LIMIT subqueries arbitrariamente (você pode fazê-lo de forma limitada em novos MySQLs, mas não em 5 resultados por grupo).

Esta é uma consulta do tipo groupwise-maximum, que não é trivial no SQL. Existem várias maneiras de lidar com o que pode ser mais eficiente em alguns casos, mas para o topo da sorting em geral, você deve examinar a resposta de Bill a uma pergunta anterior semelhante.

Como na maioria das soluções para esse problema, ele pode retornar mais de cinco linhas se houver várias linhas com o mesmo valor de rate , portanto, você ainda precisará de uma quantidade de pós-processamento para verificar isso.

Tente isto:

 SELECT h.year, h.id, h.rate FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx FROM (SELECT h.year, h.id, h.rate FROM h WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, h.year ORDER BY id, rate DESC ) h, (SELECT @lastid:='', @index:=0) AS a ) h WHERE h.indx < = 5; 

Isso requer uma série de subconsultas para classificar os valores, limitá-los e, em seguida, executar a sum ao agrupar

 @Rnk:=0; @N:=2; select c.id, sum(c.val) from ( select b.id, b.bal from ( select if(@last_id=id,@Rnk+1,1) as Rnk, a.id, a.val, @last_id=id, from ( select id, val from list order by id,val desc) as a) as b where b.rnk < @N) as c group by c.id; 

Construa as colunas virtuais (como RowID no Oracle)

mesa:

 ` CREATE TABLE `stack` (`year` int(11) DEFAULT NULL, `id` varchar(10) DEFAULT NULL, `rate` float DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ` 

dados:

 insert into stack values(2006,'p01',8); insert into stack values(2001,'p01',5.9); insert into stack values(2007,'p01',5.3); insert into stack values(2009,'p01',4.4); insert into stack values(2001,'p02',12.5); insert into stack values(2004,'p02',12.4); insert into stack values(2005,'p01',2.1); insert into stack values(2000,'p01',0.8); insert into stack values(2002,'p02',12.2); insert into stack values(2002,'p01',3.9); insert into stack values(2004,'p01',3.5); insert into stack values(2003,'p02',10.3); insert into stack values(2000,'p02',8.7); insert into stack values(2006,'p02',4.6); insert into stack values(2007,'p02',3.3); insert into stack values(2003,'p01',7.4); insert into stack values(2008,'p01',6.8); 

SQL assim:

 select t3.year,t3.id,t3.rate from (select t1.*, (select count(*) from stack t2 where t1.rate< =t2.rate and t1.id=t2.id) as rownum from stack t1) t3 where rownum <=3 order by id,rate DESC; 

se deletar a cláusula where em t3, será mostrado assim:

insira a descrição da imagem aqui

OBTER "TOP N Record" -> adicione o "rownum < = 3" na cláusula where (a cláusula where de t3);

ESCOLHA "o ano" -> acrescente o "ENTRE 2000 E 2009" na cláusula where (a cláusula where de t3);

Levou algum trabalho, mas eu acho que a minha solução seria algo para compartilhar, pois parece elegante, bem como muito rápido.

 SELECT h.year, h.id, h.rate FROM ( SELECT id, SUBSTRING_INDEX(GROUP_CONCAT(CONCAT(id, '-', year) ORDER BY rate DESC), ',' , 5) AS l FROM h WHERE year BETWEEN 2000 AND 2009 GROUP BY id ORDER BY id ) AS h_temp LEFT JOIN h ON h.id = h_temp.id AND SUBSTRING_INDEX(h_temp.l, CONCAT(h.id, '-', h.year), 1) != h_temp.l 

Note que este exemplo é especificado para o propósito da questão e pode ser modificado facilmente para outros propósitos similares.

O seguinte post: sql: selcting top N registro por grupo descreve a maneira complicada de conseguir isso sem subconsultas.

Melhora em outras soluções oferecidas aqui por:

  • Fazendo tudo em uma única consulta
  • Ser capaz de utilizar adequadamente os índices
  • Evitando subconsultas, notoriamente conhecido por produzir planos de execução incorretos no MySQL

No entanto, não é bonito. Uma boa solução seria alcançável se as Funções de Janela (Funções Analíticas) estivessem habilitadas no MySQL – mas elas não são. O truque usado na dita postagem utiliza o GROUP_CONCAT, que às vezes é descrito como “Funções do Windows do pobre para o MySQL”.

 SELECT year, id, rate FROM (SELECT year, id, rate, row_number() over (partition by id order by rate DESC) FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC) as subquery WHERE row_number < = 5 

A subconsulta é quase idêntica à sua consulta. Apenas a mudança está adicionando

 row_number() over (partition by id order by rate DESC) 

para aqueles que, como eu, tiveram consultas em tempo limite. Eu fiz o abaixo para usar limites e qualquer outra coisa por um grupo específico.

 DELIMITER $$ CREATE PROCEDURE count_limit200() BEGIN DECLARE a INT Default 0; DECLARE stop_loop INT Default 0; DECLARE domain_val VARCHAR(250); DECLARE domain_list CURSOR FOR SELECT DISTINCT domain FROM db.one; OPEN domain_list; SELECT COUNT(DISTINCT(domain)) INTO stop_loop FROM db.one; -- BEGIN LOOP loop_thru_domains: LOOP FETCH domain_list INTO domain_val; SET a=a+1; INSERT INTO db.two(book,artist,title,title_count,last_updated) SELECT * FROM ( SELECT book,artist,title,COUNT(ObjectKey) AS titleCount, NOW() FROM db.one WHERE book = domain_val GROUP BY artist,title ORDER BY book,titleCount DESC LIMIT 200 ) a ON DUPLICATE KEY UPDATE title_count = titleCount, last_updated = NOW(); IF a = stop_loop THEN LEAVE loop_thru_domain; END IF; END LOOP loop_thru_domain; END $$ 

ele percorre uma lista de domínios e, em seguida, insere apenas um limite de 200 cada

Tente isto:

 SET @num := 0, @type := ''; SELECT `year`, `id`, `rate`, @num := if(@type = `id`, @num + 1, 1) AS `row_number`, @type := `id` AS `dummy` FROM ( SELECT * FROM `h` WHERE ( `year` BETWEEN '2000' AND '2009' AND `id` IN (SELECT `rid` FROM `table2`) AS `temp_rid` ) ORDER BY `id` ) AS `temph` GROUP BY `year`, `id`, `rate` HAVING `row_number`< ='5' ORDER BY `id`, `rate DESC; 

Por favor, tente abaixo o procedimento armazenado. Eu já verifiquei. Estou obtendo resultado adequado, mas sem usar groupby .

 CREATE DEFINER=`ks_root`@`%` PROCEDURE `first_five_record_per_id`() BEGIN DECLARE query_string text; DECLARE datasource1 varchar(24); DECLARE done INT DEFAULT 0; DECLARE tenants varchar(50); DECLARE cur1 CURSOR FOR SELECT rid FROM demo1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET @query_string=''; OPEN cur1; read_loop: LOOP FETCH cur1 INTO tenants ; IF done THEN LEAVE read_loop; END IF; SET @datasource1 = tenants; SET @query_string = concat(@query_string,'(select * from demo where `id` = ''',@datasource1,''' order by rate desc LIMIT 5) UNION ALL '); END LOOP; close cur1; SET @query_string = TRIM(TRAILING 'UNION ALL' FROM TRIM(@query_string)); select @query_string; PREPARE stmt FROM @query_string; EXECUTE stmt; DEALLOCATE PREPARE stmt; END