Cel mai mare query din viata mea

Posted by

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

3 comments

Comments are closed.