zložité mysql_query

turtlak69

taaakze po dlhom case (mozno aj 3 dni) by som potreboval pomoc...
je dana galeria, ktora funguje bez DB. pri galerii su moznosti komentovat fotku a aj hodnotit (1-10). tieto udaje sa uz do DB zapisuju. zapisuje sa komentar, id, komentatora, cesta k suboru atd... tento system funguje bezchybne. jeden komentar = jeden insert = jeden riadok. rating funguje tak isto. vypocet priemerneho hodnotenia je jednoduchy. vyberaju sa z DB udaje, kde je cesta taka ako cesta obrazku ktory sa nacita v galerii. cize to krasne funguje.
ale teraz prisiel problem. potreboval by som spravit sekciu TOP10. cize top10 komentovanych, top10 v hodnoteni, top10 v uzivatelskych albumoch.
ale neviem ako na to zostavim query. ten jeden zazracny riadok, ktorym by som to vycucol z DB. neviem ani ci sa to da. je tam vela neznamych. napadlo ma preskenovat vsetky priecinky s fotkami a vzdy zistit, ci je ta dana fotka komentovana a ak ano, tak kolkokrat. to by sa zapisalo do nejakej premennej a potom by sa len porovnavali premenne. to by boli komentare. velmi zlozito a neprakticky. dalej rating by sa robil podobne ale tazsie. ale tymto sposobom je to velmi neprakticke. ak by sa dalo jedno query spravit bolo by to idealne.

pre lepsie pochopenie pridam strukturu tabuliek...

Kód: Vybrať všetko


