SQL mozgalica za mene, za nekog mozda \\\'piece of cake\\\'

Pogledajte tabelu koju imam na raspolaganju (pretpostavljam podatke):


Hocu da dobijem sledece:


E sad, nijesam bio lijen, iz knjige OReily SQL Cookbook sam nasao resenje za saldo, tj. running sum, mada oni tamo predlazu da se vrsi uporedjivanje po kljucu, ja uporedjivanje moram vrsiti po datumu dospijeca, jer mi zapisi tako MORAJU biti sortirani. Evo kako to izgleda:



Pa sam jos pokusao i da ubacim ovo sa datumima i evo do cega sam dosao:


Drugi select je trenutni datum, a prvi select je datum prije njega.

Ovo mora ici kao subquery gornjeg queryja, pa je citav query:


i radi :), ali se bojim da je klimavo, i da se nedje krije kakav bug, a posto ce biti hiljade i hiljade ovih zapisa, a ovaj query ce biti zahtijevan svako malo, koliko ce se ovakav query uopste brzo izvrsavati, a mozda neko ima i totalno drugaciji pristup gornjem problemu.

Molim vas za pomoc bilo koje vrste.

Pozdrav.

1.da probas vako:


verovatno bi brze radilo ukoliko bi iso preko kursora, jer bi mogao u jednom prolazu resis problem.
Takodje ova funkcija DateDiff se mozda moze ubrazati jer verovatno u svakom redu izvrsava ponovno izracunavanje parametara, sto mozemo da premostimo sa:

Nisam pred bazom, a nisam ni veliki strucnjak za SQL, ali mi se cini da ono min(datum) from test daje minimalni datum citave tabele, tj. taj datum je konstantan tokom citavog upita, a meni treba razlika izmedju datuma dva uzastopna zapisa, trenutnog i sledeceg, koji se uvijek mijenjaju.

Ispravi me ako grijesim.

Pozdrav.

u pravu si, nisam to primetio

Pretpostavljam da se pitanje odnosi na rešavanje ovog problema na MS SQL bazi (sudeći po DateDiff).
Ako te interesuje kako se ovo elegantno rešava na Oracle bazi analitičkim funkcijama, modu da ti napišem...

Nisi nam objasnio dovoljno sta u stvari pokusavas da uradis. Iz tabele koju si dao i rezultata, ne uspevam da razumem sta radis. Mislim da znam sta hoces, ali nisi dovoljno jasno objasnio, pa mozda i gresim.

Pomoglo bi ako bi nam dao CREATE statement za tabelu i nekoliko INSER INTO da imamo neke podatke s kojima radimo. Kveri koji bi odradio sta ti treba nije jednostavan ali nije ni mozgalica. Medjutim, tesko ga je napisati iz glave, bez testiranja, pa bi zasta pomoglo ako nam das tets tabelu i neke podatke.

U odnosu na sta se racuna [Proteklo dana]? Meni se ucinilo na prvi pogled da se racuna od datuma koje ima red gde je ID=1, ali nije tako. O, vidim da je to razlika dva uzastona dana.

Sta predstavlja kolona [Saldo] u rezultatu? verovatno sumu svih ulaza i sumu svih izlaza do datuma za koji se racuna.

Pojasni sta ti treba i daj tabelu i test podatke.

Ajde da te ne mucim mnogo, ionako mi ne bi mnogo pomoglo jer su datumi formatirani drugacije nego kod mene ;-)


Tebi trebaju dve stvari: a) da izracunas razliku u danima izmedju dva uzastopna datuma u tabeli i b) saldo na tekuci dan, kao razlika ukupnog dugovanja do tog dana i ukupnog potrazivanja do tog dana. Ovo je bilo knjigovodstvo. Sledi SQL:



Sada da izracunamo saldo:


Na kraju sve ovo treba pokupiti u jedna kveri. ako odbacimo 'mdejurezultate' dobijemo nesto sto i ne izgleda tako strasno:


Napominjem da ORDER BY nema nikakvog uticaja na izracunavanje salda i razlike u danima. Svi kveriji mogu da se odrade i bez ORDER BY. Rezultati ce biti identicni za iste datume, samo rezultujuci skup podataka nece biti sortiran po datumima.

