Open-ILS/src/sql/Pg/220.schema.rating.sql

   1 DROP SCHEMA IF EXISTS rating CASCADE;
   2
   3 BEGIN;
   4
   5 -- Create these so that the queries in the UDFs will validate
   6 CREATE TEMP TABLE precalc_filter_bib_list (
   7     id  BIGINT
   8 ) ON COMMIT DROP;
   9
  10 CREATE TEMP TABLE precalc_bib_filter_bib_list (
  11     id  BIGINT
  12 ) ON COMMIT DROP;
  13
  14 CREATE TEMP TABLE precalc_src_filter_bib_list (
  15     id  BIGINT
  16 ) ON COMMIT DROP;
  17
  18 CREATE TEMP TABLE precalc_copy_filter_bib_list (
  19     id  BIGINT,
  20     copy  BIGINT
  21 ) ON COMMIT DROP;
  22
  23 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
  24     id  BIGINT,
  25     copy  BIGINT
  26 ) ON COMMIT DROP;
  27
  28 CREATE TEMP TABLE precalc_location_filter_bib_list (
  29     id  BIGINT,
  30     copy  BIGINT
  31 ) ON COMMIT DROP;
  32
  33 CREATE TEMP TABLE precalc_attr_filter_bib_list (
  34     id  BIGINT
  35 ) ON COMMIT DROP;
  36
  37 CREATE TEMP TABLE precalc_bibs_by_copy_list (
  38     id  BIGINT
  39 ) ON COMMIT DROP;
  40
  41 CREATE TEMP TABLE precalc_bibs_by_uri_list (
  42     id  BIGINT
  43 ) ON COMMIT DROP;
  44
  45 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
  46     id  BIGINT
  47 ) ON COMMIT DROP;
  48
  49 CREATE TEMP TABLE precalc_bib_list (
  50     id  BIGINT
  51 ) ON COMMIT DROP;
  52
  53 CREATE SCHEMA rating;
  54
  55 CREATE TABLE rating.popularity_parameter (
  56     id          INT     PRIMARY KEY,
  57     name        TEXT    NOT NULL UNIQUE, -- i18n
  58     description TEXT,
  59     func        TEXT,
  60     require_horizon     BOOL    NOT NULL DEFAULT FALSE,
  61     require_importance  BOOL    NOT NULL DEFAULT FALSE,
  62     require_percentile  BOOL    NOT NULL DEFAULT FALSE
  63 );
  64
  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
  83
  84 CREATE TABLE rating.badge (
  85     id                      SERIAL      PRIMARY KEY,
  86     name                    TEXT        NOT NULL,
  87     description             TEXT,
  88     scope                   INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  89     weight                  INT         NOT NULL DEFAULT 1,
  90     horizon_age             INTERVAL,
  91     importance_age          INTERVAL,
  92     importance_interval     INTERVAL    NOT NULL DEFAULT '1 day',
  93     importance_scale        NUMERIC     CHECK (importance_scale IS NULL OR importance_scale > 0.0),
  94     recalc_interval         INTERVAL    NOT NULL DEFAULT '1 month',
  95     attr_filter             TEXT,
  96     src_filter              INT         REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  97     circ_mod_filter         TEXT        REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  98     loc_grp_filter          INT         REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  99     popularity_parameter    INT         NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 100     fixed_rating            INT         CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
 101     percentile              NUMERIC     CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
 102     discard                 INT         NOT NULL DEFAULT 0,
 103     last_calc               TIMESTAMPTZ,
 104     CONSTRAINT unique_name_scope UNIQUE (name,scope)
 105 );
 106
 107 CREATE TABLE rating.record_badge_score (
 108     id          BIGSERIAL   PRIMARY KEY,
 109     record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 110     badge       INT         NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 111     score       INT         NOT NULL CHECK (score BETWEEN -5 AND 5),
 112     CONSTRAINT unique_record_badge UNIQUE (record,badge)
 113 );
 114 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
 115 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
 116
 117 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
 118     WITH    org_scope AS (
 119                 SELECT  id,
 120                         array_agg(tree) AS orgs
 121                   FROM  (SELECT id,
 122                                 (actor.org_unit_descendants(id)).id AS tree
 123                           FROM  actor.org_unit
 124                         ) x
 125                   GROUP BY 1
 126             )
 127     SELECT  b.*,
 128             s.orgs
 129       FROM  rating.badge b
 130             JOIN org_scope s ON (b.scope = s.id);
 131
 132 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
 133     RETURNS INT AS $f$
 134 DECLARE
 135     cnt     INT     := 0;
 136 BEGIN
 137
 138     SET LOCAL client_min_messages = error;
 139     DROP TABLE IF EXISTS precalc_src_filter_bib_list;
 140     IF src IS NOT NULL THEN
 141         CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
 142             SELECT id FROM biblio.record_entry
 143             WHERE source = src AND NOT deleted;
 144     ELSE
 145         CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
 146             SELECT id FROM biblio.record_entry
 147             WHERE id > 0 AND NOT deleted;
 148     END IF;
 149
 150     SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
 151     RETURN cnt;
 152 END;
 153 $f$ LANGUAGE PLPGSQL;
 154
 155 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
 156     RETURNS INT AS $f$
 157 DECLARE
 158     cnt     INT     := 0;
 159 BEGIN
 160
 161     SET LOCAL client_min_messages = error;
 162     DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
 163     IF cm IS NOT NULL THEN
 164         CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
 165             SELECT  cn.record AS id,
 166                     cp.id AS copy
 167               FROM  asset.call_number cn
 168                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 169               WHERE cp.circ_modifier = cm
 170                     AND NOT cp.deleted;
 171     ELSE
 172         CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
 173             SELECT  cn.record AS id,
 174                     cp.id AS copy
 175               FROM  asset.call_number cn
 176                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 177               WHERE NOT cp.deleted;
 178     END IF;
 179
 180     SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
 181     RETURN cnt;
 182 END;
 183 $f$ LANGUAGE PLPGSQL;
 184
 185 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
 186     RETURNS INT AS $f$
 187 DECLARE
 188     cnt     INT     := 0;
 189 BEGIN
 190
 191     SET LOCAL client_min_messages = error;
 192     DROP TABLE IF EXISTS precalc_location_filter_bib_list;
 193     IF loc IS NOT NULL THEN
 194         CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
 195             SELECT  cn.record AS id,
 196                     cp.id AS copy
 197               FROM  asset.call_number cn
 198                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 199                     JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
 200               WHERE lg.lgroup = loc
 201                     AND NOT cp.deleted;
 202     ELSE
 203         CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
 204             SELECT  cn.record AS id,
 205                     cp.id AS copy
 206               FROM  asset.call_number cn
 207                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 208               WHERE NOT cp.deleted;
 209     END IF;
 210
 211     SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
 212     RETURN cnt;
 213 END;
 214 $f$ LANGUAGE PLPGSQL;
 215
 216 -- all or limited...
 217 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
 218     RETURNS INT AS $f$
 219 DECLARE
 220     cnt     INT := 0;
 221     afilter TEXT;
 222 BEGIN
 223
 224     SET LOCAL client_min_messages = error;
 225     DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
 226     IF attr_filter IS NOT NULL THEN
 227         afilter := metabib.compile_composite_attr(attr_filter);
 228         CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
 229             SELECT source AS id FROM metabib.record_attr_vector_list
 230             WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
 231     ELSE
 232         CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
 233             SELECT source AS id FROM metabib.record_attr_vector_list;
 234     END IF;
 235
 236     SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
 237     RETURN cnt;
 238 END;
 239 $f$ LANGUAGE PLPGSQL;
 240
 241 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
 242     RETURNS INT AS $f$
 243 DECLARE
 244     cnt         INT     := 0;
 245     badge_row   rating.badge_with_orgs%ROWTYPE;
 246     base        TEXT;
 247     whr         TEXT;
 248 BEGIN
 249
 250     SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
 251
 252     SET LOCAL client_min_messages = error;
 253     DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
 254     CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
 255         SELECT  DISTINCT cn.record AS id
 256           FROM  asset.call_number cn
 257                 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
 258                 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
 259           WHERE cn.owning_lib = ANY (badge_row.orgs)
 260                 AND NOT cn.deleted;
 261
 262     SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
 263     RETURN cnt;
 264 END;
 265 $f$ LANGUAGE PLPGSQL;
 266
 267 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
 268     RETURNS INT AS $f$
 269 DECLARE
 270     cnt         INT     := 0;
 271     badge_row   rating.badge_with_orgs%ROWTYPE;
 272 BEGIN
 273
 274     SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
 275
 276     SET LOCAL client_min_messages = error;
 277     DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
 278     CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
 279         SELECT  DISTINCT record AS id
 280           FROM  asset.call_number cn
 281                 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
 282                 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
 283           WHERE cn.owning_lib = ANY (badge_row.orgs)
 284                 AND cn.label = '##URI##'
 285                 AND NOT cn.deleted;
 286
 287     SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
 288     RETURN cnt;
 289 END;
 290 $f$ LANGUAGE PLPGSQL;
 291
 292 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
 293     RETURNS INT AS $f$
 294 DECLARE
 295     cnt         INT     := 0;
 296 BEGIN
 297
 298     PERFORM rating.precalc_bibs_by_copy(badge_id);
 299     PERFORM rating.precalc_bibs_by_uri(badge_id);
 300
 301     SET LOCAL client_min_messages = error;
 302     DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
 303     CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
 304         SELECT id FROM precalc_bibs_by_copy_list
 305             UNION
 306         SELECT id FROM precalc_bibs_by_uri_list;
 307
 308     SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
 309     RETURN cnt;
 310 END;
 311 $f$ LANGUAGE PLPGSQL;
 312
 313
 314 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
 315 DECLARE
 316     badge_row           rating.badge%ROWTYPE;
 317     param           rating.popularity_parameter%ROWTYPE;
 318 BEGIN
 319     SET LOCAL client_min_messages = error;
 320
 321     -- Find what we're doing
 322     SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
 323     SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
 324
 325     -- Calculate the filtered bib set, or all bibs if none
 326     PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
 327     PERFORM rating.precalc_src_filter(badge_row.src_filter);
 328     PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
 329     PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
 330
 331     -- Bring the bib-level filter lists together
 332     DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
 333     CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
 334         SELECT id FROM precalc_attr_filter_bib_list
 335             INTERSECT
 336         SELECT id FROM precalc_src_filter_bib_list;
 337
 338     -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
 339     DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
 340     CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
 341         SELECT id, copy FROM precalc_circ_mod_filter_bib_list
 342             INTERSECT
 343         SELECT id, copy FROM precalc_location_filter_bib_list;
 344
 345     -- Bring the collapsed filter lists together
 346     DROP TABLE IF EXISTS precalc_filter_bib_list;
 347     CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
 348         SELECT id FROM precalc_bib_filter_bib_list
 349             INTERSECT
 350         SELECT id FROM precalc_copy_filter_bib_list;
 351
 352     CREATE INDEX precalc_filter_bib_list_idx
 353         ON precalc_filter_bib_list (id);
 354
 355     IF setup_only THEN
 356         RETURN;
 357     END IF;
 358
 359     -- If it's a fixed-rating badge, just do it ...
 360     IF badge_row.fixed_rating IS NOT NULL THEN
 361         DELETE FROM rating.record_badge_score WHERE badge = badge_id;
 362         EXECUTE $e$
 363             INSERT INTO rating.record_badge_score (record, badge, score)
 364                 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
 365         USING badge_id, badge_row.fixed_rating;
 366
 367         UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
 368
 369         RETURN;
 370     END IF;
 371     -- else, calculate!
 372
 373     -- Make a session-local scratchpad for calculating scores
 374     CREATE TEMP TABLE record_score_scratchpad (
 375         bib     BIGINT,
 376         value   NUMERIC
 377     ) ON COMMIT DROP;
 378
 379     -- Gather raw values
 380     EXECUTE $e$
 381         INSERT INTO record_score_scratchpad (bib, value)
 382             SELECT * FROM $e$ || param.func || $e$($1)$e$
 383     USING badge_id;
 384
 385     IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
 386         -- To speed up discard-common
 387         CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
 388         ANALYZE record_score_scratchpad;
 389     END IF;
 390
 391     IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
 392         DELETE FROM record_score_scratchpad WHERE value IN (
 393             SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
 394         );
 395     END IF;
 396
 397     IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
 398         DELETE FROM record_score_scratchpad WHERE value <= (
 399             SELECT value FROM (
 400                 SELECT  value,
 401                         CUME_DIST() OVER (ORDER BY value) AS p
 402                   FROM  record_score_scratchpad
 403             ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
 404         );
 405     END IF;
 406
 407
 408     -- And, finally, push new data in
 409     DELETE FROM rating.record_badge_score WHERE badge = badge_id;
 410     INSERT INTO rating.record_badge_score (badge, record, score)
 411         SELECT  badge_id,
 412                 bib,
 413                 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
 414           FROM  record_score_scratchpad;
 415
 416     DROP TABLE record_score_scratchpad;
 417
 418     -- Now, finally-finally, mark the badge as recalculated
 419     UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
 420
 421     RETURN;
 422 END;
 423 $f$ LANGUAGE PLPGSQL;
 424
 425 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
 426     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 427 DECLARE
 428     badge   rating.badge_with_orgs%ROWTYPE;
 429     iage    INT     := 1;
 430     iint    INT     := NULL;
 431     iscale  NUMERIC := NULL;
 432 BEGIN
 433
 434     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 435
 436     IF badge.horizon_age IS NULL THEN
 437         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 438             badge.name,
 439             badge.id;
 440     END IF;
 441
 442     PERFORM rating.precalc_bibs_by_copy(badge_id);
 443
 444     SET LOCAL client_min_messages = error;
 445     DROP TABLE IF EXISTS precalc_bib_list;
 446     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 447         SELECT id FROM precalc_filter_bib_list
 448             INTERSECT
 449         SELECT id FROM precalc_bibs_by_copy_list;
 450
 451     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 452     IF badge.importance_age IS NOT NULL THEN
 453         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 454     END IF;
 455
 456     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 457     iscale := COALESCE(badge.importance_scale, 1.0);
 458
 459     RETURN QUERY
 460      SELECT bib,
 461             SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
 462       FROM (
 463          SELECT f.id AS bib,
 464                 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
 465                 COUNT(h.id)::INT AS holds
 466           FROM  action.hold_request h
 467                 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 468                 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
 469           WHERE h.fulfillment_time >= NOW() - badge.horizon_age
 470                 AND h.request_lib = ANY (badge.orgs)
 471           GROUP BY 1, 2
 472       ) x
 473       GROUP BY 1;
 474 END;
 475 $f$ LANGUAGE PLPGSQL STRICT;
 476
 477 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
 478     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 479 DECLARE
 480     badge   rating.badge_with_orgs%ROWTYPE;
 481     iage    INT     := 1;
 482     iint    INT     := NULL;
 483     iscale  NUMERIC := NULL;
 484 BEGIN
 485
 486     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 487
 488     IF badge.horizon_age IS NULL THEN
 489         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 490             badge.name,
 491             badge.id;
 492     END IF;
 493
 494     PERFORM rating.precalc_bibs_by_copy(badge_id);
 495
 496     SET LOCAL client_min_messages = error;
 497     DROP TABLE IF EXISTS precalc_bib_list;
 498     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 499         SELECT id FROM precalc_filter_bib_list
 500             INTERSECT
 501         SELECT id FROM precalc_bibs_by_copy_list;
 502
 503     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 504     IF badge.importance_age IS NOT NULL THEN
 505         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 506     END IF;
 507
 508     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 509     iscale := COALESCE(badge.importance_scale, 1.0);
 510
 511     RETURN QUERY
 512      SELECT bib,
 513             SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
 514       FROM (
 515          SELECT f.id AS bib,
 516                 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
 517                 COUNT(h.id)::INT AS holds
 518           FROM  action.hold_request h
 519                 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 520                 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
 521           WHERE h.request_time >= NOW() - badge.horizon_age
 522                 AND h.request_lib = ANY (badge.orgs)
 523           GROUP BY 1, 2
 524       ) x
 525       GROUP BY 1;
 526 END;
 527 $f$ LANGUAGE PLPGSQL STRICT;
 528
 529 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
 530     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 531 DECLARE
 532     badge   rating.badge_with_orgs%ROWTYPE;
 533 BEGIN
 534
 535     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 536
 537     PERFORM rating.precalc_bibs_by_copy(badge_id);
 538
 539     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 540         SELECT id FROM precalc_filter_bib_list
 541             INTERSECT
 542         SELECT id FROM precalc_bibs_by_copy_list
 543     );
 544
 545     ANALYZE precalc_copy_filter_bib_list;
 546
 547     RETURN QUERY
 548      SELECT rhrr.bib_record AS bib,
 549             COUNT(DISTINCT h.id)::NUMERIC AS holds
 550       FROM  action.hold_request h
 551             JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 552             JOIN action.hold_copy_map m ON (m.hold = h.id)
 553             JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
 554       WHERE h.fulfillment_time IS NULL
 555             AND h.request_lib = ANY (badge.orgs)
 556       GROUP BY 1;
 557 END;
 558 $f$ LANGUAGE PLPGSQL STRICT;
 559
 560 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
 561     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 562 DECLARE
 563     badge   rating.badge_with_orgs%ROWTYPE;
 564     iage    INT     := 1;
 565     iint    INT     := NULL;
 566     iscale  NUMERIC := NULL;
 567 BEGIN
 568
 569     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 570
 571     IF badge.horizon_age IS NULL THEN
 572         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 573             badge.name,
 574             badge.id;
 575     END IF;
 576
 577     PERFORM rating.precalc_bibs_by_copy(badge_id);
 578
 579     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 580         SELECT id FROM precalc_filter_bib_list
 581             INTERSECT
 582         SELECT id FROM precalc_bibs_by_copy_list
 583     );
 584
 585     ANALYZE precalc_copy_filter_bib_list;
 586
 587     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 588     IF badge.importance_age IS NOT NULL THEN
 589         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 590     END IF;
 591
 592     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 593     iscale := COALESCE(badge.importance_scale, 1.0);
 594
 595     RETURN QUERY
 596      SELECT bib,
 597             SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
 598       FROM (
 599          SELECT cn.record AS bib,
 600                 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
 601                 COUNT(c.id)::INT AS circs
 602           FROM  action.circulation c
 603                 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
 604                 JOIN asset.copy cp ON (cp.id = c.target_copy)
 605                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 606           WHERE c.xact_start >= NOW() - badge.horizon_age
 607                 AND cn.owning_lib = ANY (badge.orgs)
 608                 AND c.phone_renewal IS FALSE  -- we don't count renewals
 609                 AND c.desk_renewal IS FALSE
 610                 AND c.opac_renewal IS FALSE
 611           GROUP BY 1, 2
 612       ) x
 613       GROUP BY 1;
 614 END;
 615 $f$ LANGUAGE PLPGSQL STRICT;
 616
 617 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
 618     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 619 DECLARE
 620     badge   rating.badge_with_orgs%ROWTYPE;
 621 BEGIN
 622
 623     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 624
 625     PERFORM rating.precalc_bibs_by_copy(badge_id);
 626
 627     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 628         SELECT id FROM precalc_filter_bib_list
 629             INTERSECT
 630         SELECT id FROM precalc_bibs_by_copy_list
 631     );
 632
 633     ANALYZE precalc_copy_filter_bib_list;
 634
 635     RETURN QUERY
 636      SELECT cn.record AS bib,
 637             COUNT(c.id)::NUMERIC AS circs
 638       FROM  action.circulation c
 639             JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
 640             JOIN asset.copy cp ON (cp.id = c.target_copy)
 641             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 642       WHERE c.checkin_time IS NULL
 643             AND cn.owning_lib = ANY (badge.orgs)
 644       GROUP BY 1;
 645
 646 END;
 647 $f$ LANGUAGE PLPGSQL STRICT;
 648
 649 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
 650     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 651 DECLARE
 652     badge   rating.badge_with_orgs%ROWTYPE;
 653 BEGIN
 654
 655     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 656
 657     PERFORM rating.precalc_bibs_by_copy(badge_id);
 658
 659     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 660         SELECT id FROM precalc_filter_bib_list
 661             INTERSECT
 662         SELECT id FROM precalc_bibs_by_copy_list
 663     );
 664
 665     ANALYZE precalc_copy_filter_bib_list;
 666
 667     RETURN QUERY
 668      SELECT bib,
 669             SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
 670       FROM  (SELECT cn.record AS bib,
 671                     (cp.status = 1)::INT AS checked_out,
 672                     1 AS total
 673               FROM  asset.copy cp
 674                     JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 675                     JOIN asset.call_number cn ON (cn.id = cp.call_number)
 676               WHERE cn.owning_lib = ANY (badge.orgs)
 677             ) x
 678       GROUP BY 1;
 679 END;
 680 $f$ LANGUAGE PLPGSQL STRICT;
 681
 682 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
 683     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 684 DECLARE
 685     badge   rating.badge_with_orgs%ROWTYPE;
 686 BEGIN
 687
 688     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 689
 690     PERFORM rating.precalc_bibs_by_copy(badge_id);
 691
 692     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 693         SELECT id FROM precalc_filter_bib_list
 694             INTERSECT
 695         SELECT id FROM precalc_bibs_by_copy_list
 696     );
 697
 698     ANALYZE precalc_copy_filter_bib_list;
 699
 700     RETURN QUERY
 701      SELECT cn.record AS bib,
 702             COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
 703       FROM  asset.copy cp
 704             JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 705             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 706             JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
 707       WHERE cn.owning_lib = ANY (badge.orgs)
 708       GROUP BY 1;
 709 END;
 710 $f$ LANGUAGE PLPGSQL STRICT;
 711
 712 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
 713     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 714 DECLARE
 715     badge   rating.badge_with_orgs%ROWTYPE;
 716 BEGIN
 717
 718     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 719
 720     PERFORM rating.precalc_bibs_by_copy(badge_id);
 721
 722     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 723         SELECT id FROM precalc_filter_bib_list
 724             INTERSECT
 725         SELECT id FROM precalc_bibs_by_copy_list
 726     );
 727
 728     ANALYZE precalc_copy_filter_bib_list;
 729
 730     RETURN QUERY
 731      SELECT cn.record AS bib,
 732             COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
 733       FROM  asset.copy cp
 734             JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 735             JOIN asset.copy_location cl ON (cl.id = cp.location)
 736             JOIN config.copy_status cs ON (cs.id = cp.status)
 737             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 738             JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
 739       WHERE cn.owning_lib = ANY (badge.orgs)
 740             AND cp.holdable IS TRUE
 741             AND cl.holdable IS TRUE
 742             AND cs.holdable IS TRUE
 743       GROUP BY 1;
 744 END;
 745 $f$ LANGUAGE PLPGSQL STRICT;
 746
 747 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
 748     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 749 DECLARE
 750     badge   rating.badge_with_orgs%ROWTYPE;
 751 BEGIN
 752
 753     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 754
 755     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 756
 757     SET LOCAL client_min_messages = error;
 758     DROP TABLE IF EXISTS precalc_bib_list;
 759     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 760         SELECT id FROM precalc_filter_bib_list
 761             INTERSECT
 762         SELECT id FROM precalc_bibs_by_copy_or_uri_list;
 763
 764     RETURN QUERY
 765      SELECT b.id,
 766             1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
 767       FROM  precalc_bib_list pop
 768             JOIN biblio.record_entry b ON (b.id = pop.id);
 769 END;
 770 $f$ LANGUAGE PLPGSQL STRICT;
 771
 772 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
 773     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 774 DECLARE
 775     badge   rating.badge_with_orgs%ROWTYPE;
 776 BEGIN
 777
 778     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 779
 780     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 781
 782     SET LOCAL client_min_messages = error;
 783     DROP TABLE IF EXISTS precalc_bib_list;
 784     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 785         SELECT id FROM precalc_filter_bib_list
 786             INTERSECT
 787         SELECT id FROM precalc_bibs_by_copy_or_uri_list;
 788
 789     RETURN QUERY
 790      SELECT pop.id AS bib,
 791             s.value::NUMERIC
 792       FROM  precalc_bib_list pop
 793             JOIN metabib.record_sorter s ON (
 794                 s.source = pop.id
 795                 AND s.attr = 'pubdate'
 796                 AND s.value ~ '^\d+$'
 797             )
 798       WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
 799 END;
 800 $f$ LANGUAGE PLPGSQL STRICT;
 801
 802 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
 803     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 804 DECLARE
 805     badge   rating.badge_with_orgs%ROWTYPE;
 806 BEGIN
 807
 808     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 809
 810     PERFORM rating.precalc_bibs_by_copy(badge_id);
 811
 812     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 813         SELECT id FROM precalc_filter_bib_list
 814             INTERSECT
 815         SELECT id FROM precalc_bibs_by_copy_list
 816     );
 817
 818     ANALYZE precalc_copy_filter_bib_list;
 819
 820     RETURN QUERY
 821      SELECT bib,
 822             SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
 823       FROM  (SELECT cn.record AS bib,
 824                     cp.id,
 825                     EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
 826                     SUM(  -- time copy spent circulating
 827                         EXTRACT(
 828                             EPOCH FROM
 829                             AGE(
 830                                 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
 831                                 circ.xact_start
 832                             )
 833                         )
 834                     )::NUMERIC AS circ_time
 835               FROM  asset.copy cp
 836                     JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 837                     JOIN asset.call_number cn ON (cn.id = cp.call_number)
 838                     LEFT JOIN action.all_circulation circ ON (
 839                         circ.target_copy = cp.id
 840                         AND stop_fines NOT IN (
 841                             'LOST',
 842                             'LONGOVERDUE',
 843                             'CLAIMSRETURNED',
 844                             'LONGOVERDUE'
 845                         )
 846                         AND NOT (
 847                             checkin_time IS NULL AND
 848                             stop_fines = 'MAXFINES'
 849                         )
 850                     )
 851               WHERE cn.owning_lib = ANY (badge.orgs)
 852                     AND cp.active_date IS NOT NULL
 853                     -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
 854                     AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
 855               GROUP BY 1,2,3
 856             ) x
 857       GROUP BY 1;
 858 END;
 859 $f$ LANGUAGE PLPGSQL STRICT;
 860
 861 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
 862     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 863 BEGIN
 864     PERFORM rating.precalc_bibs_by_copy(badge_id);
 865     RETURN QUERY
 866         SELECT id, 1.0 FROM precalc_filter_bib_list
 867             INTERSECT
 868         SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
 869 END;
 870 $f$ LANGUAGE PLPGSQL STRICT;
 871
 872 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
 873     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 874 BEGIN
 875     PERFORM rating.precalc_bibs_by_uri(badge_id);
 876     RETURN QUERY
 877         SELECT id, 1.0 FROM precalc_bib_filter_bib_list
 878             INTERSECT
 879         SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
 880 END;
 881 $f$ LANGUAGE PLPGSQL STRICT;
 882
 883 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
 884     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 885 BEGIN
 886     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 887     RETURN QUERY
 888         (SELECT id, 1.0 FROM precalc_filter_bib_list
 889             INTERSECT
 890         SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
 891             UNION
 892         (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
 893             INTERSECT
 894         SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
 895 END;
 896 $f$ LANGUAGE PLPGSQL STRICT;
 897
 898 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
 899     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 900 BEGIN
 901     RETURN QUERY
 902         SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
 903 END;
 904 $f$ LANGUAGE PLPGSQL STRICT;
 905
 906 CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT)
 907     RETURNS TABLE (record INT, value NUMERIC) AS $f$
 908 DECLARE
 909     badge   rating.badge_with_orgs%ROWTYPE;
 910 BEGIN
 911
 912     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 913
 914     PERFORM rating.precalc_bibs_by_copy(badge_id);
 915
 916     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 917         SELECT id FROM precalc_filter_bib_list
 918             INTERSECT
 919         SELECT id FROM precalc_bibs_by_copy_list
 920     );
 921     ANALYZE precalc_copy_filter_bib_list;
 922
 923     RETURN QUERY
 924      SELECT f.id::INT AS bib,
 925             COUNT(f.copy)::NUMERIC
 926       FROM  precalc_copy_filter_bib_list f
 927             JOIN asset.copy cp ON (f.copy = cp.id)
 928             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 929       WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
 930
 931 END;
 932 $f$ LANGUAGE PLPGSQL STRICT;
 933
 934 COMMIT;
 935