DevProTalk

DevProTalk (http://www.devprotalk.com/index.php)
-   SQL baze podataka - Sponzor: Baze-Podataka.net (http://www.devprotalk.com/forumdisplay.php?f=10)
-   -   Nezgodan upit (http://www.devprotalk.com/showthread.php?t=707)

Pedja 23. 02. 2006. 21:03
Nezgodan upit
 
Imam jedan problem ali nikako da ga namikarim.

Imam tabelu:

t1 (id, value, desc)

Value je unique, dakle ne mogu biti dva sloga sa istim value.


Iz ove treba da napravim:

t2 (id, value_min, value_max, desc_for_min, desc_for_max)

Dosao sam do:

Kôd:
SELECT
  id,
  MIN(value) as value_min,
  MAX(value) as value_max,
FROM t1
GROUP BY id
ORDER BY id
Sto je jednostavno ... ali nikako da mi sevne kako da prosirim upit da dodam i polja desc_for_min i desc_for_max gde ova dva polja predstavljaju vrednost desc za logove u t1 koji imaju za value vrednosti value_min i value_max.

Znam kako da resim sa dva SELECT-a ali bih ja hteo jednim :)

Hitno je ljudi...

noviKorisnik 23. 02. 2006. 21:56
Joj kako me zbunjuješ - id ti je strani ključ u T1, zar ne? A primarni ključ je u stvari value. Inače nema smisla da tražiš minimume i maksimume od value po id. ...

Trebalo bi da može sa subselektima da se to odradi, ali sam prilično zarđao po tom pitanju ;-) - glupo mi je da lupam sad ... ali verovatno hoću :-D

zextra 24. 02. 2006. 00:37
... ja kazem:



mysql> select 'asdf';

+------+

| asdf |

+------+

| asdf |

+------+

1 row in set (0.00 sec)



mysql> select 'asdf' as desc1;

+-------+

| desc1 |

+-------+

| asdf   |

+-------+

1 row in set (0.01 sec)


Jel bi sad znao? :)

Da kompletiram resenje:

Kôd:
INSERT `t2` (id, value_min, value_max, desc_for_min, desc_for_max)
SELECT
  id,
  MIN(value) as value_min,
  MAX(value) as value_max,
  'description min' as desc_for_min,
  'description max' as desc_for_max
FROM t1
GROUP BY id
ORDER BY id
U datom slucaju ti ne trebaju "as col_name" dodaci, posto je samo bitno da se poklopi broj kolona.

[edit]Napomena: gornji kod je sve jedan query, da se neko ne zbuni :D[/edit]

Pedja 24. 02. 2006. 01:10
Citat:
Originalno napisao noviKorisnik
Joj kako me zbunjuješ - id ti je strani ključ u T1, zar ne? A primarni ključ je u stvari value. Inače nema smisla da tražiš minimume i maksimume od value po id. ...
Da, bas tako.

Citat:
Originalno napisao zextra
'description min' as desc_for_min,
'description max' as desc_for_max
Xe xe, dobra sala :)

Evo kako sam resio stvar sa pomocnom tabelom:

Citat:
CREATE TABLE tempdata
SELECT
id,
MIN(value) as value_min,
MAX(value) as value_max,
FROM t1
GROUP BY id
ORDER BY id

SELECT
t.id,
t.value_min,
t.value_max,
tmin.desc as desc_for_min,
tmax.desc as desc_for_max
FROM tempdata t
LEFT JOIN t1 as tmin ON t.id = tmin.id
LEFT JOIN t1 as tmax ON t.id = tmax.id

DROP TABLE tempdata

zextra 24. 02. 2006. 01:16
[edit]Eve ga.. Prethodno sam mislio da hoces proizvoljan komentar za min i max vrednosti, lol :)[/edit]

mysql> select * from t1 order by id, value;

+------+-------+-------+

| id   | value | descr |

+------+-------+-------+

|    1 |     8 | var 8 |

|    1 |    24 | var 2 |

|    1 |    97 | var 4 |

|    2 |    12 | var 1 |

|    2 |    73 | var 7 |

|    2 |   102 | var 5 |

+------+-------+-------+

6 rows in set (0.02 sec)



