Sql petlja za prikaz rekorda

[ adopilot @ 19.11.2007. 08:02 ] @

Poštovani !

Zahtjev je slijdeći

Želio bih napraviti query gdije bi tableu artikala prikazao tako da svki pojedini artikal se ponovi onoliko puta kolika je njegova količina.

Ako imamo u tabli art imam polja id, sifra, naziv, kolikolina
kako forumlisati upit koji bi radio nešto na ovom pricipu

while (row_nuber < kolicina)
begin
select * from art
end

Unaprijed zahvalan

[ Teks @ 19.11.2007. 17:03 ] @

Pogledaj help oko kursora, nabaciću samo ideju kako bi išlo (treba dodati koda)

DECLARE clsArtikal CURSOR FOR
SELECT Artikal, Kolicina
FROM Artikal

OPEN clsArtikal
FETCH NEXT FROM clsArtikal INTO @Artikal,@Kolicina

WHILE @@FETCH_STATUS = 0
BEGIN

While @Kolicina>0
bEGIN
--INSERT PODATAKA u promenljivu tipa TABELA

@kolicina = @Kolicina - 1
END
END

CLOSE clsArtikal
DEALLOCATE clsArtikal

Select * from PrivremenaTabela

Imaj u vidu da su kursori sa petljama nužno ZLO!!!
Ako je @Kolicina veliko vredelo bi razmisliti kako bi se moglo
ovo izvesti bez petlje

[ adopilot @ 22.11.2007. 10:39 ] @

Evo jas sam uspio riješiti problem pa ću staviti skriptu
Možda posluži nekim novim narašajima kuji budu imali sličan problem

Code:

declare @skl_id int
set @skl_id=11
declare @kol float;
declare @brojac integer;
declare @art_naziv varchar (35);
declare @art_sifra varchar (15);
declare @barcode varchar (30);
declare @vpc money;
declare @c_stavke cursor;

set @brojac=1;

create table #deklaracija
(art_sifra varchar(15),art_naziv varchar(35),barcode varchar(30),vpc money);

set @c_stavke = cursor for
    select
        art.sifra as art_sifra,
        art.naziv as art_naziv,
        (select max(barcode.barcode) from barcode where barcode.ART_id=lager.art_id and osnovni=1) as barcode,
        lager.vpc as vpc,
        lager.kolicina as kol
        from lager inner join art on (lager.art_id=art.id)
        where skl_id=@skl_id;

open @c_stavke;

fetch next from @c_stavke into  @art_sifra, @art_naziv, @barcode, @vpc ,@kol;
while @@fetch_status=0
begin
    while (@brojac between 1 and @kol)
        begin
            insert into #deklaracija values (@art_sifra,@art_naziv,@barcode,@vpc);
            set @brojac=@brojac+1;
        end;
    fetch next from @c_stavke into  @art_sifra, @art_naziv, @barcode, @vpc,@kol;
    set @brojac = 1;
end;

close @c_stavke;

DEALLOCATE @c_stavke;

select * from #narudzba;

Lijep pozdrav

[ Zidar @ 22.11.2007. 14:36 ] @

Kursor ti absulutno ne treba, niti bilo kakva petlja. Ovako:

1. Kreiras tabelu Brojevi, CREATE TABLE Brojevi (Broj, int PRIMARY KEY)
2. Upises u tabelu Brojevi mnogo brojeva, vise nego sto ima sbilo kog artikla, recimo 100,000 brojeva od 1 do 100,000
Za ovo ti je dovoljan Excel, Access a moze i direkyno iz SQL servera

Onad napravis kveri ovako:

Code:

SELECT A.Sifra_Artikla, A.Naziv_Artikla, A.Kolicina, B.Broj
FROM Artikli AS A, Brojevi AS B
WHERE B.Broj <= A.Kolicina

Tablu Brojevi mozes popuniti i iz samog MS SQL na primer ovako:

Code:

DECLARE @i int
SEt @i=1

WHILE @i<=100000
    BEGIN
    INSERT INTO Brojevi (Broj) VALUES (@i)
    SET @i=@i+1
    print @i
    END

ali to ce ptrajati nekoliko minuta.

Iam i brzi nacin, ne mogu iz glave da napisem, zurim, ali dopisacu u sledecem postu.

[ Zidar @ 22.11.2007. 18:32 ] @

Kao što je obećano, evo brži način da se u tabelu Brojevi upiše 100,000 ili malo više redova (brojeva)

Code:

