Utilizzare LIMIT in GROUP BY per ottenere N risultati per gruppo?

La seguente domanda:

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 

rendimenti:

 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 

Quello che mi piacerebbe sono solo i primi 5 risultati per ogni 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 

C’è un modo per farlo usando qualche tipo di LIMIT come modificatore che funziona all’interno di GROUP BY?

Puoi utilizzare la funzione aggregata GROUP_CONCAT per ottenere tutti gli anni in una singola colonna, raggruppata per id e ordinata per rate :

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

Risultato:

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

E poi potresti usare FIND_IN_SET , che restituisce la posizione del primo argomento all’interno del secondo, es.

 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 

Utilizzando una combinazione di GROUP_CONCAT e FIND_IN_SET e filtrando in base alla posizione restituita da find_in_set, è ansible utilizzare questa query che restituisce solo i primi 5 anni per ogni 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; 

Per favore, guarda qui .

Tieni presente che se più di una riga può avere la stessa velocità, dovresti prendere in considerazione l’utilizzo di GROUP_CONCAT (tasso DISTINCT ORDER BY rate) sulla colonna della tariffa anziché della colonna dell’anno.

La lunghezza massima della stringa restituita da GROUP_CONCAT è limitata, quindi funziona bene se è necessario selezionare alcuni record per ogni gruppo.

La query originale utilizzava variabili utente e ORDER BY su tabelle derivate; il comportamento di entrambi i quirks non è garantito. Risposta modificata come segue.

È ansible utilizzare il grado inferiore della partizione del povero per ottenere il risultato desiderato. Solo l’esterno si unisce alla tabella con se stesso e per ogni riga, conta il numero di righe minore di quello:

 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 

Nota che:

  1. COUNT è a base zero
  2. Per la classificazione decrescente, la riga minore è quella con una velocità maggiore
  3. Vengono restituite tutte le righe che vincolano per l'ultimo posto

Risultato:

 +------+-------+------+------+ | 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 | +------+-------+------+------+ 

Per me qualcosa di simile

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

funziona perfettamente Nessuna query complicata.


per esempio: ottieni il primo posto per ogni gruppo

 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; 

No, non è ansible LIMITARE le subquery arbitrariamente (è ansible farlo in misura limitata nei nuovi MySQL, ma non per 5 risultati per gruppo).

Questa è una query di tipo groupwise-maximum, che non è banale da fare in SQL. Ci sono vari modi per affrontare ciò che può essere più efficace in alcuni casi, ma per top-n in generale si vorrà guardare la risposta di Bill a una domanda precedente simile.

Come con la maggior parte delle soluzioni a questo problema, può restituire più di cinque righe se sono presenti più righe con lo stesso valore di rate , quindi è ansible che sia necessario un numero di post-elaborazione per verificarlo.

Ciò richiede una serie di subquery per classificare i valori, limitarli, quindi eseguire la sum durante il raggruppamento

 @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; 

Prova questo:

 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; 

Costruisci le colonne virtuali (come RowID in Oracle)

tavolo:

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

dati:

 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 come questo:

 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 cancelli la clausola where in t3, mostra in questo modo:

inserisci la descrizione dell'immagine qui

OTTENERE "TOP N Record" -> aggiungere la clausola "rownum < = 3" in where (la clausola where di t3);

SCEGLIERE "l'anno" -> aggiungere la clausola "TRA IL 2000 E IL 2009" in where (la clausola where di t3);

Ci sono voluti dei lavori, ma pensavo che la mia soluzione sarebbe stata qualcosa da condividere in quanto sembra elegante e abbastanza veloce.

 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 

Si noti che questo esempio è specificato ai fini della domanda e può essere modificato abbastanza facilmente per altri scopi simili.

Il seguente post: sql: selcting top N record per gruppo descrive il modo complicato per ottenere ciò senza subquery.

Migliora su altre soluzioni offerte qui da:

  • Fare tutto in una singola query
  • Essere in grado di utilizzare correttamente gli indici
  • Evitando le sottoquery, notoriamente note per produrre piani di esecuzione sbagliati in MySQL

Tuttavia non è carina. Una buona soluzione sarebbe realizzabile con le funzioni Window (dette anche funzioni analitiche) abilitate in MySQL, ma non lo sono. Il trucco utilizzato in questo post utilizza GROUP_CONCAT, che a volte viene descritto come “Funzioni della finestra povere per 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 

La sottoquery è quasi identica alla tua query. Solo il cambiamento sta aggiungendo

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

per quelli come me che hanno avuto delle domande. Ho fatto il seguente per usare i limiti e qualsiasi altra cosa da un gruppo specifico.

 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 $$ 

scorre in un elenco di domini e quindi inserisce solo un limite di 200 ciascuno

Prova questo:

 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; 

Si prega di provare sotto la procedura memorizzata. Ho già verificato Sto ottenendo il risultato corretto ma senza usare 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