108.Spark大型电商项目-各区域热门商品统计-使用内置case when函数给各个区域打上级别标记

目录

代码

AreaTop3ProductSpark.java

---

本篇文章记录各区域热门商品统计-使用内置case when函数给各个区域打上级别标记。

代码

AreaTop3ProductSpark.java

	/**
	 * 获取各区域top3热门商品
	 * @param sqlContext
	 * @return
	 */
	private static JavaRDD<Row> getAreaTop3ProductRDD(SQLContext sqlContext) {
		// 技术点：开窗函数
		
		// 使用开窗函数先进行一个子查询
		// 按照area进行分组，给每个分组内的数据，按照点击次数降序排序，打上一个组内的行号
		// 接着在外层查询中，过滤出各个组内的行号排名前3的数据
		// 其实就是咱们的各个区域下top3热门商品
		
		// 华北、华东、华南、华中、西北、西南、东北
		// A级：华北、华东
		// B级：华南、华中
		// C级：西北、西南
		// D级：东北
		
		// case when
		// 根据多个条件，不同的条件对应不同的值
		// case when then ... when then ... else ... end
		
		String sql = 
				"SELECT "
					+ "area,"
					+ "CASE "
						+ "WHEN area='华北' OR area='华东' THEN 'A级' "
						+ "WHEN area='华南' OR area='华中' THEN 'B级' "
						+ "WHEN area='西北' OR area='西南' THEN 'C级' "
						+ "ELSE 'D级' "
					+ "END aera_level,"
					+ "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";
		
		DataFrame df = sqlContext.sql(sql);
		
		return df.javaRDD();
	}