value - sql group by min max

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:

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

Isso requer uma série de subconsultas para classificar os valores, limitá-los e, em seguida, executar a soma enquanto agrupa

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

Não, você não pode LIMIT subqueries arbitrariamente (você pode fazê-lo de forma limitada em novos MySQLs, mas não para 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 classificação 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.

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 conhecidas 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 no post mencionado utiliza GROUP_CONCAT, que às vezes é descrito como "Funções do Windows do pobre para o MySQL".

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

Tente isto:

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:[email protected]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;

Você pode usar a função 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 here .

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.

para aqueles que, como eu, tiveram consultas demoradas. 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

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)