liquid_feedback_core
changeset 312:c951f0ed6cb8
Added "harmonic_weight" for suggestions; Bugfix in function "set_harmonic_initiative_weights" (clear weights prior calculation)
author | jbe |
---|---|
date | Fri Feb 01 18:36:02 2013 +0100 (2013-02-01) |
parents | 4dd3339453b8 |
children | abf6ea622741 |
files | core.sql |
line diff
1.1 --- a/core.sql Fri Feb 01 05:44:34 2013 +0100 1.2 +++ b/core.sql Fri Feb 01 18:36:02 2013 +0100 1.3 @@ -668,7 +668,7 @@ 1.4 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.5 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.6 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.7 -COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiatives sorting position too much'; 1.8 +COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting position too much'; 1.9 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.10 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.11 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; 1.12 @@ -771,7 +771,8 @@ 1.13 "plus1_unfulfilled_count" INT4, 1.14 "plus1_fulfilled_count" INT4, 1.15 "plus2_unfulfilled_count" INT4, 1.16 - "plus2_fulfilled_count" INT4 ); 1.17 + "plus2_fulfilled_count" INT4, 1.18 + "harmonic_weight" NUMERIC(12, 2) ); 1.19 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.20 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.21 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.22 @@ -792,6 +793,7 @@ 1.23 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.24 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.25 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.26 +COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sorting position too much'; 1.27 1.28 1.29 CREATE TABLE "rendered_suggestion" ( 1.30 @@ -3051,6 +3053,7 @@ 1.31 -- Calculation of harmonic weights -- 1.32 ------------------------------------- 1.33 1.34 + 1.35 CREATE VIEW "remaining_harmonic_supporter_weight" AS 1.36 SELECT 1.37 "direct_interest_snapshot"."issue_id", 1.38 @@ -3058,10 +3061,10 @@ 1.39 "direct_interest_snapshot"."member_id", 1.40 "direct_interest_snapshot"."weight" AS "weight_num", 1.41 count("initiative"."id") AS "weight_den" 1.42 - FROM "direct_interest_snapshot" 1.43 - JOIN "issue" 1.44 - ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.45 - AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.46 + FROM "issue" 1.47 + JOIN "direct_interest_snapshot" 1.48 + ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.49 + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.50 JOIN "direct_supporter_snapshot" 1.51 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" 1.52 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 1.53 @@ -3113,6 +3116,8 @@ 1.54 "weight_ary" FLOAT[]; 1.55 "min_weight_v" FLOAT; 1.56 BEGIN 1.57 + UPDATE "initiative" SET "harmonic_weight" = NULL 1.58 + WHERE "issue_id" = "issue_id_p"; 1.59 LOOP 1.60 "min_weight_v" := NULL; 1.61 "i" := 0; 1.62 @@ -3161,10 +3166,121 @@ 1.63 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 1.64 1.65 1.66 +CREATE VIEW "remaining_harmonic_opinion_weight" AS 1.67 + SELECT 1.68 + "initiative"."issue_id", 1.69 + "opinion"."initiative_id", 1.70 + "direct_interest_snapshot"."member_id", 1.71 + "direct_interest_snapshot"."weight" AS "weight_num", 1.72 + count("opinion"."suggestion_id") AS "weight_den" 1.73 + FROM "issue" 1.74 + JOIN "direct_interest_snapshot" 1.75 + ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.76 + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.77 + JOIN "initiative" 1.78 + ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id" 1.79 + JOIN "opinion" 1.80 + ON "direct_interest_snapshot"."member_id" = "opinion"."member_id" 1.81 + AND "initiative"."id" = "opinion"."initiative_id" 1.82 + AND "opinion"."degree" > 0 1.83 + GROUP BY 1.84 + "initiative"."issue_id", 1.85 + "opinion"."initiative_id", 1.86 + "direct_interest_snapshot"."member_id", 1.87 + "direct_interest_snapshot"."weight"; 1.88 + 1.89 +COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.90 + 1.91 + 1.92 +CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS 1.93 + SELECT 1.94 + "suggestion"."initiative_id", 1.95 + "opinion"."suggestion_id", 1.96 + sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num", 1.97 + "remaining_harmonic_opinion_weight"."weight_den" 1.98 + FROM "remaining_harmonic_opinion_weight" 1.99 + JOIN "opinion" 1.100 + ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id" 1.101 + AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id" 1.102 + JOIN "suggestion" 1.103 + ON "opinion"."suggestion_id" = "suggestion"."id" 1.104 + AND "suggestion"."harmonic_weight" ISNULL 1.105 + GROUP BY 1.106 + "suggestion"."initiative_id", 1.107 + "opinion"."suggestion_id", 1.108 + "remaining_harmonic_opinion_weight"."weight_den"; 1.109 + 1.110 +COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.111 + 1.112 + 1.113 +CREATE FUNCTION "set_harmonic_suggestion_weights" 1.114 + ( "initiative_id_p" "initiative"."id"%TYPE ) 1.115 + RETURNS VOID 1.116 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.117 + DECLARE 1.118 + "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE; 1.119 + "i" INT4; 1.120 + "count_v" INT4; 1.121 + "summand_v" FLOAT; 1.122 + "id_ary" INT4[]; 1.123 + "weight_ary" FLOAT[]; 1.124 + "min_weight_v" FLOAT; 1.125 + BEGIN 1.126 + UPDATE "suggestion" SET "harmonic_weight" = NULL 1.127 + WHERE "initiative_id" = "initiative_id_p"; 1.128 + LOOP 1.129 + "min_weight_v" := NULL; 1.130 + "i" := 0; 1.131 + "count_v" := 0; 1.132 + FOR "weight_row" IN 1.133 + SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 1.134 + WHERE "initiative_id" = "initiative_id_p" 1.135 + ORDER BY "suggestion_id" DESC, "weight_den" DESC 1.136 + -- NOTE: latest suggestions treated worse 1.137 + LOOP 1.138 + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.139 + IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN 1.140 + "i" := "i" + 1; 1.141 + "count_v" := "i"; 1.142 + "id_ary"["i"] := "weight_row"."suggestion_id"; 1.143 + "weight_ary"["i"] := "summand_v"; 1.144 + ELSE 1.145 + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.146 + END IF; 1.147 + END LOOP; 1.148 + EXIT WHEN "count_v" = 0; 1.149 + "i" := 1; 1.150 + LOOP 1.151 + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(20,10)::NUMERIC(12,2); 1.152 + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.153 + "min_weight_v" := "weight_ary"["i"]; 1.154 + END IF; 1.155 + "i" := "i" + 1; 1.156 + EXIT WHEN "i" > "count_v"; 1.157 + END LOOP; 1.158 + "i" := 1; 1.159 + LOOP 1.160 + IF "weight_ary"["i"] = "min_weight_v" THEN 1.161 + UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v" 1.162 + WHERE "id" = "id_ary"["i"]; 1.163 + EXIT; 1.164 + END IF; 1.165 + "i" := "i" + 1; 1.166 + END LOOP; 1.167 + END LOOP; 1.168 + END; 1.169 + $$; 1.170 + 1.171 +COMMENT ON FUNCTION "set_harmonic_suggestion_weights" 1.172 + ( "issue"."id"%TYPE ) 1.173 + IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; 1.174 + 1.175 + 1.176 ------------------------------ 1.177 -- Calculation of snapshots -- 1.178 ------------------------------ 1.179 1.180 + 1.181 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot" 1.182 ( "issue_id_p" "issue"."id"%TYPE, 1.183 "member_id_p" "member"."id"%TYPE, 1.184 @@ -3624,6 +3740,7 @@ 1.185 ) 1.186 WHERE "suggestion"."id" = "suggestion_id_v"; 1.187 END LOOP; 1.188 + PERFORM "set_harmonic_suggestion_weights"("initiative_id_v"); 1.189 END LOOP; 1.190 PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.191 RETURN;