spark实现hive练习题（中高级）

Hive练习

第一题

查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。期望结果如下：

sku_id
11

    spark.table("hivetest.order_detail")
      .groupBy("sku_id")
      .agg(count("sku_num").as("num"))
      .withColumn("row",dense_rank().over(partitionBy().orderBy(desc("num"))))
      .where(col("row") === 2)
      .drop("row","num")
      .withColumn("s",count(col("sku_id")).over(partitionBy()))
      .withColumn("t",col("s") === "0")
      .withColumn("sku_id",when(col("s") =!= "0",col("sku_id")))
      .select("sku_id")
      .distinct()
      .createOrReplaceTempView("voo")
    spark.sql("select * from voo o right join (select 1) t on 1 = 1")
      .select("sku_id")
    .show()

如果为空

第二题

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

user_id
101

    spark.table("hivetest.order_info")
      .withColumn("nextday",lead("create_date",1,0).over(partitionBy("user_id").orderBy("create_date")))
      .withColumn("co",when(datediff(col("nextday"),col("create_date")) === 1,1).otherwise(0))
      .withColumn("days",sum("co").over(partitionBy("user_id").orderBy(asc("create_date"))))
      .where(col("days") >= 3)
      .select(col("user_id"))
      .distinct()
      .show()

第三题

从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：

category_id **(**分类id)	category_name **(**分类名称)	sku_id **(**销量最好的商品id)	name **(**商品名称)	order_num （销量最好的商品销量）	order_cnt **(**商品种类数量)
1	数码	2	手机壳	302	4
2	厨卫	8	微波炉	253	4
3	户外	12	遮阳伞	349	4

    spark.sql(
      """
        |select o.sku_id,o.sku_num,o.price,s.name,s.category_id,c.category_name
        |from hivetest.order_detail o join hivetest.sku_info s on o.sku_id = s.sku_id
        |join hivetest.category_info c on s.category_id = c.category_id
        |""".stripMargin)
      .withColumn("order_num",sum(col("sku_num")).over(partitionBy("sku_id")))
      .withColumn("order_cnt",count(col("sku_id")).over(partitionBy("category_id")))
      .select(col("category_id").cast("int"),col("category_name").cast("string")
        ,col("sku_id").cast("int"),col("name").cast("string")
        ,col("order_num").cast("int"),col("order_cnt").cast("int"))
      .distinct()
      .withColumn("row",row_number().over(partitionBy("category_id").orderBy(desc("order_num"))))
      .withColumn("order_cnt",count(col("sku_id")).over(partitionBy("category_id")))
      .where(col("row") === 1)
      .drop("row")
      .orderBy("category_id")
      .show()

第四题

从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。

用户vip等级根据累积消费金额计算，计算规则如下：

设累积消费总额为X，

若0=<X<10000,则vip等级为普通会员

若10000<=X<30000,则vip等级为青铜会员

若30000<=X<50000,则vip等级为白银会员

若50000<=X<80000,则vip为黄金会员

若80000<=X<100000,则vip等级为白金会员

若X>=100000,则vip等级为钻石会员

期望结果如下：

user_id **(**用户id)	create_date **(**下单日期)	sum_so_far **(**截至每个下单日期的累计下单金额)	vip_level **(**每个下单日期的VIP等级)
101	2021-09-27	29000.00	青铜会员
101	2021-09-28	99500.00	白金会员
101	2021-09-29	142800.00	钻石会员
101	2021-09-30	143660.00	钻石会员
102	2021-10-01	171680.00	钻石会员
102	2021-10-02	177850.00	钻石会员
103	2021-10-02	69980.00	黄金会员
103	2021-10-03	75890.00	黄金会员
104	2021-10-03	89880.00	白金会员
105	2021-10-04	120100.00	钻石会员
106	2021-10-04	9390.00	普通会员
106	2021-10-05	119150.00	钻石会员
107	2021-10-05	69850.00	黄金会员
107	2021-10-06	124150.00	钻石会员
108	2021-10-06	101070.00	钻石会员
108	2021-10-07	155770.00	钻石会员
109	2021-10-07	129480.00	钻石会员
109	2021-10-08	153500.00	钻石会员
1010	2021-10-08	51950.00	黄金会员

