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

Malo komplikovaniji upit

[es] :: MySQL :: Malo komplikovaniji upit

[ Pregleda: 2130 | Odgovora: 9 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

nnn
Tech Lead
Haxr.io
Beograd

Član broj: 16743
Poruke: 679
79.101.209.*



+87 Profil

icon Malo komplikovaniji upit12.02.2010. u 18:50 - pre 171 meseci
Malo komplikovaniji upit. Radim nesto u php/mysql apache i mysql se nalaze na odvojenim serverima a meni je bitno da sto vise olaksam apache, tako da bi hteo da prenesem jedan deo na mysql.

Pojednostavnicu malo
Imam dve tabele, games i players

new_game_info(ID,winnrer) i jos dosta stvari ali ove su vazne, winner moze biti 0, 1, 2. 0- nije se zavrsila igra( ignorisemo ), 1 prvi tim, 2 drugi tim.
new_game_players(ID, nickname, game_id, player_id, kills, deaths) i opet jos dsta ali ove su kljucne. ID-auto incerement, nebitan. game_id = ID u games. player_id - 1..10 evo je bitno, prvih 5 je u timu 1, drugih 5 u timu 2. kills, deaths - koliko kilova, smrti..

Ono sto meni treba je view ili upit koji bi mi vratio ovako nesto:

nickname, sum(kills), sum(deaths),total games, wins, losses, total score

Total scroe bi trebao da se racuna po formuli( kills-deaths + wins*5 - losses*5)

ovako nesto mi da nick, kills, deaths, total games

Code:
Select p.nickname, sum(kills), sum(deaths), count(p.nickname)as 'total games' from new_game_players p, new_game_info g where p.game_id=g.id and g.winner <>0 group by(p.nickname)


Sad, mogu da izvucem zasebno za svakog igraca koliko je puta pobedio ili koliko je puta izgubio ali u zasebnoj tabeli, tako sto bi umesto g.winner <>0 stavio (g.winner=1 and p.player_id<6)

ali voleo bi da imam jedan view koji bi drzao celu tabelu koja meni treba, ili da nekako drugarcie sve to resim preko mysql, da http server samo prosledi upitili vise njih, i dobije tabelu..

Hvala
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: Malo komplikovaniji upit12.02.2010. u 19:23 - pre 171 meseci
Citat:

...
new_game_info(ID,winnrer)
new_game_players(ID, nickname, game_id, player_id, kills, deaths)
...
nickname, sum(kills), sum(deaths),total games, wins, losses, total score
...
Select p.nickname, sum(kills), sum(deaths), count(p.nickname)as 'total games' from new_game_players p, new_game_info g where p.game_id=g.id and g.winner <>0 group by(p.nickname)
...


ne kapiram .. pa vec si ga napisao ? samo ti fali wins/losses/total score?

evo ga ... nije ludilo od brzine al je jednostavan za skontati

Code:

SELECT 
p.nickname, 
sum(kills), 
sum(deaths), 
count(p.nickname) AS 'total games',

SUM( IF( p.player_id >5, ( IF( g.winner=2, 1, 0) ), ( IF( g.winner=1, 1, 0) ) ) ) as wins,
SUM( IF( p.player_id >5, ( IF( g.winner=2, 0, 1) ), ( IF( g.winner=1, 0, 1) ) ) ) as losses,
kills-deaths+wins*5-losses*5 as t_score

FROM 
new_game_players p, 
new_game_info g 
WHERE
p.game_id=g.id and 
g.winner <>0 
group by(p.nickname)


probaj pa reci el previse sporo
 
Odgovor na temu

nnn
Tech Lead
Haxr.io
Beograd

Član broj: 16743
Poruke: 679
79.101.209.*



+87 Profil

icon Re: Malo komplikovaniji upit12.02.2010. u 19:36 - pre 171 meseci
Ne radi ovo

+wins*5-losses*5

Unknown column in field list.

Inace hvala, radi kada skinem to, prikazuje wins i losses.

Sto se tice brzine ne mogu trenutno proveriti kuci, posto imam samo deo baze, ali da ti dam predstavu ima oko 400,000 redova u new_game_playes i oko 40,000 u new_game_info.. :)


