Pomoc u vezi sa MySQL query-jem

**Balsa** · 03-07-09, 10:15

Postavicu slucaj najjednostavnije, jer me, u sustini, najjednostavnija stvar koci.

Imam dvije tabele koje koristim u Query-ju:

1. Tabela simbola hartija od vrijednosti (crnogorske berze (Elektroprivreda, telekom i dr.))

2. Tabela svih transakcijama svih hartija u posljednjih nekoliko godina (preko 100.000 redova)

Treba mi query koji ce za svaki simbol iz prve tabele da izracuna prosjecnu cijenu simbola za njegov zadnji dan trgovanja.

NPR:

Code:

SELECT
s.simbol AS sim, 
SUM(t.cijena*t.kolicina)/SUM(t.kolicina) 

FROM simboli s LEFT JOIN transakcije t ON s.simbol = t.simbol 

WHERE 

t.datum ??????

GROUP BY sim

Imam query koji racuna kako treba, ali je prespor (treba mi minut da se izvrsi):

Code:

SELECT

simbol AS sim, 
(SELECT SUM(cijena*kolicina)/SUM(kolicina) FROM transakcije WHERE simbol=sim AND datum=(SELECT MAX(datum) FROM transakcije WHERE simbol=sim)) AS PosljednjaProsjecnaCijena 

FROM simboli

GROUP BY simbol

Bio bih zahvalan na svakoj pomoci

p.s. radi se o websajtu za mobilne telefone m.bullandbear.me.

**c00li0** · 03-07-09, 11:09

tesko da ces mozes ubrzati izvrsavanje kod ovolike tabele
probaj samo
SELECT SUM(cijena*kolicina)/SUM(kolicina)

zamijeniti sa

AVG(cijena*kolicina)

recimo ovako :

SELECT s.simbol as sim,AVG(t.cijena*t.kolicina)
from s,t
where s.simbol = t.simbol
and datum = (select max(datum) from t where simbol = sim)
group by s.simbol

mada ti ja i nisam neki strucnjak, al eto probaj...to ti je ono skolski

**Balsa** · 03-07-09, 11:18

Originally Posted by c00li0

tesko da ces mozes ubrzati izvrsavanje kod ovolike tabele
probaj samo
SELECT SUM(cijena*kolicina)/SUM(kolicina)

zamijeniti sa

AVG(cijena*kolicina)

recimo ovako :

SELECT s.simbol as sim,AVG(t.cijena*t.kolicina)
from s,t
where s.simbol = t.simbol
and datum = (select max(datum) from t where simbol = sim)
group by s.simbol

mada ti ja i nisam neki strucnjak, al eto probaj...to ti je ono skolski

Ovim cu dobiti prosjecnu vrijednost transakcije za datum. Meni treba prosjecna cijena ponderisana kolicinom

**Sandman** · 03-07-09, 15:24

Balsa, ti vidim imas query koji radi, ne znam koliko ga mozes ubrzati zamjenom nekih operacija (AVG umjesto SUM i sl...)

mozda da pogledas
http://www.mysqludf.org/ da napravis C f-ju pa je dodas u SQL

ili posto vidim da radis sajt (ne znam sa cime) pogledaj sajt:

http://pureform.wordpress.com/2008/0...mysql-and-php/

Nisam nikad radio to ali ako ti sto pomogne javi.

**Omega** · 03-07-09, 20:56

Imam query koji racuna kako treba, ali je prespor (treba mi minut da se izvrsi):

Ako ne nadjes drugi nacin zaobilazno to mozes rijesiti tako sto ce cron job pokretati skript svakih neznam koliko i snima nekifajl.htm tj kesiras sadrzaj

inace u ovim tabelama koja su polja indeksirana?

**Djoks** · 04-07-09, 23:17

Nijesam, nažalost, mnogo radio sa MySQL-om, ali teoretski:

- Uskladištena procedura bi mogla malo da pomogne (ubrza situaciju), ali ne mnogo,
- Cron Job ti neće biti od koristi jer se izvršava periodično, a tebi trebaju tačni podaci u svakom trenutku.

Moj savjet je:

U glavnu tabelu dodaj kolonu za prosječnu cijenu.

