3 SELECT evergreen.upgrade_deps_block_check('1052', :eg_version);
5 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
6 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
8 badge rating.badge_with_orgs%ROWTYPE;
11 iscale NUMERIC := NULL;
14 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
16 IF badge.horizon_age IS NULL THEN
17 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
22 PERFORM rating.precalc_bibs_by_copy(badge_id);
24 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
25 SELECT id FROM precalc_filter_bib_list
27 SELECT id FROM precalc_bibs_by_copy_list
30 ANALYZE precalc_copy_filter_bib_list;
32 iint := EXTRACT(EPOCH FROM badge.importance_interval);
33 IF badge.importance_age IS NOT NULL THEN
34 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
37 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
38 iscale := COALESCE(badge.importance_scale, 1.0);
42 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
44 SELECT cn.record AS bib,
45 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
46 COUNT(u.id)::INT AS uses
47 FROM action.in_house_use u
48 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
49 JOIN asset.copy cp ON (cp.id = u.item)
50 JOIN asset.call_number cn ON (cn.id = cp.call_number)
51 WHERE u.use_time >= NOW() - badge.horizon_age
52 AND cn.owning_lib = ANY (badge.orgs)
57 $f$ LANGUAGE PLPGSQL STRICT;
59 INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
60 (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);