liquid_feedback_core
changeset 310:fa09513482aa
"harmonic_weight" for initiatives
author | jbe |
---|---|
date | Fri Feb 01 05:41:52 2013 +0100 (2013-02-01) |
parents | 3ab95ace7ffa |
children | 4dd3339453b8 |
files | core.sql |
line diff
1.1 --- a/core.sql Mon Oct 15 20:46:11 2012 +0200 1.2 +++ b/core.sql Fri Feb 01 05:41:52 2013 +0100 1.3 @@ -612,6 +612,7 @@ 1.4 "informed_supporter_count" INT4, 1.5 "satisfied_supporter_count" INT4, 1.6 "satisfied_informed_supporter_count" INT4, 1.7 + "harmonic_weight" NUMERIC(12, 2), 1.8 "positive_votes" INT4, 1.9 "negative_votes" INT4, 1.10 "direct_majority" BOOLEAN, 1.11 @@ -667,6 +668,7 @@ 1.12 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.13 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.14 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.15 +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.16 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.17 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.18 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.19 @@ -3045,6 +3047,121 @@ 1.20 1.21 1.22 1.23 +------------------------------------- 1.24 +-- Calculation of harmonic weights -- 1.25 +------------------------------------- 1.26 + 1.27 +CREATE VIEW "remaining_harmonic_supporter_weight" AS 1.28 + SELECT 1.29 + "direct_interest_snapshot"."issue_id", 1.30 + "direct_interest_snapshot"."event", 1.31 + "direct_interest_snapshot"."member_id", 1.32 + "direct_interest_snapshot"."weight" AS "weight_num", 1.33 + count("initiative"."id") AS "weight_den" 1.34 + FROM "direct_interest_snapshot" 1.35 + JOIN "issue" 1.36 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.37 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.38 + JOIN "direct_supporter_snapshot" 1.39 + ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" 1.40 + AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 1.41 + AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" 1.42 + JOIN "initiative" 1.43 + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.44 + AND "initiative"."harmonic_weight" ISNULL 1.45 + GROUP BY 1.46 + "direct_interest_snapshot"."issue_id", 1.47 + "direct_interest_snapshot"."event", 1.48 + "direct_interest_snapshot"."member_id", 1.49 + "direct_interest_snapshot"."weight"; 1.50 + 1.51 +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.52 + 1.53 + 1.54 +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS 1.55 + SELECT 1.56 + "initiative"."issue_id", 1.57 + "initiative"."id" AS "initiative_id", 1.58 + sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 1.59 + "remaining_harmonic_supporter_weight"."weight_den" 1.60 + FROM "remaining_harmonic_supporter_weight" 1.61 + JOIN "direct_supporter_snapshot" 1.62 + ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id" 1.63 + AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" 1.64 + AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" 1.65 + JOIN "initiative" 1.66 + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.67 + AND "initiative"."harmonic_weight" ISNULL 1.68 + GROUP BY 1.69 + "initiative"."issue_id", 1.70 + "initiative"."id", 1.71 + "remaining_harmonic_supporter_weight"."weight_den"; 1.72 + 1.73 +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.74 + 1.75 + 1.76 +CREATE FUNCTION "set_harmonic_initiative_weights" 1.77 + ( "issue_id_p" "issue"."id"%TYPE ) 1.78 + RETURNS VOID 1.79 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.80 + DECLARE 1.81 + "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; 1.82 + "i" INT4; 1.83 + "count_v" INT4; 1.84 + "summand_v" FLOAT; 1.85 + "id_ary" INT4[]; 1.86 + "weight_ary" FLOAT[]; 1.87 + "min_weight_v" FLOAT; 1.88 + BEGIN 1.89 + LOOP 1.90 + "min_weight_v" := NULL; 1.91 + "i" := 0; 1.92 + "count_v" := 0; 1.93 + FOR "weight_row" IN 1.94 + SELECT * FROM "remaining_harmonic_initiative_weight_summands" 1.95 + WHERE "issue_id" = "issue_id_p" 1.96 + ORDER BY "initiative_id" DESC, "weight_den" DESC 1.97 + -- NOTE: latest initiatives treated worse 1.98 + LOOP 1.99 + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.100 + IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 1.101 + "i" := "i" + 1; 1.102 + "count_v" := "i"; 1.103 + "id_ary"["i"] := "weight_row"."initiative_id"; 1.104 + "weight_ary"["i"] := "summand_v"; 1.105 + ELSE 1.106 + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.107 + END IF; 1.108 + END LOOP; 1.109 + EXIT WHEN "count_v" = 0; 1.110 + "i" := 1; 1.111 + LOOP 1.112 + RAISE NOTICE 'DEBUG: id: %, weight: %', "id_ary"["i"], "weight_ary"["i"]; 1.113 + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(20,10)::NUMERIC(12,2); 1.114 + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.115 + "min_weight_v" := "weight_ary"["i"]; 1.116 + END IF; 1.117 + "i" := "i" + 1; 1.118 + EXIT WHEN "i" > "count_v"; 1.119 + END LOOP; 1.120 + "i" := 1; 1.121 + LOOP 1.122 + IF "weight_ary"["i"] = "min_weight_v" THEN 1.123 + UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" 1.124 + WHERE "id" = "id_ary"["i"]; 1.125 + EXIT; 1.126 + END IF; 1.127 + "i" := "i" + 1; 1.128 + END LOOP; 1.129 + END LOOP; 1.130 + END; 1.131 + $$; 1.132 + 1.133 +COMMENT ON FUNCTION "set_harmonic_initiative_weights" 1.134 + ( "issue"."id"%TYPE ) 1.135 + IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 1.136 + 1.137 + 1.138 ------------------------------ 1.139 -- Calculation of snapshots -- 1.140 ------------------------------ 1.141 @@ -3509,6 +3626,7 @@ 1.142 WHERE "suggestion"."id" = "suggestion_id_v"; 1.143 END LOOP; 1.144 END LOOP; 1.145 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.146 RETURN; 1.147 END; 1.148 $$;