Dotaz: SQL - jak zobrazit stejny sloupec dvakrat jinak

[HonzaZ]

Ahoj, mam databazi nejakych sportovnich vykonu (treba hod ostepem v metrech). Lidi mohli podat vice vykonu a rozlisit se mezi nimi da casem, kdy vykon podali.

(pro orientaci: mam tabulky lidi, vykonu a zavodu)

Zobrazit nejlepsi vykony vsech lidi je v pohode.

Zobrazit nejlepsi vykony vsech lidi dosazene mezi lety 2005-2009 je taky v pohode.

Ale jak do jedne tabulky zobrazim oboji? Tj. chci tabulku kde bude

• jmeno cloveka
• jeho nejlepsi vykon
• jeho nejlepsi vykon v letech 2005-2009

.

Sedim nad tim uz hodiny. Ted prave studuji, jak v SQL napsat funkci, ktera mi to vyfiltruje, ale moc nadejne to nevidim.

Neexistuje na tohle nejaky trik? Jde vubec tentyz sloupec s jinou hodnotou vypsat pouhym sql dotazem?

Odpovědi

[FooBar]

Epicky zjednodusene:

SELECT
 clovek.clovek_id,
 clovek.jmeno,
 vykon_celkem.result,
 vykon_v_letech.result
FROM
 clovek
JOIN ( SELECT clovek_id, MAX(vykon) FROM vykon GROUP BY clovek_id ) AS vykon_celkem ON vykon_celkem.clovek_id = clovek.clovek_id
JOIN ( SELECT clovek_id, MAX(vykon) FROM vykon WHERE leta BETWEEN 2005 AND 2009 GROUP BY clovek_id ) AS vykon_v_letech ON vykon_v_letech.clovek_id = clovek.clovek_id

[HonzaZ]

Uf, diky moc! Odpoledne si to prostuduji

[jeleniste]

Je ještě jedna možnost s použitím case.

Select 
  clovek_id,
  clovek_jmeno,
  max(vykon) vykon,
  max(case when rok in (2005,2006,2007,2008,2009) then vykon else NULL end) vykon_v_letech
  from
   tabulka
  group by
   clovek_id, clovek_jmeno 

finta je vodkoukana vocaď
a bacha na hodnoty NULL, u nekterych db vraci aggregacni fce NULL, kdyz je ve sloupci NULL

[kuka]

v Oracle 10 a vyssim:

select clovek, vykon, case when rok between 2005 and 2009 then vykon else null end; vykon59

vybere vykony podle zadanych kriterii do ruznych sloupcu. De facto jde jen o priznak, zda je vykon z daneho obdobi, ale pro nasledne grupovani je nejjednodussi tam tu hodnotu rovnou vlozit. Dale pak

select max(vykon), max(vykon59) from dotaz_vyse group by clovek

[GeoRW]

Pre Oracle:

WITH max_vykony AS (SELECT /*+ materialize */ clovek_id, max(vykon) max1
                    FROM tabulka
                    GROUP BY clovek_id),
     max_vykony_05_09 AS (SELECT /*+ materialize */ clovek_id, max(vykon) max2
                          FROM tabulka
                          WHERE rok BETWEEN 2005 AND 2009
                          GROUP BY clovek_id)
SELECT DISTINCT tab.clovek_id,
                (SELECT max1
                 FROM max_vykony
                 WHERE clovek_id = tab.clovek_id),
                (SELECT max2
                 FROM max_vykony
                 WHERE clovek_id = tab.clovek_id)
FROM tabulka tab

[GeoRW]

Teda ak mas 3 samostatne tabulky tak:

WITH max_vykony AS (SELECT /*+ materialize */ clovek_id, max(vykon) max1
                    FROM vykony
                    GROUP BY clovek_id),
     max_vykony_05_09 AS (SELECT /*+ materialize */ clovek_id, max(vykon) max2
                          FROM vykony
                          WHERE rok BETWEEN 2005 AND 2009
                          GROUP BY clovek_id)
