Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

SQL Mozgalice, mozgalica broj 3:

[es] :: Baze podataka :: SQL Mozgalice, mozgalica broj 3:

[ Pregleda: 4685 | Odgovora: 17 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.dialup.neobee.net.

Sajt: www.baze-podataka.net


+4 Profil

icon SQL Mozgalice, mozgalica broj 3:12.03.2007. u 16:58 - pre 207 meseci
Imamo dve tabele:
- fakture = Tabela u kojoj cuvamo podatke o fakturama (broj, datum, rok placanja, ...)
- stavke_faktura = Tabela u kojoj cuvamo podatke o stavkama samih faktura (sifru proizvoda, kolicinu, cenu, ...)

Tabela stavke_faktura je povezana sa tabelom fakture preko broja fakture. Zbog jednostavnosti, izostavljamo nepotrebne atribute, i zadrzavamo samo kljucne kolone. Dakle imamo:
Code:

CREATE TABLE fakture (
  broj_fakture INTEGER NOT NULL,
  CONSTRAINT pk_fak PRIMARY KEY (broj_fakture),
  CONSTRAINT ch_fak_broj CHECK (broj_fakture > 0)
);

CREATE TABLE stavke_faktura (
  broj_fakture INTEGER NOT NULL,
  redni_broj_stavke INTEGER NOT NULL,
  CONSTRAINT pk_sfa PRIMARY KEY (broj_fakture, redni_broj_stavke),
  CONSTRAINT ch_sfa_redni_broj CHECK (redni_broj_stavke > 0),
  CONSTRAINT fk_sfa_fak FOREIGN KEY (broj_fakture) REFERENCES fakture
);

U ovim tabelama su se vremenom nakupili sledeci podaci:
Code:

INSERT INTO fakture (broj_fakture) VALUES (1);
INSERT INTO fakture (broj_fakture) VALUES (2);
INSERT INTO fakture (broj_fakture) VALUES (3);
COMMIT;

INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (1, 1);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (1, 2);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (1, 4);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (1, 7);

INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (2, 1);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (2, 2);

INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (3, 2);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (3, 3);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (3, 4);
INSERT INTO stavke_faktura (broj_fakture, redni_broj_stavke) VALUES (3, 6);
COMMIT;

Kao sto se da videti, imaamo rupe u numerisanju redova.
Kako se stavke mogu iznova numerisati tako da nemamo rupa i da na nam tabela stavke_faktura sadrzi:
Code:

broj_fakture   redni_broj_stavke
------------   -----------------
           1                   1
           1                   2
           1                   3
           1                   4
           2                   1
           2                   2
           3                   1
           3                   2
           3                   3
           3                   4

???

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming."
- Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
 
Odgovor na temu

goranvuc
Goran Vucicevic
Novi Sad

Član broj: 4934
Poruke: 1846
*.dialup.neobee.net.



+41 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:12.03.2007. u 17:53 - pre 207 meseci
Ako si mislio na cisti SQL UPDATE evo nesto sto radi u MS SQL:
Code:

UPDATE stavke_faktura 
SET stavke_faktura.redni_broj_stavke = TFinal.TBroj 
FROM stavke_faktura, 
    (SELECT broj_fakture, redni_broj_stavke, 
           (SELECT COUNT(*) 
            FROM stavke_faktura AS TT 
            WHERE TT.broj_fakture = TS .broj_fakture AND TT.redni_broj_stavke <= TS .redni_broj_stavke) AS TBroj 
     FROM stavke_faktura AS TS) AS TFinal 
WHERE stavke_faktura.broj_fakture = TFinal.broj_fakture AND stavke_faktura.redni_broj_stavke = TFinal.redni_broj_stavke

Trebalo bi da prodje u svim DBMS koji imaju mogucnost referenciranja na podupite. Naravno, u praksi ovo nikad ne bi koristio na nekom uslovno receno "vecem" setu podataka, a nadam se da znas i zasto.
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:12.03.2007. u 19:55 - pre 207 meseci
Malo sam formatirao radni deo Goranovog kverija, da se lakse razume

Code:

SELECT 
     TS.broj_fakture
   , TS.redni_broj_stavke
   , (SELECT COUNT(*) 
       FROM stavke_faktura AS TT 
       WHERE TT.broj_fakture = TS .broj_fakture 
           AND TT.redni_broj_stavke <= TS .redni_broj_stavke) AS TBroj 
FROM stavke_faktura AS TS

Subkveri koji izracunava TBroj

Code:

(SELECT COUNT(*) 
       FROM stavke_faktura AS TT 
       WHERE TT.broj_fakture = TS .broj_fakture 
           AND TT.redni_broj_stavke <= TS .redni_broj_stavke) AS TBroj 

izracunava redosled tako sto broji koliko stavki ima isti ili jednak redni_broj_stavke u okviru tekuce fakture. Goran se plasi da bi kveri bio spor u produkciji. Mozda, ako ste maler da imate nekoliko stotina hiljada faktura i nekoliko miliona stavki. A onda padne noc i DBA odradi UPDATE u 2 po ponoci i sta vas briga ako ce to da se vrti 20 minuta. Ako do sledeceg dana imate novih 500,000 faktura da popravite, vi ste srecna kompanija. Sa tolikom prodajom mozete da kupite svemirski brod od racunara za 256 procesora i 128 ultra hiper brzih diskova i 512 TB RAM na svakom procesoru i ovo ce da se izvrsi veom brzo

Ako zamislite ovaj kveri kao deo neke stored procedure, koja prima podatke od web aplikacije, koja obradjuje jednu fakturu u jednom trenutku, i pre upise u tabele treba da prenumerise redni_broj_stavke, ovo je sasvim OK resenje. I programeri ne moraju na front endu da se muce sa prenumeracijom redova na fakturi. eto kako dobar SQL majstor moze da ustedi vreme programerima

Posto subkveri radi u stvari numeraciju redova, moze li neko da se seti jos jednog nacina za numeraciju redova? To doduse nije po standard SQL, i novst je u MS SQL, ima i u ORACLE i DB@ i zasigurno i vecina novijih verzija ostalih RDBMS to ima. Mislim da se zove Row_Number OVER() ili nesto slicno. Ne radi nista brze nego Goranov subkveri, ali bar ne morate da mozgate kako da sastavite subkveri i sa cime da ga vezete u glavnom kveriju. Zna li neko?


Edit: Ubačeni su kod tagovi

[Ovu poruku je menjao chachka dana 02.09.2007. u 01:50 GMT+1]
 
Odgovor na temu

srki
Srdjan Mitrovic
Auckland, N.Z.

Član broj: 2237
Poruke: 3654
..lecom.global-gateway.net.nz.



+3 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 11:33 - pre 207 meseci
Citat:
Zidar:Mislim da se zove Row_Number OVER() ili nesto slicno. Ne radi nista brze nego Goranov subkveri, ali bar ne morate da mozgate kako da sastavite subkveri i sa cime da ga vezete u glavnom kveriju. Zna li neko?
:-)

