本篇文章记录各区域热门商品统计-使用开窗函数统计各区域的top3热门商品。
代码
AreaTop3ProductSpark.java
private static JavaRDD<Row> getAreaTop3ProductRDD(SQLContext sqlContext){ // 技术点:开窗函数 // 使用开窗函数先进行一个子查询 // 按照area进行分组,给每个分组内的数据,按照点击次数降序排序,打上一个组内的行号 // 接着在外层查询中,过滤出各个组内的行号排名前3的数据 // 其实就是咱们的各个区域下top3热门商品 String sql = "SELECT " + "area, " + "product_id, " + "click_count," + "city_infos," + "product_name, " + "product_status " + "FROM (" + "SELECT " + "area, " + "product_id, " + "click_count," + "city_infos," + "product_name, " + "product_status," + "ROW_NUMBER() OVER(PARTITION BY area ORDER BY click_count DESC) rank " + "FROM tmp_area_fullprod_click_count " + ") t " + "WHERE rank <=3"; Dataset ds = sqlContext.sql(sql); return ds.javaRDD(); }