egdb3_13_6.rating

Parameters

Name	Type	Mode
badge_id	integer	IN
record	bigint	TABLE
value	numeric	TABLE

Definition

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

PERFORM rating.precalc_bibs_by_copy(badge_id);

SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bib_list;
    CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list;

iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

-- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

RETURN QUERY
     SELECT bib,
            SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
      FROM (
         SELECT f.id AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
                COUNT(h.id)::INT AS holds
          FROM  action.hold_request h
                JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
                JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
          WHERE h.request_time >= NOW() - badge.horizon_age
                AND h.request_lib = ANY (badge.orgs)
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

holds_placed_over_time(badge_id integer)

Parameters

Definition