SQL dotázek

SQL dotázek
« kdy: 13. 11. 2019, 10:51:29 »
Jak z SQL databáze záznamy odpovídající podmínce? Názorný příklad : tabulka lidí(bez normalizovaní), která vlastní auta (pro jednoduchost značky). Chci vybrat lidi, kteří vlastní alespoň Nissan a audi (nebo alternativně řádky tabulky patřící této osobě).
jmeno;auto
Petr;Citroen
Petr;Audi
Petr;Nissan

Ludvik;NIssan
Jarda;Nissan;
Jarda;Citroen
Karel;Audi
Filip;Audi

Obejde se to bez subselectu?
Případně to jde udělat nějak přes WHERE auto="Nissan" AND/OR auto="Audi" -> group by jmeno -> having(count(auto))>=2 ? Je to čisté řešení?

Jak se nazývají podobné dotazy(úlohy)? Mám tušení že nějak komponované/korelované....


gill

  • ****
  • 270
    • Zobrazit profil
    • E-mail
Re:SQL dotázek
« Odpověď #1 kdy: 13. 11. 2019, 11:07:40 »
určitě bez subselectu, použij self join

Kit

  • *****
  • 523
    • Zobrazit profil
    • E-mail
Re:SQL dotázek
« Odpověď #2 kdy: 13. 11. 2019, 11:36:53 »
Kód: [Vybrat]
SELECT DISTINCT jmeno FROM Karta
    JOIN Karta AS K2 ON Karta.jmeno=K2.jmeno
    WHERE Karta.auto='Nissan' AND K2.auto='Audi';

Z toho je vidět, jak je normalizace důležitá.

Re:SQL dotázek
« Odpověď #3 kdy: 13. 11. 2019, 12:26:50 »
Je to čisté řešení?
Není, čisté řešení je ty tabulky normalizovat.

Případně to jde udělat nějak přes WHERE auto="Nissan" AND/OR auto="Audi" -> group by jmeno -> having(count(auto))>=2 ?
S AND to určitě nebude fungovat, nebudete mít v jednom řádku Nissan i Audi. COUNT(auto) vám vrátí 2 i v případě, že tam budete mít dva záznamy s Nissan. Tj. pokud je podmínkou, že má Nissan i Audi, je ta podmínka špatně.

Začněte ale tou normalizací. A pak bych použil SELECT FROM seznam_osob WHERE EXIST (vlastní Audi) AND (vlastní Nissan) (pokud je podmínkou, že vlastní oba). Je to přesný popis toho, co chcete získat, a optimalizátor dotazů si s tím nejspíš poradí.


Re:SQL dotázek
« Odpověď #4 kdy: 15. 11. 2019, 09:28:27 »
Tohle je klasicka vazba M:N, takze aby to bylo ciste, potrebujes 3 tabulky

Person (id [PK], name), Car (id [PK]), Person2Car(person_id, car_id)  PK oba sloupce + 2x foreign key na obe strany

select distinct p.* from Person p
  join Person2Car pc on p.id=pc.person_id
  join Car c on c.id on c.id=pc.car_id
  where c.name in ('Nissan','Audi')


gill

  • ****
  • 270
    • Zobrazit profil
    • E-mail
Re:SQL dotázek
« Odpověď #5 kdy: 15. 11. 2019, 10:09:29 »
on se zeptal na dotaz a tady mu místo jednoduché odpovědi radí překopat celé schéma.

Re:SQL dotázek
« Odpověď #6 kdy: 15. 11. 2019, 20:08:12 »
Když jedna z otázek autora je "Je to čisté řešení?", tak se není čemu divit.  :)

gill

  • ****
  • 270
    • Zobrazit profil
    • E-mail
Re:SQL dotázek
« Odpověď #7 kdy: 15. 11. 2019, 21:16:05 »
Když jedna z otázek autora je "Je to čisté řešení?", tak se není čemu divit.  :)

on se ptal jestli je ten jeho dotaz čisté řešení.

Re:SQL dotázek
« Odpověď #8 kdy: 18. 11. 2019, 09:43:19 »
Není, čisté řešení je ty tabulky normalizovat.
Ach jo. zase Jiráskovo hnidopišství o detailech, které vůbec nehrají roli. Tak si představte, že místo Petr je 123456 a místo Nissan 9876, zase někdo našel detail.

Jak je tady normalizace důležitá?


A dovolím si rozšířit dotaz: co když bude , že má 4 vybraná auta. To se bude dělat 3násobný join? Bude to efektivní?
« Poslední změna: 18. 11. 2019, 09:47:41 od Pivotal »

gill

  • ****
  • 270
    • Zobrazit profil
    • E-mail
