abclinuxu.cz AbcLinuxu.cz itbiz.cz ITBiz.cz HDmag.cz HDmag.cz abcprace.cz AbcPráce.cz
AbcLinuxu hledá autory!
Inzerujte na AbcPráce.cz od 950 Kč
Rozšířené hledání
×
eParkomat, startup z ČR, postoupil mezi finalisty evropského akcelerátoru ChallengeUp!
Robot na pivo mu otevřel dveře k opravdovému byznysu
Internet věcí: Propojený svět? Už se to blíží...
dnes 17:02 | Pozvánky

Přijďte si popovídat o open source obecně a openSUSE konkrétně s dalšími uživateli a vývojáři. Oslava nového vydání openSUSE Leap se uskuteční 16. prosince od 17:00 v nových prostorách firmy SUSE v Praze. K dispozici bude nějaké občerstvení a DVD pro ty, kdo je sbírají nebo ještě mají mechaniku. Po párty v kanceláři se bude pokračovat v některé z hospod v okolí.

Miška | Komentářů: 7
dnes 14:55 | Zajímavý software

Byla vydána verze Alpha 1.0 otevřeného operačního systému pro chytré hodinky AsteroidOS. Podporovány jsou hodinky LG G Watch, LG G Watch Urbane, Asus ZenWatch 2 a Sony Smartwatch 3. Ukázka ovládání hodinek na YouTube. Jaroslav Řezník přednášel o AsteroidOS na chytrých hodinkách (videozáznam) na letošní konferenci OpenAlt.

Ladislav Hagara | Komentářů: 0
dnes 13:30 | Zajímavý software

Byly uvolněny zdrojové kódy známé rogue-like hry DoomRL. Počátky hry jsou v roce 2002. Je napsána ve FreePascalu a zdrojový kód je nyní k dispozici na GitHubu pod licencí GNU GPL 2.0. Autor pracuje na nové hře Jupiter Hell, která je moderním nástupcem DoomRL a na jejíž vývoj shání peníze prostřednictvím Kickstarteru.

Blaazen | Komentářů: 0
dnes 13:15 | Pozvánky

Přijďte s námi oslavit vydání Fedory 25. Na programu budou přednášky o novinkách, diskuse, neřízený networking atd. Release Party se bude konat 16. prosince v prostorách společnosti Etnetera. Na party budou volně k dispozici také propagační materiály, nová DVD s Fedorou 25 a samozřejmě občerstvení. Přednášky budou probíhat v češtině. Pro více informací se můžete podívat na web MojeFedora.cz. Jen připomínám, že tentokrát jsme zavedli

… více »
frantisekz | Komentářů: 0
včera 16:38 | Komunita

Byly zveřejněny videozáznamy přednášek a workshopů z letošní konference OpenAlt konané 5. a 6. listopadu v Brně. K videozáznamům lze přistupovat ze stránky na SuperLectures nebo přes program konference, detaily o vybrané přednášce nebo workshopu a dále kliknutím na ikonku filmového pásu. Celkově bylo zpracováno 65 hodin z 89 přednášek a workshopů.

Ladislav Hagara | Komentářů: 0
včera 11:30 | Komunita

Bylo oznámeno, že bude proveden bezpečnostní audit zdrojových kódů open source softwaru pro implementaci virtuálních privátních sítí OpenVPN. Audit provede Matthew D. Green (blog), uznávaný kryptolog a profesor na Univerzitě Johnse Hopkinse. Auditována bude verze 2.4 (aktuálně RC 1, stabilní verze je 2.3.14). Audit bude financován společností Private Internet Access [reddit].

Ladislav Hagara | Komentářů: 4
včera 06:00 | Komunita

Na YouTube byl publikován Blender Institute Reel 2016, ani ne dvouminutový sestřih z filmů, které vznikly za posledních 10 let díky Blender Institutu. V institutu aktuálně pracují na novém filmu Agent 327. Dění kolem filmu lze sledovat na Blender Cloudu. Videoukázka Agenta 327 z června letošního roku na YouTube.

Ladislav Hagara | Komentářů: 0
včera 01:02 | Zajímavý článek