mysql> select q1.id, q1.min, q2.max, s1.descr as 'd_min', s2.descr as 'd_max' from t1 s1, t1 s2, (select id, min(value) as 'min' from t1 group by id order by id) q1, (select id, max(value) as 'max' from t1 group by id order by id) q2 where s1.value=q1.min and s2.value=q2.max and q1.id=q2.id;

+------+------+------+-------+-------+

| id   | min  | max  | d_min | d_max |

+------+------+------+-------+-------+

|    1 |    8 |   97 | var 8 | var 4 |

|    2 |   12 |  102 | var 1 | var 5 |

+------+------+------+-------+-------+

2 rows in set (0.00 sec)


Kôd:
SELECT
  q1.id AS 'id',
  q1.min AS 'value_min',
  q2.max AS 'value_max',
  s1.descr AS 'desc_for_min',
  s2.descr AS 'desc_for_max'
FROM
  `t1` s1,
  `t1` s2,
  ( SELECT id, min(value) AS 'min' FROM `t1` GROUP BY id ORDER BY id ) q1,
  ( SELECT id, max(value) AS 'max' FROM `t1` GROUP BY id ORDER BY id ) q2
WHERE
      s1.value = q1.min
  AND s2.value = q2.max
  AND q1.id = q2.id

zextra 24. 02. 2006. 01:58
Off Topic: postovano greskom, moze li remove?

bluesman 24. 02. 2006. 03:42
Ja mislim da je ovde "skuplja dara nego mera" :) (ili kako vec bese... )

2 simple querija bi zavrsila posao jednostavnije nego sve predlozeno.

zextra 24. 02. 2006. 03:44
Moze li primer?

Pedja 24. 02. 2006. 07:50
Citat:
Originalno napisao zextra
Kôd:
...
FROM
  `t1` s1,
  `t1` s2,
  ( SELECT id, min(value) AS 'min' FROM `t1` GROUP BY id ORDER BY id ) q1,
  ( SELECT id, max(value) AS 'max' FROM `t1` GROUP BY id ORDER BY id ) q2
WHERE
...
Ovo moram da probam. Nikad mi ne bi palo na pamet da ovde uglavim SELECT. Ja sam probavao da slicno uradim u LEFT JOIN ali nije proslo posto funkcije ne rade u LEFT JOIN uslovu.

Dejan Topalovic 24. 02. 2006. 10:38
@Pedja: Ono rjesenje sa temp tabelom ce ti biti dosta sporije, nego da radis sa subselectima... Izbjegavaj to kad god mozes...

Inace, pokusao sam i ja da sklepam odgovarajuci upit, ali sam dosao do slicnog rjesenja kao zextra:

Kôd:
select t1.id, t2.id, t1.value, t2.value, t1.description, t2.description
  from test_dejan t1, test_dejan t2,
  (select min(value) min_value, max(value) max_value from test_dejan group by id) sub
  where t1.id = t2.id
    and t1.value = sub.min_value
        and t2.value = sub.max_value
Ne znam da li mogu da kopiram ovdje output od explain plan komande, a da se tekst ispravno prikaze... ? Ja cu kopirati, a nek neko od moderatora doda odgovarajuce tagove za formatiranje teksta.. :)

Prvo je izlistan rezultat moga upita, a potom rezultat upita, kojeg je postavio zextra.