-- Ako ste ubacili neke brojeve, da ih uklonimo
DELETE Brojevi

-- sad da ih dodamo, u grupama od po 32,000

DECLARE @N int
SET @N=0    --- menjajte @N od 0 do 3 na primer
--- Svaki put ce se dodati 32000 brojeva u tabelu trenutno
; WITH MyCTE(i)AS
    (SELECT i = 1
        UNION  ALL
    SELECT i = i + 1
    FROM MyCTE
    WHERE i < 32000
    )
INSERT INTO Brojevi
SELECT i + @N * 32000
FROM MyCTE
OPTION (maxrecursion 32000)

Zadajte @N=0 i odradite skriptu. 32000 brojeva dodato u tabelu za 1 sek.
Zadajte @N=2 i ponovite skriptu. Novih 32000 brojeva dodato za 1 sek.
Zadajte @N=3 i ponovite skriptu. Novih 32000 brojeva dodato za 1 sek.

Imate 128,000 brojeva u tabeli za manje od 10 sekundi sa sve kucanjem. Ako to strpate u WHILE loop, onda je sve zaista trenutno.

Sad mogu da uradim ovo:

Code:

CREATE TABLE Proizvodi
([Proizvod] varchar(50) NOT NULL
, [Kolicina] int NOT NULL
, PRIMARY KEY ([Proizvod])
)

INSERT INTO Proizvodi (Proizvod, Kolicina) VALUES ('Jabuke', 5)
INSERT INTO Proizvodi (Proizvod, Kolicina) VALUES ('Kruske', 3)
INSERT INTO Proizvodi (Proizvod, Kolicina) VALUES ('Brskve', 4)

SELECT P.Proizvod, P.Kolicina, B.Broj
FROM Proizvodi AS P, Brojevi AS B
WHERE B.Broj <= P.Kolicina

Rezultat je naravno kako treba:

Code:

Proizvod    Kolicina        Broj
-------- ----------- -----------
Brskve             4           1
Brskve             4           2
Brskve             4           3
Brskve             4           4
Jabuke             5           1
Jabuke             5           2
Jabuke             5           3
Jabuke             5           4
Jabuke             5           5
Kruske             3           1
Kruske             3           2
Kruske             3           3

(12 row(s) affected)

[ degojs @ 23.11.2007. 02:08 ] @

^Komšo, eve ti +1 za poruku :)

[ adopilot @ 23.11.2007. 07:12 ] @

Dobra ideja vezano sa poređenjem količine naspram nekog rastućeg niza

Čak i nemoram puniti posebne tablice brojevima jer imam mijesta gdije mi je PK integer autoincrement već došao do 2 miliona rekorda

[ M E N E @ 23.11.2007. 13:16 ] @

Nemoj zaboraviti da mozda nekog IDENTITY reda nemas, mozda je obrisan. Ako imas 1, 2, 4 dobices 3 umesto 4 breskve :-)

[ Zidar @ 23.11.2007. 22:05 ] @

Kad smo već spomenuli IDENTITY, šta mislite, koji je najbrži način da se pronađu "rupe" u identity sekvencama?

[ DarkMan @ 25.11.2007. 20:29 ] @

Ja znam da pronadjem prvu "rupu" u identity sekvenci jednim upitom. U sustini i ne mora da bude identity sekvenca, dovoljno je da je PK samo rastuci prirodan broj.

[ Zidar @ 26.11.2007. 13:39 ] @

@Darkman:
OK, i prva rupa je rupa. Pokazi nam kako, pa cemo da vidimo sta cemo sa ostalim rupama :-)

[ DarkMan @ 26.11.2007. 19:36 ] @

Code:

declare @temp table(ID int)

insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
insert into @temp values(5)
insert into @temp values(6)
insert into @temp values(7)
insert into @temp values(9)
insert into @temp values(10)

-- prva rupa
select min(ID)+1 from @temp where ID+1 not in (select ID from @temp)
-- sve rupe
select ID+1 from @temp where ID+1 not in (select ID from @temp) and ID < (select max(ID) from @temp)

[ Teks @ 26.11.2007. 20:54 ] @

ne radi ako su rupe veće od 1

[ Zidar @ 27.11.2007. 14:01 ] @

Dovoljno dobro za diskusiju

Code:

SELECT ID+1
FROM @temp
WHERE ID+1 NOT IN (SELECTID FROM @temp)
AND ID < (SELECT MAX(ID) FROM @temp)