Minulý týden byly vydány verze 1.2.3 a 1.1.7 webového poštovního klienta Roundcube. V oznámení o vydání bylo zmíněno řešení bezpečnostního problému nalezeného společností RIPS a souvisejícího s voláním funkce mail() v PHP. Tento týden byly zveřejněny podrobnosti. Útočník mohl pomocí speciálně připraveného emailu spustit na serveru libovolný příkaz. Stejně, jak je popsáno v článku Exploit PHP’s mail() to get remote code execution z roku 2014.

Ladislav Hagara | Komentářů: 1
8.12. 16:00 | Nová verze

Byla vydána verze 0.98 svobodného nelineárního video editoru Pitivi. Z novinek lze zmínit například přizpůsobitelné klávesové zkratky. Videoukázka práce s nejnovější verzí Pitivi na YouTube.

Ladislav Hagara | Komentářů: 1
8.12. 15:00 | Zajímavý software

Stop motion je technika animace, při níž je reálný objekt mezi jednotlivými snímky ručně upravován a posouván o malé úseky, tak aby po spojení vyvolala animace dojem spojitosti. Jaký software lze pro stop motion použít na Linuxu? Článek na OMG! Ubuntu! představuje Heron Animation. Ten bohužel podporuje pouze webové kamery. Podpora digitálních zrcadlovek je začleněna například v programu qStopMotion.

Ladislav Hagara | Komentářů: 5
Kolik máte dat ve svém domovském adresáři na svém primárním osobním počítači?
 (32%)
 (23%)
 (29%)
 (7%)
 (5%)
 (3%)
Celkem 809 hlasů
 Komentářů: 50, poslední 29.11. 15:50
Rozcestník
Reklama

Dotaz: nadvakrat COUNT(*)

8.10.2009 14:48 Jirka
nadvakrat COUNT(*)
Přečteno: 1018×

 Ahoj, mám jen otázku, zda-li je můj SQL dotaz ideální,. Mám následujicí tabulku a data:

CREATE TABLE tbl (
     id_link INT NOT NULL,
     id_label INT NOT NULL
);
INSERT INTO tbl (id_link, id_label) VALUES (10,2);
INSERT INTO tbl (id_link, id_label) VALUES (10,3);
INSERT INTO tbl (id_link, id_label) VALUES (11,1);
INSERT INTO tbl (id_link, id_label) VALUES (11,2);
INSERT INTO tbl (id_link, id_label) VALUES (11,3);
INSERT INTO tbl (id_link, id_label) VALUES (12,1);
INSERT INTO tbl (id_link, id_label) VALUES (12,3);

SELECT * FROM tbl;
+---------+----------+
| id_link | id_label |
+---------+----------+
|      10 |        2 | 
|      10 |        3 | 
|      11 |        1 | 
|      11 |        2 | 
|      11 |        3 | 
|      12 |        1 | 
|      12 |        3 | 
+---------+----------+
7 rows in set (0.00 sec)

A nyní potřebuji zjistit počet id_link, které mají id_label 1 a současně 3, jde o id_link 11 a 12, celkem tedy 2. Dělám to takto:

SELECT COUNT(*)
     FROM (SELECT COUNT(*) AS  kolikrat 
          FROM tbl 
          WHERE id_label IN (1,3) 
          GROUP BY id_link 
          HAVING kolikrat=2
     ) AS tbl2;

+----------+
| COUNT(*) |
+----------+
|        2 | 
+----------+

Obdobně, když mě zajímá počet id_link, které mají současně id_label 1,2 a 3 (jde o jeden případ a to id_link 11):

SELECT COUNT(*)
     FROM (SELECT COUNT(*) AS  kolikrat 
          FROM tbl 
          WHERE id_label IN (1,2,3) 
          GROUP BY id_link 
          HAVING kolikrat=3
     ) AS tbl2;