Kôd:
  Operation                                                                      Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart        PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS                        1                              11.0246977930899                                                     
  HASH JOIN                                                                                            1                  114          11.0246977930899                                                     
    HASH JOIN                                                                                1                  70          7.51653332072425                                                     
      TABLE ACCESS FULL        ONOPER.TEST_DEJAN                        7                  308          3.00797875329891                                                     
      VIEW                                                                                          7                  182          4.00817220464087                                                     
        SORT GROUP BY                                                                7                  182          4.00817220464087                                                     
          TABLE ACCESS FULL        ONOPER.TEST_DEJAN                7                  182          3.00797875329891                                                     
    TABLE ACCESS FULL        ONOPER.TEST_DEJAN                        7                  308          3.00797875329891                                                     

       
       
        Operation                                                  Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart        PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS                        7                                  15.533449004233                                                     
  HASH JOIN                                                                                            7                  798          15.533449004233                                                     
    HASH JOIN                                                                                7                  581          12.0250878881496                                                     
      HASH JOIN                                                                                7                  399          7.51653332072425                                                     
        TABLE ACCESS FULL        ONOPER.TEST_DEJAN                7                  217          3.00797875329891                                                     
        VIEW                                                                                7                  182          4.00817220464087                                                     
          SORT GROUP BY                                                                7                  182          4.00817220464087                                                     
            TABLE ACCESS FULL        ONOPER.TEST_DEJAN        7                  182          3.00797875329891                                                     
      VIEW                                                                                          7                  182          4.00817220464087                                                     
        SORT GROUP BY                                                                7                  182          4.00817220464087                                                     
          TABLE ACCESS FULL        ONOPER.TEST_DEJAN                7                  182          3.00797875329891                                                     
    TABLE ACCESS FULL        ONOPER.TEST_DEJAN                        7                  217          3.00797875329891

zextra 24. 02. 2006. 11:20
Pa da, moze i tako a i bolje je - prvobitno sam uradio isto sto i ti pa sam posle rastavio na dva subselecta, za svaki slucaj :)

noviKorisnik 24. 02. 2006. 11:25
Hm, Stripy, kad si već ostavio ovo, jel možeš malo da rastumačiš ... nemam pojma o "explain plan" komandi, a deluje mi kao korisna stvar.

zextra 24. 02. 2006. 11:34
Mene cudi output ove explain komande - nije bio takav kad sam ja poslednji put gledao :) Osim ako nije koristio nesto razlicito od MySQL?

Explain komanda pomaze kod optimizacije upita koji se proteze na nekoliko tabela (npr 10) - pokazuje da li se definisani indeksi koriste efikasno, i da li se uopste koriste. Nije zgodno bas otvoriti 10 tabela, i procitati ih od pocetka do kraja u svakom upitu... Upotreba: prefiksujes kompletan SELECT upit sa EXPLAIN i rastumacis tabelu sa rezultatima :)

Imas vise o tome ovde: http://dev.mysql.com/doc/refman/4.1/en/explain.html

Dejan Topalovic 24. 02. 2006. 14:39
@zextra: Da, pogodio si - nije rijec o MySQL-ovom Explainu, nego o TOAD-ovom za Oracle, posto nemam pri ruci MySQL. :)
Doduse, to je cisti SQL ...

@noviKorisnik: Eto, dao ti je zextra link, pa malo prolistaj... Zna biti jako korisno kod velikih i komplikovanih upita, u kojima imas nekoliko tabela, subselecta i td.

ivanhoe 24. 02. 2006. 14:52
Citat:
Originalno napisao Dejan Topalovic
@Pedja: Ono rjesenje sa temp tabelom ce ti biti dosta sporije, nego da radis sa subselectima... Izbjegavaj to kad god mozes...
Jesi siguran oko ovoga? MySQL koristi interno temp tabele za subselecte ( a pretpostavljam i druge baze?), pa ne bi trebalo da je tako velika razlika ?!

Cisto na osnovu neke logike pricam, nisam merio, znaci ne kazem da nisi u pravu, nego me samo zanima da li pricas na osnovu nekog benchmarka ili odokativno?

Dejan Topalovic 24. 02. 2006. 15:02
@ivanhoe: Radio sam neke benchmarkove, ali davno jos, na verziji 3.23.*. Ne bi bilo lose uraditi i neki benchmark sa stored procedurom na verziji 5.*, sta kazes? Steta samo sto nemam pri ruci MySQL sa tom verzijom, ali ako ugrabim volje i vremena, sklepacu neki benchmark u vezi doticne situacije: temp tabela vs. subselect.

Mi u firmi radimo iskljucivo na Oracle-u i vrlo rijetko koristimo DDL komande (CREATE temp tabele i DROP temp tabele) u procedurama, osim kad bas moramo.

