4 -- Create these so that the queries in the UDFs will validate
5 CREATE TEMP TABLE precalc_filter_bib_list (
9 CREATE TEMP TABLE precalc_bib_filter_bib_list (
13 CREATE TEMP TABLE precalc_src_filter_bib_list (
17 CREATE TEMP TABLE precalc_copy_filter_bib_list (
22 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
27 CREATE TEMP TABLE precalc_location_filter_bib_list (
32 CREATE TEMP TABLE precalc_attr_filter_bib_list (
36 CREATE TEMP TABLE precalc_bibs_by_copy_list (
40 CREATE TEMP TABLE precalc_bibs_by_uri_list (
44 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
48 CREATE TEMP TABLE precalc_bib_list (
52 -- rhrr needs to be a real table, so it can be fast. To that end, we use
53 -- a materialized view updated via a trigger.
55 DROP VIEW reporter.hold_request_record;
57 CREATE TABLE reporter.hold_request_record AS
65 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
67 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
68 WHEN hold_type IN ('C','R','F')
69 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
71 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
73 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
75 FROM action.hold_request ahr;
77 CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
78 CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);
80 ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;
82 CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
84 IF TG_OP = 'INSERT' THEN
85 INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
90 WHEN NEW.hold_type = 'T'
92 WHEN NEW.hold_type = 'I'
93 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
94 WHEN NEW.hold_type = 'V'
95 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
96 WHEN NEW.hold_type IN ('C','R','F')
97 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
98 WHEN NEW.hold_type = 'M'
99 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
100 WHEN NEW.hold_type = 'P'
101 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
103 ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
104 UPDATE reporter.hold_request_record
105 SET target = NEW.target,
106 hold_type = NEW.hold_type,
108 WHEN NEW.hold_type = 'T'
110 WHEN NEW.hold_type = 'I'
111 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
112 WHEN NEW.hold_type = 'V'
113 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
114 WHEN NEW.hold_type IN ('C','R','F')
115 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
116 WHEN NEW.hold_type = 'M'
117 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
118 WHEN NEW.hold_type = 'P'
119 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
126 CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
127 FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();
129 CREATE SCHEMA rating;
131 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
133 'OPAC Default Sort (titlesort, authorsort, pubdate, popularity, poprel, or empty for relevance)',
138 CREATE TABLE rating.popularity_parameter (
140 name TEXT NOT NULL UNIQUE, -- i18n
143 require_horizon BOOL NOT NULL DEFAULT FALSE,
144 require_importance BOOL NOT NULL DEFAULT FALSE,
145 require_percentile BOOL NOT NULL DEFAULT FALSE
148 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
149 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
150 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
151 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
152 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
153 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
154 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
155 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
156 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
157 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
158 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
159 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
160 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
161 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
162 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
163 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
165 CREATE TABLE rating.badge (
166 id SERIAL PRIMARY KEY,
169 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
170 weight INT NOT NULL DEFAULT 1,
171 horizon_age INTERVAL,
172 importance_age INTERVAL,
173 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
174 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
175 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
177 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
178 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
179 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
180 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
181 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
182 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
183 discard INT NOT NULL DEFAULT 0,
184 last_calc TIMESTAMPTZ,
185 CONSTRAINT unique_name_scope UNIQUE (name,scope)
188 CREATE TABLE rating.record_badge_score (
189 id BIGSERIAL PRIMARY KEY,
190 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
191 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
192 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
193 CONSTRAINT unique_record_badge UNIQUE (record,badge)
195 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
196 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
198 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
201 array_agg(tree) AS orgs
203 (actor.org_unit_descendants(id)).id AS tree
211 JOIN org_scope s ON (b.scope = s.id);
213 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
219 SET LOCAL client_min_messages = error;
220 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
221 IF src IS NOT NULL THEN
222 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
223 SELECT id FROM biblio.record_entry
224 WHERE source = src AND NOT deleted;
226 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
227 SELECT id FROM biblio.record_entry
228 WHERE id > 0 AND NOT deleted;
231 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
234 $f$ LANGUAGE PLPGSQL;
236 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
242 SET LOCAL client_min_messages = error;
243 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
244 IF cm IS NOT NULL THEN
245 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
246 SELECT cn.record AS id,
248 FROM asset.call_number cn
249 JOIN asset.copy cp ON (cn.id = cp.call_number)
250 WHERE cp.circ_modifier = cm
253 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
254 SELECT cn.record AS id,
256 FROM asset.call_number cn
257 JOIN asset.copy cp ON (cn.id = cp.call_number)
258 WHERE NOT cp.deleted;
261 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
264 $f$ LANGUAGE PLPGSQL;
266 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
272 SET LOCAL client_min_messages = error;
273 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
274 IF loc IS NOT NULL THEN
275 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
276 SELECT cn.record AS id,
278 FROM asset.call_number cn
279 JOIN asset.copy cp ON (cn.id = cp.call_number)
280 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
281 WHERE lg.lgroup = loc
284 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
285 SELECT cn.record AS id,
287 FROM asset.call_number cn
288 JOIN asset.copy cp ON (cn.id = cp.call_number)
289 WHERE NOT cp.deleted;
292 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
295 $f$ LANGUAGE PLPGSQL;
298 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
305 SET LOCAL client_min_messages = error;
306 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
307 IF attr_filter IS NOT NULL THEN
308 afilter := metabib.compile_composite_attr(attr_filter);
309 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
310 SELECT source AS id FROM metabib.record_attr_vector_list
311 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
313 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
314 SELECT source AS id FROM metabib.record_attr_vector_list;
317 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
320 $f$ LANGUAGE PLPGSQL;
322 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
326 badge_row rating.badge_with_orgs%ROWTYPE;
331 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
333 SET LOCAL client_min_messages = error;
334 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
335 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
336 SELECT DISTINCT cn.record AS id
337 FROM asset.call_number cn
338 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
339 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
340 WHERE cn.owning_lib = ANY (badge_row.orgs)
343 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
346 $f$ LANGUAGE PLPGSQL;
348 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
352 badge_row rating.badge_with_orgs%ROWTYPE;
355 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
357 SET LOCAL client_min_messages = error;
358 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
359 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
360 SELECT DISTINCT record AS id
361 FROM asset.call_number cn
362 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
363 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
364 WHERE cn.owning_lib = ANY (badge_row.orgs)
365 AND cn.label = '##URI##'
368 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
371 $f$ LANGUAGE PLPGSQL;
373 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
379 PERFORM rating.precalc_bibs_by_copy(badge_id);
380 PERFORM rating.precalc_bibs_by_uri(badge_id);
382 SET LOCAL client_min_messages = error;
383 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
384 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
385 SELECT id FROM precalc_bibs_by_copy_list
387 SELECT id FROM precalc_bibs_by_uri_list;
389 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
392 $f$ LANGUAGE PLPGSQL;
395 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
397 badge_row rating.badge%ROWTYPE;
398 param rating.popularity_parameter%ROWTYPE;
400 SET LOCAL client_min_messages = error;
402 -- Find what we're doing
403 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
404 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
406 -- Calculate the filtered bib set, or all bibs if none
407 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
408 PERFORM rating.precalc_src_filter(badge_row.src_filter);
409 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
410 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
412 -- Bring the bib-level filter lists together
413 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
414 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
415 SELECT id FROM precalc_attr_filter_bib_list
417 SELECT id FROM precalc_src_filter_bib_list;
419 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
420 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
421 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
422 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
424 SELECT id, copy FROM precalc_location_filter_bib_list;
426 -- Bring the collapsed filter lists together
427 DROP TABLE IF EXISTS precalc_filter_bib_list;
428 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
429 SELECT id FROM precalc_bib_filter_bib_list
431 SELECT id FROM precalc_copy_filter_bib_list;
433 CREATE INDEX precalc_filter_bib_list_idx
434 ON precalc_filter_bib_list (id);
440 -- If it's a fixed-rating badge, just do it ...
441 IF badge_row.fixed_rating IS NOT NULL THEN
442 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
444 INSERT INTO rating.record_badge_score (record, badge, score)
445 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
446 USING badge_id, badge_row.fixed_rating;
448 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
454 -- Make a session-local scratchpad for calculating scores
455 CREATE TEMP TABLE record_score_scratchpad (
462 INSERT INTO record_score_scratchpad (bib, value)
463 SELECT * FROM $e$ || param.func || $e$($1)$e$
466 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
467 -- To speed up discard-common
468 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
469 ANALYZE record_score_scratchpad;
472 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
473 DELETE FROM record_score_scratchpad WHERE value IN (
474 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
478 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
479 DELETE FROM record_score_scratchpad WHERE value <= (
482 CUME_DIST() OVER (ORDER BY value) AS p
483 FROM record_score_scratchpad
484 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
489 -- And, finally, push new data in
490 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
491 INSERT INTO rating.record_badge_score (badge, record, score)
494 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
495 FROM record_score_scratchpad;
497 DROP TABLE record_score_scratchpad;
499 -- Now, finally-finally, mark the badge as recalculated
500 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
504 $f$ LANGUAGE PLPGSQL;
506 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
507 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
509 badge rating.badge_with_orgs%ROWTYPE;
512 iscale NUMERIC := NULL;
515 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
517 IF badge.horizon_age IS NULL THEN
518 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
523 PERFORM rating.precalc_bibs_by_copy(badge_id);
525 SET LOCAL client_min_messages = error;
526 DROP TABLE IF EXISTS precalc_bib_list;
527 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
528 SELECT id FROM precalc_filter_bib_list
530 SELECT id FROM precalc_bibs_by_copy_list;
532 iint := EXTRACT(EPOCH FROM badge.importance_interval);
533 IF badge.importance_age IS NOT NULL THEN
534 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
537 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
538 iscale := COALESCE(badge.importance_scale, 1.0);
542 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
545 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
546 COUNT(h.id)::INT AS holds
547 FROM action.hold_request h
548 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
549 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
550 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
551 AND h.request_lib = ANY (badge.orgs)
556 $f$ LANGUAGE PLPGSQL STRICT;
558 CREATE OR REPLACE FUNCTION rating.holds_placed_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 SET LOCAL client_min_messages = error;
578 DROP TABLE IF EXISTS precalc_bib_list;
579 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
580 SELECT id FROM precalc_filter_bib_list
582 SELECT id FROM precalc_bibs_by_copy_list;
584 iint := EXTRACT(EPOCH FROM badge.importance_interval);
585 IF badge.importance_age IS NOT NULL THEN
586 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
589 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
590 iscale := COALESCE(badge.importance_scale, 1.0);
594 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
597 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
598 COUNT(h.id)::INT AS holds
599 FROM action.hold_request h
600 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
601 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
602 WHERE h.request_time >= NOW() - badge.horizon_age
603 AND h.request_lib = ANY (badge.orgs)
608 $f$ LANGUAGE PLPGSQL STRICT;
610 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
611 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
613 badge rating.badge_with_orgs%ROWTYPE;
616 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
618 PERFORM rating.precalc_bibs_by_copy(badge_id);
620 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
621 SELECT id FROM precalc_filter_bib_list
623 SELECT id FROM precalc_bibs_by_copy_list
626 ANALYZE precalc_copy_filter_bib_list;
629 SELECT rhrr.bib_record AS bib,
630 COUNT(DISTINCT h.id)::NUMERIC AS holds
631 FROM action.hold_request h
632 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
633 JOIN action.hold_copy_map m ON (m.hold = h.id)
634 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
635 WHERE h.fulfillment_time IS NULL
636 AND h.request_lib = ANY (badge.orgs)
639 $f$ LANGUAGE PLPGSQL STRICT;
641 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
642 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
644 badge rating.badge_with_orgs%ROWTYPE;
647 iscale NUMERIC := NULL;
650 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
652 IF badge.horizon_age IS NULL THEN
653 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
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;
668 iint := EXTRACT(EPOCH FROM badge.importance_interval);
669 IF badge.importance_age IS NOT NULL THEN
670 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
673 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
674 iscale := COALESCE(badge.importance_scale, 1.0);
678 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
680 SELECT cn.record AS bib,
681 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
682 COUNT(c.id)::INT AS circs
683 FROM action.circulation c
684 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
685 JOIN asset.copy cp ON (cp.id = c.target_copy)
686 JOIN asset.call_number cn ON (cn.id = cp.call_number)
687 WHERE c.xact_start >= NOW() - badge.horizon_age
688 AND cn.owning_lib = ANY (badge.orgs)
689 AND c.phone_renewal IS FALSE -- we don't count renewals
690 AND c.desk_renewal IS FALSE
691 AND c.opac_renewal IS FALSE
696 $f$ LANGUAGE PLPGSQL STRICT;
698 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
699 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
701 badge rating.badge_with_orgs%ROWTYPE;
704 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
706 PERFORM rating.precalc_bibs_by_copy(badge_id);
708 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
709 SELECT id FROM precalc_filter_bib_list
711 SELECT id FROM precalc_bibs_by_copy_list
714 ANALYZE precalc_copy_filter_bib_list;
717 SELECT cn.record AS bib,
718 COUNT(c.id)::NUMERIC AS circs
719 FROM action.circulation c
720 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
721 JOIN asset.copy cp ON (cp.id = c.target_copy)
722 JOIN asset.call_number cn ON (cn.id = cp.call_number)
723 WHERE c.checkin_time IS NULL
724 AND cn.owning_lib = ANY (badge.orgs)
728 $f$ LANGUAGE PLPGSQL STRICT;
730 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
731 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
733 badge rating.badge_with_orgs%ROWTYPE;
736 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
738 PERFORM rating.precalc_bibs_by_copy(badge_id);
740 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
741 SELECT id FROM precalc_filter_bib_list
743 SELECT id FROM precalc_bibs_by_copy_list
746 ANALYZE precalc_copy_filter_bib_list;
750 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
751 FROM (SELECT cn.record AS bib,
752 (cp.status = 1)::INT AS checked_out,
755 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
756 JOIN asset.call_number cn ON (cn.id = cp.call_number)
757 WHERE cn.owning_lib = ANY (badge.orgs)
761 $f$ LANGUAGE PLPGSQL STRICT;
763 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
764 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
766 badge rating.badge_with_orgs%ROWTYPE;
769 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
771 PERFORM rating.precalc_bibs_by_copy(badge_id);
773 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
774 SELECT id FROM precalc_filter_bib_list
776 SELECT id FROM precalc_bibs_by_copy_list
779 ANALYZE precalc_copy_filter_bib_list;
782 SELECT cn.record AS bib,
783 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
785 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
786 JOIN asset.call_number cn ON (cn.id = cp.call_number)
787 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
788 WHERE cn.owning_lib = ANY (badge.orgs)
791 $f$ LANGUAGE PLPGSQL STRICT;
793 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
794 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
796 badge rating.badge_with_orgs%ROWTYPE;
799 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
801 PERFORM rating.precalc_bibs_by_copy(badge_id);
803 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
804 SELECT id FROM precalc_filter_bib_list
806 SELECT id FROM precalc_bibs_by_copy_list
809 ANALYZE precalc_copy_filter_bib_list;
812 SELECT cn.record AS bib,
813 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
815 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
816 JOIN asset.copy_location cl ON (cl.id = cp.location)
817 JOIN config.copy_status cs ON (cs.id = cp.status)
818 JOIN asset.call_number cn ON (cn.id = cp.call_number)
819 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
820 WHERE cn.owning_lib = ANY (badge.orgs)
821 AND cp.holdable IS TRUE
822 AND cl.holdable IS TRUE
823 AND cs.holdable IS TRUE
826 $f$ LANGUAGE PLPGSQL STRICT;
828 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
829 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
831 badge rating.badge_with_orgs%ROWTYPE;
834 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
836 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
838 SET LOCAL client_min_messages = error;
839 DROP TABLE IF EXISTS precalc_bib_list;
840 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
841 SELECT id FROM precalc_filter_bib_list
843 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
847 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
848 FROM precalc_bib_list pop
849 JOIN biblio.record_entry b ON (b.id = pop.id);
851 $f$ LANGUAGE PLPGSQL STRICT;
853 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
854 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
856 badge rating.badge_with_orgs%ROWTYPE;
859 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
861 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
863 SET LOCAL client_min_messages = error;
864 DROP TABLE IF EXISTS precalc_bib_list;
865 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
866 SELECT id FROM precalc_filter_bib_list
868 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
871 SELECT pop.id AS bib,
873 FROM precalc_bib_list pop
874 JOIN metabib.record_sorter s ON (
876 AND s.attr = 'pubdate'
877 AND s.value ~ '^\d+$'
879 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
881 $f$ LANGUAGE PLPGSQL STRICT;
883 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
884 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
886 badge rating.badge_with_orgs%ROWTYPE;
889 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
891 PERFORM rating.precalc_bibs_by_copy(badge_id);
893 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
894 SELECT id FROM precalc_filter_bib_list
896 SELECT id FROM precalc_bibs_by_copy_list
899 ANALYZE precalc_copy_filter_bib_list;
903 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
904 FROM (SELECT cn.record AS bib,
906 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
907 SUM( -- time copy spent circulating
911 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
915 )::NUMERIC AS circ_time
917 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
918 JOIN asset.call_number cn ON (cn.id = cp.call_number)
919 LEFT JOIN action.all_circulation circ ON (
920 circ.target_copy = cp.id
921 AND stop_fines NOT IN (
928 checkin_time IS NULL AND
929 stop_fines = 'MAXFINES'
932 WHERE cn.owning_lib = ANY (badge.orgs)
933 AND cp.active_date IS NOT NULL
934 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
935 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
940 $f$ LANGUAGE PLPGSQL STRICT;
942 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
943 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
945 PERFORM rating.precalc_bibs_by_copy(badge_id);
947 SELECT id, 1.0 FROM precalc_filter_bib_list
949 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
951 $f$ LANGUAGE PLPGSQL STRICT;
953 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
954 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
956 PERFORM rating.precalc_bibs_by_uri(badge_id);
958 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
960 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
962 $f$ LANGUAGE PLPGSQL STRICT;
964 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
965 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
967 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
969 (SELECT id, 1.0 FROM precalc_filter_bib_list
971 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
973 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
975 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
977 $f$ LANGUAGE PLPGSQL STRICT;
979 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
980 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
983 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
985 $f$ LANGUAGE PLPGSQL STRICT;
987 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
988 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);