From 2f8bf6c328c2caa7ded0cfe7debe3b7423fb8f70 Mon Sep 17 00:00:00 2001 From: Kathy Lussier Date: Mon, 31 Jul 2017 16:36:50 -0400 Subject: [PATCH] LP#1688096: Popularity parameter for in-house use Adds a new popularity parameter based on in-house use over time. The parameter can be used for creating a statistically-generated popularity badge for titles. Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/220.schema.rating.sql | 58 ++++++++++++++++- .../upgrade/xxxx.data-inhouse-use-badge.sql | 63 +++++++++++++++++++ .../OPAC/new-activity-metric-badge.adoc | 6 ++ 3 files changed, 126 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql create mode 100644 docs/RELEASE_NOTES_NEXT/OPAC/new-activity-metric-badge.adoc diff --git a/Open-ILS/src/sql/Pg/220.schema.rating.sql b/Open-ILS/src/sql/Pg/220.schema.rating.sql index 8fed4cd7ae..1b258141eb 100644 --- a/Open-ILS/src/sql/Pg/220.schema.rating.sql +++ b/Open-ILS/src/sql/Pg/220.schema.rating.sql @@ -78,7 +78,8 @@ INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_im (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE), (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE), (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE), - (16,'Copy Count','rating.copy_count',FALSE,FALSE,TRUE); + (16,'Copy Count','rating.copy_count',FALSE,FALSE,TRUE), + (18, 'In-House Use Over Time', 'rating.inhouse_over_time',TRUE,FALSE,TRUE); CREATE TABLE rating.badge ( @@ -931,5 +932,60 @@ BEGIN END; $f$ LANGUAGE PLPGSQL STRICT; +BEGIN; + +CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_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( uses * GREATEST( iscale * (iage - cage), 1.0 )) + FROM ( + SELECT cn.record AS bib, + (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage, + COUNT(u.id)::INT AS uses + FROM action.in_house_use u + JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy) + JOIN asset.copy cp ON (cp.id = u.item) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE u.use_time >= NOW() - badge.horizon_age + AND cn.owning_lib = ANY (badge.orgs) + GROUP BY 1, 2 + ) x + GROUP BY 1; +END; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql new file mode 100644 index 0000000000..16d3f160bd --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql @@ -0,0 +1,63 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_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( uses * GREATEST( iscale * (iage - cage), 1.0 )) + FROM ( + SELECT cn.record AS bib, + (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage, + COUNT(u.id)::INT AS uses + FROM action.in_house_use u + JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy) + JOIN asset.copy cp ON (cp.id = u.item) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE u.use_time >= NOW() - badge.horizon_age + AND cn.owning_lib = ANY (badge.orgs) + GROUP BY 1, 2 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES + (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE); + +COMMIT; + diff --git a/docs/RELEASE_NOTES_NEXT/OPAC/new-activity-metric-badge.adoc b/docs/RELEASE_NOTES_NEXT/OPAC/new-activity-metric-badge.adoc new file mode 100644 index 0000000000..993f404bb4 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/OPAC/new-activity-metric-badge.adoc @@ -0,0 +1,6 @@ +New Popularity Parameter +^^^^^^^^^^^^^^^^^^^^^^^^ +A new popularity parameter for in-house use over time is now available. +Evergreen sites can use the parameter to create a new statistical popularity +badge based on in-house use of materials. + -- 2.43.2