Da li bi ovo islo brze pomocu kursora? Za jako veliki broj podataka, verovatno. da li treba odmah napisati kursor? Verovatno da ne. Ako se lepo indeksiraju datumske kolone, moglo bi da bude brzo i ovako kako je. Nako bi mogao da testira za recimo 100,000 podataka ili 500,000 pa 5 miliona i tako dalje. U jednom momentu ce kveri postati suvise spor. Poslednji broj redova koji je bio dovoljno brz je granica kdaa treba preci na kursor.

Kako napraviti test tabelu sa 5 miliona rekorda? Koristite tabelu brojeva i tabelu kalendar. O cemu se radi? Pogledajte ovde:
http://sqlserver2000.databases...n-auxiliary-numbers-table.html
http://sqlserver2000.databases...-auxiliary-calendar-table.html
Niko se nije naucen rodio pa ni Zidar ;-) Sve trikove je pokupio po knjigama i po internetu.

Evo da se ispravim, pojasnjenje problema, retroaktivno:

Prvo, Zidar, svaka cast, majstor si!

Mada sam i ja zadovoljan kako sam sklepao onaj moj, ali sam se izglupirao trazeci trenutni datum, kada mi je on vec datum spoljasnjeg upita, i sto sam mjesto MAX isao sa TOP 1 ... ORDER BY ... DESC, e debila :)

Dakle, u pitanju je opstinska sluzba gradica dje ja zivim, kojoj su konacno dozlogrdila brda papira i hoce na racunaru da vodi evidenciju poreskih obveznika. Problem nastaje kod racunanja kamata kada obveznici zakasne sa uplatom (uplate nakon datuma dospijeca). Uplata obveznika je "Duguje" a vrijednost poreske prijave "Potrazuje". Kada poreski obveznik zakasni sa uplatom zaracunava mu se kamata od 0.03% dnevno, ali isto tako, kada je u pretplati, ima pravo na pozitivnu kamatu iste stope.

Prvo sam krenuo da "kopiram" sistem iz Podgorice (dali su mi na uvid primjer njihove kartice). Oni tamo nakon svake uplate obveznika rasknjizavaju kamate smjestajuci iznos na konto kamata (kreirajuci zapise u bazi).

Medjutim, kada dodje do retroaktivnih promjena, bilo izmijenjenom poreskom prijavom, ili pogresnom uplatom ili greskom operatera, kamate se skroz pobrkaju nakon ispravki, tako da je potrebno obaviti dosta posla. Ja sam razmisljao da kreiram posebnu log tabelu gdje bih cuvao podatke o tome koja uplata je "cistila" koje prijave i koliku je kamatu generisala, itd. kako bih kasnije mogao ispraviti stvari, ali sam brzo od toga odustao i krenuo u trazenje "cistijeg" rjesenja.

Naumio sam da svaki put nanovo racunam kamate, jer su one, na kraju krajeva, redundantne, zavise od drugih zapisa, te da izracunatu vrijednost umanjim za iznos uplata koje je obveznik izvrsio uplatama kojima regulise kamate (ne uplate koje se ondose na same poreske obaveze), i tako dobijem tacnu vrijednost.

I, evo, upravo dok kucam ovo, kapiram da imam propust u logici rjesenja, ali nije veliki, valjda. Da uzmem rezultat Zidarovog konacnog querija, samo Saldu mijenjam predznak jer kod njih ide Duguje - Potrazuje.



Prvih 45eur generise negativnu kamatu za 9 dana, a ne za NULL dana, tako da je: kamata = -45 * 9 * 0.0003.
Sledecih 22 dana generise negativnu kamatu za 56eur, dok bi za 14 eura pretplate isla pozitivna kamata za 11 dana.

Dakle, ispravno rjesenje bi znacilo da se "Proteklo dana" popne za jedan red navise, a da se zadnji zapis uporedjuje sa "danasnjim" datumom.

Penjanje bi se, cini mi se, rijesilo tako sto bi Zidareva datediff f-ja izgledala:


Ali kako da zadnji datum uporedim sa trenutnim. Ja totalno razmisljam na if, for, while nacin, tek treba da savladam SQL tako.

Mozda bi moglo na kraju:


ako to sintaksa dozvoljava...

Pozdrav, i hvala na trudu momci :)

P.S.
Da li sta mijenja ako ima dvije aktivnosti na isti datum?

