CREATE VIEW KeyValue AS
SELECT `CustomerID`,`StockCode` 
FROM `ONline_retail` 
WHERE `CustomerID` !="" 
AND `StockCode` < "15004B"
GROUP BY `CustomerID`, `StockCode`  
ORDER BY `ONline_retail`.`CustomerID` ASC

SELECT kv1.`StockCode` AS Code1, kv2.`StockCode`AS Code2, COUNT(*) AS CoOccur
FROM KeyValue kv1 join
     KeyValue kv2
     ON kv1.`CustomerID` = kv2.`CustomerID`
WHERE kv1.`StockCode` != kv2.`StockCode`
GROUP BY kv1.`StockCode`, kv2.`StockCode`

CREATE VIEW countview AS
SELECT `StockCode`,COUNT(*) AS Occur FROM `keyvalue` GROUP BY `StockCode`

CREATE VIEW graphview AS
SELECT kv1.`StockCode`, kv2.`StockCode`, COUNT(*)
FROM KeyValue kv1 join
     KeyValue kv2
     ON kv1.`CustomerID` = kv2.`CustomerID`
WHERE kv1.`StockCode` != kv2.`StockCode`
GROUP BY kv1.`StockCode`, kv2.`StockCode`

CREATE VIEW probview AS
SELECT graphview.Code1, graphview.Code2, graphview.CoOccur, COUNTview.Occur, (graphview.CoOccur/COUNTview.Occur) AS prob
FROM countview, graphview 
WHERE countview.StockCode = graphview.Code1 


SELECT Code2, SUM(prob) AS Recommend FROM `probview`
JOIN (SELECT `StockCode` FROM `keyvalue` WHERE `CustomerID`="12748") Q1
  ON `probview`.Code1 = Q1.StockCode
JOIN (SELECT `StockCode` FROM `COUNTview`
	WHERE `StockCode`NOT IN 
	(SELECT `StockCode` FROM `keyvalue` WHERE `CustomerID`="12748")) Q2
  ON `probview`.Code2 = Q2.StockCode
GROUP BY Code2
ORDER BY Recommend DESC






































# TABLEs creatiON 
CREATE TABLE ratingsdata ( 
   userid int, 
   itemid int, 
   rating int, 
   timestamp int, 
   PRIMAKEY KEY (userid, itemid)); 
 
CREATE TABLE items ( 
   itemid int PRIMAKEY KEY, 
   title text, 
   date text, 
   videodate text, 
   imdb text, 
   actiON boolean, 
   adventure boolean, 
   animatiON boolean, 
   childrens boolean, 
   comedy boolean, 
   crime boolean, 
   documentary boolean, 
   drama boolean, 
   fantASy boolean, 
   noir boolean, 
   horror boolean, 
   musical boolean, 
   mystery boolean, 
   romance boolean, 
   scifi boolean, 
   thriller boolean, 
   war boolean, 
   western boolean); 
 
CREATE TABLE users ( 
   userid int PRIMAKEY KEY, 
   age int, 
   gender char, 
   occupation text, 
   zip int); 
 
# Indexes creation 
CREATE INDEX usersdata_index ON ratingsdata (userid); 
CREATE INDEX itemsdata_index ON ratingsdata (itemid); 


CREATE TABLE ratings ( 
   userid int, 
   itemid int, 
   rating int, 
   timestamp int, 
   PRIMAKEY KEY (userid, itemid)); 
CREATE INDEX usersratings_index ON ratings (userid); 
CREATE INDEX itemsratings_index ON ratings (itemid); 

INSERT INTO ratings (userid,itemid,rating,timestamp) 
   (SELECT ratingsdata.userid, ratingsdata.itemid, 
        ratingsdata.rating-AVGratingbyusers.AVGrating, 
        ratingsdata.timestamp 
    FROM ratingsdata, 
       (SELECT userid, AVG(rating) 
        FROM ratingsdata 
        GROUP BY userid 
       ) AS AVGratingbyusers 
    WHERE ratingsdata.userid=AVGratingbyusers.userid 
   ); 
   
   
CREATE TABLE ratings ( 
   userid int, 
   itemid int, 
   rating int, 
   timestamp int, 
   PRIMAKEY KEY (userid, itemid)); 
CREATE INDEX usersratings_index ON ratings (userid); 
CREATE INDEX itemsratings_index ON ratings (itemid); 
INSERT INTO ratings (userid,itemid,rating,timestamp) 
   (SELECT ratingsdata.userid, ratingsdata.itemid, 
        ratingsdata.rating-AVGratingbyusers.AVGrating, 
        ratingsdata.timestamp 
    FROM ratingsdata, 
       (SELECT userid, AVG(rating) 
        FROM ratingsdata 
        GROUP BY userid 
       ) AS AVGratingbyusers 
    WHERE ratingsdata.userid=AVGratingbyusers.userid 
   ); 
   
   
SELECT title, items.itemid, score, nbratings 
FROM items, 
   (SELECT itemid, SUM(ratings.rating)/simsize.SIZE AS score, 
       COUNT(sim.userid) AS nbratings 
    FROM sim, ratings, 
       (SELECT  COUNT(*) AS SIZE FROM sim) AS simsize 
    WHERE sim.userid= ratings.userid 
    GROUP BY itemid 
    ORDER BY score DESC 
    LIMIT 10 
   ) AS itemscores 
WHERE items.itemid = itemscores.itemid 
ORDER BY score DESC;