Konkretno, temp tabele koristimo za proces obrade racuna za Telekom Austria, a koji traje nekoliko sati. Zbog intezivnog sortiranja, grupisanja i td., ne smijemo da zatrpamo memoriju (za one koji poznaju Oracle - TEMP tablespace i SGA memorija), nego se sluzimo pomocnim temp tabelama, ali ih ne dropujemo, nego samo uradimo truncate tabele sa opcijom REUSE STORAGE, sto daleko poboljsava performanse cijelog procesa...

Iskreno, do sada u praksi jos nikada nisam imao potrebu da radim sa temp tabelama u MySQL-u.

Osim toga, zasto kreirati temp tabelu, kad se u novijim verzijama MySQL-a moze koristiti i View, koji je daleko performantniji od kreiranja i brisanja temp tabele?

Dejan Topalovic 28. 03. 2006. 17:49
Evo obavih par testova nabrzaka, da provjerim/dokazem da li je i koliko je brze koristiti podupite (subselects), nego privremene (temporary) tabele...

Kreirao sam dvije procedure - jednu sa privremenim tabelama, a drugu sa podupitom.
Osnovna tabela sa podacima se zove table1 i ima polja id, value i descr, pri cemu je value auto_increment. Tu tabelu sam popunio koristeci obicnu proceduru:

Kôd:
DELIMITER $$

DROP PROCEDURE IF EXISTS `testDB`.`fill_table1` $$
CREATE PROCEDURE `fill_table1`(INOUT broj_redova INT,
                              OUT poruka VARCHAR(45))
BEGIN
  -- deklaracija potrebnih varijabli
  DECLARE v_counter INT DEFAULT 0;
  DECLARE v_id INT DEFAULT 1;
  DECLARE v_descr VARCHAR(45);
  DECLARE v_ostatak INT DEFAULT 1;

  DECLARE exit HANDLER FOR SQLEXCEPTION ROLLBACK;

  -- oznacimo pocetak transakcije
  START TRANSACTION;

  -- petlja_za_unos_podataka
  WHILE v_counter < broj_redova
  DO
    -- povecaj brojac za 1
    SET v_counter = v_counter + 1;

    -- na osnovu ostatka dobijenog kada brojac podijelimo sa 7,
    -- odredjivacemo vrijednost v_id, odnosno kolone id:
    SET v_ostatak = MOD (v_counter, 7);

    IF v_ostatak = 1 THEN
      SET v_id = 1;
    ELSEIF v_ostatak = 2 THEN
      SET v_id = 2;
    ELSEIF v_ostatak = 3 THEN
      SET v_id = 3;
    ELSEIF v_ostatak = 4 THEN
      SET v_id = 4;
    ELSE
      SET v_id = 5;
    END IF;

    -- ovdje vrsimo unos podataka
    INSERT INTO table1 (id, descr)
    VALUES (v_id, CONCAT('description var ', v_counter));


    -- nakon svakih 100 iteracija (unosa), potvrdi unos sa COMMIT
    IF MOD (v_counter, 100) = 0 THEN
      COMMIT;
    END IF;

  END WHILE;

  COMMIT;

  SET poruka = CONCAT('Ukupno je uneseno ', v_counter, ' redova u tabelu');

END $$

DELIMITER ;
Pozovite tu proceduru sa npr. CALL fill_table1(500, @poruka).

Prva procedura sa temp tabelama - proc_temp():
Kôd:
DELIMITER $$

DROP PROCEDURE IF EXISTS `testDB`.`proc_temp` $$
CREATE PROCEDURE `testDB`.`proc_temp` ()
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
  CREATE TABLE tempdata AS
  SELECT id, MIN(value) min_value, MAX(value) max_value
    FROM table1
    GROUP BY id
    ORDER BY id;

    SELECT t1.id,
            t1.descr "Description za min",
            t2.descr "Description za max"
      FROM table1 t1,
            table1 t2,
            tempdata t
      WHERE t1.id = t.id
        AND t1.value = t.min_value
        AND t2.value = t.max_value;

  DROP TABLE tempdata;
END $$

DELIMITER ;
Druga procedura sa podupitom - proc_subselect():
Kôd:
DELIMITER $$