CREATE TABLE `namieru_ratings` (
  `rating_id` int(11) NOT NULL auto_increment,
  `rating_user` int(11) NOT NULL,
  `rating_file` varchar(250) collate cp1250_bin NOT NULL,
  `rating_value` varchar(20) collate cp1250_bin NOT NULL default '0',
  `rating_type` varchar(20) collate cp1250_bin NOT NULL,
  `rating_date` date NOT NULL,
  PRIMARY KEY  (`rating_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1250 COLLATE=cp1250_bin AUTO_INCREMENT=12 ;

dalej...


CREATE TABLE `namieru_comments` (
  `com_id` int(11) NOT NULL auto_increment,
  `com_user` int(11) NOT NULL,
  `com_file` varchar(250) collate cp1250_bin NOT NULL,
  `com_text` varchar(250) collate cp1250_bin NOT NULL,
  `com_type` varchar(20) collate cp1250_bin NOT NULL,
  `com_date` date NOT NULL,
  PRIMARY KEY  (`com_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1250 COLLATE=cp1250_bin AUTO_INCREMENT=21 ;

1 komentar = 1 riadok
1 hodnotenie = 1 riadok

da sa to vobec dosiahnut tym, ze si rovno pomocou query vytiahnem co mi treba? alebo bude treba naozaj skenovat adresare s obrazkami?

gwixt

top 10 komentovanych

Kód: Vybrať všetko

SELECT * , COUNT( * ) AS pocet
FROM `namieru_ratings` 
GROUP BY rating_file
ORDER BY pocet DESC 
LIMIT 0,10

turtlak69

thx

ale nejako stale neviem odkial sa tma zobrala ta cast ORDER BY pocet

pocet? odkial to je? co to je? kde sa to tam zjavilo. mysql nerozumiem az tak nejako extra

zatial mi stacilo vzdy, delete, insert, update, select

gwixt

top 10 podla ratingu (predpokladam ze cim vacsi rating tym lepsie)

Kód: Vybrať všetko

SELECT rating_file AS rf1, 
 (SELECT AVG( rating_value ) FROM `namieru_ratings` WHERE rating_file=rf1) AS avg_rating
FROM `namieru_ratings` 
GROUP BY rating_file
ORDER BY avg_rating DESC
LIMIT 0,10

turtlak69 napísal:thx ale nejako stale neviem odkial sa tma zobrala ta cast ORDER BY pocet pocet? odkial to je? co to je? kde sa to tam zjavilo. mysql nerozumiem az tak nejako extra zatial mi stacilo vzdy, delete, insert, update, select

to pocet je len pomenovany ten atribut COUNT(*) co vyberam z DB .... musel som ho nejak pomenovat aby som podla neho to mohol potom usporiadat

a to top 10 v uzivatelskych albumoch neviem co znamena

turtlak69

no ako vidim este mam s mysql co robit aby som aspon pochopil query (nie to este zostavit). kazdopadne velmi dakujem, idem to poskusat, ze co to a ako s tym narabat

k+

samozrejme, ze na prvykrat mi to nemoze fungovat, tak poprosim nezamykat este

edit> no neveriiiim. tak takto lahko som si to teda fakt nepredstavoval. ono to vazne ide. zatial som to prehnal cez phpmyadmina a vyhodilo mi pravdive vysledky

som fakt nadseny (nemyslim ironicky)

turtlak69

gwixt napísal:top 10 podla ratingu (predpokladam ze cim vacsi rating tym lepsie)
Kód: Vybrať všetko
SELECT rating_file AS rf1, 
 (SELECT AVG( rating_value ) FROM `namieru_ratings` WHERE rating_file=rf1) AS avg_rating
FROM `namieru_ratings` 
GROUP BY rating_file
ORDER BY avg_rating DESC
LIMIT 0,10

toto funguje uplne dobre az na maly detail... teraz mi to vyhodi sice fotky maximalne hodnotene, ale neberie to do uvahy pocet hlasov. cize fotka, ktora ma napriklad hodnotenie 10 a ma len jeden hlas je uplne navrchu a fotka, ktora ma napriklad 150 hlasov a priemerne hodnotenie ma 9,5 je pod nou a to je dost neobjektivne. nedalo by sa nejakym sposobom zabezpecit aby sa vybrali fotky ktore maju najviac hlasov a su zaroven aj s najvyssim ratingom? to by bolo uplne idealne a objektivne

edit: este som upravil tabulku a zmenil format datumu. takze nie je uz typ date, ale int a cas sa zapisuje ako cislo (mktime) v ktorom je informacia len rok, mesiac, den (hodiny a tak uz nie). este by bolo uplne najlepsie keby sa z DB vybrali len hodnoty za poslednych 5 dni. aby tie najkomentovanejsie fotky tam neobsmrdali stale, ale aby sa to aj menilo. no ak by som toto dosiahol, bol by som velmo happy

hlp pls

edit> komentare som vyriesil takto:

Kód: Vybrať všetko

$dni = time() - (7 * 24 * 60 * 60);
$sql = mysql_query("
SELECT * , COUNT( * ) AS pocet
FROM `$tab_comments`
WHERE com_date >= $dni
GROUP BY com_file
ORDER BY pocet DESC
LIMIT 0,10

final

ten date si daj ako unsigned int(10).. celé razítko si tam nechávaj zapisovať.. nevieš kedy ho budeš potrebovať..

Kód: Vybrať všetko

SELECT COUNT(*),
       AVG(`rating_value`) AS avg_rating,
       rating_file AS rf1
FROM `namieru_ratings`
GROUP BY 3
ORDER BY 1 DESC, 2 DESC
LIMIT 0, 10

snaď som pochopil všetko dobre..

edit:ako inač, nepochopil.. som si tie komentáre pomýlil s časom hodnotenia

(query upravený)

turtlak69

hmm thx, toto funguje presne tak ako som chcel

ale stale mam pocit, ze to nie je to prave orechove... ono to sice ide a aj to tak zoraduje, ale nieco mi tam nesedi. fotka, ktora ma hodnotenie 3,333333 je na prvom mieste lebo ma viac hlasov a fotka, ktora je na druhom ma jeden hlas za plny pocet. nejako sa mi to nezda. neviem akym sposobom mam vybrat vlastne tie najlepsie fotky. toto sice funguje, ale ma to byt takto aby to objektivne vybralo tie naj fotky? jaaj dnes mi to nepali teda vobec...
btw k+

final

nie, určite to takto nemá byť..
sa vži do role uživateľa, ktorý príde na stránku s fotkami, alebo videami.. dáš si top 10 a zobrazia sa ti výsledky kde je kladený dôraz na počet hodnotení viac ako na hodnotenie samotné.. čiže keď bude 100 hodnotení a priemer bude 1, zatiaľ čo bude 99 hodnotení niekde a priemer bude 10.. a práve taký výsledok bude niekde dole nedovidno.. čo asi tak napadne uživateľa? spokojný nebude určite

chce to zložitejší systém aby to bolo fakt že objektívne.. ja som dnes už unavený, ale nemyslím si, že by sa mi to chcelo vymýšľať aj keby som nebol

však skús nejaký nápad vymyslieť.. dopyt potom hádam nebude problém spraviť..

mimochodom prečo máš rating_value ako varchar ? nebolo by lepšie to mať ako číslo? - ak to je tak ako píšeš od 1 do 10, poprípade float pre desatinné

turtlak69

final napísal:mimochodom prečo máš rating_value ako varchar ? nebolo by lepšie to mať ako číslo? - ak to je tak ako píšeš od 1 do 10, poprípade float pre desatinné

hmm nie je zly napad

ja som tu tabulu hnusnu spravil len narychlo. taketo kozmeticke upravy ma nenapadli. no ten system je asi zlozite vytvorit. fakt zacinam mat pocit, ze mi to proste nepali

kamarat mi povedal, ze mozno keby som skusil spravit sucin hodnoteni (poctu) a hodnot (rating value), tak by to bolo fajn. no neviem neviem... este ma napadla takato teoria:
zistit priemerny pocet hlasov (kolkokrat priemerne je fotka hodnotena)
potom uz len zistit maximalne hodnotene, kde je pocet hlasov viac ako priemer
a potom uz len vypisat
ma niekto lepsi napad? alebo aspon nejaku pripomienku alebo hocico?

final · Príspevok od používateľa **final** » 17 aug 2007, 0:37

no kozmetická to ani tak neni.. v predošlých dopytoch tá avg funkcia pravdepodobne pretypovala takmer všetko čo sa dalo a preto to tak dobre radilo, .. keby si dal radenie bez tej funkcie tak výsledok bude špatný..

btw.. k tomu priemeru počtu hodnotení, to by bolo tiež na nič, lebo by si tak mohol odseknúť aj tie dobré..

jedno riešenie by som aj mal.. teda nie ja, ale ukradnuté z imdb

http://imdb.com/chart/top?tt0068646

tam na spodku je ten vzorec.. nie je to úplne to best, lebo najlepšie sa asi ani nedá spraviť.. budeš si tam musieť regulovať niektoré hodnoty.. tam dole sa dočítaš.. som dal kritérium počtu hlasov 1, a odskúšal na pár záznamoch, vyzerá to fajn, nejaké rozšírenejšie testovanie nechám na teba

Kód: Vybrať všetko

SELECT ((COUNT(*) / (COUNT(*) + 1)) * AVG(`rating_value`)) + ((COUNT(*) / (COUNT(*) + 1)) * 6.7) AS avg_rating,
       rating_file AS rf1
FROM `namieru_ratings`
GROUP BY 2
ORDER BY 1 DESC
LIMIT 0, 10

alebo

Kód: Vybrať všetko

CREATE FUNCTION fotka_hodnotenie(v INT(5), R FLOAT)
RETURNS FLOAT
RETURN ((v / (v + 1)) * R) + ((v / (v + 1)) * 6.7);

Kód: Vybrať všetko

SELECT fotka_hodnotenie(COUNT(*), AVG(`rating_value`)) AS avg_rating,
       rating_file AS rf1
FROM `namieru_ratings`
GROUP BY 2
ORDER BY 1 DESC
LIMIT 0, 10

gwixt · Príspevok od používateľa **gwixt** » 17 aug 2007, 9:05

ja by som zobral len priemerne hodnotenie ako hlavny ukazovatel ...
ale iba ak je hodnoteni viac ako nejaky pocet .. napr 10

turtlak69

dakujem vam obom velmi moc. poskusam a potom dam vediet.
gwixt: toto napadlo aj mna, ale nemyslim, ze je to objektivne.
napriklad: jedna fotka ma hodnotenie 10*10 (10 hlasov po 10 bodov) a druha ma 5000*9. cize ta ktora ma tych 5000 hlasov a vsetko za 9 je na tom urcite lepsie ako ta ktora ma 10 hlasov hoci za 10 bodov. ta fotka, ktora ma malo hlasov sa moze dostat z rebricka ovela lahsie ako ta ktora je suverene na prvom

i ked z tych 3 query neviem ani poriadne co to znamena, hura do skusania.