4 SELECT evergreen.upgrade_deps_block_check('0983', :eg_version);
6 -- Create these so that the queries in the UDFs will validate
7 CREATE TEMP TABLE precalc_filter_bib_list (
11 CREATE TEMP TABLE precalc_bib_filter_bib_list (
15 CREATE TEMP TABLE precalc_src_filter_bib_list (
19 CREATE TEMP TABLE precalc_copy_filter_bib_list (
24 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
29 CREATE TEMP TABLE precalc_location_filter_bib_list (
34 CREATE TEMP TABLE precalc_attr_filter_bib_list (
38 CREATE TEMP TABLE precalc_bibs_by_copy_list (
42 CREATE TEMP TABLE precalc_bibs_by_uri_list (
46 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
50 CREATE TEMP TABLE precalc_bib_list (
54 -- rhrr needs to be a real table, so it can be fast. To that end, we use
55 -- a materialized view updated via a trigger.
57 DROP VIEW reporter.hold_request_record;
59 CREATE TABLE reporter.hold_request_record AS
67 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
69 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
70 WHEN hold_type IN ('C','R','F')
71 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
73 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
75 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
77 FROM action.hold_request ahr;
79 CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
80 CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);
82 ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;
84 CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
86 IF TG_OP = 'INSERT' THEN
87 INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
92 WHEN NEW.hold_type = 'T'
94 WHEN NEW.hold_type = 'I'
95 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
96 WHEN NEW.hold_type = 'V'
97 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
98 WHEN NEW.hold_type IN ('C','R','F')
99 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
100 WHEN NEW.hold_type = 'M'
101 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
102 WHEN NEW.hold_type = 'P'
103 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
105 ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
106 UPDATE reporter.hold_request_record
107 SET target = NEW.target,
108 hold_type = NEW.hold_type,
110 WHEN NEW.hold_type = 'T'
112 WHEN NEW.hold_type = 'I'
113 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
114 WHEN NEW.hold_type = 'V'
115 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
116 WHEN NEW.hold_type IN ('C','R','F')
117 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
118 WHEN NEW.hold_type = 'M'
119 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
120 WHEN NEW.hold_type = 'P'
121 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
128 CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
129 FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();
131 CREATE SCHEMA rating;
133 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
135 'OPAC Default Sort (titlesort, authorsort, pubdate, popularity, poprel, or empty for relevance)',
140 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
141 'search.max_popularity_importance_multiplier',
143 'search.max_popularity_importance_multiplier',
144 'Maximum popularity importance multiplier for popularity-adjusted relevance searches (decimal value between 1.0 and 2.0)',
152 CREATE TABLE rating.popularity_parameter (
154 name TEXT NOT NULL UNIQUE, -- i18n
157 require_horizon BOOL NOT NULL DEFAULT FALSE,
158 require_importance BOOL NOT NULL DEFAULT FALSE,
159 require_percentile BOOL NOT NULL DEFAULT FALSE
162 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
163 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
164 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
165 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
166 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
167 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
168 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
169 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
170 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
171 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
172 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
173 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
174 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
175 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
176 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
177 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
179 CREATE TABLE rating.badge (
180 id SERIAL PRIMARY KEY,
183 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
184 weight INT NOT NULL DEFAULT 1,
185 horizon_age INTERVAL,
186 importance_age INTERVAL,
187 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
188 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
189 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
191 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
192 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
193 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
194 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
195 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
196 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
197 discard INT NOT NULL DEFAULT 0,
198 last_calc TIMESTAMPTZ,
199 CONSTRAINT unique_name_scope UNIQUE (name,scope)
202 CREATE TABLE rating.record_badge_score (
203 id BIGSERIAL PRIMARY KEY,
204 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
205 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
206 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
207 CONSTRAINT unique_record_badge UNIQUE (record,badge)
209 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
210 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
212 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
215 array_agg(tree) AS orgs
217 (actor.org_unit_descendants(id)).id AS tree
225 JOIN org_scope s ON (b.scope = s.id);
227 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
233 SET LOCAL client_min_messages = error;
234 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
235 IF src IS NOT NULL THEN
236 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
237 SELECT id FROM biblio.record_entry
238 WHERE source = src AND NOT deleted;
240 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
241 SELECT id FROM biblio.record_entry
242 WHERE id > 0 AND NOT deleted;
245 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
248 $f$ LANGUAGE PLPGSQL;
250 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
256 SET LOCAL client_min_messages = error;
257 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
258 IF cm IS NOT NULL THEN
259 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
260 SELECT cn.record AS id,
262 FROM asset.call_number cn
263 JOIN asset.copy cp ON (cn.id = cp.call_number)
264 WHERE cp.circ_modifier = cm
267 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
268 SELECT cn.record AS id,
270 FROM asset.call_number cn
271 JOIN asset.copy cp ON (cn.id = cp.call_number)
272 WHERE NOT cp.deleted;
275 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
278 $f$ LANGUAGE PLPGSQL;
280 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
286 SET LOCAL client_min_messages = error;
287 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
288 IF loc IS NOT NULL THEN
289 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
290 SELECT cn.record AS id,
292 FROM asset.call_number cn
293 JOIN asset.copy cp ON (cn.id = cp.call_number)
294 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
295 WHERE lg.lgroup = loc
298 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
299 SELECT cn.record AS id,
301 FROM asset.call_number cn
302 JOIN asset.copy cp ON (cn.id = cp.call_number)
303 WHERE NOT cp.deleted;
306 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
309 $f$ LANGUAGE PLPGSQL;
312 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
319 SET LOCAL client_min_messages = error;
320 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
321 IF attr_filter IS NOT NULL THEN
322 afilter := metabib.compile_composite_attr(attr_filter);
323 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
324 SELECT source AS id FROM metabib.record_attr_vector_list
325 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
327 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
328 SELECT source AS id FROM metabib.record_attr_vector_list;
331 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
334 $f$ LANGUAGE PLPGSQL;
336 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
340 badge_row rating.badge_with_orgs%ROWTYPE;
345 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
347 SET LOCAL client_min_messages = error;
348 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
349 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
350 SELECT DISTINCT cn.record AS id
351 FROM asset.call_number cn
352 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
353 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
354 WHERE cn.owning_lib = ANY (badge_row.orgs)
357 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
360 $f$ LANGUAGE PLPGSQL;
362 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
366 badge_row rating.badge_with_orgs%ROWTYPE;
369 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
371 SET LOCAL client_min_messages = error;
372 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
373 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
374 SELECT DISTINCT record AS id
375 FROM asset.call_number cn
376 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
377 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
378 WHERE cn.owning_lib = ANY (badge_row.orgs)
379 AND cn.label = '##URI##'
382 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
385 $f$ LANGUAGE PLPGSQL;
387 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
393 PERFORM rating.precalc_bibs_by_copy(badge_id);
394 PERFORM rating.precalc_bibs_by_uri(badge_id);
396 SET LOCAL client_min_messages = error;
397 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
398 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
399 SELECT id FROM precalc_bibs_by_copy_list
401 SELECT id FROM precalc_bibs_by_uri_list;
403 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
406 $f$ LANGUAGE PLPGSQL;
409 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
411 badge_row rating.badge%ROWTYPE;
412 param rating.popularity_parameter%ROWTYPE;
414 SET LOCAL client_min_messages = error;
416 -- Find what we're doing
417 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
418 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
420 -- Calculate the filtered bib set, or all bibs if none
421 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
422 PERFORM rating.precalc_src_filter(badge_row.src_filter);
423 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
424 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
426 -- Bring the bib-level filter lists together
427 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
428 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
429 SELECT id FROM precalc_attr_filter_bib_list
431 SELECT id FROM precalc_src_filter_bib_list;
433 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
434 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
435 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
436 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
438 SELECT id, copy FROM precalc_location_filter_bib_list;
440 -- Bring the collapsed filter lists together
441 DROP TABLE IF EXISTS precalc_filter_bib_list;
442 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
443 SELECT id FROM precalc_bib_filter_bib_list
445 SELECT id FROM precalc_copy_filter_bib_list;
447 CREATE INDEX precalc_filter_bib_list_idx
448 ON precalc_filter_bib_list (id);
454 -- If it's a fixed-rating badge, just do it ...
455 IF badge_row.fixed_rating IS NOT NULL THEN
456 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
458 INSERT INTO rating.record_badge_score (record, badge, score)
459 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
460 USING badge_id, badge_row.fixed_rating;
462 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
468 -- Make a session-local scratchpad for calculating scores
469 CREATE TEMP TABLE record_score_scratchpad (
476 INSERT INTO record_score_scratchpad (bib, value)
477 SELECT * FROM $e$ || param.func || $e$($1)$e$
480 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
481 -- To speed up discard-common
482 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
483 ANALYZE record_score_scratchpad;
486 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
487 DELETE FROM record_score_scratchpad WHERE value IN (
488 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
492 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
493 DELETE FROM record_score_scratchpad WHERE value <= (
496 CUME_DIST() OVER (ORDER BY value) AS p
497 FROM record_score_scratchpad
498 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
503 -- And, finally, push new data in
504 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
505 INSERT INTO rating.record_badge_score (badge, record, score)
508 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
509 FROM record_score_scratchpad;
511 DROP TABLE record_score_scratchpad;
513 -- Now, finally-finally, mark the badge as recalculated
514 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
518 $f$ LANGUAGE PLPGSQL;
520 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
521 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
523 badge rating.badge_with_orgs%ROWTYPE;
526 iscale NUMERIC := NULL;
529 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
531 IF badge.horizon_age IS NULL THEN
532 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
537 PERFORM rating.precalc_bibs_by_copy(badge_id);
539 SET LOCAL client_min_messages = error;
540 DROP TABLE IF EXISTS precalc_bib_list;
541 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
542 SELECT id FROM precalc_filter_bib_list
544 SELECT id FROM precalc_bibs_by_copy_list;
546 iint := EXTRACT(EPOCH FROM badge.importance_interval);
547 IF badge.importance_age IS NOT NULL THEN
548 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
551 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
552 iscale := COALESCE(badge.importance_scale, 1.0);
556 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
559 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
560 COUNT(h.id)::INT AS holds
561 FROM action.hold_request h
562 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
563 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
564 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
565 AND h.request_lib = ANY (badge.orgs)
570 $f$ LANGUAGE PLPGSQL STRICT;
572 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
573 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
575 badge rating.badge_with_orgs%ROWTYPE;
578 iscale NUMERIC := NULL;
581 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
583 IF badge.horizon_age IS NULL THEN
584 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
589 PERFORM rating.precalc_bibs_by_copy(badge_id);
591 SET LOCAL client_min_messages = error;
592 DROP TABLE IF EXISTS precalc_bib_list;
593 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
594 SELECT id FROM precalc_filter_bib_list
596 SELECT id FROM precalc_bibs_by_copy_list;
598 iint := EXTRACT(EPOCH FROM badge.importance_interval);
599 IF badge.importance_age IS NOT NULL THEN
600 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
603 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
604 iscale := COALESCE(badge.importance_scale, 1.0);
608 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
611 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
612 COUNT(h.id)::INT AS holds
613 FROM action.hold_request h
614 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
615 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
616 WHERE h.request_time >= NOW() - badge.horizon_age
617 AND h.request_lib = ANY (badge.orgs)
622 $f$ LANGUAGE PLPGSQL STRICT;
624 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
625 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
627 badge rating.badge_with_orgs%ROWTYPE;
630 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
632 PERFORM rating.precalc_bibs_by_copy(badge_id);
634 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
635 SELECT id FROM precalc_filter_bib_list
637 SELECT id FROM precalc_bibs_by_copy_list
640 ANALYZE precalc_copy_filter_bib_list;
643 SELECT rhrr.bib_record AS bib,
644 COUNT(DISTINCT h.id)::NUMERIC AS holds
645 FROM action.hold_request h
646 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
647 JOIN action.hold_copy_map m ON (m.hold = h.id)
648 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
649 WHERE h.fulfillment_time IS NULL
650 AND h.request_lib = ANY (badge.orgs)
653 $f$ LANGUAGE PLPGSQL STRICT;
655 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
656 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
658 badge rating.badge_with_orgs%ROWTYPE;
661 iscale NUMERIC := NULL;
664 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
666 IF badge.horizon_age IS NULL THEN
667 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
672 PERFORM rating.precalc_bibs_by_copy(badge_id);
674 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
675 SELECT id FROM precalc_filter_bib_list
677 SELECT id FROM precalc_bibs_by_copy_list
680 ANALYZE precalc_copy_filter_bib_list;
682 iint := EXTRACT(EPOCH FROM badge.importance_interval);
683 IF badge.importance_age IS NOT NULL THEN
684 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
687 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
688 iscale := COALESCE(badge.importance_scale, 1.0);
692 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
694 SELECT cn.record AS bib,
695 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
696 COUNT(c.id)::INT AS circs
697 FROM action.circulation c
698 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
699 JOIN asset.copy cp ON (cp.id = c.target_copy)
700 JOIN asset.call_number cn ON (cn.id = cp.call_number)
701 WHERE c.xact_start >= NOW() - badge.horizon_age
702 AND cn.owning_lib = ANY (badge.orgs)
703 AND c.phone_renewal IS FALSE -- we don't count renewals
704 AND c.desk_renewal IS FALSE
705 AND c.opac_renewal IS FALSE
710 $f$ LANGUAGE PLPGSQL STRICT;
712 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
713 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
715 badge rating.badge_with_orgs%ROWTYPE;
718 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
720 PERFORM rating.precalc_bibs_by_copy(badge_id);
722 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
723 SELECT id FROM precalc_filter_bib_list
725 SELECT id FROM precalc_bibs_by_copy_list
728 ANALYZE precalc_copy_filter_bib_list;
731 SELECT cn.record AS bib,
732 COUNT(c.id)::NUMERIC AS circs
733 FROM action.circulation c
734 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
735 JOIN asset.copy cp ON (cp.id = c.target_copy)
736 JOIN asset.call_number cn ON (cn.id = cp.call_number)
737 WHERE c.checkin_time IS NULL
738 AND cn.owning_lib = ANY (badge.orgs)
742 $f$ LANGUAGE PLPGSQL STRICT;
744 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
745 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
747 badge rating.badge_with_orgs%ROWTYPE;
750 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
752 PERFORM rating.precalc_bibs_by_copy(badge_id);
754 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
755 SELECT id FROM precalc_filter_bib_list
757 SELECT id FROM precalc_bibs_by_copy_list
760 ANALYZE precalc_copy_filter_bib_list;
764 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
765 FROM (SELECT cn.record AS bib,
766 (cp.status = 1)::INT AS checked_out,
769 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
770 JOIN asset.call_number cn ON (cn.id = cp.call_number)
771 WHERE cn.owning_lib = ANY (badge.orgs)
775 $f$ LANGUAGE PLPGSQL STRICT;
777 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
778 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
780 badge rating.badge_with_orgs%ROWTYPE;
783 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
785 PERFORM rating.precalc_bibs_by_copy(badge_id);
787 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
788 SELECT id FROM precalc_filter_bib_list
790 SELECT id FROM precalc_bibs_by_copy_list
793 ANALYZE precalc_copy_filter_bib_list;
796 SELECT cn.record AS bib,
797 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
799 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
800 JOIN asset.call_number cn ON (cn.id = cp.call_number)
801 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
802 WHERE cn.owning_lib = ANY (badge.orgs)
805 $f$ LANGUAGE PLPGSQL STRICT;
807 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
808 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
810 badge rating.badge_with_orgs%ROWTYPE;
813 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
815 PERFORM rating.precalc_bibs_by_copy(badge_id);
817 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
818 SELECT id FROM precalc_filter_bib_list
820 SELECT id FROM precalc_bibs_by_copy_list
823 ANALYZE precalc_copy_filter_bib_list;
826 SELECT cn.record AS bib,
827 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
829 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
830 JOIN asset.copy_location cl ON (cl.id = cp.location)
831 JOIN config.copy_status cs ON (cs.id = cp.status)
832 JOIN asset.call_number cn ON (cn.id = cp.call_number)
833 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
834 WHERE cn.owning_lib = ANY (badge.orgs)
835 AND cp.holdable IS TRUE
836 AND cl.holdable IS TRUE
837 AND cs.holdable IS TRUE
840 $f$ LANGUAGE PLPGSQL STRICT;
842 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
843 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
845 badge rating.badge_with_orgs%ROWTYPE;
848 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
850 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
852 SET LOCAL client_min_messages = error;
853 DROP TABLE IF EXISTS precalc_bib_list;
854 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
855 SELECT id FROM precalc_filter_bib_list
857 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
861 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
862 FROM precalc_bib_list pop
863 JOIN biblio.record_entry b ON (b.id = pop.id);
865 $f$ LANGUAGE PLPGSQL STRICT;
867 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
868 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
870 badge rating.badge_with_orgs%ROWTYPE;
873 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
875 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
877 SET LOCAL client_min_messages = error;
878 DROP TABLE IF EXISTS precalc_bib_list;
879 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
880 SELECT id FROM precalc_filter_bib_list
882 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
885 SELECT pop.id AS bib,
887 FROM precalc_bib_list pop
888 JOIN metabib.record_sorter s ON (
890 AND s.attr = 'pubdate'
891 AND s.value ~ '^\d+$'
893 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
895 $f$ LANGUAGE PLPGSQL STRICT;
897 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
898 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
900 badge rating.badge_with_orgs%ROWTYPE;
903 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
905 PERFORM rating.precalc_bibs_by_copy(badge_id);
907 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
908 SELECT id FROM precalc_filter_bib_list
910 SELECT id FROM precalc_bibs_by_copy_list
913 ANALYZE precalc_copy_filter_bib_list;
917 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
918 FROM (SELECT cn.record AS bib,
920 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
921 SUM( -- time copy spent circulating
925 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
929 )::NUMERIC AS circ_time
931 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
932 JOIN asset.call_number cn ON (cn.id = cp.call_number)
933 LEFT JOIN action.all_circulation circ ON (
934 circ.target_copy = cp.id
935 AND stop_fines NOT IN (
942 checkin_time IS NULL AND
943 stop_fines = 'MAXFINES'
946 WHERE cn.owning_lib = ANY (badge.orgs)
947 AND cp.active_date IS NOT NULL
948 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
949 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
954 $f$ LANGUAGE PLPGSQL STRICT;
956 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
957 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
959 PERFORM rating.precalc_bibs_by_copy(badge_id);
961 SELECT id, 1.0 FROM precalc_filter_bib_list
963 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
965 $f$ LANGUAGE PLPGSQL STRICT;
967 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
968 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
970 PERFORM rating.precalc_bibs_by_uri(badge_id);
972 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
974 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
976 $f$ LANGUAGE PLPGSQL STRICT;
978 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
979 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
981 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
983 (SELECT id, 1.0 FROM precalc_filter_bib_list
985 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
987 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
989 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
991 $f$ LANGUAGE PLPGSQL STRICT;
993 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
994 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
997 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
999 $f$ LANGUAGE PLPGSQL STRICT;
1001 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
1002 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);