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),
81 (16,'Copy Count','rating.copy_count',FALSE,FALSE,TRUE);
84 CREATE TABLE rating.badge (
85 id SERIAL PRIMARY KEY,
88 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
89 weight INT NOT NULL DEFAULT 1,
91 importance_age INTERVAL,
92 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
93 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
94 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
96 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
97 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
98 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
99 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
100 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
101 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
102 discard INT NOT NULL DEFAULT 0,
103 last_calc TIMESTAMPTZ,
104 CONSTRAINT unique_name_scope UNIQUE (name,scope)
107 CREATE TABLE rating.record_badge_score (
108 id BIGSERIAL PRIMARY KEY,
109 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
110 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
111 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
112 CONSTRAINT unique_record_badge UNIQUE (record,badge)
114 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
115 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
117 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
120 array_agg(tree) AS orgs
122 (actor.org_unit_descendants(id)).id AS tree
130 JOIN org_scope s ON (b.scope = s.id);
132 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
138 SET LOCAL client_min_messages = error;
139 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
140 IF src IS NOT NULL THEN
141 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
142 SELECT id FROM biblio.record_entry
143 WHERE source = src AND NOT deleted;
145 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
146 SELECT id FROM biblio.record_entry
147 WHERE id > 0 AND NOT deleted;
150 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
153 $f$ LANGUAGE PLPGSQL;
155 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
161 SET LOCAL client_min_messages = error;
162 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
163 IF cm IS NOT NULL THEN
164 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
165 SELECT cn.record AS id,
167 FROM asset.call_number cn
168 JOIN asset.copy cp ON (cn.id = cp.call_number)
169 WHERE cp.circ_modifier = cm
172 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
173 SELECT cn.record AS id,
175 FROM asset.call_number cn
176 JOIN asset.copy cp ON (cn.id = cp.call_number)
177 WHERE NOT cp.deleted;
180 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
183 $f$ LANGUAGE PLPGSQL;
185 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
191 SET LOCAL client_min_messages = error;
192 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
193 IF loc IS NOT NULL THEN
194 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
195 SELECT cn.record AS id,
197 FROM asset.call_number cn
198 JOIN asset.copy cp ON (cn.id = cp.call_number)
199 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
200 WHERE lg.lgroup = loc
203 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
204 SELECT cn.record AS id,
206 FROM asset.call_number cn
207 JOIN asset.copy cp ON (cn.id = cp.call_number)
208 WHERE NOT cp.deleted;
211 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
214 $f$ LANGUAGE PLPGSQL;
217 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
224 SET LOCAL client_min_messages = error;
225 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
226 IF attr_filter IS NOT NULL THEN
227 afilter := metabib.compile_composite_attr(attr_filter);
228 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
229 SELECT source AS id FROM metabib.record_attr_vector_list
230 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
232 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
233 SELECT source AS id FROM metabib.record_attr_vector_list;
236 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
239 $f$ LANGUAGE PLPGSQL;
241 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
245 badge_row rating.badge_with_orgs%ROWTYPE;
250 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
252 SET LOCAL client_min_messages = error;
253 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
254 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
255 SELECT DISTINCT cn.record AS id
256 FROM asset.call_number cn
257 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
258 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
259 WHERE cn.owning_lib = ANY (badge_row.orgs)
262 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
265 $f$ LANGUAGE PLPGSQL;
267 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
271 badge_row rating.badge_with_orgs%ROWTYPE;
274 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
276 SET LOCAL client_min_messages = error;
277 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
278 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
279 SELECT DISTINCT record AS id
280 FROM asset.call_number cn
281 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
282 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
283 WHERE cn.owning_lib = ANY (badge_row.orgs)
284 AND cn.label = '##URI##'
287 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
290 $f$ LANGUAGE PLPGSQL;
292 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
298 PERFORM rating.precalc_bibs_by_copy(badge_id);
299 PERFORM rating.precalc_bibs_by_uri(badge_id);
301 SET LOCAL client_min_messages = error;
302 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
303 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
304 SELECT id FROM precalc_bibs_by_copy_list
306 SELECT id FROM precalc_bibs_by_uri_list;
308 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
311 $f$ LANGUAGE PLPGSQL;
314 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
316 badge_row rating.badge%ROWTYPE;
317 param rating.popularity_parameter%ROWTYPE;
319 SET LOCAL client_min_messages = error;
321 -- Find what we're doing
322 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
323 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
325 -- Calculate the filtered bib set, or all bibs if none
326 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
327 PERFORM rating.precalc_src_filter(badge_row.src_filter);
328 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
329 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
331 -- Bring the bib-level filter lists together
332 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
333 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
334 SELECT id FROM precalc_attr_filter_bib_list
336 SELECT id FROM precalc_src_filter_bib_list;
338 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
339 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
340 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
341 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
343 SELECT id, copy FROM precalc_location_filter_bib_list;
345 -- Bring the collapsed filter lists together
346 DROP TABLE IF EXISTS precalc_filter_bib_list;
347 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
348 SELECT id FROM precalc_bib_filter_bib_list
350 SELECT id FROM precalc_copy_filter_bib_list;
352 CREATE INDEX precalc_filter_bib_list_idx
353 ON precalc_filter_bib_list (id);
359 -- If it's a fixed-rating badge, just do it ...
360 IF badge_row.fixed_rating IS NOT NULL THEN
361 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
363 INSERT INTO rating.record_badge_score (record, badge, score)
364 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
365 USING badge_id, badge_row.fixed_rating;
367 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
373 -- Make a session-local scratchpad for calculating scores
374 CREATE TEMP TABLE record_score_scratchpad (
381 INSERT INTO record_score_scratchpad (bib, value)
382 SELECT * FROM $e$ || param.func || $e$($1)$e$
385 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
386 -- To speed up discard-common
387 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
388 ANALYZE record_score_scratchpad;
391 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
392 DELETE FROM record_score_scratchpad WHERE value IN (
393 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
397 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
398 DELETE FROM record_score_scratchpad WHERE value <= (
401 CUME_DIST() OVER (ORDER BY value) AS p
402 FROM record_score_scratchpad
403 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
408 -- And, finally, push new data in
409 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
410 INSERT INTO rating.record_badge_score (badge, record, score)
413 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
414 FROM record_score_scratchpad;
416 DROP TABLE record_score_scratchpad;
418 -- Now, finally-finally, mark the badge as recalculated
419 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
423 $f$ LANGUAGE PLPGSQL;
425 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
426 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
428 badge rating.badge_with_orgs%ROWTYPE;
431 iscale NUMERIC := NULL;
434 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
436 IF badge.horizon_age IS NULL THEN
437 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
442 PERFORM rating.precalc_bibs_by_copy(badge_id);
444 SET LOCAL client_min_messages = error;
445 DROP TABLE IF EXISTS precalc_bib_list;
446 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
447 SELECT id FROM precalc_filter_bib_list
449 SELECT id FROM precalc_bibs_by_copy_list;
451 iint := EXTRACT(EPOCH FROM badge.importance_interval);
452 IF badge.importance_age IS NOT NULL THEN
453 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
456 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
457 iscale := COALESCE(badge.importance_scale, 1.0);
461 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
464 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
465 COUNT(h.id)::INT AS holds
466 FROM action.hold_request h
467 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
468 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
469 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
470 AND h.request_lib = ANY (badge.orgs)
475 $f$ LANGUAGE PLPGSQL STRICT;
477 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
478 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
480 badge rating.badge_with_orgs%ROWTYPE;
483 iscale NUMERIC := NULL;
486 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
488 IF badge.horizon_age IS NULL THEN
489 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
494 PERFORM rating.precalc_bibs_by_copy(badge_id);
496 SET LOCAL client_min_messages = error;
497 DROP TABLE IF EXISTS precalc_bib_list;
498 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
499 SELECT id FROM precalc_filter_bib_list
501 SELECT id FROM precalc_bibs_by_copy_list;
503 iint := EXTRACT(EPOCH FROM badge.importance_interval);
504 IF badge.importance_age IS NOT NULL THEN
505 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
508 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
509 iscale := COALESCE(badge.importance_scale, 1.0);
513 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
516 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
517 COUNT(h.id)::INT AS holds
518 FROM action.hold_request h
519 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
520 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
521 WHERE h.request_time >= NOW() - badge.horizon_age
522 AND h.request_lib = ANY (badge.orgs)
527 $f$ LANGUAGE PLPGSQL STRICT;
529 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
530 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
532 badge rating.badge_with_orgs%ROWTYPE;
535 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
537 PERFORM rating.precalc_bibs_by_copy(badge_id);
539 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
540 SELECT id FROM precalc_filter_bib_list
542 SELECT id FROM precalc_bibs_by_copy_list
545 ANALYZE precalc_copy_filter_bib_list;
548 SELECT rhrr.bib_record AS bib,
549 COUNT(DISTINCT h.id)::NUMERIC AS holds
550 FROM action.hold_request h
551 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
552 JOIN action.hold_copy_map m ON (m.hold = h.id)
553 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
554 WHERE h.fulfillment_time IS NULL
555 AND h.request_lib = ANY (badge.orgs)
558 $f$ LANGUAGE PLPGSQL STRICT;
560 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
561 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
563 badge rating.badge_with_orgs%ROWTYPE;
566 iscale NUMERIC := NULL;
569 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
571 IF badge.horizon_age IS NULL THEN
572 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
577 PERFORM rating.precalc_bibs_by_copy(badge_id);
579 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
580 SELECT id FROM precalc_filter_bib_list
582 SELECT id FROM precalc_bibs_by_copy_list
585 ANALYZE precalc_copy_filter_bib_list;
587 iint := EXTRACT(EPOCH FROM badge.importance_interval);
588 IF badge.importance_age IS NOT NULL THEN
589 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
592 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
593 iscale := COALESCE(badge.importance_scale, 1.0);
597 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
599 SELECT cn.record AS bib,
600 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
601 COUNT(c.id)::INT AS circs
602 FROM action.circulation c
603 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
604 JOIN asset.copy cp ON (cp.id = c.target_copy)
605 JOIN asset.call_number cn ON (cn.id = cp.call_number)
606 WHERE c.xact_start >= NOW() - badge.horizon_age
607 AND cn.owning_lib = ANY (badge.orgs)
608 AND c.phone_renewal IS FALSE -- we don't count renewals
609 AND c.desk_renewal IS FALSE
610 AND c.opac_renewal IS FALSE
615 $f$ LANGUAGE PLPGSQL STRICT;
617 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
618 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
620 badge rating.badge_with_orgs%ROWTYPE;
623 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
625 PERFORM rating.precalc_bibs_by_copy(badge_id);
627 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
628 SELECT id FROM precalc_filter_bib_list
630 SELECT id FROM precalc_bibs_by_copy_list
633 ANALYZE precalc_copy_filter_bib_list;
636 SELECT cn.record AS bib,
637 COUNT(c.id)::NUMERIC AS circs
638 FROM action.circulation c
639 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
640 JOIN asset.copy cp ON (cp.id = c.target_copy)
641 JOIN asset.call_number cn ON (cn.id = cp.call_number)
642 WHERE c.checkin_time IS NULL
643 AND cn.owning_lib = ANY (badge.orgs)
647 $f$ LANGUAGE PLPGSQL STRICT;
649 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
650 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
652 badge rating.badge_with_orgs%ROWTYPE;
655 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
657 PERFORM rating.precalc_bibs_by_copy(badge_id);
659 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
660 SELECT id FROM precalc_filter_bib_list
662 SELECT id FROM precalc_bibs_by_copy_list
665 ANALYZE precalc_copy_filter_bib_list;
669 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
670 FROM (SELECT cn.record AS bib,
671 (cp.status = 1)::INT AS checked_out,
674 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
675 JOIN asset.call_number cn ON (cn.id = cp.call_number)
676 WHERE cn.owning_lib = ANY (badge.orgs)
680 $f$ LANGUAGE PLPGSQL STRICT;
682 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
683 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
685 badge rating.badge_with_orgs%ROWTYPE;
688 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
690 PERFORM rating.precalc_bibs_by_copy(badge_id);
692 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
693 SELECT id FROM precalc_filter_bib_list
695 SELECT id FROM precalc_bibs_by_copy_list
698 ANALYZE precalc_copy_filter_bib_list;
701 SELECT cn.record AS bib,
702 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
704 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
705 JOIN asset.call_number cn ON (cn.id = cp.call_number)
706 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
707 WHERE cn.owning_lib = ANY (badge.orgs)
710 $f$ LANGUAGE PLPGSQL STRICT;
712 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(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(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
734 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
735 JOIN asset.copy_location cl ON (cl.id = cp.location)
736 JOIN config.copy_status cs ON (cs.id = cp.status)
737 JOIN asset.call_number cn ON (cn.id = cp.call_number)
738 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
739 WHERE cn.owning_lib = ANY (badge.orgs)
740 AND cp.holdable IS TRUE
741 AND cl.holdable IS TRUE
742 AND cs.holdable IS TRUE
745 $f$ LANGUAGE PLPGSQL STRICT;
747 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
748 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
750 badge rating.badge_with_orgs%ROWTYPE;
753 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
755 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
757 SET LOCAL client_min_messages = error;
758 DROP TABLE IF EXISTS precalc_bib_list;
759 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
760 SELECT id FROM precalc_filter_bib_list
762 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
766 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
767 FROM precalc_bib_list pop
768 JOIN biblio.record_entry b ON (b.id = pop.id);
770 $f$ LANGUAGE PLPGSQL STRICT;
772 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
773 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
775 badge rating.badge_with_orgs%ROWTYPE;
778 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
780 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
782 SET LOCAL client_min_messages = error;
783 DROP TABLE IF EXISTS precalc_bib_list;
784 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
785 SELECT id FROM precalc_filter_bib_list
787 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
790 SELECT pop.id AS bib,
792 FROM precalc_bib_list pop
793 JOIN metabib.record_sorter s ON (
795 AND s.attr = 'pubdate'
796 AND s.value ~ '^\d+$'
798 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
800 $f$ LANGUAGE PLPGSQL STRICT;
802 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
803 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
805 badge rating.badge_with_orgs%ROWTYPE;
808 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
810 PERFORM rating.precalc_bibs_by_copy(badge_id);
812 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
813 SELECT id FROM precalc_filter_bib_list
815 SELECT id FROM precalc_bibs_by_copy_list
818 ANALYZE precalc_copy_filter_bib_list;
822 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
823 FROM (SELECT cn.record AS bib,
825 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
826 SUM( -- time copy spent circulating
830 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
834 )::NUMERIC AS circ_time
836 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
837 JOIN asset.call_number cn ON (cn.id = cp.call_number)
838 LEFT JOIN action.all_circulation circ ON (
839 circ.target_copy = cp.id
840 AND stop_fines NOT IN (
847 checkin_time IS NULL AND
848 stop_fines = 'MAXFINES'
851 WHERE cn.owning_lib = ANY (badge.orgs)
852 AND cp.active_date IS NOT NULL
853 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
854 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
859 $f$ LANGUAGE PLPGSQL STRICT;
861 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
862 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
864 PERFORM rating.precalc_bibs_by_copy(badge_id);
866 SELECT id, 1.0 FROM precalc_filter_bib_list
868 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
870 $f$ LANGUAGE PLPGSQL STRICT;
872 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
873 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
875 PERFORM rating.precalc_bibs_by_uri(badge_id);
877 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
879 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
881 $f$ LANGUAGE PLPGSQL STRICT;
883 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
884 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
886 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
888 (SELECT id, 1.0 FROM precalc_filter_bib_list
890 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
892 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
894 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
896 $f$ LANGUAGE PLPGSQL STRICT;
898 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
899 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
902 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
904 $f$ LANGUAGE PLPGSQL STRICT;
906 CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT)
907 RETURNS TABLE (record INT, value NUMERIC) AS $f$
909 badge rating.badge_with_orgs%ROWTYPE;
912 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
914 PERFORM rating.precalc_bibs_by_copy(badge_id);
916 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
917 SELECT id FROM precalc_filter_bib_list
919 SELECT id FROM precalc_bibs_by_copy_list
921 ANALYZE precalc_copy_filter_bib_list;
924 SELECT f.id::INT AS bib,
925 COUNT(f.copy)::NUMERIC
926 FROM precalc_copy_filter_bib_list f
927 JOIN asset.copy cp ON (f.copy = cp.id)
928 JOIN asset.call_number cn ON (cn.id = cp.call_number)
929 WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
932 $f$ LANGUAGE PLPGSQL STRICT;