Implementiraj TRIGGER za tabelu transakcija: kada se transakcija doda/izmijeni/obriše - ažurira se prosječna cijena trgovanja za taj simbol u glavnoj tabeli.

Taj trigger će raditi izuzetno brzo, nećeš morati da misliš o "kobasicama" kod-a za traženi podatak dalje u kod-u i u svakom trenutku ćeš imati podatak o cijeni prostim SELECT upitom.

**Omega** · 05-07-09, 09:29

dobra ideja

**Balsa** · 05-07-09, 21:13

Originally Posted by Djoks

Nijesam, nažalost, mnogo radio sa MySQL-om, ali teoretski:

- Uskladištena procedura bi mogla malo da pomogne (ubrza situaciju), ali ne mnogo,
- Cron Job ti neće biti od koristi jer se izvršava periodično, a tebi trebaju tačni podaci u svakom trenutku.

Moj savjet je:

U glavnu tabelu dodaj kolonu za prosječnu cijenu.

Implementiraj TRIGGER za tabelu transakcija: kada se transakcija doda/izmijeni/obriše - ažurira se prosječna cijena trgovanja za taj simbol u glavnoj tabeli.

Taj trigger će raditi izuzetno brzo, nećeš morati da misliš o "kobasicama" kod-a za traženi podatak dalje u kod-u i u svakom trenutku ćeš imati podatak o cijeni prostim SELECT upitom.

Shto se uskladistenih procedura tiche, neizvodljivo je. MySQL verzija hostinga je 4.x a uskladistene procedure, chini mi se, su podrzane tek od MySQL 5.1.x.

Koliko mi se chini, i triggers zahtijevaju MySQL 5. Moracu da vidim sa hosting podrskom da prebace nas hosting na MySQL 5 server mashini...

Triggers-i mogu biti najbolje rjesenje za moj problem. ALI postoji jedno veliko ALI... Table Engine servera je MyISAM. Mislim da on zakljuchava cijelu tabelu prilikom nekog update-a. Ako su update-i chesti, moglo bi da dodje do gresaka prilikom ucitavanja podataka, a InnoDB kao table engine nije dozvoljen...

Znachi imam dvije misije:
1. Prebaciti server na MySQL 5
2. Ubijediti podrsku da omoguce InnoDB table engine

Vidjecemo kako sve ovo napreduje

**Djoks** · 05-07-09, 22:39

Mislim da o zaključavanju uopšte ne moraš da brineš na toliki obim transakcija. Već da je NYSE ili NASDAQ u pitanju - e onda bi se razmislilo o nečem boljem.

**Sandman** · 06-07-09, 00:03

Posto radis samo sa podacima iz zadnjeg dana (koliko sam razumio) sta mislis o kreiranju jos jedne tabele u kojoj ce biti kopija podataka samo za taj dan? Tu ne bi trebalo biti kasnjenja pri izvrsavanju.

**Balsa** · 06-07-09, 08:14

Originally Posted by Djoks

Mislim da o zaključavanju uopšte ne moraš da brineš na toliki obim transakcija. Već da je NYSE ili NASDAQ u pitanju - e onda bi se razmislilo o nečem boljem.

I to sto kazes

Sumnjam da ce iko naleceti na gresku

Hosting prebacen na MySQL 5.0.77. Sad cu da provjerim podrzava li triggers-e, i kako to sve funkcionise.

Originally Posted by Sandman

Posto radis samo sa podacima iz zadnjeg dana (koliko sam razumio) sta mislis o kreiranju jos jedne tabele u kojoj ce biti kopija podataka samo za taj dan? Tu ne bi trebalo biti kasnjenja pri izvrsavanju.

Nema nikakvih problema kad znam datum. Bez obzira na velicinu tabele, kad su mi datumi indexirani, racunanje za posljednji dan je brzo kao i kod prazne tabele. U mom slucaju problem se stvara kad treba da izracunam posljednju cijenu a datum je nepoznat, tj. mora da se nadje za svaki simbol. Onda pravi veliku tabeli u bufferu i to traje dugo.

Triggers-i ce mi pomoci da potpuno izbjegnem bilo kakvo racunanje - samo cu selektovati jedno polje i to je sve