Definitivno je moguce napisati kveri koji pronalazi SVE rupe u sekvencama. Ima dosta knjiga u kojima je to objasnjeno. U jednoj od knjiga je dato i jedno neobicno resenje - pomocu tabele brojeva. Ako napravite dovoljno veliku tabelu brojeva, koja nema rupa, napravite LEFT JOIN sa vasom tabelom i eto vam svih brojeva koji nedostaju vasoj sekvenci.

Code:

SELECT B.Broj AS NedostajuciID
FROM Brojevi AS B
LEFT JOIN TabelaSaRupama AS T ON T.ID = B.Broj
WHERE T.ID IS NULL

U čemu je lepota ovakvog rešenja? Pre svega u jednostavnosti. LEFT JOIN se uči u svakoj školi i kursu, pa je broj ljudi koji bi znali da napišu ovakvo rešenje jako veliki. Broj ljudi koji ume da koristi subkverije je znatno manji. broj ljudi koji ume da upotrebi subkverije da bi pronašao rupe u sekvencama je još manji. Da nije tako, imali bismo bar 6 odgovora ovde, svi različiti i svi rade korektno. Umesto toga imamo jedan odgovor, koji radi skoro korektno, ali ne i potpuno korektno. Znači, kad pomislimo da imamo rešenje, treba da ga testiramo i pronađemo slabosti. Sa stanovišta efikasnosti rada, bolje je ono rešenje koje zahteva manje vremena i ima veću verovatnoću realizovanja.

[ Teks @ 27.11.2007. 18:37 ] @

Koliko sam razumeo, ideja je bila da rešenje bude u jednom query-ju,
dozvoljeno je koristiti samo postojeću tabelu brojeva sa rupama

Korišćenje dodatna tabela nije bilo zamišljeno u početku
jer da jeste mislim da bi se ova diskusija davno završila

Poenta je bila izbeći masovno dodavanje podataka u bazu (reda miliona)
čemu je svako rešenje manje više vodilo

Subquery ne posmatram kao poslednju reč tehnologije poznatu samo manjini
mislim da u svakom priručnike za sql možete naći nešto o tome u prvih par strana

[ Zidar @ 27.11.2007. 21:43 ] @

Citat:

Poenta je bila izbeći masovno dodavanje podataka u bazu (reda miliona)
čemu je svako rešenje manje više vodilo

Nisam to nigde primetio :-)
Covek je na pocetku trazio kveri, dali su mu kursor. Ponudjen je jos jedan predlog, pomocna tabela, sto se nekome moze dopasti a nekome ne. U svakom slucaju, nije zabranjeno niti nemoralno resenje:-)

Citat:

Subquery ne posmatram kao poslednju reč tehnologije poznatu samo manjini
mislim da u svakom priručnike za sql možete naći nešto o tome u prvih par strana

Iskustva su razlicita, ono sto sam ja video u praksi govori mi da subkveri i nije tako jednostavna stvar za vecinu. Ako spadas u manjinu kojoj pisanje subkverija u letu nije problem, svaka cast, kako ovde kazu 'Good for you'.

Forum je da se razmenjuju ideje. Kroz ideju o tabeli pokazali smo i kako se moze koristiti opcija WITH, novina koju je doneo SQL 2005.
Sto se tice miliona redova u nekoj tamo tabeli, ne bih se uzbudjivao mnogo oko toga, prostor na disku je danas jeftin. Gde ja radim, programer koji ume da pise subkverije u letu kosta bar 50,000 na godinu. Za 1800 sati godisnje to izadje oko 30 dolara na sat. Da se napise kveri koji pronalazi rupe u sekvencama, na primer, a radi korektno, treba 2 sata, za nekoga ko se prvi put srece sa takvim problemom. To je 60 dolara. Za 60 dolara mogu da kupim dovoljno gigabajta da pokrijem jednu takvu tabelu. A ne moram ni da je kreiram kao stalnu tabelu, sa WITH mogu da napravim virtualnu tabelu (nije ni temp, nego bas virtualna). Koliko RAMa mogu da kupim za 60 dolara?

Usput, jos ne videsmo potpuno korektno resenje za problem "pronaci brojeve koji nedostaju u sekvencijalnim nizovima rekorda" . Potrazite po prirucnicima, u prvih par strana, mozda ima cak i gotovo resenje, da se samo prepise.

Evo, kako je darkMan poceo:

Code:

DROP TABLE temp (ID int PRIMARY KEY CHECK (ID>0))

