[datatable-help] data table question sql equivalent in data table with subqueries

DS ds5j at excite.com
Sat Feb 18 17:38:11 CET 2012


Hi Matt,
    Is there a way to do the following in data.table:

(select avg(rating) from data x
where x.itemid=a.itemid and x.userid!=a.userid
and
exists (select 1 from (select * from data y where y.userid=x.userid) y, (select * from data z where z.userid=a.userid) z where  y.itemid=z.itemid  
and y.itemid!=a.itemid
and abs(y.rating-z.rating)>=4 )
) pred_rating_disimilar

 from data1 a')

This is based on movielens movie data of (userid, itemid, rating and timestamp).

its finding average rating on movie by all users who rated a movie differently from the current user in the row.

thanks
Dhruv



More information about the datatable-help mailing list