**Djoks** · 06-07-09, 09:25

Podržava, ali za MySQL najbolje da skineš "MySQL Maestro" kako bi mogao lakše kreirati/ažurirati trigere (koji se inicijalno ne vide u klasičnim MySQL GUI alatima sa matičnog Web sajta).

http://www.sqlmaestro.com/products/mysql/maestro/

**Balsa** · 06-07-09, 12:10

Originally Posted by Djoks

Podržava, ali za MySQL najbolje da skineš "MySQL Maestro" kako bi mogao lakše kreirati/ažurirati trigere (koji se inicijalno ne vide u klasičnim MySQL GUI alatima sa matičnog Web sajta).

http://www.sqlmaestro.com/products/mysql/maestro/

E pa strashno... Prebachen hosting na MySQL 5 server, ali nemam "SUPER privilege" kako bih mogao da kreiram trigger. Websajt je hostovan na UniWebHosting-u. Kontaktirao sam podrsku sa zahtjevom da omoguce kreiranje Trigger-a.

Inace, ja koristim Navicat for MySQL koji ima tab za pregled Trigger-a. Sad cu da skinem ovaj koji si preporuchio da vidim je li sto bolji.

**Djoks** · 06-07-09, 12:23

U pravu si - evo sad gledam i ja (Uniwebhosting):

"SQL Error: Access denied; you need the SUPER privilege for this operation"

Kakva glupost MySQL-a... ne treba ti SUPER level da obrišeš tabelu, ali ti treba da kreiraš TRIGGER

**bass** · 08-07-09, 14:24

koji su tipovi podataka cijena i kolicina? da li ti je tabela indeksirana po datumu? ovo drugo ti je najbitnije. i naravno, razmotri ideju sa privremenom tabelom koja ce sadrzati gotove podatke. imas tabelu u koju se dodaju novi podaci (koliko-toliko) hronoloskim redosledom, a stari podaci se ne diraju. zasto bi svaki put preracunavao jedne te iste podatke, i to u tabeli od 100000 redova?

stored procedure, funkcije, triggeri i proracunata polja izbjegavaj u ovoj situaciji, zadace ti vise muke nego koristi.

**Djoks** · 08-07-09, 15:05

bass, o kakvoj privremenoj tabeli sa gotovim podacima govoriš?

to je rješenje gore od onoga što su prethodni naveli (uključujući mene). Zašto bi njemu trebala nova tabela u koju će unositi nove transakcije?

To bi, valjda, trebalo da skrati vrijeme izvršavanja pomenutog upita iz razloga što ima manje redova u tabeli? To je logika koje se drže programi za knjigovodstvo pisani u starijim tehnologijama, pa svake godine prazne tabele i stare podatke trpaju u backup.

A ako im zatreba analiza više godina - e onda, trt.

Elem, da ne skrećem - proces ne može biti MANJE opterećen u slučaju da se koristi trigger. Zašto? Postupak:

- Doda se nova transakcija u tabelu,
- SQL upit izračuna cijenu za sve transakcije iz tabele za datum unijete transakcije (ne za SVE datume iz tabele),
- Izračunata cijena se upisuje u glavnu tabelu hartije.

Ne vidim zašto bi ovo trebalo da zada više muke nego koristi? Lakše mu je da radi sa dvije tabele i piše kobasice od SQL-a svaki put kada treba da proračuna cijenu, a da ne govorim o vremenu izvršavanja SQL upita...

Ovako potpuno eliminiše overhead nad tabelama transakcija. Prolazi kroz minimalno neophodan broj redova, i ako nema transakcija - ne mora upitom ni da ulazi u tu tabelu da bi dobio statistike.

Stored procedure, funkcije, triggeri su IZMIŠLJENI da bi se olakšalo programeru i ubrzao postupak (da ne govorim sad o prednostima uskladištenih procedura, osobinama itd.), a ne da bi se time imalo "više muke nego koristi".

Još jedna dodatna tabela mu SIGURNO neće olakšati rad... Uopšte joj ne vidim svrhu... Uostalom - 100,000 redova u nekoliko godina je: ništa.

**Balsa** · 09-07-09, 08:26

Originally Posted by bass