Radi brze jer ne mora za svaki red da se ponovo racuna taj select count(*). Umesto da pisemo
Code:
SELECT broj_fakture, redni_broj_stavke, 
           (SELECT COUNT(*) 
            FROM stavke_faktura AS TT 
            WHERE TT.broj_fakture = TS .broj_fakture AND TT.redni_broj_stavke <= TS .redni_broj_stavke) AS TBroj 
     FROM stavke_faktura AS TS

mozemo da napisemo
Code:

SELECT broj_fakture, redni_broj_stavke, row_number() over(order by redni_broj_stavke) AS TBroj
FROM stavke_faktura 
GROUP BY broj_fakture


Ovo ce samo jednom da ga grupira pa onda za svaku tu grupu da gleda redni broj a ne da za svaki red ponovo indirektno grupira po tom redu (sto radi onaj condition iz where)
Umesto row_number() moglo je da se koristi count(*).

[Ovu poruku je menjao srki dana 14.03.2007. u 02:42 GMT+1]
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 16:06 - pre 207 meseci
Hvala Srki, to na to sam mislio. Nisam bio siguran da li radi brze, ali ti verujem na rec. U svakom slucaju lakse se pise. Ali, to je resenj za MS SQL.

Ima li neko nesto za Firebird, PostgreSQL, MySQL, Access ili sta bilo drugo? Nemojte da ovo psotane MS SQL pricaonica.
Ako imate ideju koja lici na razumno resenja, dajte je pa makar i ne radila. Ili ce se naci majstor koji ce ideju razraditi, ili ce neko dokazati da ne valja ideja, pa cemo ubuduce tu ideju izbegavati.

 
Odgovor na temu

srki
Srdjan Mitrovic
Auckland, N.Z.

Član broj: 2237
Poruke: 3654
..lecom.global-gateway.net.nz.



+3 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 20:29 - pre 207 meseci
To je bilo resenje za Oracle :). Mislim da je ovo resenje skladu sa SQL-99 (sql-2003 sigurno ima) ali samo treba zameniti row_num() sa count(*) jer ne znam da li row_number() pripada standardu. Postgre isto podrzava analiticke funkcije.

[Ovu poruku je menjao srki dana 14.03.2007. u 02:42 GMT+1]
 
Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.dialup.neobee.net.

Sajt: www.baze-podataka.net


+4 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 21:09 - pre 207 meseci
Evo i resenja sa grupisanjem, koje provereno radi na PostgreSQL 8.1:
Code:

