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