koji su tipovi podataka cijena i kolicina? da li ti je tabela indeksirana po datumu? ovo drugo ti je najbitnije. i naravno, razmotri ideju sa privremenom tabelom koja ce sadrzati gotove podatke. imas tabelu u koju se dodaju novi podaci (koliko-toliko) hronoloskim redosledom, a stari podaci se ne diraju. zasto bi svaki put preracunavao jedne te iste podatke, i to u tabeli od 100000 redova?

stored procedure, funkcije, triggeri i proracunata polja izbjegavaj u ovoj situaciji, zadace ti vise muke nego koristi.

Naravno da su datum i simbol index-irani. Cini mi se da je u mom slucaju najbolje rjesenje trigger. Pokrece se samo kad se unese novi red (100-300 puta dnevno) i to koristi svega nekoliko redova kako bi izracunao cijenu i updateovao jedno polje.

Sve sto meni kasnije treba da dobijem posljednju prosjecnu cijenu je SELECT iz tabele. Nema nikakvih racunanja.

P.S. Testirao sam trigger na lokalnoj bazi i radi perfektno. Koliko mi muke zadaju ova glupa ogranichenja raznih webhostinga, dodje mi da sve lokalno radim i racunam i samo updateujem remote bazu sa izracunatim podacima...

P.S. 2
djoks, jesi li probao da ubacish foreign key na neku tabelu

I to je zabranjeno...

**Djoks** · 09-07-09, 08:58

Originally Posted by Balsa

P.S. 2
djoks, jesi li probao da ubacish foreign key na neku tabelu

I to je zabranjeno...

Užas

Toliko nelogičnosti u pravima pristupa... Nadam se da to nije nekakva predefinisana grupa koju je osmislio MySQL - to bi bilo poražavajuće...

Hoće li ti povećati prava admini, što vele?

---

Samo da ne bude ono kao kod nekadašnjeg Internet CG-a... Mom kolegi na Win hostingu trebali ASP.NET Web servisi, i nijesu radili... Zove on u PG i pita da li mu mogu dozvoliti hostovanje Web servisa (ASMX), a oni odgovore: "možda, ali nam prvo napišite jedno tekstualno objašnjenje što su Web servisi i kako funkcionišu, pa ćemo razmisliti da li da vam to omogućimo."

Pa da ne bude tako i sa triggerima kod Uniwebhostinga... :?

**Balsa** · 09-07-09, 09:56

Originally Posted by Djoks

Užas

Toliko nelogičnosti u pravima pristupa... Nadam se da to nije nekakva predefinisana grupa koju je osmislio MySQL - to bi bilo poražavajuće...

Hoće li ti povećati prava admini, što vele?

---

Samo da ne bude ono kao kod nekadašnjeg Internet CG-a... Mom kolegi na Win hostingu trebali ASP.NET Web servisi, i nijesu radili... Zove on u PG i pita da li mu mogu dozvoliti hostovanje Web servisa (ASMX), a oni odgovore: "možda, ali nam prvo napišite jedno tekstualno objašnjenje što su Web servisi i kako funkcionišu, pa ćemo razmisliti da li da vam to omogućimo."

Pa da ne bude tako i sa triggerima kod Uniwebhostinga... :?

Bojim se da ce tako biti sa uniweb-om. Dozvolili su remote MySQL (zbog chega sam i uzeo hosting kod njih) a, s druge strane, ne dozvoljavaju triggere i foreign keys... Mislim da nije MySQL predefinisana grupa, vec je UniWeb takvu grupu napravio. Mislim da na MySQL-u mozhesh da definishesh grupe sa pravima. Nevjerovatna glupost bi bila po defaultu oskrnaviti MySQL zabranom triggera, i shto je josh gore i meni neshvatljivo, zabranom foreign key-eva....

Mi inache imamo besplatan hosting kod Internet CG, ali ga ne koristimo jer je toliko ogranichen i tako davno podesen i nepromijenjen, da MySQL mozhda podrzava SELECT

**Balsa** · 22-07-09, 10:11

Posto jos nijesam dobio odgovor od podrske, i pitanje je ocu li ikad a i kad ga dobijem oce li me obradovati odobravanjem triggera - odlucio sam da potrazim neko rjesenje.