Problem se moze ovajko preformulisati:
U skupu podataka koji generis 'zidarev kveri' treba izracunati kamatu tako sto se vrednost u koloni "Saldo" mnozi sa vrednoscu kolone "Proteklo dana" iz sledeceg reda. "Saldo" u poslednjem redu se uporedjuej sa "Proteklo dana" u odnosu na danasnji dan.

Da se ovo resi nije potreban loop niti kursor. Prvo je potrebno modifikovati 'zidarev kveri' tako da svi redovi dobiju redne brojeve.
Za tu svrhu MS SQL ima funkciju Row_number. Sve sto nam treba je da na pocetek kverija ubacimo liniju

Sintaksu za row_number mozes da nadjes u Books online ili u negde drugo. Zasto ne koristim kolonu ID iz tabele - pa nema garancije da ce redovi biti uneti bas u redosledu koji nama treba. Nije dobro biti zavistan od toga kako su fizicki uneti redovi u tabelu.

Kveri bi dakle izgledao ovako:

Primedbe na racunanje razlike u danima stoje. U tekstu ja sam zadrazo moj originalno kveri jer mi je tako lakse, sustina se na menja, za ono dsto hocemo da pokazemo a ja manje kucam Hocemo dakle da pokazemo kako se red uporedjuej sa sledecim redom. Sad bi poslednji kveri trebao sam sebe da pozove nekako. Postoji vise nacina da se to uradi. Starinsk nacin, iz vremena SQL 2000 i ranije jeste da se poslednji kveri sacuva kao view, pa se onda napravi JOIN na samog sebe. Ovako nekako:


Sad povezemo svaki red sa sledecim redom:

Rezultat je dobar ali nije kompletan, nedostaje resenje za poslednji red. Ideja sa UNION je na dobrom putu. Pitanje ej samo gde i kada ubaciti UNION. Ako pokusas sada, na kraju svega, moglo bi da prodje ali bi bilo komplikovano. Mnogo je lakse ubaciti UNION na samom pocetku. View koji smo kreirali cita podatke iz tabele SaldoTest. Tabela Saldotest u stvari ima jedan red manje nego sto bi trebalo. Tabeli treba nekako dodati jedan red na kraju koji bi imao Datum_Dospijeca = danasnji dan i nule u Duguje/Potrazuje. Najlakse je da se view napise ovako:


Svi kveriji bazirani vwSaldo radice isto kao i pre jer nismo promenili nazive kolona niti dodavali oduzimali kolone u view. Promenili smo FROM deo i dobili jedan red vise na izlazu.

Sto se tice ispravljanja greski i retroaktivne promene kamate, to je slozeniji pronblem koji opet mozemo da razbijemo na dva dela.
A) Ako uvedes tabelu Kamate sa kolonama (KamatnaStopa, VaziOd) i koja bi izgledala otprilike ovako:

KamatnaStopa VaziOd
----------------------
0.003 1 Jan 2008
0.0035 15 Jan 2008
0.0028 1 Apr 2008

Sada mozes da na scvaki datum u tabeli SaldoTest primenis kamatnu stopu koja je vazila na taj dan. Nije jednostavno ali tako nekako moze.

B) Sto se tice ispravke gresaka, i tu pomaze kolona VaziOd. Za svaki red u tabeli SaldoTest uvedes kolonu VaziOd, to je datum od kada vazi ta kolona. Red koji ima gresku se pretvara u novi red, sa novim datumom vazenja. Kad uzimas podatke za obracun kamate, uzimas one gde datum maksimalan. Kveriji se diodatno komplikuju, ali sta se moze.

AKom odes na ovaj sajt http://books.google.ca/books?i...;printsec=frontcover#PPA315,M1 i izaberes "Preview the is book" mozes da nadjes u sadrzaju poglavlje Working with temporal data. Tamo imas primer bas iz knjigovodstva o ispravljanju gresaka u transakcijama.

Bolji link: http://www.simple-talk.com/sql...on-managing-data-bitemporally/











_{[Ovu poruku je menjao Zidar dana 01.12.2008. u 17:04 GMT+1]}

Opet, Zidar, svaka cast na fori sa rednim brojevima, majstor si!

Posto cu te jos ponesto pitat u vezi ove iste stvari, ako te ne bude mrzjelo da odgovaras, evo test podataka iz moje baze:



Obveznik i konto su strani kljucevi drugih tabela. Obveznika ce biti oko 500, a konta ce biti 5 plus jos jedan konto na koji se uplacuju kamate.

Evo kako sam ja rijesio problem:



