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 (17,'Circulation Library Count', 'rating.org_unit_count',FALSE,FALSE, TRUE),
83 (18, 'In-House Use Over Time', 'rating.inhouse_over_time',TRUE,FALSE,TRUE);
86 CREATE TABLE rating.badge (
87 id SERIAL PRIMARY KEY,
90 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
91 weight INT NOT NULL DEFAULT 1,
93 importance_age INTERVAL,
94 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
95 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
96 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
98 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
99 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
100 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
101 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
102 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
103 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
104 discard INT NOT NULL DEFAULT 0,
105 last_calc TIMESTAMPTZ,
106 CONSTRAINT unique_name_scope UNIQUE (name,scope)
109 CREATE TABLE rating.record_badge_score (
110 id BIGSERIAL PRIMARY KEY,
111 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
112 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
113 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
114 CONSTRAINT unique_record_badge UNIQUE (record,badge)
116 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
117 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
119 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
122 array_agg(tree) AS orgs
124 (actor.org_unit_descendants(id)).id AS tree
132 JOIN org_scope s ON (b.scope = s.id);
134 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
140 SET LOCAL client_min_messages = error;
141 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
142 IF src IS NOT NULL THEN
143 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
144 SELECT id FROM biblio.record_entry
145 WHERE source = src AND NOT deleted;
147 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
148 SELECT id FROM biblio.record_entry
149 WHERE id > 0 AND NOT deleted;
152 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
155 $f$ LANGUAGE PLPGSQL;
157 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
163 SET LOCAL client_min_messages = error;
164 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
165 IF cm IS NOT NULL THEN
166 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
167 SELECT cn.record AS id,
169 FROM asset.call_number cn
170 JOIN asset.copy cp ON (cn.id = cp.call_number)
171 WHERE cp.circ_modifier = cm
174 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
175 SELECT cn.record AS id,
177 FROM asset.call_number cn
178 JOIN asset.copy cp ON (cn.id = cp.call_number)
179 WHERE NOT cp.deleted;
182 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
185 $f$ LANGUAGE PLPGSQL;
187 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
193 SET LOCAL client_min_messages = error;
194 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
195 IF loc IS NOT NULL THEN
196 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
197 SELECT cn.record AS id,
199 FROM asset.call_number cn
200 JOIN asset.copy cp ON (cn.id = cp.call_number)
201 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
202 WHERE lg.lgroup = loc
205 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
206 SELECT cn.record AS id,
208 FROM asset.call_number cn
209 JOIN asset.copy cp ON (cn.id = cp.call_number)
210 WHERE NOT cp.deleted;
213 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
216 $f$ LANGUAGE PLPGSQL;
219 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
226 SET LOCAL client_min_messages = error;
227 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
228 IF attr_filter IS NOT NULL THEN
229 afilter := 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
232 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
234 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
235 SELECT source AS id FROM metabib.record_attr_vector_list;
238 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
241 $f$ LANGUAGE PLPGSQL;
243 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
247 badge_row rating.badge_with_orgs%ROWTYPE;
252 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
254 SET LOCAL client_min_messages = error;
255 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
256 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
257 SELECT DISTINCT cn.record AS id
258 FROM asset.call_number cn
259 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
260 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
261 WHERE cn.owning_lib = ANY (badge_row.orgs)
264 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
267 $f$ LANGUAGE PLPGSQL;
269 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
273 badge_row rating.badge_with_orgs%ROWTYPE;
276 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
278 SET LOCAL client_min_messages = error;
279 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
280 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
281 SELECT DISTINCT record AS id
282 FROM asset.call_number cn
283 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
284 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
285 WHERE cn.owning_lib = ANY (badge_row.orgs)
286 AND cn.label = '##URI##'
289 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
292 $f$ LANGUAGE PLPGSQL;
294 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
300 PERFORM rating.precalc_bibs_by_copy(badge_id);
301 PERFORM rating.precalc_bibs_by_uri(badge_id);
303 SET LOCAL client_min_messages = error;
304 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
305 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
306 SELECT id FROM precalc_bibs_by_copy_list
308 SELECT id FROM precalc_bibs_by_uri_list;
310 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
313 $f$ LANGUAGE PLPGSQL;
316 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
318 badge_row rating.badge%ROWTYPE;
319 param rating.popularity_parameter%ROWTYPE;
321 SET LOCAL client_min_messages = error;
323 -- Find what we're doing
324 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
325 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
327 -- Calculate the filtered bib set, or all bibs if none
328 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
329 PERFORM rating.precalc_src_filter(badge_row.src_filter);
330 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
331 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
333 -- Bring the bib-level filter lists together
334 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
335 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
336 SELECT id FROM precalc_attr_filter_bib_list
338 SELECT id FROM precalc_src_filter_bib_list;
340 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
341 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
342 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
343 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
345 SELECT id, copy FROM precalc_location_filter_bib_list;
347 -- Bring the collapsed filter lists together
348 DROP TABLE IF EXISTS precalc_filter_bib_list;
349 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
350 SELECT id FROM precalc_bib_filter_bib_list
352 SELECT id FROM precalc_copy_filter_bib_list;
354 CREATE INDEX precalc_filter_bib_list_idx
355 ON precalc_filter_bib_list (id);
361 -- If it's a fixed-rating badge, just do it ...
362 IF badge_row.fixed_rating IS NOT NULL THEN
363 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
365 INSERT INTO rating.record_badge_score (record, badge, score)
366 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
367 USING badge_id, badge_row.fixed_rating;
369 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
375 -- Make a session-local scratchpad for calculating scores
376 CREATE TEMP TABLE record_score_scratchpad (
383 INSERT INTO record_score_scratchpad (bib, value)
384 SELECT * FROM $e$ || param.func || $e$($1)$e$
387 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
388 -- To speed up discard-common
389 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
390 ANALYZE record_score_scratchpad;
393 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
394 DELETE FROM record_score_scratchpad WHERE value IN (
395 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
399 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
400 DELETE FROM record_score_scratchpad WHERE value <= (
403 CUME_DIST() OVER (ORDER BY value) AS p
404 FROM record_score_scratchpad
405 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
410 -- And, finally, push new data in
411 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
412 INSERT INTO rating.record_badge_score (badge, record, score)
415 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
416 FROM record_score_scratchpad;
418 DROP TABLE record_score_scratchpad;
420 -- Now, finally-finally, mark the badge as recalculated
421 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
425 $f$ LANGUAGE PLPGSQL;
427 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
428 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
430 badge rating.badge_with_orgs%ROWTYPE;
433 iscale NUMERIC := NULL;
436 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
438 IF badge.horizon_age IS NULL THEN
439 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
444 PERFORM rating.precalc_bibs_by_copy(badge_id);
446 SET LOCAL client_min_messages = error;
447 DROP TABLE IF EXISTS precalc_bib_list;
448 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
449 SELECT id FROM precalc_filter_bib_list
451 SELECT id FROM precalc_bibs_by_copy_list;
453 iint := EXTRACT(EPOCH FROM badge.importance_interval);
454 IF badge.importance_age IS NOT NULL THEN
455 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
458 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
459 iscale := COALESCE(badge.importance_scale, 1.0);
463 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
466 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
467 COUNT(h.id)::INT AS holds
468 FROM action.hold_request h
469 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
470 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
471 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
472 AND h.request_lib = ANY (badge.orgs)
477 $f$ LANGUAGE PLPGSQL STRICT;
479 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
480 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
482 badge rating.badge_with_orgs%ROWTYPE;
485 iscale NUMERIC := NULL;
488 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
490 IF badge.horizon_age IS NULL THEN
491 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
496 PERFORM rating.precalc_bibs_by_copy(badge_id);
498 SET LOCAL client_min_messages = error;
499 DROP TABLE IF EXISTS precalc_bib_list;
500 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
501 SELECT id FROM precalc_filter_bib_list
503 SELECT id FROM precalc_bibs_by_copy_list;
505 iint := EXTRACT(EPOCH FROM badge.importance_interval);
506 IF badge.importance_age IS NOT NULL THEN
507 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
510 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
511 iscale := COALESCE(badge.importance_scale, 1.0);
515 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
518 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
519 COUNT(h.id)::INT AS holds
520 FROM action.hold_request h
521 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
522 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
523 WHERE h.request_time >= NOW() - badge.horizon_age
524 AND h.request_lib = ANY (badge.orgs)
529 $f$ LANGUAGE PLPGSQL STRICT;
531 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
532 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
534 badge rating.badge_with_orgs%ROWTYPE;
537 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
539 PERFORM rating.precalc_bibs_by_copy(badge_id);
541 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
542 SELECT id FROM precalc_filter_bib_list
544 SELECT id FROM precalc_bibs_by_copy_list
547 ANALYZE precalc_copy_filter_bib_list;
550 SELECT rhrr.bib_record AS bib,
551 COUNT(DISTINCT h.id)::NUMERIC AS holds
552 FROM action.hold_request h
553 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
554 JOIN action.hold_copy_map m ON (m.hold = h.id)
555 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
556 WHERE h.fulfillment_time IS NULL
557 AND h.request_lib = ANY (badge.orgs)
560 $f$ LANGUAGE PLPGSQL STRICT;
562 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
563 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
565 badge rating.badge_with_orgs%ROWTYPE;
568 iscale NUMERIC := NULL;
571 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
573 IF badge.horizon_age IS NULL THEN
574 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
579 PERFORM rating.precalc_bibs_by_copy(badge_id);
581 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
582 SELECT id FROM precalc_filter_bib_list
584 SELECT id FROM precalc_bibs_by_copy_list
587 ANALYZE precalc_copy_filter_bib_list;
589 iint := EXTRACT(EPOCH FROM badge.importance_interval);
590 IF badge.importance_age IS NOT NULL THEN
591 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
594 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
595 iscale := COALESCE(badge.importance_scale, 1.0);
599 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
601 SELECT cn.record AS bib,
602 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
603 COUNT(c.id)::INT AS circs
604 FROM action.circulation c
605 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
606 JOIN asset.copy cp ON (cp.id = c.target_copy)
607 JOIN asset.call_number cn ON (cn.id = cp.call_number)
608 WHERE c.xact_start >= NOW() - badge.horizon_age
609 AND cn.owning_lib = ANY (badge.orgs)
610 AND c.phone_renewal IS FALSE -- we don't count renewals
611 AND c.desk_renewal IS FALSE
612 AND c.opac_renewal IS FALSE
617 $f$ LANGUAGE PLPGSQL STRICT;
619 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
620 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
622 badge rating.badge_with_orgs%ROWTYPE;
625 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
627 PERFORM rating.precalc_bibs_by_copy(badge_id);
629 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
630 SELECT id FROM precalc_filter_bib_list
632 SELECT id FROM precalc_bibs_by_copy_list
635 ANALYZE precalc_copy_filter_bib_list;
638 SELECT cn.record AS bib,
639 COUNT(c.id)::NUMERIC AS circs
640 FROM action.circulation c
641 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
642 JOIN asset.copy cp ON (cp.id = c.target_copy)
643 JOIN asset.call_number cn ON (cn.id = cp.call_number)
644 WHERE c.checkin_time IS NULL
645 AND cn.owning_lib = ANY (badge.orgs)
649 $f$ LANGUAGE PLPGSQL STRICT;
651 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
652 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
654 badge rating.badge_with_orgs%ROWTYPE;
657 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
659 PERFORM rating.precalc_bibs_by_copy(badge_id);
661 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
662 SELECT id FROM precalc_filter_bib_list
664 SELECT id FROM precalc_bibs_by_copy_list
667 ANALYZE precalc_copy_filter_bib_list;
671 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
672 FROM (SELECT cn.record AS bib,
673 (cp.status = 1)::INT AS checked_out,
676 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
677 JOIN asset.call_number cn ON (cn.id = cp.call_number)
678 WHERE cn.owning_lib = ANY (badge.orgs)
682 $f$ LANGUAGE PLPGSQL STRICT;
684 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
685 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
687 badge rating.badge_with_orgs%ROWTYPE;
690 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
692 PERFORM rating.precalc_bibs_by_copy(badge_id);
694 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
695 SELECT id FROM precalc_filter_bib_list
697 SELECT id FROM precalc_bibs_by_copy_list
700 ANALYZE precalc_copy_filter_bib_list;
703 SELECT cn.record AS bib,
704 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
706 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
707 JOIN asset.call_number cn ON (cn.id = cp.call_number)
708 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
709 WHERE cn.owning_lib = ANY (badge.orgs)
712 $f$ LANGUAGE PLPGSQL STRICT;
714 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
715 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
717 badge rating.badge_with_orgs%ROWTYPE;
720 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
722 PERFORM rating.precalc_bibs_by_copy(badge_id);
724 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
725 SELECT id FROM precalc_filter_bib_list
727 SELECT id FROM precalc_bibs_by_copy_list
730 ANALYZE precalc_copy_filter_bib_list;
733 SELECT cn.record AS bib,
734 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
736 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
737 JOIN asset.copy_location cl ON (cl.id = cp.location)
738 JOIN config.copy_status cs ON (cs.id = cp.status)
739 JOIN asset.call_number cn ON (cn.id = cp.call_number)
740 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
741 WHERE cn.owning_lib = ANY (badge.orgs)
742 AND cp.holdable IS TRUE
743 AND cl.holdable IS TRUE
744 AND cs.holdable IS TRUE
747 $f$ LANGUAGE PLPGSQL STRICT;
749 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
750 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
752 badge rating.badge_with_orgs%ROWTYPE;
755 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
757 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
759 SET LOCAL client_min_messages = error;
760 DROP TABLE IF EXISTS precalc_bib_list;
761 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
762 SELECT id FROM precalc_filter_bib_list
764 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
768 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
769 FROM precalc_bib_list pop
770 JOIN biblio.record_entry b ON (b.id = pop.id);
772 $f$ LANGUAGE PLPGSQL STRICT;
774 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
775 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
777 badge rating.badge_with_orgs%ROWTYPE;
780 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
782 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
784 SET LOCAL client_min_messages = error;
785 DROP TABLE IF EXISTS precalc_bib_list;
786 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
787 SELECT id FROM precalc_filter_bib_list
789 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
792 SELECT pop.id AS bib,
794 FROM precalc_bib_list pop
795 JOIN metabib.record_sorter s ON (
797 AND s.attr = 'pubdate'
798 AND s.value ~ '^\d+$'
800 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
802 $f$ LANGUAGE PLPGSQL STRICT;
804 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
805 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
807 badge rating.badge_with_orgs%ROWTYPE;
810 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
812 PERFORM rating.precalc_bibs_by_copy(badge_id);
814 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
815 SELECT id FROM precalc_filter_bib_list
817 SELECT id FROM precalc_bibs_by_copy_list
820 ANALYZE precalc_copy_filter_bib_list;
824 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
825 FROM (SELECT cn.record AS bib,
827 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
828 SUM( -- time copy spent circulating
832 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
836 )::NUMERIC AS circ_time
838 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
839 JOIN asset.call_number cn ON (cn.id = cp.call_number)
840 LEFT JOIN action.all_circulation_slim circ ON (
841 circ.target_copy = cp.id
842 AND stop_fines NOT IN (
849 checkin_time IS NULL AND
850 stop_fines = 'MAXFINES'
853 WHERE cn.owning_lib = ANY (badge.orgs)
854 AND cp.active_date IS NOT NULL
855 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
856 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
861 $f$ LANGUAGE PLPGSQL STRICT;
863 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
864 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
866 PERFORM rating.precalc_bibs_by_copy(badge_id);
868 SELECT id, 1.0 FROM precalc_filter_bib_list
870 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
872 $f$ LANGUAGE PLPGSQL STRICT;
874 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
875 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
877 PERFORM rating.precalc_bibs_by_uri(badge_id);
879 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
881 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
883 $f$ LANGUAGE PLPGSQL STRICT;
885 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
886 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
888 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
890 (SELECT id, 1.0 FROM precalc_filter_bib_list
892 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
894 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
896 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
898 $f$ LANGUAGE PLPGSQL STRICT;
900 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
901 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
904 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
906 $f$ LANGUAGE PLPGSQL STRICT;
908 CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT)
909 RETURNS TABLE (record INT, value NUMERIC) AS $f$
911 badge rating.badge_with_orgs%ROWTYPE;
914 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
916 PERFORM rating.precalc_bibs_by_copy(badge_id);
918 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
919 SELECT id FROM precalc_filter_bib_list
921 SELECT id FROM precalc_bibs_by_copy_list
923 ANALYZE precalc_copy_filter_bib_list;
926 SELECT f.id::INT AS bib,
927 COUNT(f.copy)::NUMERIC
928 FROM precalc_copy_filter_bib_list f
929 JOIN asset.copy cp ON (f.copy = cp.id)
930 JOIN asset.call_number cn ON (cn.id = cp.call_number)
931 WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
934 $f$ LANGUAGE PLPGSQL STRICT;
936 CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
937 RETURNS TABLE (record INT, value NUMERIC) AS $f$
939 badge rating.badge_with_orgs%ROWTYPE;
942 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
944 PERFORM rating.precalc_bibs_by_copy(badge_id);
946 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
947 SELECT id FROM precalc_filter_bib_list
949 SELECT id FROM precalc_bibs_by_copy_list
951 ANALYZE precalc_copy_filter_bib_list;
953 -- Use circ rather than owning lib here as that means "on the shelf at..."
955 SELECT f.id::INT AS bib,
956 COUNT(DISTINCT cp.circ_lib)::NUMERIC
958 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
959 WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;
962 $f$ LANGUAGE PLPGSQL STRICT;
964 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
965 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
967 badge rating.badge_with_orgs%ROWTYPE;
970 iscale NUMERIC := NULL;
973 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
975 IF badge.horizon_age IS NULL THEN
976 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
981 PERFORM rating.precalc_bibs_by_copy(badge_id);
983 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
984 SELECT id FROM precalc_filter_bib_list
986 SELECT id FROM precalc_bibs_by_copy_list
989 ANALYZE precalc_copy_filter_bib_list;
991 iint := EXTRACT(EPOCH FROM badge.importance_interval);
992 IF badge.importance_age IS NOT NULL THEN
993 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
996 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
997 iscale := COALESCE(badge.importance_scale, 1.0);
1001 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
1003 SELECT cn.record AS bib,
1004 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
1005 COUNT(u.id)::INT AS uses
1006 FROM action.in_house_use u
1007 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
1008 JOIN asset.copy cp ON (cp.id = u.item)
1009 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1010 WHERE u.use_time >= NOW() - badge.horizon_age
1011 AND cn.owning_lib = ANY (badge.orgs)
1016 $f$ LANGUAGE PLPGSQL STRICT;