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 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
139 'search.max_popularity_importance_multiplier',
141 'search.max_popularity_importance_multiplier',
142 'Maximum popularity importance multiplier for popularity-adjusted relevance searches (decimal value between 1.0 and 2.0)',
150 CREATE TABLE rating.popularity_parameter (
152 name TEXT NOT NULL UNIQUE, -- i18n
155 require_horizon BOOL NOT NULL DEFAULT FALSE,
156 require_importance BOOL NOT NULL DEFAULT FALSE,
157 require_percentile BOOL NOT NULL DEFAULT FALSE
160 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
161 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
162 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
163 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
164 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
165 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
166 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
167 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
168 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
169 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
170 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
171 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
172 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
173 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
174 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
175 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
177 CREATE TABLE rating.badge (
178 id SERIAL PRIMARY KEY,
181 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
182 weight INT NOT NULL DEFAULT 1,
183 horizon_age INTERVAL,
184 importance_age INTERVAL,
185 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
186 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
187 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
189 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
190 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
191 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
192 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
193 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
194 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
195 discard INT NOT NULL DEFAULT 0,
196 last_calc TIMESTAMPTZ,
197 CONSTRAINT unique_name_scope UNIQUE (name,scope)
200 CREATE TABLE rating.record_badge_score (
201 id BIGSERIAL PRIMARY KEY,
202 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
203 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
204 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
205 CONSTRAINT unique_record_badge UNIQUE (record,badge)
207 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
208 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
210 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
213 array_agg(tree) AS orgs
215 (actor.org_unit_descendants(id)).id AS tree
223 JOIN org_scope s ON (b.scope = s.id);
225 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
231 SET LOCAL client_min_messages = error;
232 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
233 IF src IS NOT NULL THEN
234 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
235 SELECT id FROM biblio.record_entry
236 WHERE source = src AND NOT deleted;
238 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
239 SELECT id FROM biblio.record_entry
240 WHERE id > 0 AND NOT deleted;
243 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
246 $f$ LANGUAGE PLPGSQL;
248 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
254 SET LOCAL client_min_messages = error;
255 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
256 IF cm IS NOT NULL THEN
257 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
258 SELECT cn.record AS id,
260 FROM asset.call_number cn
261 JOIN asset.copy cp ON (cn.id = cp.call_number)
262 WHERE cp.circ_modifier = cm
265 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
266 SELECT cn.record AS id,
268 FROM asset.call_number cn
269 JOIN asset.copy cp ON (cn.id = cp.call_number)
270 WHERE NOT cp.deleted;
273 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
276 $f$ LANGUAGE PLPGSQL;
278 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
284 SET LOCAL client_min_messages = error;
285 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
286 IF loc IS NOT NULL THEN
287 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
288 SELECT cn.record AS id,
290 FROM asset.call_number cn
291 JOIN asset.copy cp ON (cn.id = cp.call_number)
292 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
293 WHERE lg.lgroup = loc
296 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
297 SELECT cn.record AS id,
299 FROM asset.call_number cn
300 JOIN asset.copy cp ON (cn.id = cp.call_number)
301 WHERE NOT cp.deleted;
304 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
307 $f$ LANGUAGE PLPGSQL;
310 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
317 SET LOCAL client_min_messages = error;
318 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
319 IF attr_filter IS NOT NULL THEN
320 afilter := metabib.compile_composite_attr(attr_filter);
321 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
322 SELECT source AS id FROM metabib.record_attr_vector_list
323 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
325 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
326 SELECT source AS id FROM metabib.record_attr_vector_list;
329 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
332 $f$ LANGUAGE PLPGSQL;
334 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
338 badge_row rating.badge_with_orgs%ROWTYPE;
343 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
345 SET LOCAL client_min_messages = error;
346 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
347 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
348 SELECT DISTINCT cn.record AS id
349 FROM asset.call_number cn
350 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
351 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
352 WHERE cn.owning_lib = ANY (badge_row.orgs)
355 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
358 $f$ LANGUAGE PLPGSQL;
360 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
364 badge_row rating.badge_with_orgs%ROWTYPE;
367 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
369 SET LOCAL client_min_messages = error;
370 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
371 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
372 SELECT DISTINCT record AS id
373 FROM asset.call_number cn
374 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
375 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
376 WHERE cn.owning_lib = ANY (badge_row.orgs)
377 AND cn.label = '##URI##'
380 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
383 $f$ LANGUAGE PLPGSQL;
385 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
391 PERFORM rating.precalc_bibs_by_copy(badge_id);
392 PERFORM rating.precalc_bibs_by_uri(badge_id);
394 SET LOCAL client_min_messages = error;
395 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
396 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
397 SELECT id FROM precalc_bibs_by_copy_list
399 SELECT id FROM precalc_bibs_by_uri_list;
401 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
404 $f$ LANGUAGE PLPGSQL;
407 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
409 badge_row rating.badge%ROWTYPE;
410 param rating.popularity_parameter%ROWTYPE;
412 SET LOCAL client_min_messages = error;
414 -- Find what we're doing
415 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
416 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
418 -- Calculate the filtered bib set, or all bibs if none
419 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
420 PERFORM rating.precalc_src_filter(badge_row.src_filter);
421 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
422 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
424 -- Bring the bib-level filter lists together
425 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
426 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
427 SELECT id FROM precalc_attr_filter_bib_list
429 SELECT id FROM precalc_src_filter_bib_list;
431 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
432 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
433 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
434 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
436 SELECT id, copy FROM precalc_location_filter_bib_list;
438 -- Bring the collapsed filter lists together
439 DROP TABLE IF EXISTS precalc_filter_bib_list;
440 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
441 SELECT id FROM precalc_bib_filter_bib_list
443 SELECT id FROM precalc_copy_filter_bib_list;
445 CREATE INDEX precalc_filter_bib_list_idx
446 ON precalc_filter_bib_list (id);
452 -- If it's a fixed-rating badge, just do it ...
453 IF badge_row.fixed_rating IS NOT NULL THEN
454 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
456 INSERT INTO rating.record_badge_score (record, badge, score)
457 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
458 USING badge_id, badge_row.fixed_rating;
460 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
466 -- Make a session-local scratchpad for calculating scores
467 CREATE TEMP TABLE record_score_scratchpad (
474 INSERT INTO record_score_scratchpad (bib, value)
475 SELECT * FROM $e$ || param.func || $e$($1)$e$
478 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
479 -- To speed up discard-common
480 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
481 ANALYZE record_score_scratchpad;
484 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
485 DELETE FROM record_score_scratchpad WHERE value IN (
486 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
490 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
491 DELETE FROM record_score_scratchpad WHERE value <= (
494 CUME_DIST() OVER (ORDER BY value) AS p
495 FROM record_score_scratchpad
496 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
501 -- And, finally, push new data in
502 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
503 INSERT INTO rating.record_badge_score (badge, record, score)
506 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
507 FROM record_score_scratchpad;
509 DROP TABLE record_score_scratchpad;
511 -- Now, finally-finally, mark the badge as recalculated
512 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
516 $f$ LANGUAGE PLPGSQL;
518 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
519 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
521 badge rating.badge_with_orgs%ROWTYPE;
524 iscale NUMERIC := NULL;
527 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
529 IF badge.horizon_age IS NULL THEN
530 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
535 PERFORM rating.precalc_bibs_by_copy(badge_id);
537 SET LOCAL client_min_messages = error;
538 DROP TABLE IF EXISTS precalc_bib_list;
539 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
540 SELECT id FROM precalc_filter_bib_list
542 SELECT id FROM precalc_bibs_by_copy_list;
544 iint := EXTRACT(EPOCH FROM badge.importance_interval);
545 IF badge.importance_age IS NOT NULL THEN
546 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
549 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
550 iscale := COALESCE(badge.importance_scale, 1.0);
554 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
557 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
558 COUNT(h.id)::INT AS holds
559 FROM action.hold_request h
560 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
561 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
562 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
563 AND h.request_lib = ANY (badge.orgs)
568 $f$ LANGUAGE PLPGSQL STRICT;
570 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
571 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
573 badge rating.badge_with_orgs%ROWTYPE;
576 iscale NUMERIC := NULL;
579 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
581 IF badge.horizon_age IS NULL THEN
582 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
587 PERFORM rating.precalc_bibs_by_copy(badge_id);
589 SET LOCAL client_min_messages = error;
590 DROP TABLE IF EXISTS precalc_bib_list;
591 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
592 SELECT id FROM precalc_filter_bib_list
594 SELECT id FROM precalc_bibs_by_copy_list;
596 iint := EXTRACT(EPOCH FROM badge.importance_interval);
597 IF badge.importance_age IS NOT NULL THEN
598 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
601 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
602 iscale := COALESCE(badge.importance_scale, 1.0);
606 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
609 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
610 COUNT(h.id)::INT AS holds
611 FROM action.hold_request h
612 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
613 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
614 WHERE h.request_time >= NOW() - badge.horizon_age
615 AND h.request_lib = ANY (badge.orgs)
620 $f$ LANGUAGE PLPGSQL STRICT;
622 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
623 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
625 badge rating.badge_with_orgs%ROWTYPE;
628 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
630 PERFORM rating.precalc_bibs_by_copy(badge_id);
632 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
633 SELECT id FROM precalc_filter_bib_list
635 SELECT id FROM precalc_bibs_by_copy_list
638 ANALYZE precalc_copy_filter_bib_list;
641 SELECT rhrr.bib_record AS bib,
642 COUNT(DISTINCT h.id)::NUMERIC AS holds
643 FROM action.hold_request h
644 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
645 JOIN action.hold_copy_map m ON (m.hold = h.id)
646 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
647 WHERE h.fulfillment_time IS NULL
648 AND h.request_lib = ANY (badge.orgs)
651 $f$ LANGUAGE PLPGSQL STRICT;
653 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
654 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
656 badge rating.badge_with_orgs%ROWTYPE;
659 iscale NUMERIC := NULL;
662 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
664 IF badge.horizon_age IS NULL THEN
665 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
670 PERFORM rating.precalc_bibs_by_copy(badge_id);
672 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
673 SELECT id FROM precalc_filter_bib_list
675 SELECT id FROM precalc_bibs_by_copy_list
678 ANALYZE precalc_copy_filter_bib_list;
680 iint := EXTRACT(EPOCH FROM badge.importance_interval);
681 IF badge.importance_age IS NOT NULL THEN
682 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
685 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
686 iscale := COALESCE(badge.importance_scale, 1.0);
690 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
692 SELECT cn.record AS bib,
693 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
694 COUNT(c.id)::INT AS circs
695 FROM action.circulation c
696 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
697 JOIN asset.copy cp ON (cp.id = c.target_copy)
698 JOIN asset.call_number cn ON (cn.id = cp.call_number)
699 WHERE c.xact_start >= NOW() - badge.horizon_age
700 AND cn.owning_lib = ANY (badge.orgs)
701 AND c.phone_renewal IS FALSE -- we don't count renewals
702 AND c.desk_renewal IS FALSE
703 AND c.opac_renewal IS FALSE
708 $f$ LANGUAGE PLPGSQL STRICT;
710 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
711 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
713 badge rating.badge_with_orgs%ROWTYPE;
716 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
718 PERFORM rating.precalc_bibs_by_copy(badge_id);
720 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
721 SELECT id FROM precalc_filter_bib_list
723 SELECT id FROM precalc_bibs_by_copy_list
726 ANALYZE precalc_copy_filter_bib_list;
729 SELECT cn.record AS bib,
730 COUNT(c.id)::NUMERIC AS circs
731 FROM action.circulation c
732 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
733 JOIN asset.copy cp ON (cp.id = c.target_copy)
734 JOIN asset.call_number cn ON (cn.id = cp.call_number)
735 WHERE c.checkin_time IS NULL
736 AND cn.owning_lib = ANY (badge.orgs)
740 $f$ LANGUAGE PLPGSQL STRICT;
742 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
743 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
745 badge rating.badge_with_orgs%ROWTYPE;
748 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
750 PERFORM rating.precalc_bibs_by_copy(badge_id);
752 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
753 SELECT id FROM precalc_filter_bib_list
755 SELECT id FROM precalc_bibs_by_copy_list
758 ANALYZE precalc_copy_filter_bib_list;
762 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
763 FROM (SELECT cn.record AS bib,
764 (cp.status = 1)::INT AS checked_out,
767 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
768 JOIN asset.call_number cn ON (cn.id = cp.call_number)
769 WHERE cn.owning_lib = ANY (badge.orgs)
773 $f$ LANGUAGE PLPGSQL STRICT;
775 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
776 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
778 badge rating.badge_with_orgs%ROWTYPE;
781 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
783 PERFORM rating.precalc_bibs_by_copy(badge_id);
785 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
786 SELECT id FROM precalc_filter_bib_list
788 SELECT id FROM precalc_bibs_by_copy_list
791 ANALYZE precalc_copy_filter_bib_list;
794 SELECT cn.record AS bib,
795 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
797 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
798 JOIN asset.call_number cn ON (cn.id = cp.call_number)
799 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
800 WHERE cn.owning_lib = ANY (badge.orgs)
803 $f$ LANGUAGE PLPGSQL STRICT;
805 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
806 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
808 badge rating.badge_with_orgs%ROWTYPE;
811 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
813 PERFORM rating.precalc_bibs_by_copy(badge_id);
815 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
816 SELECT id FROM precalc_filter_bib_list
818 SELECT id FROM precalc_bibs_by_copy_list
821 ANALYZE precalc_copy_filter_bib_list;
824 SELECT cn.record AS bib,
825 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
827 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
828 JOIN asset.copy_location cl ON (cl.id = cp.location)
829 JOIN config.copy_status cs ON (cs.id = cp.status)
830 JOIN asset.call_number cn ON (cn.id = cp.call_number)
831 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
832 WHERE cn.owning_lib = ANY (badge.orgs)
833 AND cp.holdable IS TRUE
834 AND cl.holdable IS TRUE
835 AND cs.holdable IS TRUE
838 $f$ LANGUAGE PLPGSQL STRICT;
840 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
841 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
843 badge rating.badge_with_orgs%ROWTYPE;
846 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
848 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
850 SET LOCAL client_min_messages = error;
851 DROP TABLE IF EXISTS precalc_bib_list;
852 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
853 SELECT id FROM precalc_filter_bib_list
855 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
859 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
860 FROM precalc_bib_list pop
861 JOIN biblio.record_entry b ON (b.id = pop.id);
863 $f$ LANGUAGE PLPGSQL STRICT;
865 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
866 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
868 badge rating.badge_with_orgs%ROWTYPE;
871 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
873 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
875 SET LOCAL client_min_messages = error;
876 DROP TABLE IF EXISTS precalc_bib_list;
877 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
878 SELECT id FROM precalc_filter_bib_list
880 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
883 SELECT pop.id AS bib,
885 FROM precalc_bib_list pop
886 JOIN metabib.record_sorter s ON (
888 AND s.attr = 'pubdate'
889 AND s.value ~ '^\d+$'
891 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
893 $f$ LANGUAGE PLPGSQL STRICT;
895 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
896 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
898 badge rating.badge_with_orgs%ROWTYPE;
901 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
903 PERFORM rating.precalc_bibs_by_copy(badge_id);
905 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
906 SELECT id FROM precalc_filter_bib_list
908 SELECT id FROM precalc_bibs_by_copy_list
911 ANALYZE precalc_copy_filter_bib_list;
915 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
916 FROM (SELECT cn.record AS bib,
918 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
919 SUM( -- time copy spent circulating
923 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
927 )::NUMERIC AS circ_time
929 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
930 JOIN asset.call_number cn ON (cn.id = cp.call_number)
931 LEFT JOIN action.all_circulation circ ON (
932 circ.target_copy = cp.id
933 AND stop_fines NOT IN (
940 checkin_time IS NULL AND
941 stop_fines = 'MAXFINES'
944 WHERE cn.owning_lib = ANY (badge.orgs)
945 AND cp.active_date IS NOT NULL
946 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
947 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
952 $f$ LANGUAGE PLPGSQL STRICT;
954 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
955 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
957 PERFORM rating.precalc_bibs_by_copy(badge_id);
959 SELECT id, 1.0 FROM precalc_filter_bib_list
961 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
963 $f$ LANGUAGE PLPGSQL STRICT;
965 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
966 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
968 PERFORM rating.precalc_bibs_by_uri(badge_id);
970 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
972 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
974 $f$ LANGUAGE PLPGSQL STRICT;
976 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
977 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
979 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
981 (SELECT id, 1.0 FROM precalc_filter_bib_list
983 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
985 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
987 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
989 $f$ LANGUAGE PLPGSQL STRICT;
991 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
992 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
995 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
997 $f$ LANGUAGE PLPGSQL STRICT;
999 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
1000 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);