Rezultat queryja:


Ja sam isao drugim pravcem, mjesto da mnozim Saldo jednog reda sa ProtekloDana narednog reda, ProtekloDana racunam kao razliku trenutnog i sledeceg, a ne kao razliku trenutnog i proslog kao prije, tako da vec imam Saldo i ProtekloDana u istom redu. I to je radi super.

Medjutim, odamah ima nesto sto ne razumijem. Naime, ne mogu da referencirama kolone na osnovu alijasa, nego mi izbacuje gresku tipa ne moze da nadje kolonu Saldo i ProtekloDana, pa mjesto prostog Saldo * ProtekloDana moram ponovo da pisem izraze. Valjda je SQL server dovoljno pametan, pa nece istu stvar dva puta racunat, ali mi nije jasno zasto alijasi ne rade. Koristim SQL Server Express 2005.

Onda, vec razmisljam dalje, htio bih da napravim upit koji ce mi dati gotove iznose kamata za svakog obveznika i konto.

Zbog toga sam proba da rijesim isti problem bez WITH:



Ali pogledaj rezultat:


Tu je druga stvar koju ne razumijem. Ako stavim da je alijas FROM odjeljka BILO STA DRUGO osim rijeci DATA, prilikom pozivanja procedure prijavice mi gresku 'ne mogu da nadjem objekat, recimo, 'Test'. Tek kad stavim da je alijas DATA procedura onda prodje, ali se zadnji - UNION - zapis ne obradjuje. Uzmimo predzadnji zapis, datum 2008-04-11. Imamo vrijednost nula u koloni ProtekloDana, sto znaci da zadnje uporedjivanje t.Datum_dospijeca > Data.Datum_dospijeca daje false, pa SELECT MIN daje Null. Dakle, SELECT MIN ne vidi zadnji zapis. Kolona Saldo sadrzi ispravnih 51, jer ona zavisi samo od proslih zapisa i trenutnog zapisa (<=) dok kolona ProtekloDana zavisi od trenutnog i sledeceg (>). Ali ipak, eto ga zadnji zapis u rezultatu.

Problem je vjerovatno sto sam alijas nazvao Data. Ko zna koje Data SQL Server odje koristi, tabelu Data ili alijas Data???



Sto se tice zbirnog upita za sve obveznike, mora se opet preko ovih ugnjezdenih upita. Valjda. Prvo sracunam sve moguce kombinacije Obveznika i Konta koje se pojavljuju, pa onda treba da racunam kamate za svaku od njih. Ovi ugnjezdeni upiti su kao for each u C#.

E sad, treba da limitiram set podataka unutrasnjeg upita tako da odgovaraju samo onima iz spoljasnjeg. Tu WHERE i GROUP BY nista ne pomazu, jedva ukapirah. Moram WHERE unutar FROM, ili bi eventualno mogao da dodam jos dva uporedivanja t1.Obveznik = Data.Obveznik i t1.Konto = t2.Konto onome t1.Datum_dospijeca > Data.Datum_dospijeca. To je ono sto si pricao da ORDER BY nema uticaja na rezultat upita, jel?

Od resenja sa WITH sam odma odusta, ta klauzula mora valjda bit van svih ugnjezdavanja.

Evo kako sam probao:


Srednji SELECT je tu jer mi ne da da racunam tu sumu direktno u unutrasnjem upitu, kaze ne moze da racuna sumu nad poljem koje vec ima agregatnu f-ju ili ima korelisan upit. Ajde dobro.

Evo sta je rezultat ovog upita:


A trebalo bi da bude ovo (kada dva puta odradim pojedinacni upit):


Sigurno alijasi prave problem, ali ne znam zasto mi to rade. Pogledaj molim te, ako budes imao kad.


Pozdrav i hvala.

:-)
Moras da usporis malo. Ne razumem se mnogo u posao koji radis pa ne verujem da mogu dalje da ti pomognem mnogo. Gledanje u etst podatke ne pomaze mi mnogo, jer se u postu sve prelomi pa se ne vidi bas dobro. Dalje, da bih bilo sta uradio sa kverijima moram da imam podatke kod mene. Treba da prilozis CREATE izraz za atbelu i INERT izraze tako da ja mogu da reprodukujem podatrke kod mene. tek onda mogu da probam kverije koji rade i koji ne rade i mozda budem u stanju da pomognem. Bez toga - nista.

Vazi, majstore!