edit:

SUM( IF( p.player_id >5, ( IF( g.winner=2, 1, 0) ), ( IF( g.winner=1, 1, 0) ) ) ) as wins,
SUM( IF( p.player_id >5, ( IF( g.winner=2, 0, 1) ), ( IF( g.winner=1, 0, 1) ) ) ) as losses,

jel treba tu <5?
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: Malo komplikovaniji upit12.02.2010. u 19:47 - pre 171 meseci
Citat:
nnn: Ne radi ovo

+wins*5-losses*5


da .. ne zna ih u tom trenutku ... no to mozes da uradis sa php strane, nece da crkne php ako treba da sabere tri broja :D

[quote[
Sto se tice brzine ne mogu trenutno proveriti kuci, posto imam samo deo baze, ali da ti dam predstavu ima oko 400,000 redova u new_game_playes i oko 40,000 u new_game_info.. :)
[/quote]

ne znaci mi nista :) ... to je vrlo mala baza no zavisno od rama na serveru, konfiguracije etc .. to moze da bude sporo ili brzo ... problem je sto kada koristis IF() vezano za te kolone ne koristis index tako da realno ti napravis p*g tabelu i onda sekvencijalno ides kroz nju za svaki nickname ... nije strasno, trebalo bi da to radi ok

Citat:

SUM( IF( p.player_id >5, ( IF( g.winner=2, 1, 0) ), ( IF( g.winner=1, 1, 0) ) ) ) as wins,
SUM( IF( p.player_id >5, ( IF( g.winner=2, 0, 1) ), ( IF( g.winner=1, 0, 1) ) ) ) as losses,

jel treba tu <5?


jok - ne treba ... vidis da unutrasnji if vraca 0,1 za razliku od gornjeg if-a koji vraca 1,0 .. ako bi tu stavio <6 onda bi unutrasnji ifovi trebali da vracaju isto sto i gornji 1,0

dakle spoljni IF pita da li je igrac u drugog grupi, onda prvi unutrasnji if pita ako je pobednik druga grupa vrati 1 ako nije vrati 0, a drugi if koji se izvrsi ako spoljni if nije tacan, dakle igrac je u prvoj grupi, ako je pobednik prva grupa vrati jedan a ako nije onda 0 ....

za loses, ista prica samo ako sam ja pobednik vrati 0 ..


 
Odgovor na temu

nnn
Tech Lead
Haxr.io
Beograd

Član broj: 16743
Poruke: 679
79.101.209.*



+87 Profil

icon Re: Malo komplikovaniji upit12.02.2010. u 20:18 - pre 171 meseci
Hvala snaci cu se vec.. Jos jedno brzo pitanje, u tabeli new_game_players ima i kolona hero, kako da vidim koga je igrac najvise puta birao. koji se najvise ponavlja?

Hvala puno, pomogao si mi dosta..
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: Malo komplikovaniji upit12.02.2010. u 20:35 - pre 171 meseci
unutar istog upita ....

