File indexing completed on 2024-04-21 06:00:37
0001 0002 update project_rating set score = 9 where user_like = 1 0003 and created_at < '2019-05-14 10:00:00'; 0004 0005 update project_rating set score = round(((user_like-6)*9+(user_dislike-6)*3+4*5)/(user_like+user_dislike+4-12),2) 0006 where user_like >=6 and user_dislike>=6 ; 0007 0008 0009 update project_rating set score_test = 8 where user_like = 1 0010 and created_at < '2019-05-14 10:00:00'; 0011 update project_rating set score_test = round(((user_like-6)*8+(user_dislike-6)*3+10*5)/(user_like+user_dislike+10-12),2) 0012 where user_like >=6 and user_dislike>=6 ; 0013 0014 0015 DELIMITER $$ 0016 drop PROCEDURE generate_stat_rating_project; 0017 CREATE PROCEDURE `generate_stat_rating_project`() 0018 BEGIN 0019 DROP TABLE IF EXISTS tmp_stat_rating_project; 0020 CREATE TABLE tmp_stat_rating_project 0021 ( 0022 `project_id` int(11) NOT NULL, 0023 `likes` int(11) NOT NULL, 0024 `dislikes` int(11) NOT NULL, 0025 `votes_total` int(11) NOT NULL, 0026 `score` int(11) NOT NULL, 0027 `score_with_pling` int(11) NOT NULL, 0028 `score_test` int(11) NOT NULL, 0029 PRIMARY KEY `primary` (project_id) 0030 ) 0031 AS 0032 SELECT pr.project_id, 0033 sum(pr.likes) AS likes, 0034 sum(pr.dislikes) AS dislikes, 0035 sum(pr.likes) + sum(pr.dislikes) AS votes_total, 0036 laplace_score(sum(pr.likes), sum(pr.dislikes)) AS score, 0037 (sum(pr.totalscore)+4*5)/(sum(pr.count)+4)*100 AS score_with_pling, 0038 (sum(pr.totalscore_test)+2*5)/(sum(pr.count)+2)*100 AS score_test 0039 from 0040 ( 0041 select project_id 0042 ,user_like as likes 0043 ,user_dislike as dislikes 0044 ,1 as count 0045 ,score as totalscore 0046 ,score_test as totalscore_test 0047 from project_rating pr where pr.rating_active = 1 0048 union all 0049 select 0050 project_id 0051 ,user_like-6 as likes 0052 ,user_dislike-6 as dislikes 0053 ,user_like+user_dislike-12 as count 0054 ,(user_like-6)*9+(user_dislike-6)*3 as totalscore 0055 ,(user_like-6)*8+(user_dislike-6)*3 as totalscore_test 0056 from project_rating pr 0057 where pr.rating_active = 0 and user_dislike >=6 and user_like>=6 0058 ) pr 0059 group by project_id; 0060 0061 IF EXISTS(SELECT table_name 0062 FROM INFORMATION_SCHEMA.TABLES 0063 WHERE table_schema = DATABASE() 0064 AND table_name = 'stat_rating_project') 0065 THEN 0066 RENAME TABLE stat_rating_project TO old_stat_rating_project, tmp_stat_rating_project TO stat_rating_project; 0067 0068 ELSE 0069 RENAME TABLE tmp_stat_rating_project TO stat_rating_project; 0070 0071 END IF; 0072 0073 0074 DROP TABLE IF EXISTS old_stat_rating_project; 0075 END$$ 0076 DELIMITER ;