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 CREATE TABLE rating.popularity_parameter (
133 name TEXT NOT NULL UNIQUE, -- i18n
136 require_horizon BOOL NOT NULL DEFAULT FALSE,
137 require_importance BOOL NOT NULL DEFAULT FALSE,
138 require_percentile BOOL NOT NULL DEFAULT FALSE
141 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
142 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
143 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
144 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
145 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
146 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
147 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
148 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
149 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
150 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
151 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
152 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
153 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
154 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
155 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
156 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
158 CREATE TABLE rating.badge (
159 id SERIAL PRIMARY KEY,
162 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
163 weight INT NOT NULL DEFAULT 1,
164 horizon_age INTERVAL,
165 importance_age INTERVAL,
166 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
167 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
168 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
170 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
171 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
172 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
173 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
174 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
175 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
176 discard INT NOT NULL DEFAULT 0,
177 last_calc TIMESTAMPTZ,
178 CONSTRAINT unique_name_scope UNIQUE (name,scope)
181 CREATE TABLE rating.record_badge_score (
182 id BIGSERIAL PRIMARY KEY,
183 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
184 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
185 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
186 CONSTRAINT unique_record_badge UNIQUE (record,badge)
188 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
189 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
191 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
194 array_agg(tree) AS orgs
196 (actor.org_unit_descendants(id)).id AS tree
204 JOIN org_scope s ON (b.scope = s.id);
206 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
212 SET LOCAL client_min_messages = error;
213 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
214 IF src IS NOT NULL THEN
215 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
216 SELECT id FROM biblio.record_entry
217 WHERE source = src AND NOT deleted;
219 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
220 SELECT id FROM biblio.record_entry
221 WHERE id > 0 AND NOT deleted;
224 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
227 $f$ LANGUAGE PLPGSQL;
229 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
235 SET LOCAL client_min_messages = error;
236 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
237 IF cm IS NOT NULL THEN
238 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
239 SELECT cn.record AS id,
241 FROM asset.call_number cn
242 JOIN asset.copy cp ON (cn.id = cp.call_number)
243 WHERE cp.circ_modifier = cm
246 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
247 SELECT cn.record AS id,
249 FROM asset.call_number cn
250 JOIN asset.copy cp ON (cn.id = cp.call_number)
251 WHERE NOT cp.deleted;
254 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
257 $f$ LANGUAGE PLPGSQL;
259 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
265 SET LOCAL client_min_messages = error;
266 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
267 IF loc IS NOT NULL THEN
268 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
269 SELECT cn.record AS id,
271 FROM asset.call_number cn
272 JOIN asset.copy cp ON (cn.id = cp.call_number)
273 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
274 WHERE lg.lgroup = loc
277 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
278 SELECT cn.record AS id,
280 FROM asset.call_number cn
281 JOIN asset.copy cp ON (cn.id = cp.call_number)
282 WHERE NOT cp.deleted;
285 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
288 $f$ LANGUAGE PLPGSQL;
291 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
298 SET LOCAL client_min_messages = error;
299 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
300 IF attr_filter IS NOT NULL THEN
301 afilter := metabib.compile_composite_attr(attr_filter);
302 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
303 SELECT source AS id FROM metabib.record_attr_vector_list
304 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
306 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
307 SELECT source AS id FROM metabib.record_attr_vector_list;
310 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
313 $f$ LANGUAGE PLPGSQL;
315 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
319 badge_row rating.badge_with_orgs%ROWTYPE;
324 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
326 SET LOCAL client_min_messages = error;
327 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
328 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
329 SELECT DISTINCT cn.record AS id
330 FROM asset.call_number cn
331 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
332 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
333 WHERE cn.owning_lib = ANY (badge_row.orgs)
336 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
339 $f$ LANGUAGE PLPGSQL;
341 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
345 badge_row rating.badge_with_orgs%ROWTYPE;
348 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
350 SET LOCAL client_min_messages = error;
351 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
352 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
353 SELECT DISTINCT record AS id
354 FROM asset.call_number cn
355 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
356 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
357 WHERE cn.owning_lib = ANY (badge_row.orgs)
358 AND cn.label = '##URI##'
361 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
364 $f$ LANGUAGE PLPGSQL;
366 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
372 PERFORM rating.precalc_bibs_by_copy(badge_id);
373 PERFORM rating.precalc_bibs_by_uri(badge_id);
375 SET LOCAL client_min_messages = error;
376 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
377 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
378 SELECT id FROM precalc_bibs_by_copy_list
380 SELECT id FROM precalc_bibs_by_uri_list;
382 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
385 $f$ LANGUAGE PLPGSQL;
388 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
390 badge_row rating.badge%ROWTYPE;
391 param rating.popularity_parameter%ROWTYPE;
393 SET LOCAL client_min_messages = error;
395 -- Find what we're doing
396 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
397 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
399 -- Calculate the filtered bib set, or all bibs if none
400 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
401 PERFORM rating.precalc_src_filter(badge_row.src_filter);
402 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
403 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
405 -- Bring the bib-level filter lists together
406 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
407 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
408 SELECT id FROM precalc_attr_filter_bib_list
410 SELECT id FROM precalc_src_filter_bib_list;
412 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
413 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
414 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
415 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
417 SELECT id, copy FROM precalc_location_filter_bib_list;
419 -- Bring the collapsed filter lists together
420 DROP TABLE IF EXISTS precalc_filter_bib_list;
421 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
422 SELECT id FROM precalc_bib_filter_bib_list
424 SELECT id FROM precalc_copy_filter_bib_list;
426 CREATE INDEX precalc_filter_bib_list_idx
427 ON precalc_filter_bib_list (id);
433 -- If it's a fixed-rating badge, just do it ...
434 IF badge_row.fixed_rating IS NOT NULL THEN
435 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
437 INSERT INTO rating.record_badge_score (record, badge, score)
438 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
439 USING badge_id, badge_row.fixed_rating;
441 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
447 -- Make a session-local scratchpad for calculating scores
448 CREATE TEMP TABLE record_score_scratchpad (
455 INSERT INTO record_score_scratchpad (bib, value)
456 SELECT * FROM $e$ || param.func || $e$($1)$e$
459 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
460 -- To speed up discard-common
461 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
462 ANALYZE record_score_scratchpad;
465 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
466 DELETE FROM record_score_scratchpad WHERE value IN (
467 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
471 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
472 DELETE FROM record_score_scratchpad WHERE value <= (
475 CUME_DIST() OVER (ORDER BY value) AS p
476 FROM record_score_scratchpad
477 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
482 -- And, finally, push new data in
483 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
484 INSERT INTO rating.record_badge_score (badge, record, score)
487 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
488 FROM record_score_scratchpad;
490 DROP TABLE record_score_scratchpad;
492 -- Now, finally-finally, mark the badge as recalculated
493 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
497 $f$ LANGUAGE PLPGSQL;
499 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
500 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
502 badge rating.badge_with_orgs%ROWTYPE;
505 iscale NUMERIC := NULL;
508 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
510 IF badge.horizon_age IS NULL THEN
511 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
516 PERFORM rating.precalc_bibs_by_copy(badge_id);
518 SET LOCAL client_min_messages = error;
519 DROP TABLE IF EXISTS precalc_bib_list;
520 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
521 SELECT id FROM precalc_filter_bib_list
523 SELECT id FROM precalc_bibs_by_copy_list;
525 iint := EXTRACT(EPOCH FROM badge.importance_interval);
526 IF badge.importance_age IS NOT NULL THEN
527 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
530 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
531 iscale := COALESCE(badge.importance_scale, 1.0);
535 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
538 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
539 COUNT(h.id)::INT AS holds
540 FROM action.hold_request h
541 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
542 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
543 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
544 AND h.request_lib = ANY (badge.orgs)
549 $f$ LANGUAGE PLPGSQL STRICT;
551 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
552 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
554 badge rating.badge_with_orgs%ROWTYPE;
557 iscale NUMERIC := NULL;
560 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
562 IF badge.horizon_age IS NULL THEN
563 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
568 PERFORM rating.precalc_bibs_by_copy(badge_id);
570 SET LOCAL client_min_messages = error;
571 DROP TABLE IF EXISTS precalc_bib_list;
572 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
573 SELECT id FROM precalc_filter_bib_list
575 SELECT id FROM precalc_bibs_by_copy_list;
577 iint := EXTRACT(EPOCH FROM badge.importance_interval);
578 IF badge.importance_age IS NOT NULL THEN
579 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
582 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
583 iscale := COALESCE(badge.importance_scale, 1.0);
587 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
590 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
591 COUNT(h.id)::INT AS holds
592 FROM action.hold_request h
593 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
594 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
595 WHERE h.request_time >= NOW() - badge.horizon_age
596 AND h.request_lib = ANY (badge.orgs)
601 $f$ LANGUAGE PLPGSQL STRICT;
603 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
604 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
606 badge rating.badge_with_orgs%ROWTYPE;
609 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
611 PERFORM rating.precalc_bibs_by_copy(badge_id);
613 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
614 SELECT id FROM precalc_filter_bib_list
616 SELECT id FROM precalc_bibs_by_copy_list
619 ANALYZE precalc_copy_filter_bib_list;
622 SELECT rhrr.bib_record AS bib,
623 COUNT(DISTINCT h.id)::NUMERIC AS holds
624 FROM action.hold_request h
625 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
626 JOIN action.hold_copy_map m ON (m.hold = h.id)
627 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
628 WHERE h.fulfillment_time IS NULL
629 AND h.request_lib = ANY (badge.orgs)
632 $f$ LANGUAGE PLPGSQL STRICT;
634 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
635 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
637 badge rating.badge_with_orgs%ROWTYPE;
640 iscale NUMERIC := NULL;
643 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
645 IF badge.horizon_age IS NULL THEN
646 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
651 PERFORM rating.precalc_bibs_by_copy(badge_id);
653 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
654 SELECT id FROM precalc_filter_bib_list
656 SELECT id FROM precalc_bibs_by_copy_list
659 ANALYZE precalc_copy_filter_bib_list;
661 iint := EXTRACT(EPOCH FROM badge.importance_interval);
662 IF badge.importance_age IS NOT NULL THEN
663 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
666 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
667 iscale := COALESCE(badge.importance_scale, 1.0);
671 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
673 SELECT cn.record AS bib,
674 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
675 COUNT(c.id)::INT AS circs
676 FROM action.circulation c
677 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
678 JOIN asset.copy cp ON (cp.id = c.target_copy)
679 JOIN asset.call_number cn ON (cn.id = cp.call_number)
680 WHERE c.xact_start >= NOW() - badge.horizon_age
681 AND cn.owning_lib = ANY (badge.orgs)
682 AND c.phone_renewal IS FALSE -- we don't count renewals
683 AND c.desk_renewal IS FALSE
684 AND c.opac_renewal IS FALSE
689 $f$ LANGUAGE PLPGSQL STRICT;
691 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
692 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
694 badge rating.badge_with_orgs%ROWTYPE;
697 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
699 PERFORM rating.precalc_bibs_by_copy(badge_id);
701 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
702 SELECT id FROM precalc_filter_bib_list
704 SELECT id FROM precalc_bibs_by_copy_list
707 ANALYZE precalc_copy_filter_bib_list;
710 SELECT cn.record AS bib,
711 COUNT(c.id)::NUMERIC AS circs
712 FROM action.circulation c
713 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
714 JOIN asset.copy cp ON (cp.id = c.target_copy)
715 JOIN asset.call_number cn ON (cn.id = cp.call_number)
716 WHERE c.checkin_time IS NULL
717 AND cn.owning_lib = ANY (badge.orgs)
721 $f$ LANGUAGE PLPGSQL STRICT;
723 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
724 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
726 badge rating.badge_with_orgs%ROWTYPE;
729 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
731 PERFORM rating.precalc_bibs_by_copy(badge_id);
733 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
734 SELECT id FROM precalc_filter_bib_list
736 SELECT id FROM precalc_bibs_by_copy_list
739 ANALYZE precalc_copy_filter_bib_list;
743 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
744 FROM (SELECT cn.record AS bib,
745 (cp.status = 1)::INT AS checked_out,
748 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
749 JOIN asset.call_number cn ON (cn.id = cp.call_number)
750 WHERE cn.owning_lib = ANY (badge.orgs)
754 $f$ LANGUAGE PLPGSQL STRICT;
756 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
757 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
759 badge rating.badge_with_orgs%ROWTYPE;
762 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
764 PERFORM rating.precalc_bibs_by_copy(badge_id);
766 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
767 SELECT id FROM precalc_filter_bib_list
769 SELECT id FROM precalc_bibs_by_copy_list
772 ANALYZE precalc_copy_filter_bib_list;
775 SELECT cn.record AS bib,
776 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
778 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
779 JOIN asset.call_number cn ON (cn.id = cp.call_number)
780 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
781 WHERE cn.owning_lib = ANY (badge.orgs)
784 $f$ LANGUAGE PLPGSQL STRICT;
786 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
787 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
789 badge rating.badge_with_orgs%ROWTYPE;
792 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
794 PERFORM rating.precalc_bibs_by_copy(badge_id);
796 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
797 SELECT id FROM precalc_filter_bib_list
799 SELECT id FROM precalc_bibs_by_copy_list
802 ANALYZE precalc_copy_filter_bib_list;
805 SELECT cn.record AS bib,
806 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
808 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
809 JOIN asset.copy_location cl ON (cl.id = cp.location)
810 JOIN config.copy_status cs ON (cs.id = cp.status)
811 JOIN asset.call_number cn ON (cn.id = cp.call_number)
812 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
813 WHERE cn.owning_lib = ANY (badge.orgs)
814 AND cp.holdable IS TRUE
815 AND cl.holdable IS TRUE
816 AND cs.holdable IS TRUE
819 $f$ LANGUAGE PLPGSQL STRICT;
821 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
822 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
824 badge rating.badge_with_orgs%ROWTYPE;
827 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
829 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
831 SET LOCAL client_min_messages = error;
832 DROP TABLE IF EXISTS precalc_bib_list;
833 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
834 SELECT id FROM precalc_filter_bib_list
836 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
840 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
841 FROM precalc_bib_list pop
842 JOIN biblio.record_entry b ON (b.id = pop.id);
844 $f$ LANGUAGE PLPGSQL STRICT;
846 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
847 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
849 badge rating.badge_with_orgs%ROWTYPE;
852 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
854 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
856 SET LOCAL client_min_messages = error;
857 DROP TABLE IF EXISTS precalc_bib_list;
858 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
859 SELECT id FROM precalc_filter_bib_list
861 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
864 SELECT pop.id AS bib,
866 FROM precalc_bib_list pop
867 JOIN metabib.record_sorter s ON (
869 AND s.attr = 'pubdate'
870 AND s.value ~ '^\d+$'
872 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
874 $f$ LANGUAGE PLPGSQL STRICT;
876 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
877 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
879 badge rating.badge_with_orgs%ROWTYPE;
882 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
884 PERFORM rating.precalc_bibs_by_copy(badge_id);
886 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
887 SELECT id FROM precalc_filter_bib_list
889 SELECT id FROM precalc_bibs_by_copy_list
892 ANALYZE precalc_copy_filter_bib_list;
896 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
897 FROM (SELECT cn.record AS bib,
899 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
900 SUM( -- time copy spent circulating
904 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
908 )::NUMERIC AS circ_time
910 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
911 JOIN asset.call_number cn ON (cn.id = cp.call_number)
912 LEFT JOIN action.all_circulation circ ON (
913 circ.target_copy = cp.id
914 AND stop_fines NOT IN (
921 checkin_time IS NULL AND
922 stop_fines = 'MAXFINES'
925 WHERE cn.owning_lib = ANY (badge.orgs)
926 AND cp.active_date IS NOT NULL
927 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
928 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
933 $f$ LANGUAGE PLPGSQL STRICT;
935 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
936 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
938 PERFORM rating.precalc_bibs_by_copy(badge_id);
940 SELECT id, 1.0 FROM precalc_filter_bib_list
942 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
944 $f$ LANGUAGE PLPGSQL STRICT;
946 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
947 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
949 PERFORM rating.precalc_bibs_by_uri(badge_id);
951 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
953 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
955 $f$ LANGUAGE PLPGSQL STRICT;
957 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
958 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
960 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
962 (SELECT id, 1.0 FROM precalc_filter_bib_list
964 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
966 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
968 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
970 $f$ LANGUAGE PLPGSQL STRICT;
972 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
973 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
976 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
978 $f$ LANGUAGE PLPGSQL STRICT;
980 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
981 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);