generate_cv.sql

generate_cv.sql

-- 10-folds cross validation

create or replace view training1
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 1;

create or replace view testing1
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 1;

create or replace view predict1
as
with model1 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training1
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing1 t1 LEFT OUTER JOIN model1 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model1 p2
ON (t2.movieid = p2.idx);

create or replace view training2
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 2;

create or replace view testing2
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 2;

create or replace view predict2
as
with model2 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training2
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing2 t1 LEFT OUTER JOIN model2 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model2 p2
ON (t2.movieid = p2.idx);

create or replace view training3
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 3;

create or replace view testing3
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 3;

create or replace view predict3
as
with model3 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training3
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing3 t1 LEFT OUTER JOIN model3 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model3 p2
ON (t2.movieid = p2.idx);

create or replace view training4
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 4;

create or replace view testing4
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 4;

create or replace view predict4
as
with model4 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training4
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing4 t1 LEFT OUTER JOIN model4 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model4 p2
ON (t2.movieid = p2.idx);

create or replace view training5
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 5;

create or replace view testing5
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 5;

create or replace view predict5
as
with model5 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training5
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing5 t1 LEFT OUTER JOIN model5 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model5 p2
ON (t2.movieid = p2.idx);

create or replace view training6
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 6;

create or replace view testing6
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 6;

create or replace view predict6
as
with model6 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training6
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing6 t1 LEFT OUTER JOIN model6 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model6 p2
ON (t2.movieid = p2.idx);

create or replace view training7
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 7;

create or replace view testing7
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 7;

create or replace view predict7
as
with model7 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training7
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing7 t1 LEFT OUTER JOIN model7 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model7 p2
ON (t2.movieid = p2.idx);

create or replace view training8
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 8;

create or replace view testing8
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 8;

create or replace view predict8
as
with model8 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training8
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing8 t1 LEFT OUTER JOIN model8 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model8 p2
ON (t2.movieid = p2.idx);

create or replace view training9
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 9;

create or replace view testing9
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 9;

create or replace view predict9
as
with model9 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training9
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing9 t1 LEFT OUTER JOIN model9 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model9 p2
ON (t2.movieid = p2.idx);

create or replace view training10
as
select 
  userid, movieid, rating
from 
  ratings_groupded
where
  gid != 10;

create or replace view testing10
as
select 
  userid,
  movieid,
  rating
from 
  ratings_groupded
where
  gid = 10;

create or replace view predict10
as
with model10 as (
select
  idx, 
  array_avg(u_rank) as Pu, 
  array_avg(m_rank) as Qi, 
  avg(u_bias) as Bu, 
  avg(m_bias) as Bi
from (
  select 
    train_mf_sgd(userid, movieid, rating, "-factor ${factor} -mu ${mu} -iterations ${iters} -cv_rate ${cv_rate} -lambda ${lambda} -eta ${eta}") as (idx, u_rank, m_rank, u_bias, m_bias)
  from 
    training10
) t
group by idx
) 
select
  t2.actual,
  mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
from (
  select
    t1.userid, 
    t1.movieid,
    t1.rating as actual,
    p1.Pu,
    p1.Bu
  from
    testing10 t1 LEFT OUTER JOIN model10 p1
    ON (t1.userid = p1.idx) 
) t2 
LEFT OUTER JOIN model10 p2
ON (t2.movieid = p2.idx);

-- evaluation 
select
  mae(predicted, actual) as mae,
  rmse(predicted, actual) as rmse
from (
  select * from predict1
  UNION ALL
  select * from predict2
  UNION ALL
  select * from predict3
  UNION ALL
  select * from predict4
  UNION ALL
  select * from predict5
  UNION ALL
  select * from predict6
  UNION ALL
  select * from predict7
  UNION ALL
  select * from predict8
  UNION ALL
  select * from predict9
  UNION ALL
  select * from predict10
) t;