Există o modalitate mai simplă de a găsi MODE(S) a unor valori în MySQL (Programare, Mysql, Mod)

Tin Tran a intrebat.
a intrebat.

MODE este valoarea care apare de CELE MAI MULTE ori în date, poate exista UN MODE sau MULTE MODE-uri

iată câteva valori din două tabele (sqlFiddle)

create table t100(id int auto_increment primary key, value int);
create table t200(id int auto_increment primary key, value int);

insert into t100(value) values (1),
                               (2),(2),(2),
                               (3),(3),
                               (4);
insert into t200(value) values (1),
                               (2),(2),(2),
                               (3),(3),
                               (4),(4),(4);

în acest moment, pentru a obține MODE(S) returnate ca listă separată prin virgulă, execut interogarea de mai jos pentru tabel t100

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T100
            GROUP BY value)T1,
        (SELECT max(occurs) as maxoccurs FROM 
            (SELECT value,count(*) as occurs
             FROM
             T100
             GROUP BY value)T2
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

și interogarea de mai jos pentru tabelul t200 (aceeași interogare doar cu numele tabelului schimbat) Am 2 tabele în acest exemplu pentru că pentru a arăta că funcționează pentru cazurile în care există 1 MODE și în care există mai multe MODES.

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T200
            GROUP BY value)T1,
        (SELECT max(occurs) as maxoccurs FROM 
            (SELECT value,count(*) as occurs
             FROM
             T200
             GROUP BY value)T2
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

Întrebarea mea este „Există o modalitate mai simplă?”.

Mă gândeam să folosesc HAVING count(*) = max(count(*)) sau ceva similar pentru a scăpa de join-ul suplimentar, dar nu am reușit să obțin HAVING să returneze rezultatul pe care îl doream.

ACTUALIZARE: așa cum a sugerat @zneak, pot simplifica T3 ca mai jos:

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T200
            GROUP BY value)T1,
        (SELECT count(*) as maxoccurs
             FROM
             T200
             GROUP BY value
             ORDER BY count(*) DESC
             LIMIT 1
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

Acum există o modalitate de a scăpa de T3? Am încercat asta, dar nu returnează niciun rând din anumite motive.

  SELECT value,occurs FROM  
    (SELECT value,count(*) as occurs
     FROM t200
     GROUP BY `value`)T1
  HAVING occurs=max(occurs)  

în principiu, mă întreb dacă există o modalitate de a face în așa fel încât să trebuiască să specific doar t100 sau t200 o singură dată.

ACTUALIZARE: am găsit o modalitate de a specifica t100 sau t200 doar o singură dată prin adăugarea unei variabile pentru a seta propriul meu maxocurs ca mai jos

  SELECT GROUP_CONCAT(CASE WHEN [email protected] THEN value ELSE NULL END) as modes 
  FROM 
    (SELECT value,occurs,@maxoccurs:=GREATEST(@maxoccurs,occurs) as maxoccurs
     FROM (SELECT value,count(*) as occurs
           FROM t200
           GROUP BY `value`)T1,(SELECT @maxoccurs:=0)mo
     )T2

Comentarii

  • Are ORDER BY COUNT(*) DESC LIMIT 1 funcționează? –  > Por zneak.
  • Nu cred că va funcționa pentru că uneori există mai multe MODURI, ca în t200 table, există 2 moduri, iar eu vreau ca ambele să fie returnate pentru a le folosi în group_concat –  > Por Tin Tran.
  • @zneak, drăguț, voi folosi ORDER By count(*) desc LIMIT 1 pentru T3, dar există o modalitate de a scăpa de T3 –  > Por Tin Tran.
1 răspunsuri
Gordon Linoff

Sunteți foarte aproape cu ultima interogare. În cele ce urmează se găsește unul mod:

SELECT value, occurs
FROM (SELECT value,count(*) as occurs
      FROM t200
      GROUP BY `value`
      LIMIT 1
     ) T1

Totuși, cred că întrebarea dumneavoastră se referea la mai multe moduri:

SELECT value, occurs
FROM (SELECT value, count(*) as occurs
      FROM t200
      GROUP BY `value`
     ) T1
WHERE occurs = (select max(occurs)
                from (select `value`, count(*) as occurs
                      from t200
                      group by `value`
                     ) t
               );

EDIT:

Acest lucru este mult mai ușor în aproape orice altă bază de date. MySQL nu acceptă nici with nici funcțiile de fereastră/analiză.

Interogarea dvs. (prezentată mai jos) nu face ceea ce credeți că face:

  SELECT value, occurs  
  FROM (SELECT value, count(*) as occurs
        FROM t200
        GROUP BY `value`
       ) T1
  HAVING occurs = max(occurs) ; 

Finalul having se referă la variabila occurs dar utilizează max(occurs). Din cauza utilizării lui max(occurs) aceasta este o interogare de agregare care returnează un singur rând, rezumând toate rândurile din interogarea secundară.

Variabila occurs nu este utilizată pentru grupare. Așadar, ce valoare folosește MySQL? Folosește o arbitrară valoare arbitrară din unul dintre rândurile din subinterogare. Această valoare arbitrară se poate potrivi sau nu. Dar, valoarea provine doar dintr-un singur rând. Nu există o iterație asupra acesteia.

Comentarii

  • Deci nu există o modalitate de a compara direct cu max(occurs) din T1? De ce interogarea mea cu HAVING se execută și nu returnează nimic? –  > Por Tin Tran.
  • Ce se întâmplă dacă există mai multe „valori” care au același occurs? –  > Por Dean Chiu.
  • @DeanChiu . . . V-aș sugera hat să puneți o nouă întrebare, cu date de exemplu și rezultatele dorite. –  > Por Gordon Linoff.

Tags:,