sql - valores - ¿Usando LIMIT dentro de GROUP BY para obtener N resultados por grupo?

sql promedio varios campos

Question

La siguiente consulta:

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

rendimientos

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

Lo que me gustaría es solo los 5 mejores 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

¿Hay alguna manera de hacer esto usando algún tipo de modificador LIMIT como el que funciona dentro de GROUP BY?

Answer 1

Construye las columnas virtuales （como RowID en Oracle）

mesa:

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

datos:

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 como este:

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;

Si elimina la cláusula where en t3, se muestra así:

OBTENGA "TOP N Record" -> agregue el "rownum <= 3" en la cláusula where (la cláusula where de t3);

ELIJA "el año" -> agregue "ENTRE 2000 Y 2009" en la cláusula where (la cláusula where de t3);

Answer 2

Esto requiere una serie de subconsultas para clasificar los valores, limitarlos y luego realizar la suma mientras se agrupan

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

Answer 3

La siguiente publicación: sql: selcting top N record por grupo describe la manera complicada de lograr esto sin subconsultas.

Mejora otras soluciones ofrecidas aquí por:

• Haciendo todo en una sola consulta.
• Ser capaz de utilizar correctamente los índices.
• Evitar las subconsultas, se sabe que producen planes de ejecución erróneos en MySQL

Sin embargo, no es bonito. Se podría lograr una buena solución si se habilitaran las funciones de ventana (también conocidas como funciones analíticas) en MySQL, pero no lo están. El truco utilizado en dicha publicación utiliza GROUP_CONCAT, que a veces se describe como "Funciones de la ventana del hombre pobre para MySQL".

Answer 4

No, no puede LIMITAR las subconsultas arbitrariamente (puede hacerlo de forma limitada en MySQL más recientes, pero no para 5 resultados por grupo).

Esta es una consulta de tipo de grupo máximo, que no es trivial de hacer en SQL. Hay varias formas de abordar lo que puede ser más eficiente para algunos casos, pero para top-n en general, querrá ver la respuesta de Bill a una pregunta anterior similar.

Al igual que con la mayoría de las soluciones a este problema, puede devolver más de cinco filas si hay varias filas con el mismo valor de rate , por lo que es posible que aún necesite una cantidad de procesamiento posterior para verificarlo.

Answer 5

Para aquellos como yo que tuvieron consultas fuera de tiempo. Hice lo siguiente para usar los límites y cualquier otra cosa de un 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 $$

recorre una lista de dominios y luego inserta solo un límite de 200 cada uno

Answer 6

Para mi algo como

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

funciona perfectamente. No hay consulta complicada.

por ejemplo: obtener 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;

Answer 7

Podría usar la función agregada GROUP_CONCAT para obtener todos los años en una sola columna, agrupados por id y 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 |
-----------------------------------------------------------

Y luego podrías usar FIND_IN_SET , que devuelve la posición del primer argumento dentro del segundo, por ejemplo.

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 una combinación de GROUP_CONCAT y FIND_IN_SET , y filtrando por la posición devuelta por find_in_set, podría usar esta consulta que devuelve solo los primeros 5 años 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 vea el violín here .

Tenga en cuenta que si más de una fila puede tener la misma tasa, debe considerar el uso de GROUP_CONCAT (tasa DISTINCT rate ORDER BY) en la columna de tarifas en lugar de la columna del año.

La longitud máxima de la cadena devuelta por GROUP_CONCAT es limitada, por lo que esto funciona bien si necesita seleccionar algunos registros para cada grupo.

Answer 8

Por favor intente a continuación el procedimiento almacenado. Ya lo he verificado. Estoy obteniendo el resultado adecuado pero sin 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

Answer 9

Prueba esto:

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;

Answer 10

Prueba esto:

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;

Answer 11

Tomé algo de trabajo, pero creo que mi solución sería algo para compartir, ya que parece elegante y bastante 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

Tenga en cuenta que este ejemplo se especifica para el propósito de la pregunta y se puede modificar muy fácilmente para otros propósitos similares.

Answer 12

^{La consulta original usaba variables de usuario y ORDER BY en tablas derivadas; El comportamiento de ambas peculiaridades no está garantizado. Respuesta revisada de la siguiente manera.}

Puede usar el rango de hombre pobre sobre la partición para lograr el resultado deseado. Solo une la tabla con ella misma y para cada fila, cuenta el número de filas menor que esta:

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

Tenga en cuenta que:

1. COUNT está basado en cero
2. Para la clasificación descendente, la fila menor es la que tiene una tasa más alta.
3. Se devuelven todas las filas que empatan para el último lugar.

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

Answer 13

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 subconsulta es casi idéntica a su consulta. Solo se agrega cambio

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