insert into temp values(1)
insert into temp values(2)
insert into temp values(5)
insert into temp values(6)
insert into temp values(7)
insert into temp values(10)
insert into temp values(15)

SELECT * FROM temp

         ID
-----------
          1
          2
          5
          6
          7
         10
         15

(7 row(s) affected)

-- DarkManovo resenje, sve rupe
select ID+1 from temp where ID+1 not in (select ID from temp) and ID < (select max(ID) from temp)

-----------
          3
          8
         11

(3 row(s) affected)

DarkMan je pronasao samo pocetak svake rupe. To nam ne kazuje kolika je rupa, koliko brojeva nedostaje. Hocu da vidim nesto kao

Code:

Nedostaju_Brojevi_OD          DO
-------------------- -----------
                   3           4
                   8           9
                  11          14

(3 row(s) affected)

Ovo sam dobio pomocu subkverija. Toliko je komplikovano da ne zelim da ga objavim odmah, dok ne vidimo da li ce neko ponuditi jednostavnije resenje

Teks, ti si na potezu :-)

A posle toga cemo prosiriti pitanje na: pokazati sve brojeve koji nedostaju. - hocemo recimo da ih insertujemo posto nedostaju.

[ chachka @ 27.11.2007. 23:53 ] @

Ah, napokon mozgalica :)

Code:
SELECT t1.id + 1 AS Nedostaju_Brojevi_OD,
       MIN(t2.id) - 1 AS Nedostaju_Brojevi_DO
  FROM temp AS t1
       LEFT OUTER JOIN
       temp AS t2
         ON t1.id < t2.id
GROUP BY t1.id
HAVING t1.id + 1 < MIN(t2.id)

Nemora $60, dovoljno je i pivo, ali lavovsko od 7% :)

[ BezPanike @ 28.11.2007. 11:50 ] @

Citat:

...Forum je da se razmenjuju ideje. Kroz ideju o tabeli pokazali smo i kako se moze koristiti opcija WITH, novina koju je doneo SQL 2005.
Sto se tice miliona redova u nekoj tamo tabeli, ne bih se uzbudjivao mnogo oko toga, prostor na disku je danas jeftin. Gde ja radim, programer koji ume da pise subkverije u letu kosta bar 50,000 na godinu. Za 1800 sati godisnje to izadje oko 30 dolara na sat. Da se napise kveri koji pronalazi rupe u sekvencama, na primer, a radi korektno, treba 2 sata, za nekoga ko se prvi put srece sa takvim problemom. To je 60 dolara. Za 60 dolara mogu da kupim dovoljno gigabajta da pokrijem jednu takvu tabelu. A ne moram ni da je kreiram kao stalnu tabelu, sa WITH mogu da napravim virtualnu tabelu (nije ni temp, nego bas virtualna). Koliko RAMa mogu da kupim za 60 dolara? ...

Zar se vrednost programera meri u odnosu na cenu RAM-a? Ili kako reče jedan (loš) programer kojeg znam: "Šta me briga što mi je kod spor, kad su računari su sve brži!"
Nije sve ni u $...
Gde nestade kreativnost, maštovitost i još par takvih sitnica?
Ako odavno niste videli elegantno rešenje problema pogledajte kod koji je dao chachka!
Imaš pivo od mene

[ srki @ 28.11.2007. 14:19 ] @

Nije vam fora da mozgalice stavljate samo u MSSQL forum :-)

Evo kako se slicna stvar radi u Oracle-u:

Code:

SELECT * FROM
     (SELECT id+1 AS "Nedostaju Brojevi OD",
             LEAD(id-1) OVER (ORDER BY id) AS "Nedostaju Brojevi Do"
      FROM temp)
WHERE "Nedostaju Brojevi OD" <= "Nedostaju Brojevi Do"

Kada krenete redovno da koristite analiticke funkcije onda vam se totalno promeni mindset pa skoro uvek prvo pokusate tako da nadjete resenje. Nazalost MSSQL ima samo parcijalnu podrsku za analiticke funkcije pa ovo tu nije moguce tako uraditi i zbog toga ce sporije da se izvrsava jer mora da se radi veoma skup join. Na Oracle-u klasicno resenje radi 3-4 puta sporije nego ovo uz pomoc analitickih funkcija.

_{[Ovu poruku je menjao srki dana 29.11.2007. u 11:32 GMT+1]}

[ adopilot @ 28.11.2007. 14:55 ] @

Pošto smo pronašli šuplje brojeve šta onda

