Spark SQL（十）之基于物品的推荐公式

╰半夏微凉° 2023-01-18 10:30 99阅读 0赞

### 一、基于物品的推荐公式 ###

![20210505225652708.png][]

其中，S(j,K)表示与物品j最相似的K个物品，N(u)表示用户u喜欢的物品集合，Rui表示用户u对物品i的评分。

### 二、代码 ###

public class ItemCFRecommendApp {
    
        public static void main(String[]args){
    
            SparkConf sparkConf = new SparkConf();
            sparkConf.setAppName("ItemCFRecommendApp");
            sparkConf.setMaster("local[*]");
            SparkSession sparkSession = SparkSession.builder().config(sparkConf).getOrCreate();
            
            String url = "jdbc:mysql://localhost:3306/spark-mysql?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false";
            String driver = "com.mysql.jdbc.Driver";
            String user = "root";
            String password = "admin";
            Dataset<Row> score = sparkSession.read()
                    .format("jdbc")
                    .option("driver", driver)
                    .option("url",url)
                    .option("dbtable","user_item")
                    .option("user",user)
                    .option("password",password)
                    .load();
    
            Dataset<Row> similar = sparkSession.read()
                    .format("jdbc")
                    .option("driver", driver)
                    .option("url",url)
                    .option("dbtable","item_similar")
                    .option("user",user)
                    .option("password",password)
                    .load();
    
            //分组 top k
    //        similar = similar.selectExpr("a_item_id", "b_item_id", "count",
    //                "ROW_NUMBER() OVER (PARTITION BY a_item_id ORDER BY count DESC) rank")
    //                .where("rank <= 10");
    //        similar.show();
            Dataset<Row> result =  similar.as("is")
                     .join(score.as("s"), functions.column("is.b_item_id").$eq$eq$eq(functions.column("s.item_id")))
                    .selectExpr("is.a_item_id item_id", "s.user_id", "is.count * s.score score")
                    .groupBy("user_id", "item_id").sum("score")
                    .selectExpr("user_id", "item_id", "`sum(score)` score");
    
    
            result.show();
    //        result.write()
    //                .mode(SaveMode.Overwrite)
    //                .format("jdbc")
    //                .option("driver", driver)
    //                .option("url",url)
    //                .option("dbtable","item_user_recom")
    //                .option("user",user)
    //                .option("password",password)
    //                .save();
    
             sparkSession.stop();
        }
    }

[20210505225652708.png]: /images/20221021/47456eefbbc545e6b4e90f7a12e83a82.png