Bookdata-tools/bx-index.sql

CREATE INDEX IF NOT EXISTS bx_rating_user_idx ON bx_ratings (user_id);
CREATE INDEX IF NOT EXISTS bx_rating_isbn_idx ON bx_ratings (isbn);
ANALYZE bx_ratings;

INSERT INTO loc_isbn_book_id (isbn, book_id)
    WITH bad_isbns AS (SELECT DISTINCT isbn
                       FROM bx_ratings br
                       WHERE NOT EXISTS (SELECT * FROM loc_isbn_book_id ib WHERE ib.isbn = br.isbn))
    SELECT isbn, nextval('loc_synthetic_book_id') FROM bad_isbns;
ANALYZE loc_isbn_book_id;

DROP VIEW IF EXISTS bx_loc_explicit_ratings;
CREATE VIEW bx_loc_explicit_ratings
  AS SELECT user_id, book_id, MEDIAN(rating) AS rating, COUNT(rating) AS nratings
     FROM bx_ratings
       JOIN loc_isbn_book_id USING (isbn)
     WHERE rating > 0
     GROUP BY user_id, book_id;

DROP VIEW IF EXISTS bx_loc_all_ratings;
CREATE VIEW bx_loc_all_ratings
  AS SELECT user_id, book_id, MEDIAN(rating) AS rating, COUNT(rating) AS nratings
     FROM bx_ratings
       JOIN loc_isbn_book_id USING (isbn)
     GROUP BY user_id, book_id;