Kako se to najefikasnije može ponovo presortirati

Pogotovo kada se radi rastućem broju koji je PK za tablicu i FK na oko 10 tablica

Da li se mora naruke prenumerisati ID polje ili postoji neka funkcija cascade update
kao što postoji u nekim slučajevima cascade delete
kada postoje veze definisane

[ Zidar @ 28.11.2007. 17:50 ] @

@Srki, Chachka

Citat:

Nije vam fora da mozgalice stavljate samo u MSSQL forum :-)

Hvala obojici na ukljucenju u diskusiju i zaista elegentnim resenjima.
Ma nisam ocekivao da ovo ispadne mozgalica, Teks me je ubedjivao da je ovo prosto i ja mu poverovao. Kad sam pokusao sam da resim problem, iz glave, shvatio sam da nije prosto jer ni u knjigama nisam mogao da nadjem dovoljno prosto resenje. Sva sreca sto nisam svoj kod prilozio:-)

@BezPanike:

Citat:

Zar se vrednost programera meri u odnosu na cenu RAM-a?

Sve se meri parama. Proizvodjna softvera za trziste ili za internu upotrebu jeste proizvodnja i svaka proizvodnja mora da bude sto ekonomicnija. Primeti da smo resenja dobili od Chachke i Srkija. Njih dvojica ni slucajno ne spadaju u kategoriju prosecnih programera. Takvi kao njih dvojica se ovde kod mene tesko nalaze, a i kad ih nadjes obicno nemas para da ih platis....

Ovo ti je kao fudbal. Imas nemacki fudbal, kako se nekad zvao 'industrijski fudbal', i imao si nas jugoslovenski, sinonim za kreativnost, maštovitost i još par takvih sitnica. Nemacka taktika se zasnivala na sto kracem putu da se lopta ubaci u mrezu, nasa na tome da Dzaja izludi Fogtsa na sredini terena. Mi ih dobijemo prvo poluvreme 2:0 i stanemo, dosta nam je bilo lepote, pokazali smo im sto znamo. Oni uvedu rezervistu Bonofa i nastave da trce kao da se nista nije desilo i mi izgubimo 4:2. Utakmica se igrala davne 1976 na finalnom turniru prvenstva Evrope u Beogradu. Pobedili su Česi, 1:0 nad nemcima u finalu. Mi smo zavrsili na cetvrtom mestu, posto smo izgubili od Holandije 3:2 u borbi za trece mesto. Od tada su nemci osvojili dve tri svetska prvenstva, holandjani igrali u nekoliko finala, a mi, znamo sta je bio. Toliko o dugorocnim efektima efikasnosti :-)

Kreativnost, maštovitost i još par takvih sitnica je dobra kombinacija, i to se trazi i podrzava, nema zbora. Probelm je sto kreativnost, maštovitost i još par takvih sitnica ponekad proizvede rezultat a ponekad ne, a utrosak vremena je isti u oba slucaja. Ako sam sa klijentom dogovorio da se trazenje rupa u sekvencama plati dva sata po 150 dolara, a kreativnost, maštovitost i još par takvih sitnica potrose 6 sati koje placam 50 na sat, ja sam na nuli a resenja mozda imama a mozda i nemam. Ako mi glupava tabela od 100 miliona rekorda resi taj problem za 1 sat, moj kreativni i mastoviti programer je slobodan da se posveti nekom drugom problemu za koga nemam primitivno resenje pomocu tabele od 100 miliona rekorda. A zaradili smo neto $250 pa ce biti za platu i sledeceg meseca. Vuk sit i jarici na broju.

@ adopilot

Citat:

Pošto smo pronašli šuplje brojeve šta onda

Kako se to najefikasnije može ponovo presortirati
Pogotovo kada se radi rastućem broju koji je PK za tablicu i FK na oko 10 tablica

Ako bas moras, mozes da na FK stavis CASCADE UPDATE. CASCADE UPDATE postoji na MS SQL, pitanej je da li ti zaista zelis to da uradis. Ako ti je sekvencijalni PK potpuno vestacki pa ga korisnik nikada ne vidi, CASCADE UPDATE ce verovatno zavrsiti posao (uradi back up pre toga :-) Ako se tvoj PK nasao na nekim odstampanim dokumentima, recimo fakturama koje su otisle kupcu, onda ne diraj nista. Ako pak imas prirodni kljuc, makar ga nisi proglasio za PK, zapitaj se da li ti treba vestacki kljuc povrh toga.

:-)