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),
82 (18, 'In-House Use Over Time', 'rating.inhouse_over_time',TRUE,FALSE,TRUE);
85 CREATE TABLE rating.badge (
86 id SERIAL PRIMARY KEY,
89 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
90 weight INT NOT NULL DEFAULT 1,
92 importance_age INTERVAL,
93 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
94 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
95 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
97 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
98 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
99 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
100 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
101 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
102 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
103 discard INT NOT NULL DEFAULT 0,
104 last_calc TIMESTAMPTZ,
105 CONSTRAINT unique_name_scope UNIQUE (name,scope)
108 CREATE TABLE rating.record_badge_score (
109 id BIGSERIAL PRIMARY KEY,
110 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
111 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
112 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
113 CONSTRAINT unique_record_badge UNIQUE (record,badge)
115 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
116 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
118 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
121 array_agg(tree) AS orgs
123 (actor.org_unit_descendants(id)).id AS tree
131 JOIN org_scope s ON (b.scope = s.id);
133 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
139 SET LOCAL client_min_messages = error;
140 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
141 IF src IS NOT NULL THEN
142 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
143 SELECT id FROM biblio.record_entry
144 WHERE source = src AND NOT deleted;
146 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
147 SELECT id FROM biblio.record_entry
148 WHERE id > 0 AND NOT deleted;
151 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
154 $f$ LANGUAGE PLPGSQL;
156 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
162 SET LOCAL client_min_messages = error;
163 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
164 IF cm IS NOT NULL THEN
165 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
166 SELECT cn.record AS id,
168 FROM asset.call_number cn
169 JOIN asset.copy cp ON (cn.id = cp.call_number)
170 WHERE cp.circ_modifier = cm
173 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
174 SELECT cn.record AS id,
176 FROM asset.call_number cn
177 JOIN asset.copy cp ON (cn.id = cp.call_number)
178 WHERE NOT cp.deleted;
181 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
184 $f$ LANGUAGE PLPGSQL;
186 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
192 SET LOCAL client_min_messages = error;
193 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
194 IF loc IS NOT NULL THEN
195 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
196 SELECT cn.record AS id,
198 FROM asset.call_number cn
199 JOIN asset.copy cp ON (cn.id = cp.call_number)
200 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
201 WHERE lg.lgroup = loc
204 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
205 SELECT cn.record AS id,
207 FROM asset.call_number cn
208 JOIN asset.copy cp ON (cn.id = cp.call_number)
209 WHERE NOT cp.deleted;
212 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
215 $f$ LANGUAGE PLPGSQL;
218 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
225 SET LOCAL client_min_messages = error;
226 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
227 IF attr_filter IS NOT NULL THEN
228 afilter := metabib.compile_composite_attr(attr_filter);
229 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
230 SELECT source AS id FROM metabib.record_attr_vector_list
231 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
233 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
234 SELECT source AS id FROM metabib.record_attr_vector_list;
237 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
240 $f$ LANGUAGE PLPGSQL;
242 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
246 badge_row rating.badge_with_orgs%ROWTYPE;
251 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
253 SET LOCAL client_min_messages = error;
254 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
255 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
256 SELECT DISTINCT cn.record AS id
257 FROM asset.call_number cn
258 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
259 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
260 WHERE cn.owning_lib = ANY (badge_row.orgs)
263 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
266 $f$ LANGUAGE PLPGSQL;
268 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
272 badge_row rating.badge_with_orgs%ROWTYPE;
275 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
277 SET LOCAL client_min_messages = error;
278 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
279 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
280 SELECT DISTINCT record AS id
281 FROM asset.call_number cn
282 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
283 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
284 WHERE cn.owning_lib = ANY (badge_row.orgs)
285 AND cn.label = '##URI##'
288 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
291 $f$ LANGUAGE PLPGSQL;
293 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
299 PERFORM rating.precalc_bibs_by_copy(badge_id);
300 PERFORM rating.precalc_bibs_by_uri(badge_id);
302 SET LOCAL client_min_messages = error;
303 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
304 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
305 SELECT id FROM precalc_bibs_by_copy_list
307 SELECT id FROM precalc_bibs_by_uri_list;
309 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
312 $f$ LANGUAGE PLPGSQL;
315 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
317 badge_row rating.badge%ROWTYPE;
318 param rating.popularity_parameter%ROWTYPE;
320 SET LOCAL client_min_messages = error;
322 -- Find what we're doing
323 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
324 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
326 -- Calculate the filtered bib set, or all bibs if none
327 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
328 PERFORM rating.precalc_src_filter(badge_row.src_filter);
329 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
330 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
332 -- Bring the bib-level filter lists together
333 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
334 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
335 SELECT id FROM precalc_attr_filter_bib_list
337 SELECT id FROM precalc_src_filter_bib_list;
339 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
340 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
341 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
342 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
344 SELECT id, copy FROM precalc_location_filter_bib_list;
346 -- Bring the collapsed filter lists together
347 DROP TABLE IF EXISTS precalc_filter_bib_list;
348 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
349 SELECT id FROM precalc_bib_filter_bib_list
351 SELECT id FROM precalc_copy_filter_bib_list;
353 CREATE INDEX precalc_filter_bib_list_idx
354 ON precalc_filter_bib_list (id);
360 -- If it's a fixed-rating badge, just do it ...
361 IF badge_row.fixed_rating IS NOT NULL THEN
362 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
364 INSERT INTO rating.record_badge_score (record, badge, score)
365 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
366 USING badge_id, badge_row.fixed_rating;
368 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
374 -- Make a session-local scratchpad for calculating scores
375 CREATE TEMP TABLE record_score_scratchpad (
382 INSERT INTO record_score_scratchpad (bib, value)
383 SELECT * FROM $e$ || param.func || $e$($1)$e$
386 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
387 -- To speed up discard-common
388 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
389 ANALYZE record_score_scratchpad;
392 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
393 DELETE FROM record_score_scratchpad WHERE value IN (
394 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
398 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
399 DELETE FROM record_score_scratchpad WHERE value <= (
402 CUME_DIST() OVER (ORDER BY value) AS p
403 FROM record_score_scratchpad
404 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
409 -- And, finally, push new data in
410 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
411 INSERT INTO rating.record_badge_score (badge, record, score)
414 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
415 FROM record_score_scratchpad;
417 DROP TABLE record_score_scratchpad;
419 -- Now, finally-finally, mark the badge as recalculated
420 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
424 $f$ LANGUAGE PLPGSQL;
426 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
427 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
429 badge rating.badge_with_orgs%ROWTYPE;
432 iscale NUMERIC := NULL;
435 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
437 IF badge.horizon_age IS NULL THEN
438 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
443 PERFORM rating.precalc_bibs_by_copy(badge_id);
445 SET LOCAL client_min_messages = error;
446 DROP TABLE IF EXISTS precalc_bib_list;
447 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
448 SELECT id FROM precalc_filter_bib_list
450 SELECT id FROM precalc_bibs_by_copy_list;
452 iint := EXTRACT(EPOCH FROM badge.importance_interval);
453 IF badge.importance_age IS NOT NULL THEN
454 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
457 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
458 iscale := COALESCE(badge.importance_scale, 1.0);
462 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
465 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
466 COUNT(h.id)::INT AS holds
467 FROM action.hold_request h
468 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
469 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
470 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
471 AND h.request_lib = ANY (badge.orgs)
476 $f$ LANGUAGE PLPGSQL STRICT;
478 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
479 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
481 badge rating.badge_with_orgs%ROWTYPE;
484 iscale NUMERIC := NULL;
487 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
489 IF badge.horizon_age IS NULL THEN
490 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
495 PERFORM rating.precalc_bibs_by_copy(badge_id);
497 SET LOCAL client_min_messages = error;
498 DROP TABLE IF EXISTS precalc_bib_list;
499 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
500 SELECT id FROM precalc_filter_bib_list
502 SELECT id FROM precalc_bibs_by_copy_list;
504 iint := EXTRACT(EPOCH FROM badge.importance_interval);
505 IF badge.importance_age IS NOT NULL THEN
506 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
509 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
510 iscale := COALESCE(badge.importance_scale, 1.0);
514 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
517 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
518 COUNT(h.id)::INT AS holds
519 FROM action.hold_request h
520 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
521 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
522 WHERE h.request_time >= NOW() - badge.horizon_age
523 AND h.request_lib = ANY (badge.orgs)
528 $f$ LANGUAGE PLPGSQL STRICT;
530 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
531 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
533 badge rating.badge_with_orgs%ROWTYPE;
536 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
538 PERFORM rating.precalc_bibs_by_copy(badge_id);
540 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
541 SELECT id FROM precalc_filter_bib_list
543 SELECT id FROM precalc_bibs_by_copy_list
546 ANALYZE precalc_copy_filter_bib_list;
549 SELECT rhrr.bib_record AS bib,
550 COUNT(DISTINCT h.id)::NUMERIC AS holds
551 FROM action.hold_request h
552 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
553 JOIN action.hold_copy_map m ON (m.hold = h.id)
554 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
555 WHERE h.fulfillment_time IS NULL
556 AND h.request_lib = ANY (badge.orgs)
559 $f$ LANGUAGE PLPGSQL STRICT;
561 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
562 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
564 badge rating.badge_with_orgs%ROWTYPE;
567 iscale NUMERIC := NULL;
570 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
572 IF badge.horizon_age IS NULL THEN
573 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
578 PERFORM rating.precalc_bibs_by_copy(badge_id);
580 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
581 SELECT id FROM precalc_filter_bib_list
583 SELECT id FROM precalc_bibs_by_copy_list
586 ANALYZE precalc_copy_filter_bib_list;
588 iint := EXTRACT(EPOCH FROM badge.importance_interval);
589 IF badge.importance_age IS NOT NULL THEN
590 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
593 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
594 iscale := COALESCE(badge.importance_scale, 1.0);
598 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
600 SELECT cn.record AS bib,
601 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
602 COUNT(c.id)::INT AS circs
603 FROM action.circulation c
604 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
605 JOIN asset.copy cp ON (cp.id = c.target_copy)
606 JOIN asset.call_number cn ON (cn.id = cp.call_number)
607 WHERE c.xact_start >= NOW() - badge.horizon_age
608 AND cn.owning_lib = ANY (badge.orgs)
609 AND c.phone_renewal IS FALSE -- we don't count renewals
610 AND c.desk_renewal IS FALSE
611 AND c.opac_renewal IS FALSE
616 $f$ LANGUAGE PLPGSQL STRICT;
618 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
619 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
621 badge rating.badge_with_orgs%ROWTYPE;
624 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
626 PERFORM rating.precalc_bibs_by_copy(badge_id);
628 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
629 SELECT id FROM precalc_filter_bib_list
631 SELECT id FROM precalc_bibs_by_copy_list
634 ANALYZE precalc_copy_filter_bib_list;
637 SELECT cn.record AS bib,
638 COUNT(c.id)::NUMERIC AS circs
639 FROM action.circulation c
640 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
641 JOIN asset.copy cp ON (cp.id = c.target_copy)
642 JOIN asset.call_number cn ON (cn.id = cp.call_number)
643 WHERE c.checkin_time IS NULL
644 AND cn.owning_lib = ANY (badge.orgs)
648 $f$ LANGUAGE PLPGSQL STRICT;
650 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
651 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
653 badge rating.badge_with_orgs%ROWTYPE;
656 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
658 PERFORM rating.precalc_bibs_by_copy(badge_id);
660 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
661 SELECT id FROM precalc_filter_bib_list
663 SELECT id FROM precalc_bibs_by_copy_list
666 ANALYZE precalc_copy_filter_bib_list;
670 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
671 FROM (SELECT cn.record AS bib,
672 (cp.status = 1)::INT AS checked_out,
675 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
676 JOIN asset.call_number cn ON (cn.id = cp.call_number)
677 WHERE cn.owning_lib = ANY (badge.orgs)
681 $f$ LANGUAGE PLPGSQL STRICT;
683 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
684 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
686 badge rating.badge_with_orgs%ROWTYPE;
689 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
691 PERFORM rating.precalc_bibs_by_copy(badge_id);
693 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
694 SELECT id FROM precalc_filter_bib_list
696 SELECT id FROM precalc_bibs_by_copy_list
699 ANALYZE precalc_copy_filter_bib_list;
702 SELECT cn.record AS bib,
703 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
705 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
706 JOIN asset.call_number cn ON (cn.id = cp.call_number)
707 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
708 WHERE cn.owning_lib = ANY (badge.orgs)
711 $f$ LANGUAGE PLPGSQL STRICT;
713 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
714 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
716 badge rating.badge_with_orgs%ROWTYPE;
719 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
721 PERFORM rating.precalc_bibs_by_copy(badge_id);
723 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
724 SELECT id FROM precalc_filter_bib_list
726 SELECT id FROM precalc_bibs_by_copy_list
729 ANALYZE precalc_copy_filter_bib_list;
732 SELECT cn.record AS bib,
733 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
735 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
736 JOIN asset.copy_location cl ON (cl.id = cp.location)
737 JOIN config.copy_status cs ON (cs.id = cp.status)
738 JOIN asset.call_number cn ON (cn.id = cp.call_number)
739 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
740 WHERE cn.owning_lib = ANY (badge.orgs)
741 AND cp.holdable IS TRUE
742 AND cl.holdable IS TRUE
743 AND cs.holdable IS TRUE
746 $f$ LANGUAGE PLPGSQL STRICT;
748 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
749 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
751 badge rating.badge_with_orgs%ROWTYPE;
754 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
756 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
758 SET LOCAL client_min_messages = error;
759 DROP TABLE IF EXISTS precalc_bib_list;
760 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
761 SELECT id FROM precalc_filter_bib_list
763 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
767 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
768 FROM precalc_bib_list pop
769 JOIN biblio.record_entry b ON (b.id = pop.id);
771 $f$ LANGUAGE PLPGSQL STRICT;
773 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
774 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
776 badge rating.badge_with_orgs%ROWTYPE;
779 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
781 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
783 SET LOCAL client_min_messages = error;
784 DROP TABLE IF EXISTS precalc_bib_list;
785 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
786 SELECT id FROM precalc_filter_bib_list
788 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
791 SELECT pop.id AS bib,
793 FROM precalc_bib_list pop
794 JOIN metabib.record_sorter s ON (
796 AND s.attr = 'pubdate'
797 AND s.value ~ '^\d+$'
799 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
801 $f$ LANGUAGE PLPGSQL STRICT;
803 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
804 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
806 badge rating.badge_with_orgs%ROWTYPE;
809 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
811 PERFORM rating.precalc_bibs_by_copy(badge_id);
813 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
814 SELECT id FROM precalc_filter_bib_list
816 SELECT id FROM precalc_bibs_by_copy_list
819 ANALYZE precalc_copy_filter_bib_list;
823 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
824 FROM (SELECT cn.record AS bib,
826 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
827 SUM( -- time copy spent circulating
831 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
835 )::NUMERIC AS circ_time
837 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
838 JOIN asset.call_number cn ON (cn.id = cp.call_number)
839 LEFT JOIN action.all_circulation_slim circ ON (
840 circ.target_copy = cp.id
841 AND stop_fines NOT IN (
848 checkin_time IS NULL AND
849 stop_fines = 'MAXFINES'
852 WHERE cn.owning_lib = ANY (badge.orgs)
853 AND cp.active_date IS NOT NULL
854 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
855 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
860 $f$ LANGUAGE PLPGSQL STRICT;
862 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
863 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
865 PERFORM rating.precalc_bibs_by_copy(badge_id);
867 SELECT id, 1.0 FROM precalc_filter_bib_list
869 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
871 $f$ LANGUAGE PLPGSQL STRICT;
873 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
874 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
876 PERFORM rating.precalc_bibs_by_uri(badge_id);
878 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
880 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
882 $f$ LANGUAGE PLPGSQL STRICT;
884 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
885 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
887 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
889 (SELECT id, 1.0 FROM precalc_filter_bib_list
891 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
893 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
895 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
897 $f$ LANGUAGE PLPGSQL STRICT;
899 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
900 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
903 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
905 $f$ LANGUAGE PLPGSQL STRICT;
907 CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT)
908 RETURNS TABLE (record INT, value NUMERIC) AS $f$
910 badge rating.badge_with_orgs%ROWTYPE;
913 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
915 PERFORM rating.precalc_bibs_by_copy(badge_id);
917 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
918 SELECT id FROM precalc_filter_bib_list
920 SELECT id FROM precalc_bibs_by_copy_list
922 ANALYZE precalc_copy_filter_bib_list;
925 SELECT f.id::INT AS bib,
926 COUNT(f.copy)::NUMERIC
927 FROM precalc_copy_filter_bib_list f
928 JOIN asset.copy cp ON (f.copy = cp.id)
929 JOIN asset.call_number cn ON (cn.id = cp.call_number)
930 WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
933 $f$ LANGUAGE PLPGSQL STRICT;
937 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
938 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
940 badge rating.badge_with_orgs%ROWTYPE;
943 iscale NUMERIC := NULL;
946 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
948 IF badge.horizon_age IS NULL THEN
949 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
954 PERFORM rating.precalc_bibs_by_copy(badge_id);
956 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
957 SELECT id FROM precalc_filter_bib_list
959 SELECT id FROM precalc_bibs_by_copy_list
962 ANALYZE precalc_copy_filter_bib_list;
964 iint := EXTRACT(EPOCH FROM badge.importance_interval);
965 IF badge.importance_age IS NOT NULL THEN
966 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
969 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
970 iscale := COALESCE(badge.importance_scale, 1.0);
974 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
976 SELECT cn.record AS bib,
977 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
978 COUNT(u.id)::INT AS uses
979 FROM action.in_house_use u
980 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
981 JOIN asset.copy cp ON (cp.id = u.item)
982 JOIN asset.call_number cn ON (cn.id = cp.call_number)
983 WHERE u.use_time >= NOW() - badge.horizon_age
984 AND cn.owning_lib = ANY (badge.orgs)