Ceea ce am facut azi nu as fi crezut ca sunt capabil vreodata, sa scriu un query de mysql atat de complicat. Eu care de obicei ma sperial la primul LEFT JOIN si fugeam de el ca de necurat. Dar azi am reusit dupa 4 ore de munca si dupa ce am luat-o de ca capat odata sa scriu nebunia de mai jos. Poate unora le va parea ceva simplu de 10 minute, dar pentru mine nu a fost asa si azi am pus o mare bila alba la cunostintele mele de mysql:
SELECT top_rt.twt_uid, top_rt.twt_username,top_rt.nr_rt, top_creativi.user_tweets, top_rt.nr_rt/top_creativi.user_tweets AS influence FROM ( SELECT users.twt_uid, top_rt_primite.twt_username, top_rt_primite.nr_rt FROM users LEFT JOIN ( SELECT REPLACE(rt,'RT @','') AS twt_username, COUNT( rt ) AS nr_rt FROM `tweets` WHERE tweets.id_event='33' AND trim(rt)<>'' GROUP BY rt ORDER BY nr_rt DESC ) AS top_rt_primite ON users.twt_username=top_rt_primite.twt_username WHERE users.twt_username=top_rt_primite.twt_username ORDER BY top_rt_primite.nr_rt DESC ) AS top_rt JOIN ( SELECT twt_uid, COUNT( twt_uid ) AS user_tweets FROM tweets WHERE id_event='33' AND rt NOT LIKE '%RT%' GROUP BY twt_uid ORDER BY user_tweets DESC ) AS top_creativi ON top_rt.twt_uid=top_creativi.twt_uid ORDER BY influence DESC
Sunt neni care au scris query-uri de cateva sute de linii. 🙂
ce timp de rulare are acea interogare?
Eu inca sunt la stadiul de o linie 😀