Matrix Factorization in Query Language
CREATE TABLE sgd_model
as
SELECT
idx,
array_avg(u_rank) as Pu,
array_avg(i_rank) as Qi,
avg(u_bias) as Bu,
avg(i_bias) as Bi
FROM (
SELECT
train_mf_sgd(
user_id, item_id, rating,
'-factor ${factor} -mu ${mu} -iter ${iters}'
) as (idx, u_rank, i_rank, u_bias, i_bias)
FROM
training
) t
GROUP BY idx
SELECT
mf_predict(t2.Pu, p2.Qi, t2.Bu, p2.Bi, ${mu}) as predicted
FROM (
SELECT
t1.user_id,
t1.item_id,
m1.Pu,
m1.Bu
FROM
target t1
LEFT OUTER JOIN
sgd_model m1
ON (t1.user_id = m1.idx)
) t2
LEFT OUTER JOIN
sgd_model m2
ON (t2.item_id = m2.idx)