Nasao sam rjesenje svom problemu. Dodao sam jos jednu kolonu i tabelu simboli - posljednja cijena. Prije otvaranja berze pokrenuce se query koji ce pokupiti sve posljednje cijene za HoV (onaj spori query) i updateovati novo polje. Ovim cu dobiti posljednju prosjecnu cijenu za simbol prije danasnjeg dana, a spori query ce se pokretati jednom dnevno, i to lokalno i updateovati remote bazu.

Sve sto mi ostaje jeste da racunam danasnje prosjecne cijene. Posto je datum poznat, query za racunanje je izuzetno brz - 0,0017s.

Code:

SELECT

Simbol AS sim,
@CijenaDanas:=(SELECT SUM(Cijena * Kolicina) / SUM(Kolicina) FROM Transakcije WHERE datum = '2009-07-22' AND Simbol = sim) AS CijenaDanas,
IF(@CijenaDanas IS NULL, (SELECT ZadnjaCijena FROM Simboli WHERE Simbol = sim), @CijenaDanas) as Cijena

FROM Simboli

**Djoks** · 22-07-09, 10:40

Balša, da li ti vršiš unos redova u tabeli transakcija, ili to radi neko drugi?

Ako ti vršiš unos (imaš kontrolu nad INSERT upitom) - onda ti nije problem da u istoj transakciji odradiš i UPDATE (uradiš posao umjesto pomenutog TRIGGER-A) - i uvijek ćeš imati ažuran podatak?

**Balsa** · 22-07-09, 11:15

Originally Posted by Djoks

Balša, da li ti vršiš unos redova u tabeli transakcija, ili to radi neko drugi?

Ako ti vršiš unos (imaš kontrolu nad INSERT upitom) - onda ti nije problem da u istoj transakciji odradiš i UPDATE (uradiš posao umjesto pomenutog TRIGGER-A) - i uvijek ćeš imati ažuran podatak?

Ja vrsim unos. Problem mi je da uradim update jer mi je problem mali izracunati cijenu. Podatke dobijam parsiranjem (sjecas se mog prethodnog topica u vezi sa parsiranjem?

) pa mi je komplikovano raditi sa svim podacima u bufferu. Uz to, neke transakcije se poniste u toku dana, pa im status bude drugaciji. Isto tako ima i blokovskih transakcija koje ne ulaze u racunanje prosjecne cijene. Takodje ima i promjena poslova...

Previse mi je slozen proces racunanja za rad sa buffer-om...

**Djoks** · 22-07-09, 11:25

panjemaju! :?

Mislio sam da je unos straight-forward, pa jedan dodatni upit ne bi mnogo zakomplikovao... ali ovako ništa...

No dobro je - najvažnije je da si pronašao rješenje.

**Balsa** · 22-07-09, 11:51

Originally Posted by Djoks

panjemaju! :?

Mislio sam da je unos straight-forward, pa jedan dodatni upit ne bi mnogo zakomplikovao... ali ovako ništa...

No dobro je - najvažnije je da si pronašao rješenje.

Pa, u sustini, to jeste bila moja druga ideja. Posto transakcije iz buffer-a ubacim u lokalnu bazu (ne direktno iz buffer-a), da izracunam prosjecnu cijenu za danasnje simbole pa da updateujem tabelu simboli. U ovom slucaju bih usporio cijeli proces dodatnih par sekundi po svakom parsiranju (zbog slanja podataka u remote bazu) sto nije previse, ali imajuci u vidu da cijeli proces parsiranja i insertovanja novih transakcija traje manje od 10 sekundi, ipak te dvije sekunde dodaju 20%

Zato sam i odlucio da koristim prvu varijantu

Thread: Pomoc u vezi sa MySQL query-jem

Thread Tools

Display

Pomoc u vezi sa MySQL query-jem

Thread Information

Users Browsing this Thread

Similar Threads

pomoc oko query-a u Access-u

Pomoc u vezi font-ova

Molim pomoc u vezi (Lineage2)

Konektovanje na MySQL na drugom serveru?

MySQL i to ne znanje...

Bookmarks

Bookmarks

Posting Permissions