+----------+
| COUNT(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

Pravda, funguje to, ale nešlo by to jednodušeji, a hlavně rychleji (při větším množství dat)?. Díky za případné náměty.


Řešení dotazu:


Odpovědi

8.10.2009 15:16 Semo | skóre: 44 | blog: Semo
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
SELECT
  COUNT(*)

FROM
tbl t1

JOIN tbl t2
  ON
    t1.id_link=t2.id_link
    AND t1.id_label=1
    AND t2.id_label=3;
Ci je to rychlejsie si otestuj sam, ale mohlo by. Predpokladam, ze riadky v tej tabulke su unikatne, inak by sa mnozili zaznamy. Trocha nevyhoda je, ze pre druhu podmienku (1,2,3) uz treba joinovat 3x, ale obecne su databazy skor prisposobene na rychle joiny (kde mozu vyuzivat indexy), nez na divoke subselecty (kde v tom subselecte nevyuziju index).
If you hold a Unix shell up to your ear, you can you hear the C.
8.10.2009 15:48 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

To funguje, problém je však v tom, že v té podmínce je proměnný počet čísel, třeba i deset.

8.10.2009 17:38 Michal Kubeček | skóre: 71 | Luštěnice
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
V tom nevidím problém, stejně ten dotaz budete muset konstruovat.
8.10.2009 20:09 jekub
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Ne nutně. Lze použít další (dočasnou, pracovní, ...) tabulku, ze které se budou brát podmínky in a having. V rozumné databázi to o moc pomalejší než semovo řešení nebude. Navíc se u frekventovaného dotazu ušetří za hard parsing.

xkucf03 avatar 20.10.2009 21:51 xkucf03 | skóre: 45 | blog: xkucf03
Rozbalit Rozbalit vše SQL injection
Ale ani tady se nesmí zapomínat na parametrizované dotazy – nespojovat dotaz a nevkládat do něj parametry jako text.
Mám rád, když se lidé přou, znamená to, že vědí, co dělají, a že mají směr. Frantovo.cz, SQL-Výuka.cz, Nekuřák.net
8.10.2009 19:35 jekub
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Váš dotaz je jednoznačně lepší než výše uvedený pomocí spojení tabulek. Netřeba to ani testovat.

8.10.2009 20:28 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Tak jsem Vaše návrhy použil na větších datech a dostal jsem tyto výsledky: při 3 číslech je metoda s JOIN 4x rychlejší, ale při 10 číslech (=10 JOINů) je zase skoro 2x pomalejší než moje původní...

9.10.2009 14:29 Semo | skóre: 44 | blog: Semo
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Na akej DB to testujes?

A joinujes vzdy s prvou tabulkou alebo 2. s 3., 3. so 4. atd.?
If you hold a Unix shell up to your ear, you can you hear the C.
9.10.2009 18:06 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Jde o MySQL, zkoušeno na 5.0, 5.1 i 5.4. Dotazy s JOIN jsou do osmi číslic 3-4krát rychlejší, pak nastane zlom, u devíti číslic je to dvakrát pomalejší a u deseti šestkrát pomalejší než můj původní select. Testováno asi takto:

SELECT COUNT(*)
     FROM tbl t1
     JOIN tbl t2 USING (id_link)
     JOIN tbl t3 USING (id_link)
     JOIN tbl t4 USING (id_link)
     JOIN tbl t5 USING (id_link)
     JOIN tbl t6 USING (id_link)
     JOIN tbl t7 USING (id_link)
     JOIN tbl t8 USING (id_link)
     JOIN tbl t9 USING (id_link)
     JOIN tbl t10 USING (id_link)
     WHERE t1.id_label=15 AND t2.id_label=16 AND t3.id_label=17 AND t4.id_label=3 AND t5.id_label=14 
     AND t6.id_label=1 AND t7.id_label=61 AND t8.id_label=81 AND t9.id_label=7 AND t10.id_label=55
9.10.2009 14:32 kulik
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Hodne zalezi na charakteru tech dat. Group by pravdepodobne provede sort mnoziny vyhovujici podminkam a zalezi na tom, jak velka mnozina do nej bude vstupovat. Pri joinu bude podstatne, v jakem poradi se bude joinovat, pokud nektera z podminek diskriminuje vyrazne vice nez jina - napriklad pokud label 1 a 2 milion zaznamua 3 pouze deset, bude join v poradi 3, 2, 1 mnohonasobne rychlejsi nez join v poradi 1, 2, 3 (pokud tam je vhodny index). Obecne pokud se nedaji udelat zadne rozumne predpoklady o datech bych preferoval reseni s group by, ktere je minimalne prehlednejsi.

9.10.2009 13:55 kk
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Já tam ještě vidím jeden problém. Pokud bude v tabulce dvakrát  jedna dvojice id_link a id_label, tak to začne počítat špatně.

 

Tarmaq avatar 9.10.2009 17:40 Tarmaq | skóre: 39
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
to by mel vyresit DISTINCT ne?
Don't panic!
Tarmaq avatar 9.10.2009 17:41 Tarmaq | skóre: 39
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
nebo jeste lip - mel by tam byt UNIQUE CONSTRAINT
Don't panic!
22.10.2009 09:20 echy
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Nebo ještě ještě líp - přes oba sloupce by měl být primární klíč (a tudíž i index).
9.10.2009 17:57 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

To ne, dvojice id_link, id_label je unikátní.

10.10.2009 00:33 FooBar
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Ja se priznam, ze jsem takhle vecer nepochopil pointu toho HAVING kolikrat=N. K cemu tam vlastne je? Ja bych pocital distinct id_link, tzn. neco jako...

SELECT COUNT(DISTINCT id_link) AS result FROM tbl WHERE id_label IN (1,2) GROUP BY id_link

10.10.2009 00:34 FooBar
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)