Re:SQL dotázek
« Odpověď #9 kdy: 18. 11. 2019, 10:14:00 »
Bude to efektivní, pokud máš nad těmi sloupci index.

Re:SQL dotázek
« Odpověď #10 kdy: 18. 11. 2019, 10:47:48 »
Záleží na množství. V malých počtech bude navržené řešení podobně efektivní, udělá se index nad textem.
Ve větších množstvích by vazba samozřejmě pomohla, protože tabulka hodnot by byla unique not null a indexoval by se pouze číselný typ. Join by to pak jednoznačně výkonově vyhrál.

Re:SQL dotázek
« Odpověď #11 kdy: 18. 11. 2019, 13:54:50 »
Ach jo. zase Jiráskovo hnidopišství o detailech, které vůbec nehrají roli. Tak si představte, že místo Petr je 123456 a místo Nissan 9876, zase někdo našel detail.
Děkuji, že jste to zkompletoval. Před čtrnácti dny jsem tu dostal vynadáno, jak si můžu dovolit psát o nenormalizované tabulce. Normalizovaná tabulka je evidentně taky špatně. Jestli on nebude problém spíš s těmi, co mají na všechno svá jediná správná řešení…

Jak je tady normalizace důležitá?
Tak, že umožní o problému strukturovaně přemýšlet. Potřebuju získat záznam o jednom člověku? Výborně, mám tady tabulku lidí, z té si ten jeden záznam vytáhnu. Potřebuju si k němu vytáhnout auta? Výborně, mám tady spojovací tabulku. A najednou nemusí tazatel řešit self-joiny, čímž odpadá polovina jeho zmatení a to zadání nejspíš dokáže vyřešit sám.

A dovolím si rozšířit dotaz: co když bude , že má 4 vybraná auta. To se bude dělat 3násobný join? Bude to efektivní?
V tom dotazu je, že vlastní Nissan a Audi. Pokud budete mít každé auto ve zvláštním řádku tabulky, bez vícenásobného JOINu se neobejdete. Leda by vaše databáze podporovala pole a vy jste z těch řádků nejprve udělal pole.  Nicméně když řešíte efektivitu, JOINy umí optimalizovat každá databáze; vytvářet z více záznamů jeden záznam s polem hodnot asi nebude zrovna věc, kterou by databáze optimalizovaly.

Re:SQL dotázek
« Odpověď #12 kdy: 18. 11. 2019, 14:01:10 »
V tom dotazu je, že vlastní Nissan a Audi. Pokud budete mít každé auto ve zvláštním řádku tabulky, bez vícenásobného JOINu se neobejdete. Leda by vaše databáze podporovala pole a vy jste z těch řádků nejprve udělal pole.  Nicméně když řešíte efektivitu, JOINy umí optimalizovat každá databáze; vytvářet z více záznamů jeden záznam s polem hodnot asi nebude zrovna věc, kterou by databáze optimalizovaly.

Ale houby, jeden join na tabulku s auty.
Buďto (a to bývá nejčastější) vyhovuje navrátit více řádků ke každému jménu (co řádek, to jedna značka), nebo
značky můžete agregovat do pole, to zvládne i MySQL.

Re:SQL dotázek
« Odpověď #13 kdy: 18. 11. 2019, 17:11:12 »
Ale houby, jeden join na tabulku s auty.
Buďto (a to bývá nejčastější) vyhovuje navrátit více řádků ke každému jménu (co řádek, to jedna značka), nebo
značky můžete agregovat do pole, to zvládne i MySQL.
Nějak tu nevidím ten SELECT, asi se vám při vkládání komentáře ztratil.

Re:SQL dotázek
« Odpověď #14 kdy: 18. 11. 2019, 17:55:05 »
Nějak tu nevidím ten SELECT, asi se vám při vkládání komentáře ztratil.

Asi narážíte na to, že joiny budou dva. Máte pravdu.

SELECT jmeno
FROM tbl1
LEFT JOIN tbl_vazba USING (vazba_left)
INNER JOIN tbl_hodnoty USING (vazba_right)
WHERE tbl_hodnoty.auto IN ('Audi', 'Nissan')
HAVING count(*) = 2
GROUP BY jmeno


Podtržené having když vynecháte, tak získáte požadovaný operátor Audi OR Nissan.
Pokud ho doplníte, získáte operátor Audi AND Nissan.

(Pro operátor OR by šlo použít DISTINCT, ale výkonnostně je totožné, takže je výhodnější si to abstrahovat do jednoho dotazu a HAVING přidat podle potřeby)

Select je jen schematicky, prosím nepeskujte mě, pokud neprojde, píšu je z ruky.
« Poslední změna: 18. 11. 2019, 17:57:05 od Miroslav Šilhavý »