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    $$;

Impressum / About Us