Aah, beru zpet, uz tu pointu kolikrat vidim :)

default avatar 21.10.2009 12:09 default | skóre: 22 | Madrid
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Ten první případ jde řešit existenčním (semi-)joinem:
SELECT
    COUNT(1) AS total
FROM
    tbl tbl1
WHERE
    tbl1.id_label = 1
    AND EXISTS (SELECT
            1
        FROM
            tbl tbl2
        WHERE
            tbl2.id_link = tbl1.id_link
            AND tbl2.id_label = 3)
/
Obecně — a pro velké objemy dat — lze zneužít analytické funkce:
SELECT
    COUNT(CASE WHEN rn = 1 THEN 1 ELSE NULL END) AS total
FROM
    (SELECT
        id_link,
        id_label,
        COUNT(CASE WHEN id_label IN (1, 2, 3) THEN 1 ELSE NULL END) OVER (PARTITION BY id_link) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY id_link ORDER BY 1) AS rn
    FROM
        tbl)
WHERE
    cnt = 3
/
Podmínku na hodnoty ID_LABEL vsuneš do výrazu agregační funkce COUNT() na řádce 7. Počet hodnot pro ID_LABEL musí být uveden ve WHERE klauzuli na řádce 12. Tedy:
SELECT
    COUNT(CASE WHEN rn = 1 THEN 1 ELSE NULL END) AS total
FROM
    (SELECT
        id_link,
        id_label,
        COUNT(CASE WHEN id_label IN (1, 3) THEN 1 ELSE NULL END) OVER (PARTITION BY id_link) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY id_link ORDER BY 1) AS rn
    FROM
        tbl)
WHERE
    cnt = 2
/
Výhoda je jasná: stačí full-scan nad tabulkou; žádný joiny. Zbytek odře WINDOW-SORT. :-D

Jo! A jestli to chceš mít lépe konfigurovatelný, pak to jde udělat ještě takto:
WITH conditions AS (
    SELECT 1 AS val FROM dual
    UNION ALL
    SELECT 3        FROM dual)
SELECT
    COUNT(CASE WHEN rn = 1 THEN 1 ELSE NULL END) AS total
FROM
    (SELECT
        id_link,
        id_label,
        COUNT(CASE WHEN id_label IN (SELECT val FROM conditions) THEN 1 ELSE NULL END) OVER (PARTITION BY id_link) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY id_link ORDER BY 1) AS rn
    FROM
        tbl)
WHERE
    cnt = (SELECT COUNT(1) FROM conditions)
