3 # 10200 is the group_id for the sfpeerratings project
4 # REPLACE 10200 with your peerratings group_id
6 DROP TABLE user_metric0;
7 DROP SEQUENCE user_metric0_pk_seq;
8 CREATE SEQUENCE "user_metric0_pk_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
10 CREATE TABLE "user_metric0" (
11 "ranking" integer DEFAULT nextval('user_metric0_pk_seq'::text) NOT NULL,
12 "user_id" integer DEFAULT '0' NOT NULL,
13 "times_ranked" integer DEFAULT '0' NOT NULL,
14 "avg_raters_importance" double precision DEFAULT '0.00000000' NOT NULL,
15 "avg_rating" double precision DEFAULT '0.00000000' NOT NULL,
16 "metric" double precision DEFAULT '0.00000000' NOT NULL,
17 "percentile" double precision DEFAULT '0.00000000' NOT NULL,
18 "importance_factor" double precision DEFAULT '0.00000000' NOT NULL,
19 Constraint "user_metric0_pkey" Primary Key ("ranking")
21 CREATE INDEX "user_metric0_user_id" on "user_metric0" using btree ( "user_id" "int4_ops" );
23 INSERT INTO user_metric0 (user_id,times_ranked,avg_raters_importance,avg_rating,metric,percentile,importance_factor)
24 SELECT user_id,5,1.25,1,0,0,1.25
28 AND user_group.admin_flags='A';
30 UPDATE user_metric0 SET
31 metric=(log(times_ranked::float)*avg_rating::float)::float,
32 percentile=(100-(100*((ranking::float-1)/(select count(*) from user_metric0))))::float;
34 UPDATE user_metric0 SET
35 importance_factor=(1+((percentile::float/100)*.5))::float;