UPDATE stavke_faktura
   SET redni_broj_stavke = s.novi_redni_broj_stavke
  FROM (SELECT s1.broj_fakture, s1.redni_broj_stavke, COUNT(s1.redni_broj_stavke)
          FROM stavke_faktura AS s1
               INNER JOIN
               stavke_faktura AS s2
                 ON s1.broj_fakture = s2.broj_fakture
                AND s1.redni_broj_stavke >= s2.redni_broj_stavke
         GROUP BY s1.broj_fakture, s1.redni_broj_stavke
       ) AS s
 WHERE s.broj_fakture = stavke_faktura.broj_fakture
   AND s.redni_broj_stavke = stavke_faktura.redni_broj_stavke

SELECT deo se izvrsava samo jednom.

Goranovo resenje takodje radi na PostgreSQL 8.1
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming."
- Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
 
Odgovor na temu

goranvuc
Goran Vucicevic
Novi Sad

Član broj: 4934
Poruke: 1846
*.DIALUP-SMIN.neobee.net.



+41 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 21:37 - pre 207 meseci
Srdjane, proveri jos jednom, cini mi se da nigde nisi naveo Alias "novi_redni_broj_stavke" a koristis ga za setovanje vrednosti (pretpostavljam da si samo zaboravio da uz onaj Count() stavis i alijas)

Pozdrav, Choka rules!
 
Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.dialup.neobee.net.

Sajt: www.baze-podataka.net


+4 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:13.03.2007. u 22:04 - pre 207 meseci
Hvala
fali COUNT(...) AS ...
Code:

UPDATE stavke_faktura
   SET redni_broj_stavke = s.novi_redni_broj_stavke
  FROM (SELECT s1.broj_fakture, s1.redni_broj_stavke, COUNT(s1.redni_broj_stavke) AS novi_redni_broj_stavke
          FROM stavke_faktura AS s1
               INNER JOIN
               stavke_faktura AS s2
                 ON s1.broj_fakture = s2.broj_fakture
                AND s1.redni_broj_stavke >= s2.redni_broj_stavke
         GROUP BY s1.broj_fakture, s1.redni_broj_stavke
       ) AS s
 WHERE s.broj_fakture = stavke_faktura.broj_fakture
   AND s.redni_broj_stavke = stavke_faktura.redni_broj_stavke

Rules nego sta!
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming."
- Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
 
Odgovor na temu

srki
Srdjan Mitrovic
Auckland, N.Z.

Član broj: 2237
Poruke: 3654
*.adsl.xtra.co.nz.



+3 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:14.03.2007. u 01:55 - pre 207 meseci
Citat:
goranvuc: Srdjane, proveri jos jednom, cini mi se da nigde nisi naveo Alias "novi_redni_broj_stavke" a koristis ga za setovanje vrednosti (pretpostavljam da si samo zaboravio da uz onaj Count() stavis i alijas)

Nisam, stavio sam alias, AS TBroj. Jedino nisam na kraju stavio za taj ceo SELECT da bude AS TS.
 
Odgovor na temu

goranvuc
Goran Vucicevic
Novi Sad

Član broj: 4934
Poruke: 1846
*.dialup.neobee.net.



+41 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:14.03.2007. u 05:49 - pre 207 meseci
Citat:
srki: Nisam, stavio sam alias, AS TBroj. Jedino nisam na kraju stavio za taj ceo SELECT da bude AS TS.

@srki, pa nisi ti jedini Srdjan ovde
 
Odgovor na temu

srki
Srdjan Mitrovic
Auckland, N.Z.

Član broj: 2237
Poruke: 3654
..lecom.global-gateway.net.nz.



+3 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:14.03.2007. u 09:52 - pre 207 meseci
izvinjavam se
 
Odgovor na temu

Miloš Baić
Miloš Baić
ERP (Dynamics NAV) programer
Beograd

Član broj: 72468
Poruke: 1155
*.dialup.neobee.net.



Profil

icon Re: SQL Mozgalice, mozgalica broj 3:06.04.2007. u 16:22 - pre 206 meseci
Srđanov(Chachka) model sam doradio:
Code:
CREATE FUNCTION stavke_update() RETURNS trigger AS $stav_update_trigger$
 BEGIN
   UPDATE stavke_faktura
    SET redni_broj_stavke = s.novi_redni_broj_stavke
     FROM (SELECT s1.broj_fakture, s1.redni_broj_stavke, COUNT(s1.redni_broj_stavke) AS novi_redni_broj_stavke
          FROM stavke_faktura AS s1
               INNER JOIN
               stavke_faktura AS s2
                 ON s1.broj_fakture = s2.broj_fakture
                AND s1.redni_broj_stavke >= s2.redni_broj_stavke
         GROUP BY s1.broj_fakture, s1.redni_broj_stavke
       ) AS s
    WHERE s.broj_fakture = stavke_faktura.broj_fakture
     AND s.redni_broj_stavke = stavke_faktura.redni_broj_stavke;
   RETURN NEW;
 END;
