1 DROP SCHEMA IF EXISTS rating CASCADE;
5 -- Create these so that the queries in the UDFs will validate
6 CREATE TEMP TABLE precalc_filter_bib_list (
10 CREATE TEMP TABLE precalc_bib_filter_bib_list (
14 CREATE TEMP TABLE precalc_src_filter_bib_list (
18 CREATE TEMP TABLE precalc_copy_filter_bib_list (
23 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
28 CREATE TEMP TABLE precalc_location_filter_bib_list (
33 CREATE TEMP TABLE precalc_attr_filter_bib_list (
37 CREATE TEMP TABLE precalc_bibs_by_copy_list (
41 CREATE TEMP TABLE precalc_bibs_by_uri_list (
45 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
49 CREATE TEMP TABLE precalc_bib_list (
55 CREATE TABLE rating.popularity_parameter (
57 name TEXT NOT NULL UNIQUE, -- i18n
60 require_horizon BOOL NOT NULL DEFAULT FALSE,
61 require_importance BOOL NOT NULL DEFAULT FALSE,
62 require_percentile BOOL NOT NULL DEFAULT FALSE
65 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
66 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
67 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
68 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
69 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
70 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
71 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
72 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
73 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
74 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
75 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
76 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
77 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
78 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
79 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
80 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
82 CREATE TABLE rating.badge (
83 id SERIAL PRIMARY KEY,
86 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
87 weight INT NOT NULL DEFAULT 1,
89 importance_age INTERVAL,
90 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
91 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
92 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
94 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
95 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
96 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
97 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
98 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
99 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
100 discard INT NOT NULL DEFAULT 0,
101 last_calc TIMESTAMPTZ,
102 CONSTRAINT unique_name_scope UNIQUE (name,scope)
105 CREATE TABLE rating.record_badge_score (
106 id BIGSERIAL PRIMARY KEY,
107 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
108 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
109 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
110 CONSTRAINT unique_record_badge UNIQUE (record,badge)
112 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
113 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
115 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
118 array_agg(tree) AS orgs
120 (actor.org_unit_descendants(id)).id AS tree
128 JOIN org_scope s ON (b.scope = s.id);
130 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
136 SET LOCAL client_min_messages = error;
137 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
138 IF src IS NOT NULL THEN
139 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
140 SELECT id FROM biblio.record_entry
141 WHERE source = src AND NOT deleted;
143 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
144 SELECT id FROM biblio.record_entry
145 WHERE id > 0 AND NOT deleted;
148 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
151 $f$ LANGUAGE PLPGSQL;
153 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
159 SET LOCAL client_min_messages = error;
160 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
161 IF cm IS NOT NULL THEN
162 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
163 SELECT cn.record AS id,
165 FROM asset.call_number cn
166 JOIN asset.copy cp ON (cn.id = cp.call_number)
167 WHERE cp.circ_modifier = cm
170 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
171 SELECT cn.record AS id,
173 FROM asset.call_number cn
174 JOIN asset.copy cp ON (cn.id = cp.call_number)
175 WHERE NOT cp.deleted;
178 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
181 $f$ LANGUAGE PLPGSQL;
183 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
189 SET LOCAL client_min_messages = error;
190 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
191 IF loc IS NOT NULL THEN
192 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
193 SELECT cn.record AS id,
195 FROM asset.call_number cn
196 JOIN asset.copy cp ON (cn.id = cp.call_number)
197 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
198 WHERE lg.lgroup = loc
201 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
202 SELECT cn.record AS id,
204 FROM asset.call_number cn
205 JOIN asset.copy cp ON (cn.id = cp.call_number)
206 WHERE NOT cp.deleted;
209 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
212 $f$ LANGUAGE PLPGSQL;
215 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
222 SET LOCAL client_min_messages = error;
223 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
224 IF attr_filter IS NOT NULL THEN
225 afilter := metabib.compile_composite_attr(attr_filter);
226 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
227 SELECT source AS id FROM metabib.record_attr_vector_list
228 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
230 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
231 SELECT source AS id FROM metabib.record_attr_vector_list;
234 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
237 $f$ LANGUAGE PLPGSQL;
239 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
243 badge_row rating.badge_with_orgs%ROWTYPE;
248 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
250 SET LOCAL client_min_messages = error;
251 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
252 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
253 SELECT DISTINCT cn.record AS id
254 FROM asset.call_number cn
255 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
256 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
257 WHERE cn.owning_lib = ANY (badge_row.orgs)
260 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
263 $f$ LANGUAGE PLPGSQL;
265 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
269 badge_row rating.badge_with_orgs%ROWTYPE;
272 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
274 SET LOCAL client_min_messages = error;
275 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
276 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
277 SELECT DISTINCT record AS id
278 FROM asset.call_number cn
279 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
280 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
281 WHERE cn.owning_lib = ANY (badge_row.orgs)
282 AND cn.label = '##URI##'
285 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
288 $f$ LANGUAGE PLPGSQL;
290 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
296 PERFORM rating.precalc_bibs_by_copy(badge_id);
297 PERFORM rating.precalc_bibs_by_uri(badge_id);
299 SET LOCAL client_min_messages = error;
300 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
301 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
302 SELECT id FROM precalc_bibs_by_copy_list
304 SELECT id FROM precalc_bibs_by_uri_list;
306 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
309 $f$ LANGUAGE PLPGSQL;
312 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
314 badge_row rating.badge%ROWTYPE;
315 param rating.popularity_parameter%ROWTYPE;
317 SET LOCAL client_min_messages = error;
319 -- Find what we're doing
320 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
321 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
323 -- Calculate the filtered bib set, or all bibs if none
324 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
325 PERFORM rating.precalc_src_filter(badge_row.src_filter);
326 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
327 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
329 -- Bring the bib-level filter lists together
330 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
331 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
332 SELECT id FROM precalc_attr_filter_bib_list
334 SELECT id FROM precalc_src_filter_bib_list;
336 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
337 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
338 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
339 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
341 SELECT id, copy FROM precalc_location_filter_bib_list;
343 -- Bring the collapsed filter lists together
344 DROP TABLE IF EXISTS precalc_filter_bib_list;
345 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
346 SELECT id FROM precalc_bib_filter_bib_list
348 SELECT id FROM precalc_copy_filter_bib_list;
350 CREATE INDEX precalc_filter_bib_list_idx
351 ON precalc_filter_bib_list (id);
357 -- If it's a fixed-rating badge, just do it ...
358 IF badge_row.fixed_rating IS NOT NULL THEN
359 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
361 INSERT INTO rating.record_badge_score (record, badge, score)
362 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
363 USING badge_id, badge_row.fixed_rating;
365 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
371 -- Make a session-local scratchpad for calculating scores
372 CREATE TEMP TABLE record_score_scratchpad (
379 INSERT INTO record_score_scratchpad (bib, value)
380 SELECT * FROM $e$ || param.func || $e$($1)$e$
383 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
384 -- To speed up discard-common
385 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
386 ANALYZE record_score_scratchpad;
389 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
390 DELETE FROM record_score_scratchpad WHERE value IN (
391 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
395 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
396 DELETE FROM record_score_scratchpad WHERE value <= (
399 CUME_DIST() OVER (ORDER BY value) AS p
400 FROM record_score_scratchpad
401 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
406 -- And, finally, push new data in
407 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
408 INSERT INTO rating.record_badge_score (badge, record, score)
411 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
412 FROM record_score_scratchpad;
414 DROP TABLE record_score_scratchpad;
416 -- Now, finally-finally, mark the badge as recalculated
417 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
421 $f$ LANGUAGE PLPGSQL;
423 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
424 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
426 badge rating.badge_with_orgs%ROWTYPE;
429 iscale NUMERIC := NULL;
432 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
434 IF badge.horizon_age IS NULL THEN
435 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
440 PERFORM rating.precalc_bibs_by_copy(badge_id);
442 SET LOCAL client_min_messages = error;
443 DROP TABLE IF EXISTS precalc_bib_list;
444 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
445 SELECT id FROM precalc_filter_bib_list
447 SELECT id FROM precalc_bibs_by_copy_list;
449 iint := EXTRACT(EPOCH FROM badge.importance_interval);
450 IF badge.importance_age IS NOT NULL THEN
451 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
454 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
455 iscale := COALESCE(badge.importance_scale, 1.0);
459 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
462 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
463 COUNT(h.id)::INT AS holds
464 FROM action.hold_request h
465 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
466 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
467 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
468 AND h.request_lib = ANY (badge.orgs)
473 $f$ LANGUAGE PLPGSQL STRICT;
475 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
476 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
478 badge rating.badge_with_orgs%ROWTYPE;
481 iscale NUMERIC := NULL;
484 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
486 IF badge.horizon_age IS NULL THEN
487 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
492 PERFORM rating.precalc_bibs_by_copy(badge_id);
494 SET LOCAL client_min_messages = error;
495 DROP TABLE IF EXISTS precalc_bib_list;
496 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
497 SELECT id FROM precalc_filter_bib_list
499 SELECT id FROM precalc_bibs_by_copy_list;
501 iint := EXTRACT(EPOCH FROM badge.importance_interval);
502 IF badge.importance_age IS NOT NULL THEN
503 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
506 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
507 iscale := COALESCE(badge.importance_scale, 1.0);
511 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
514 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
515 COUNT(h.id)::INT AS holds
516 FROM action.hold_request h
517 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
518 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
519 WHERE h.request_time >= NOW() - badge.horizon_age
520 AND h.request_lib = ANY (badge.orgs)
525 $f$ LANGUAGE PLPGSQL STRICT;
527 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
528 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
530 badge rating.badge_with_orgs%ROWTYPE;
533 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
535 PERFORM rating.precalc_bibs_by_copy(badge_id);
537 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
538 SELECT id FROM precalc_filter_bib_list
540 SELECT id FROM precalc_bibs_by_copy_list
543 ANALYZE precalc_copy_filter_bib_list;
546 SELECT rhrr.bib_record AS bib,
547 COUNT(DISTINCT h.id)::NUMERIC AS holds
548 FROM action.hold_request h
549 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
550 JOIN action.hold_copy_map m ON (m.hold = h.id)
551 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
552 WHERE h.fulfillment_time IS NULL
553 AND h.request_lib = ANY (badge.orgs)
556 $f$ LANGUAGE PLPGSQL STRICT;
558 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
559 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
561 badge rating.badge_with_orgs%ROWTYPE;
564 iscale NUMERIC := NULL;
567 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
569 IF badge.horizon_age IS NULL THEN
570 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
575 PERFORM rating.precalc_bibs_by_copy(badge_id);
577 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
578 SELECT id FROM precalc_filter_bib_list
580 SELECT id FROM precalc_bibs_by_copy_list
583 ANALYZE precalc_copy_filter_bib_list;
585 iint := EXTRACT(EPOCH FROM badge.importance_interval);
586 IF badge.importance_age IS NOT NULL THEN
587 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
590 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
591 iscale := COALESCE(badge.importance_scale, 1.0);
595 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
597 SELECT cn.record AS bib,
598 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
599 COUNT(c.id)::INT AS circs
600 FROM action.circulation c
601 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
602 JOIN asset.copy cp ON (cp.id = c.target_copy)
603 JOIN asset.call_number cn ON (cn.id = cp.call_number)
604 WHERE c.xact_start >= NOW() - badge.horizon_age
605 AND cn.owning_lib = ANY (badge.orgs)
606 AND c.phone_renewal IS FALSE -- we don't count renewals
607 AND c.desk_renewal IS FALSE
608 AND c.opac_renewal IS FALSE
613 $f$ LANGUAGE PLPGSQL STRICT;
615 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
616 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
618 badge rating.badge_with_orgs%ROWTYPE;
621 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
623 PERFORM rating.precalc_bibs_by_copy(badge_id);
625 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
626 SELECT id FROM precalc_filter_bib_list
628 SELECT id FROM precalc_bibs_by_copy_list
631 ANALYZE precalc_copy_filter_bib_list;
634 SELECT cn.record AS bib,
635 COUNT(c.id)::NUMERIC AS circs
636 FROM action.circulation c
637 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
638 JOIN asset.copy cp ON (cp.id = c.target_copy)
639 JOIN asset.call_number cn ON (cn.id = cp.call_number)
640 WHERE c.checkin_time IS NULL
641 AND cn.owning_lib = ANY (badge.orgs)
645 $f$ LANGUAGE PLPGSQL STRICT;
647 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
648 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
650 badge rating.badge_with_orgs%ROWTYPE;
653 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
655 PERFORM rating.precalc_bibs_by_copy(badge_id);
657 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
658 SELECT id FROM precalc_filter_bib_list
660 SELECT id FROM precalc_bibs_by_copy_list
663 ANALYZE precalc_copy_filter_bib_list;
667 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
668 FROM (SELECT cn.record AS bib,
669 (cp.status = 1)::INT AS checked_out,
672 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
673 JOIN asset.call_number cn ON (cn.id = cp.call_number)
674 WHERE cn.owning_lib = ANY (badge.orgs)
678 $f$ LANGUAGE PLPGSQL STRICT;
680 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
681 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
683 badge rating.badge_with_orgs%ROWTYPE;
686 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
688 PERFORM rating.precalc_bibs_by_copy(badge_id);
690 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
691 SELECT id FROM precalc_filter_bib_list
693 SELECT id FROM precalc_bibs_by_copy_list
696 ANALYZE precalc_copy_filter_bib_list;
699 SELECT cn.record AS bib,
700 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
702 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
703 JOIN asset.call_number cn ON (cn.id = cp.call_number)
704 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
705 WHERE cn.owning_lib = ANY (badge.orgs)
708 $f$ LANGUAGE PLPGSQL STRICT;
710 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
711 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
713 badge rating.badge_with_orgs%ROWTYPE;
716 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
718 PERFORM rating.precalc_bibs_by_copy(badge_id);
720 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
721 SELECT id FROM precalc_filter_bib_list
723 SELECT id FROM precalc_bibs_by_copy_list
726 ANALYZE precalc_copy_filter_bib_list;
729 SELECT cn.record AS bib,
730 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
732 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
733 JOIN asset.copy_location cl ON (cl.id = cp.location)
734 JOIN config.copy_status cs ON (cs.id = cp.status)
735 JOIN asset.call_number cn ON (cn.id = cp.call_number)
736 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
737 WHERE cn.owning_lib = ANY (badge.orgs)
738 AND cp.holdable IS TRUE
739 AND cl.holdable IS TRUE
740 AND cs.holdable IS TRUE
743 $f$ LANGUAGE PLPGSQL STRICT;
745 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
746 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
748 badge rating.badge_with_orgs%ROWTYPE;
751 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
753 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
755 SET LOCAL client_min_messages = error;
756 DROP TABLE IF EXISTS precalc_bib_list;
757 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
758 SELECT id FROM precalc_filter_bib_list
760 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
764 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
765 FROM precalc_bib_list pop
766 JOIN biblio.record_entry b ON (b.id = pop.id);
768 $f$ LANGUAGE PLPGSQL STRICT;
770 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
771 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
773 badge rating.badge_with_orgs%ROWTYPE;
776 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
778 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
780 SET LOCAL client_min_messages = error;
781 DROP TABLE IF EXISTS precalc_bib_list;
782 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
783 SELECT id FROM precalc_filter_bib_list
785 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
788 SELECT pop.id AS bib,
790 FROM precalc_bib_list pop
791 JOIN metabib.record_sorter s ON (
793 AND s.attr = 'pubdate'
794 AND s.value ~ '^\d+$'
796 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
798 $f$ LANGUAGE PLPGSQL STRICT;
800 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
801 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
803 badge rating.badge_with_orgs%ROWTYPE;
806 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
808 PERFORM rating.precalc_bibs_by_copy(badge_id);
810 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
811 SELECT id FROM precalc_filter_bib_list
813 SELECT id FROM precalc_bibs_by_copy_list
816 ANALYZE precalc_copy_filter_bib_list;
820 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
821 FROM (SELECT cn.record AS bib,
823 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
824 SUM( -- time copy spent circulating
828 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
832 )::NUMERIC AS circ_time
834 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
835 JOIN asset.call_number cn ON (cn.id = cp.call_number)
836 LEFT JOIN action.all_circulation circ ON (
837 circ.target_copy = cp.id
838 AND stop_fines NOT IN (
845 checkin_time IS NULL AND
846 stop_fines = 'MAXFINES'
849 WHERE cn.owning_lib = ANY (badge.orgs)
850 AND cp.active_date IS NOT NULL
851 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
852 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
857 $f$ LANGUAGE PLPGSQL STRICT;
859 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
860 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
862 PERFORM rating.precalc_bibs_by_copy(badge_id);
864 SELECT id, 1.0 FROM precalc_filter_bib_list
866 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
868 $f$ LANGUAGE PLPGSQL STRICT;
870 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
871 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
873 PERFORM rating.precalc_bibs_by_uri(badge_id);
875 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
877 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
879 $f$ LANGUAGE PLPGSQL STRICT;
881 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
882 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
884 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
886 (SELECT id, 1.0 FROM precalc_filter_bib_list
888 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
890 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
892 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
894 $f$ LANGUAGE PLPGSQL STRICT;
896 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
897 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
900 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
902 $f$ LANGUAGE PLPGSQL STRICT;