/
Prostě jen uvedeš veškeré hodnoty ve factoring klauzuli. :-) Klidně by to šlo udělat tak, že bys přes bindovanou proměnnou dodal řetězec čísel oddělených třebas čárkou, ale to je již nad rámec dotazu. :-D
21.10.2009 16:06 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Copak o to, vypadá to pěkně, ale obávám se, že na MySQL (vyjma prvního případu) už je to moc :-(
default avatar 21.10.2009 16:36 default | skóre: 22 | Madrid
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Tak to mě mrzí. :'( Já myslel, že se jedná o obecný dotaz. :-(
21.10.2009 16:43 Jirka
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
To nemusí, SELECTy jsem si pečlivě zaznamenal, alespoň jsem se dozvěděl něco nového, možná je později použiji. Díky!
xkucf03 avatar 21.10.2009 16:44 xkucf03 | skóre: 45 | blog: xkucf03
Rozbalit Rozbalit vše Oracle
Ale aspoň všichni víme, jak je ten Oracle super a co všechno v něm jde :-)
Mám rád, když se lidé přou, znamená to, že vědí, co dělají, a že mají směr. Frantovo.cz, SQL-Výuka.cz, Nekuřák.net
default avatar 21.10.2009 17:11 default | skóre: 22 | Madrid
Rozbalit Rozbalit vše Re: Oracle
To není jen o Oracle. Funguje to i v DB2 LUW a mělo by to fungovat i v posledním PostgreSQL.
22.10.2009 10:16 kulik
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Neni mi moc jasne, v cem by melo byt toto reseni lepsi nez puvodni reseni s group by, ktere v nejhorsim pripade (napr. neexistuji zadne indexy) provede full scan tabulky a jeji sort, coz se podle mne musi v tomto pripade provest rovnez. Puvodni reseni je radove prehlednejsi a bude fungovat na rozdil od tohoto vsude.
default avatar 22.10.2009 21:27 default | skóre: 22 | Madrid
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Protože:
  1. Autor neuvedl, o kterou databázi se jedná.
  2. Autor se chtěl zbavit SELECT COUNT(1) FROM (SELECT COUNT(1) FROM …).
Výše uvedené řešení podmínky dotazu splňuje:
  • Analytické funkce budou v tomto případě na Oracle Database 10gR2 a vyšší výkonnější než GROUP BY či JOINy. Na IBM DB2 LUW stačí nahradit klíčové slovo ROW_NUMBER klíčovým slovem ROWNUMBER (navíc — klauzule PARTITION BY a ORDER BY jsou tam jako bonus nepovinné).
  • Řešení je naprosto stejně čitelné jako řešení s GROUP BY pro člověka, který zná analytické funkce.
  • Využití indexů záleží na WHERE klauzuli, nikoli na počtu vytvořených indexů či jejich existenci. Protože autor potřebuje probrousit celou tabuli — aspoň tak jsem to z dotazu pochopil —, možná by pomohl kompozitní index nad ID_LINK a ID_LABEL. Ale vůbec bych se nedivil, kdyby optimizér zvolil TABLE FULL-SCAN. Proč zbytečně brousit index a pak — vlastně vše — číst pomocí TABLE ACCESS BY INDEX ROWID? Chtělo by to udělat PoC nad velkým počtem dat. Také záleží na hodnotách NULL… Je toho prostě víc…
23.10.2009 10:36 kulik
Rozbalit Rozbalit vše Re: nadvakrat COUNT(*)
Vyuziti indexu samozrejme zalezi na jejich existenci - kdyby tam nebyly, tak by se nemohly pouzit. Autor nepotrebuje "probrousit celou tabuli", ale jen zaznamy splnujici podminku na id_label, coz samozrejme mohou byt vsechny zaznamy, ale taky to muze byt treba tisicina vsech zaznamu, nevime o tom nic. Samotny select count from select myslim nikomu nevadil, ten nebude mit na vykon zadny vliv, zasadni je group by, kde bude dochazet k sortu. Ja analyticke funkce z oracle znam (ty ktere jsi pouzil), ale pokud by mi nekdo bez komentare predlozil tvuj dotaz, tak bych urcite na prvni pohled nevidel, co zjistuje. U puvodniho dotazu s group by to je ihned evidentni pro kazdeho, kdo zna zaklady SQL. Bohuzel jsem liny to nejak overovat, nicmene pochybuju, ze by v tomto konkretnim pripade mohlo pouziti analyticke funkce byt efektivneji vyhodnocene nez group by, protoze z vecneho hlediska se grupovat musi v obou pripadech a funkce count je nejtrivialnejsi z agregacnich funkci.

Založit nové vláknoNahoru

Tiskni Sdílej: Linkuj Jaggni to Vybrali.sme.sk Google Del.icio.us Facebook

ISSN 1214-1267   www.czech-server.cz
© 1999-2015 Nitemedia s. r. o. Všechna práva vyhrazena.