DROP PROCEDURE IF EXISTS `testDB`.`proc_subselect` $$
CREATE PROCEDURE `testDB`.`proc_subselect` ()
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
  SELECT t1.id, t1.descr, t2.descr
    FROM table1 t1, table1 t2,
        (select min(value) min_value, max(value) max_value from table1 group by id) sub
    WHERE t1.id = t2.id
      AND t1.value = sub.min_value
            AND t2.value = sub.max_value;

END $$

DELIMITER ;
Rezultat prve procedure sa privremenim tabelama:

Kôd:
mysql> call proc_temp();
+----+--------------------+---------------------+
| id | Description za min | Description za max  |
+----+--------------------+---------------------+
|  1 | description var 1  | description var 498 |
|  2 | description var 2  | description var 499 |
|  3 | description var 3  | description var 500 |
|  4 | description var 4  | description var 494 |
|  5 | description var 5  | description var 497 |
+----+--------------------+---------------------+
5 rows in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>
Rezultat druge procedure sa podupitima:

Kôd:
mysql> call proc_subselect();
+----+-------------------+---------------------+
| id | descr            | descr              |
+----+-------------------+---------------------+
|  1 | description var 1 | description var 498 |
|  2 | description var 2 | description var 499 |
|  3 | description var 3 | description var 500 |
|  4 | description var 4 | description var 494 |
|  5 | description var 5 | description var 497 |
+----+-------------------+---------------------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
Dakle, uocljivo je da su podupiti 5-6 puta brzi od koristenja privremenih tabela...


Vreme je GMT +2. Trenutno vreme je 02:02.

Powered by vBulletin® Verzija 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright © DevProTalk. All Rights Reserved.

Mišljenja, saveti, izjave, ponude ili druge informacije ili sadržaji nastali na Sajtu su vlasništvo onoga ko ih je kreirao, a ne DevProTalk.com, tako da ne morate da se oslanjate na njih.
Autori poruka su jedini odgovorni za ovakve sadržaje. DevProTalk.com ne garantuje tačnost, kompletnost ili upotrebnu vrednost informacija, stavova, saveta ili datih izjava. Ne postoje uslovi pod kojima bi mi bili odgovorni za štetu ili gubitak koji je posledica bilo čijeg oslanjanja na nepouzdane informacije, ili bilo kakve informacije nastale kroz komunikaciju između registrovanih članova.
Web sajt može sadržavati linkove na druge web sajtove na Internetu ili neke druge sadržaje. Ne kontrolišemo niti podržavamo te druge web sajtove, niti smo pregledali bilo kakve sadržaje na takvim sajtovima. Mi nećemo biti odgovorni za legalnost, tačnost ili prikladnost bilo kog sadržaja, oglasa, proizvoda, usluga ili informacije lociranim na ili distribuiranih kroz druge web sajtove, niti za bilo kakvu štetu nastalu kao posledica takvih informacija. DevProTalk.com drži i čuva druga prava vlasništva na web sajtu. Web sajt sadrže materijale zaštićene copyright-om, zaštitne znakove i druge informacije o pravu vlasništva ili softver. Članovi mogu poslatu informacije zaštićene pravima vlasništva njihovih nosilaca i ona ostaju zaštićena bez obzira da li su oni koji prenose te informacije to naveli ili ne. Osim informacija koje su u javnom vlasništvu ili za koje dobijete dozvolu, nemate pravo da kopirate, modifikujete ili na bilo koji način menjate, objavljujete, prenosite, distribuirate, izvršavate, prikazujete ili prodajte bilo koju informaciju zaštićenu pravima vlasništva. Slanjem informacija ili sadržaja na bilo koji deo DevProTalk.com, Vi automatski dozvoljavate i predstavljate garanciju da imate pravo da dozvolite DevProTalk.com ili članovima DevProTalk.com bespovratnu, kontinualnu, neograničenu, globalnu dozvolu da koriste, kopiraju, izvršavaju, prikazuju i distribuiraju takve informacije i sadržaje i da iz takvih sadžaja koriste bilo koji deo u bilo koje svrhe, kao i pravo i dozvolu da koriste gore navedene sadržaje. Svi zaštitni znakovi (trademarks), logotipi, oznake usluga, firme ili imena proizvoda koji se pominju na ovom web sajtu su vlasništvo kojim raspolažu njihovi vlasnici.