cote corr, Oracle - rextester

cote corr

--Oracle 11g Express Edition
--please drop objects you've created at the end of the script 
--or check for their existance before creating
--IMPORTANT: separate statements by ';'
--';' should not appear in comments (or appear in quotes) as it is acting as a delimiter

DROP TABLE FilmCotes;
CREATE TABLE FilmCotes(
  idFilm INTEGER,
  idClient INTEGER,
  cote INTEGER);
  
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (1, 1, 1);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (1, 2, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (1, 3, 4);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (1, 4, 4);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (1, 5, 5);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (2, 1, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (2, 2, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (2, 3, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (2, 4, 5);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (2, 5, 2);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (3, 1, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (3, 2, 3);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (3, 3, 2);
INSERT INTO FilmCotes (idFilm, idClient, cote) VALUES (3, 4, 5);

SELECT idFilm, AVG(Cote) AS CoteMoy FROM FilmCotes GROUP BY idFilm;

WITH 
  fc1m AS (SELECT idFilm, AVG(Cote) AS CoteMoy FROM FilmCotes GROUP BY idFilm),
  fc2m AS (SELECT idFilm, AVG(Cote) AS CoteMoy FROM FilmCotes GROUP BY idFilm)
SELECT 
  fc1.idFilm AS idFilm1, 
  fc2.idFilm AS idFilm2,
  fc1.cote AS Rij,
  fc1m.cotemoy AS Rj,
  fc2.cote AS Rik,
  fc2m.cotemoy AS Rk
FROM 
  FilmCotes fc1, 
  FilmCotes fc2,
  fc1m, fc2m
WHERE 
  fc1.idFilm != fc2.idFilm
  AND fc1.idFilm < fc2.idFilm
  AND fc1m.idFilm = fc1.idFilm
  AND fc2m.idFilm = fc2.idFilm;
    
  
WITH 
  fc1m AS (SELECT idFilm, AVG(Cote) AS CoteMoy FROM FilmCotes GROUP BY idFilm),
  fc2m AS (SELECT idFilm, AVG(Cote) AS CoteMoy FROM FilmCotes GROUP BY idFilm)
SELECT 
  fc1.idFilm AS idFilm1, 
  fc2.idFilm AS idFilm2,
  (SUM(fc1.cote - fc1m.cotemoy)*SUM(fc2.cote - fc2m.cotemoy)) / sqrt(power(SUM(fc1.cote - fc1m.cotemoy), 2)*power(SUM(fc2.cote - fc2m.cotemoy), 2)) AS corr
FROM 
  FilmCotes fc1, 
  FilmCotes fc2,
  fc1m, fc2m
WHERE 
  fc1.idFilm != fc2.idFilm
  AND fc1.idFilm < fc2.idFilm
  AND fc1m.idFilm = fc1.idFilm
  AND fc2m.idFilm = fc2.idFilm
GROUP BY fc1.idFilm, fc2.idFilm;


run \| edit \| history \| help	0