ne bi da ti stajem na muku ... ali prilicno si lose dizajnirao te tabele ako si ocekivao ovakve upite :( ... realno svi ovi if-ovi extra usporavaju rad ...
moze da se zbudzi i to za hero al nece biti lepo resenje :(


Code:

SELECT 
p.nickname, 
sum(kills), 
sum(deaths), 
count(p.nickname) AS 'total games',

SUM( IF( p.player_id >5, ( IF( g.winner=2, 1, 0) ), ( IF( g.winner=1, 1, 0) ) ) ) as wins,
SUM( IF( p.player_id >5, ( IF( g.winner=2, 0, 1) ), ( IF( g.winner=1, 0, 1) ) ) ) as losses,

(select hero from  new_game_players  where nickname = p.nickname group by hero order by count(*)  desc limit 1 )

FROM 
new_game_players p, 
new_game_info g 
WHERE
p.game_id=g.id and 
g.winner <>0 
group by(p.nickname)

 
Odgovor na temu

nnn
Tech Lead
Haxr.io
Beograd

Član broj: 16743
Poruke: 679
79.101.209.*



+87 Profil

icon Re: Malo komplikovaniji upit13.02.2010. u 01:37 - pre 171 meseci
Nisam ja dizajnirao te tabele, njih pravi program koji sluzi kao server za igre. Jel imas mozda neko efikasnije resenje za ove tabele, moglo bi da se implementira u programu. Posto sve mi pisemo. Ovo nije za pare vec dobrovoljni projekat na kome radi nas par iz par krajeva sveta.. :P Ja ovde treba da napisem statistike u php-u, ali imam uvid i uticaj u razvoj cele platforme..
Evo create shema:

Code:
DROP TABLE IF EXISTS `rgc_stats`.`new_game_info`;
CREATE TABLE  `rgc_stats`.`new_game_info` (
  `ID` int(13) NOT NULL AUTO_INCREMENT,
  `server_id` int(2) NOT NULL,
  `channel_id` int(6) NOT NULL DEFAULT '0',
  `hosted` int(13) DEFAULT '0',
  `started` int(13) DEFAULT '0',
  `length` int(6) NOT NULL,
  `winner` int(1) DEFAULT '0',
  `gamename` varchar(32) NOT NULL,
  `map` varchar(28) NOT NULL,
  `deprecated_id` int(11) DEFAULT NULL,
  `deprecated` varchar(32) DEFAULT NULL,
  `host_id` int(13) DEFAULT NULL,
  `host` varchar(32) NOT NULL,
  `scored` int(1) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=26001 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


DROP TABLE IF EXISTS `rgc_stats`.`new_game_players`;
CREATE TABLE  `rgc_stats`.`new_game_players` (
  `ID` int(13) NOT NULL AUTO_INCREMENT,
  `game_id` int(13) NOT NULL,
  `player_id` int(11) NOT NULL,
  `server_id` int(2) NOT NULL,
  `nickname` text NOT NULL,
  `status` int(2) DEFAULT NULL,
  `Time` int(6) DEFAULT NULL,
  `LeftReason` text NOT NULL,
  `IP` text NOT NULL,
  `Hero` varchar(50) DEFAULT NULL,
  `Kills` int(3) DEFAULT NULL,
  `Deaths` int(3) DEFAULT NULL,
  `CreepKills` int(4) DEFAULT NULL,
  `CreepDenies` int(4) DEFAULT NULL,
  `Assists` int(3) DEFAULT NULL,
  `Gold` int(6) DEFAULT NULL,
  `Neutrals` int(4) DEFAULT NULL,
  `Item1` varchar(50) DEFAULT NULL,
  `Item2` varchar(50) DEFAULT NULL,
  `Item3` varchar(50) DEFAULT NULL,
  `Item4` varchar(50) DEFAULT NULL,
  `Item5` varchar(50) DEFAULT NULL,
  `Item6` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=249951 DEFAULT CHARSET=latin1;
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: Malo komplikovaniji upit13.02.2010. u 09:25 - pre 171 meseci
ma znam koliko su gameri ludi :D ... za sada su mi najludji ovi sa lock-on igrom :D toliko para i vremena i .. i ... :D .. no to sve ide uz teritoriju (zato i pitah koja je igra u pitanju) ...

imam sigurno par ideja kako bi moglo drugacije da se lakse (brze) vade statistike kasnije, ono sto je bitno je morao bi da znam jod detalja .. npr ti sada imas fiksno da ima 10 igraca u igri ... jel to "mora" tako (pravila) ili je to tako odluceno .. posto ako nije fiksno taj deo treba malo drugacije .. onda sta znace ostale kolone etc ...
 
Odgovor na temu

nnn
Tech Lead
Haxr.io
Beograd

Član broj: 16743
Poruke: 679
109.93.169.*



+87 Profil

icon Re: Malo komplikovaniji upit13.02.2010. u 12:56 - pre 171 meseci
Igra je dota, (warcraft3 mode), uskoro dobija podrsku i za Hon, heroes of newerth. Ali su igre iste tako da nema veze, ova druga je klon prve. Bice ostalih igara uskoro, tacnije svih mogucih koje podrzavau lan( projekat je neka vrsta social/gamming network) mada to je nebitno, posto bi se za svaku "zvanicnu" igru cuvale statistike zasebno..

tacno 10 igraca je u 99,9% igara, a ove ostale ne bi ni trebale da ulaze u statistiku. Ono sto mene brine je to sto na ovoj mojoj masini(dual core malo jaci, 4 giga rama) neki upiti traju po par 3-4 sekunde,a imam samo mali deo podataka.. Sa tim da broj igraca raste iz dana u dan, a samim tim i broj igara.. :)
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: Malo komplikovaniji upit13.02.2010. u 13:54 - pre 171 meseci
hm, warcraft3 ... koliko to dugo nisam igrao :D .. nesto mi se cini da je bila i cetvorka, ili je to bio heroes of mm .. nebitno .. mod .. super .. nije fps :D ali i dalje super :)

upiti traju mnogo zato sto je denormalizovana baza i imas veliku vrtesku ...

Code:

...
SUM( IF( p.player_id >5, ( IF( g.winner=2, 1, 0) ), ( IF( g.winner=1, 1, 0) ) ) ) as wins,
...

ovaj if (kao i onaj sledeci) znaci da ce za svaki slog koji odgovara result tabeli (koja je velika ... mnoooooogo velika) ici slog po slog i sabirati vrednosti ... to je jeli - sporo

Code:

...
(select hero from  new_game_players  where nickname = p.nickname group by hero order by count(*)  desc limit 1 )
...


a ovo je smrt .. za svaki result slog u toj tabeli (dakle za svakog usera) on ce da picne jos jedan zaseban upit koji ce da vrati cudo resultata, koje ce onda da grupise pa sortira i vrati 1 ..

dakle upit je jednostavan, al je tlj za masinu samo tako ... sve i da su ti svi indexi u ramu i tabele iskesirane i dalje je sporo ....

ono sto mozes, ako mozes da utices na taj program koji generise te statistike je da normalizujes malo tu bazu ... ili, ono sto mozes je da imas program koji ce da ti generise neke statistike za korisnika u odnosu na ovu bazu ... (na primer mozes da napravis trigger koji ce svaki put kada se insertuje nesto u tabelu promeniti podatke o userima u nekim "result" tabelama) ... zavisno od toga koliko cesto idu inserti, koliko cesto deleteovi a koliko cesto update-i razlicita resenja mogu da budu bolja ..

generalno je sada najmodernija fora (nije bas "sada" .. traje to vec neko vreme) sa trigerima .. dakle imas posebnu tabelu koja ima tacno to sto tebi treba na webu kao rezultat

Code:

create table web_result (user_id int, user_name varchar, user_kills int, user_deaths int, user_winns, user_score int, user_hero varchar, user_lastip int ...);


i onda imas nad tabelom new_game_players trigger za insert i za update (valjda ne radis odatle nikad delete a i ako radis ne zelis da to smori statistiku tako da ti realno za delete ne treba trigger) pa na insert jednog soga odradis update web_result tabele tako da
- ako je winner presao iz 0 u 1 ili 2 promenis podatke za tog usera
- ako je winner i dalje 0 proveris samo da li user uopste postoji u web_Result i ako ne onda ga dodas sa blankop statistikom
 
Odgovor na temu

[es] :: MySQL :: Malo komplikovaniji upit

[ Pregleda: 2130 | Odgovora: 9 ] > FB > Twit

Postavi temu Odgovori

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