spark.table("hivetest.order_info")
  .withColumn("sum_so_far",sum(col("total_amount")).over(partitionBy("user_id").orderBy("create_date")))
  .withColumn("vip_level",when(col("sum_so_far") >= 100000,"钻石会员")
    .when(col("sum_so_far") >= 80000,"白金会员")
    .when(col("sum_so_far") >= 50000,"黄金会员")
    .when(col("sum_so_far") >= 30000,"白银会员")
    .when(col("sum_so_far") >= 10000,"青铜会员")
    .when(col("sum_so_far") >= 0,"普通会员"))
  .select(col("user_id").cast("int"),col("create_date")
    ,col("sum_so_far").cast("double"),col("vip_level"))
  .distinct()
  .orderBy("user_id","create_date")
  .show()

第五题

从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，期望结果如下：

percentage
70.0%

spark.table("hivetest.order_info")
  .select("user_id","create_date")
  .distinct()
  .withColumn("nextday",lead(col("create_date"),1).over(partitionBy("user_id").orderBy("create_date")))
  .withColumn("com",when(datediff(col("nextday"),col("create_date")) === 1,1).otherwise(0))
  .withColumn("alldesc",dense_rank().over(partitionBy().orderBy("user_id")))
  .withColumn("all",max("alldesc").over(partitionBy()))
  .where(col("com") === 1)
  .select("user_id","all")
  .distinct()
  .withColumn("pay",count("user_id").over(partitionBy()))
  .withColumn("h",col("pay")/col("all"))
  .withColumn("percentage",concat(col("h")*100,lit("%")))
  .select("percentage")
  .distinct()
  .show()

第六题

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

sku_id （商品id）	year （销售首年年份）	order_num （首年销量）	order_amount （首年销售金额）
1	2021	51	102000.00
2	2021	302	3020.00
3	2021	36	180000.00
4	2021	53	318000.00
5	2021	242	121000.00
6	2021	32	64000.00
7	2021	252	25200.00
8	2021	253	151800.00
9	2021	194	194000.00
10	2021	299	29900.00
11	2021	320	16000.00
12	2021	349	6980.00

    spark.table("hivetest.order_detail")
      .withColumn("year",date_format(col("create_date"),"yyyy"))
      .withColumn("row",dense_rank().over(partitionBy("sku_id").orderBy("year")))
      .where(col("row") === 1)
      .withColumn("order_num",sum("sku_num").over(partitionBy("sku_id")))
      .withColumn("order_amount",sum(col("sku_num") * col("price")).over(partitionBy("sku_id","year")))
      .select("sku_id","year","order_num","order_amount")
      .distinct()
      .orderBy("sku_id")
      .show()

第七题

从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品，期望结果如下：

sku_id （商品id）	name （商品名称）	order_num （销量）
1	xiaomi 10	51
3	apple 12	36
4	xiaomi 13	53
6	洗碗机	32

spark.sql(
  """
    |select o.sku_id,o.sku_num,o.create_date,s.from_date,s.name from hivetest.order_detail o join hivetest.sku_info s on o.sku_id = s.sku_id
    |""".stripMargin)
  .where(datediff(lit("2022-01-10"),col("from_date")) >=30 && year(col("create_date")) === 2021)
  .withColumn("order_num",sum("sku_num").over(partitionBy("sku_id")))
  .where(col("order_num") < 100)
  .select("sku_id","name","order_num")
  .distinct()
  .show()

第八题

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。期望结果如下：

sku_id**（商品id）**	create_date**（销量最高的日期）**	sum_num**（销量）**
1	2021-10-02	9
2	2021-10-04	60
3	2021-10-05	9
4	2021-10-07	10
5	2021-10-03	47
6	2021-10-03	8
7	2021-10-05	58
8	2021-10-08	59
9	2021-10-01	45
10	2021-10-08	94
11	2021-10-08	95
12	2021-10-08	83

spark.table("hivetest.order_detail")
  .withColumn("sum_num",sum("sku_num").over(partitionBy("sku_id","create_date")))
  .withColumn("row",row_number().over(partitionBy("sku_id").orderBy(desc("sum_num"),col("create_date"))))
  .where(col("row") === 1)
  .select("sku_id","create_date","sum_num")
  .orderBy("sku_id")
  .show()

第九题

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品，期望结果如下：

sku_id	name	sum_num	cate_avg_num
2	手机壳	302	110.5
5	破壁机	242	194.75
7	热水壶	252	194.75
8	微波炉	253	194.75
10	帐篷	299	290.5
11	烧烤架	320	290.5
12	遮阳伞	349	290.5

spark.sql(
    """
      |select o.sku_id,s.name,o.sku_num,s.category_id from hivetest.order_detail o join hivetest.sku_info s on o.sku_id = s.sku_id
      |""".stripMargin)
  .withColumn("cate_num",sum("sku_num").over(partitionBy("category_id")))
  .withColumn("dense",dense_rank().over(partitionBy("category_id")