$stav_update_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER stav_update_trigger AFTER DELETE ON stavke_faktura
    FOR EACH ROW EXECUTE PROCEDURE stavke_update();

Isprobano na PostgreSQL 8.2, gde daje željene rezultate.
U praksi, da li je korektno to tako napisati, ili UPDATE odraditi iz same aplikacije, dakle bez trigger -a!?!
Someone's sitting in the shade today because someone planted a tree a long time ago.
 
Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


+4 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:07.04.2007. u 10:10 - pre 206 meseci
Ovakav triger je smrt za performanse!

Recimo da imamo pola miliona stavki i da se cist UPDATE izvrsava za 2 minuta (realni podaci iz moje test baze).

Sta bi se desilo ako bismo imali predlozeni triger tipa AFTER DELETE FOR EACH ROW?
Brisanje jedne stavke bi izvrsavalo UPDATE nad svih pola miliona stavki i trajalo bi 2 minuta, brisanje 10 stavki bi trajalo 20 minuta, a brisanje svih stavki bi trajalo godinu dana!

Nikako se nesme dopustiti da ovaj triger barata sa svim stavkama. Ako ima malo stavki po fakturi, tada bi se eventualno mogao napraviti triger uz ogranicenje da preracunava redni broj stavki konkretne fakture kojoj je stavka obrisana.
Code:

...
WHERE s1.broj_fakture = OLD.broj_fakture
...


Prikazani UPDATE je servisnog karaktera, i treba ga izvrsavati smisleno i planirano, Goran i Zidar su jos na pocetku to napomenuli.
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming."
- Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
 
Odgovor na temu

Miloš Baić
Miloš Baić
ERP (Dynamics NAV) programer
Beograd

Član broj: 72468
Poruke: 1155
*.dialup.neobee.net.



Profil

icon Re: SQL Mozgalice, mozgalica broj 3:07.04.2007. u 14:04 - pre 206 meseci
@chachka
Hvala na objašnjenju.

BTW, test baza mi nije toliko velika, te nisam to uzeo u obzir.
Odnosno, nisam mislio na veći set podataka.
Ali vremenom, hipotetički, taj set se povećava i dolazi do svega što je objašnjeno.
Dakle, nije korektno.
Someone's sitting in the shade today because someone planted a tree a long time ago.
 
Odgovor na temu

priki

Član broj: 24732
Poruke: 700
212.103.132.*

ICQ: 174153511


+26 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:23.04.2007. u 07:56 - pre 206 meseci
prva stvar
jednom unešene stavke u bazu ne bi trebalo dirati
nikakav update slogova a pogotovo ne primary ključeva
ti redni brojevi se sredjuju na izveštaju ili ih središ na
unosu samog sloga,
recimo dodeljuješ prvi slobodan broj

ovo je za MS SQL:

Code:

SELECT ISNULL(MIN(novi_redni_broj_stavke)+1,1) AS mini 
FROM _Stavke
WHERE broj_fakture=1 -- parametarski unos
AND novi_redni_broj_stavke+1 NOT IN (SELECT novi_redni_broj_stavke 
                                                     FROM _Stavke 
                                                     WHERE WHERE broj_fakture=1)
 
Odgovor na temu

goranvuc
Goran Vucicevic
Novi Sad

Član broj: 4934
Poruke: 1846
*.dialup.neobee.net.



+41 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:23.04.2007. u 08:39 - pre 206 meseci
Citat:
priki: jednom unešene stavke u bazu ne bi trebalo dirati
nikakav update slogova a pogotovo ne primary ključeva


Bojim se da ces ovo morati da obrazlozis. Znaci, jednom kad nesto uneses ne diiiiiras - da li smo te dobro razumeli?
 
Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.suonline.net.

Sajt: www.baze-podataka.net


+4 Profil

icon Re: SQL Mozgalice, mozgalica broj 3:23.04.2007. u 09:57 - pre 206 meseci
Priki, sto se tice tvog ponudjenog resenja: ne postoji tabela '_Stavke', a kamo li njena kolona 'novi_redni_broj_stavke'. Pojasni malo.

Sto se tice sta se sme a sta se nesme, odlazimo offtopic jer poenta mozgalice nije diskusija o moralnoj i/ili krivicnoj ispravnosti menjanja podataka na fakturi. Fakture su uzete kao primer sasvim slucajno, jer sam smatrao da je to kao pojam bliska materija velikom broju ljudi.
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming."
- Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
 
Odgovor na temu

[es] :: Baze podataka :: SQL Mozgalice, mozgalica broj 3:

[ Pregleda: 4685 | Odgovora: 17 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.