SELECT l.clovek_id,
       (SELECT max1
        FROM max_vykony
        WHERE clovek_id = l.clovek_id),
       (SELECT max2
        FROM max_vykony_05_09
        WHERE clovek_id = l.clovek_id)
FROM lidi l

[kuka]

Na rozdil od reseni s case toto projde celou tabulku vicekrat a jeste to pak musi udelat join. To muze v zavislosti na velikosti tabulky dost vadit. Podstata problemu je takova, ze staci projit tabulku pouze jednou bez ohledu na to, za kolik casovych obdobi to chci sledovat, a tak by take melo vypadat optimalni reseni.

[GeoRW]

vtip je v tom, ze ten materialize hint ti vytvori agregovanu tabulku iba raz (resp. v tomto pripade 2 tabulky), takze to neprechadza viackrat; to riesenie s CASE sa da pouzit aj v tomto pripade, ze sa vytvori iba jedna agregovana tabulka (namiesto dvoch); zavisi potom, s akymi dalsimi tabulkami to budes este joinovat

[kuka]

Materializace je v tomto pripade nezadouci, protoze view se pouzije pouze jednou a bude to znamenat zbytecnou rezii navic. Hint materialize byva podle me zkusenosti optimalizatorem vzdy respektovan, takze i kdyz by asi mohl odhalit jeho nesmyslnost, nejspis se opravdu zbytecne vytvori dve temporary tabulky.

Sam pises "respektive dve tabulky" a o to jde, zdrojova tabulka se bude prochazet dvakrat (typicky fullscan + sort). Pokud to nevidis rovnou (na prvni pohled jsou tam dva vnorene dotazy do te tabulky), zobraz si explain plan.

[HonzaZ]

Diky vsem za odpovedi. Dnes se mi diky nim konecne podarilo vytvorit ten spravny dotaz! Radsi to sem davat nebudu, protoze je to prasarna - je to SELECT na stranku a trva 15 sekund. Dulezite je, ze to funguje. Diky

[HonzaZ]

Mohu se zeptat jeste na jednu vec?

Zdanlive je to uplne trivialni: Jak zobrazim prumer nejlepsich napr. 5 vykonu dosazenych v danem zavodu?

Za nasledujicich podminek:

• nemam prava vytvaret procedury
• V tom clanku na rootu se pouzivalo tohle:

  SELECT AVG(vykon) FROM vykony WHERE zavod=88 AND vykon IN (
    SELECT vykon FROM vykony WHERE zavod=88 ORDER BY vykon DESC LIMIT 5
  );
  

  To je ok. Kdyz to ale napisu, tak se mi objevi hlaska:
  This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

(nemam moznost menit verzi databaze)

Napada vas alternativni sql dotaz?

[okbob]

Ten článek se váže k PostgreSQL - jinak k Vašemu dotazu - řada věcí v MySQL prostě nejde vyřešit jedním dotazem.

[depka]

vysledek vnoreneho dotazu vloz nejprve do docasne tabulky a z te pak vyber prumer

[HonzaZ]

Dobry napad, zkousim to (create temporary table), ale na to asi taky nemam prava:

ERROR 1044 (42000): Access denied for user ... to database ...

[jeleniste]

Pokud umí Mysql cte, tak se dá možná použít cte, případně poddotaz:

Select avg(vykon)
  from
  (
    Select vykon from vykony
      where zavod = 88 
      limit 5
  )dta

[HonzaZ]

Uff.. Vubec zatim nevim, co tohle znamena.. Ale diky, zkusim to nastudovat

[HonzaZ]

aha, ted na to koukam z jineho stroje. Asi bug na abclinuxu nebo v konqueroru - v konqueroru 3.5 to zobrazuje diskuzi dost podivne (nejaky strasne dlouhy na prvni pohled SQL kod). toto vyzkousim, diky