# HG changeset patch # User jbe # Date 1359693712 -3600 # Node ID fa09513482aa965c90d78f7904290f0d3cee193e # Parent 3ab95ace7ffa64984223db034f15a3339b2f025f "harmonic_weight" for initiatives diff -r 3ab95ace7ffa -r fa09513482aa core.sql --- a/core.sql Mon Oct 15 20:46:11 2012 +0200 +++ b/core.sql Fri Feb 01 05:41:52 2013 +0100 @@ -612,6 +612,7 @@ "informed_supporter_count" INT4, "satisfied_supporter_count" INT4, "satisfied_informed_supporter_count" INT4, + "harmonic_weight" NUMERIC(12, 2), "positive_votes" INT4, "negative_votes" INT4, "direct_majority" BOOLEAN, @@ -667,6 +668,7 @@ COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; +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'; COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 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"'; @@ -3045,6 +3047,121 @@ +------------------------------------- +-- Calculation of harmonic weights -- +------------------------------------- + +CREATE VIEW "remaining_harmonic_supporter_weight" AS + SELECT + "direct_interest_snapshot"."issue_id", + "direct_interest_snapshot"."event", + "direct_interest_snapshot"."member_id", + "direct_interest_snapshot"."weight" AS "weight_num", + count("initiative"."id") AS "weight_den" + FROM "direct_interest_snapshot" + JOIN "issue" + ON "direct_interest_snapshot"."issue_id" = "issue"."id" + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" + JOIN "direct_supporter_snapshot" + ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" + AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" + AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" + JOIN "initiative" + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" + AND "initiative"."harmonic_weight" ISNULL + GROUP BY + "direct_interest_snapshot"."issue_id", + "direct_interest_snapshot"."event", + "direct_interest_snapshot"."member_id", + "direct_interest_snapshot"."weight"; + +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; + + +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS + SELECT + "initiative"."issue_id", + "initiative"."id" AS "initiative_id", + sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", + "remaining_harmonic_supporter_weight"."weight_den" + FROM "remaining_harmonic_supporter_weight" + JOIN "direct_supporter_snapshot" + ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id" + AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" + AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" + JOIN "initiative" + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" + AND "initiative"."harmonic_weight" ISNULL + GROUP BY + "initiative"."issue_id", + "initiative"."id", + "remaining_harmonic_supporter_weight"."weight_den"; + +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; + + +CREATE FUNCTION "set_harmonic_initiative_weights" + ( "issue_id_p" "issue"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; + "i" INT4; + "count_v" INT4; + "summand_v" FLOAT; + "id_ary" INT4[]; + "weight_ary" FLOAT[]; + "min_weight_v" FLOAT; + BEGIN + LOOP + "min_weight_v" := NULL; + "i" := 0; + "count_v" := 0; + FOR "weight_row" IN + SELECT * FROM "remaining_harmonic_initiative_weight_summands" + WHERE "issue_id" = "issue_id_p" + ORDER BY "initiative_id" DESC, "weight_den" DESC + -- NOTE: latest initiatives treated worse + LOOP + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; + IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN + "i" := "i" + 1; + "count_v" := "i"; + "id_ary"["i"] := "weight_row"."initiative_id"; + "weight_ary"["i"] := "summand_v"; + ELSE + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; + END IF; + END LOOP; + EXIT WHEN "count_v" = 0; + "i" := 1; + LOOP + RAISE NOTICE 'DEBUG: id: %, weight: %', "id_ary"["i"], "weight_ary"["i"]; + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(20,10)::NUMERIC(12,2); + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN + "min_weight_v" := "weight_ary"["i"]; + END IF; + "i" := "i" + 1; + EXIT WHEN "i" > "count_v"; + END LOOP; + "i" := 1; + LOOP + IF "weight_ary"["i"] = "min_weight_v" THEN + UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" + WHERE "id" = "id_ary"["i"]; + EXIT; + END IF; + "i" := "i" + 1; + END LOOP; + END LOOP; + END; + $$; + +COMMENT ON FUNCTION "set_harmonic_initiative_weights" + ( "issue"."id"%TYPE ) + IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; + + ------------------------------ -- Calculation of snapshots -- ------------------------------ @@ -3509,6 +3626,7 @@ WHERE "suggestion"."id" = "suggestion_id_v"; END LOOP; END LOOP; + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); RETURN; END; $$;