spark sql实现月销售额占比以及月环比和月同比(大厂数仓sql面试题)

寒江老翁

已于 2023-09-10 15:51:06 修改

阅读量643

点赞数

文章标签： spark sql 大数据面试

于 2023-09-10 15:49:56 首次发布

本文链接：https://blog.csdn.net/weixin_33438932/article/details/132792032

版权

1.首先创造数据，数据有4列分别是店铺id、订单id、订单金额、订单日期。

				spark.createDataFrame(Seq(
					("1","11",10,"2023-01-01"),
					("1","22",20,"2023-01-02"),
					("1","33",10,"2023-02-28"),
					("1","44",30,"2023-03-02"),
					("1","55",10,"2023-05-02"),
					("1","55",20,"2023-06-02"),
					("1","11",10,"2022-01-01"),
					("1","22",20,"2022-01-02"),
					("1","33",10,"2022-02-28"),
					("1","44",30,"2022-03-02"),
					("1","55",10,"2022-05-02"),
					("1","55",20,"2022-06-02"),
					("11","11",10,"2023-01-01"),
					("11","22",30,"2023-01-02"),
					("11","33",10,"2023-02-28"),
					("11","44",20,"2023-03-02"),
					("11","55",10,"2023-05-02"),
					("11","55",30,"2023-06-02"),
					("11","11",10,"2022-01-01"),
					("11","22",20,"2022-01-02"),
					("11","33",10,"2022-02-28"),
					("11","44",30,"2022-03-02"),
					("11","55",20,"2022-05-02"),
					("11","55",30,"2022-06-02")
				)).toDF("shop_id","order_id","amount","event_day").createOrReplaceTempView("t1")

数据如下：

+-------+--------+------+----------+
|shop_id|order_id|amount| event_day|
+-------+--------+------+----------+
|      1|      11|    10|2023-01-01|
|      1|      22|    20|2023-01-02|
|      1|      33|    10|2023-02-28|
|      1|      44|    30|2023-03-02|
|      1|      55|    10|2023-05-02|
|      1|      55|    20|2023-06-02|
|      1|      11|    10|2022-01-01|
|      1|      22|    20|2022-01-02|
|      1|      33|    10|2022-02-28|
|      1|      44|    30|2022-03-02|
|      1|      55|    10|2022-05-02|
|      1|      55|    20|2022-06-02|
|     11|      11|    10|2023-01-01|
|     11|      22|    30|2023-01-02|
|     11|      33|    10|2023-02-28|
|     11|      44|    20|2023-03-02|
|     11|      55|    10|2023-05-02|
|     11|      55|    30|2023-06-02|
|     11|      11|    10|2022-01-01|
|     11|      22|    20|2022-01-02|
+-------+--------+------+----------+

2.计算月销售额占比
通过窗口函数实现，首先聚合月销售额，之后再根据月销售额集合为年销售额，最后计算占比即可。

		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |month,
			   |year,
			   |m_amount,
			   |y_amount,
			   |round(m_amount/y_amount,4) ratio
			   |from
			   |(
			   |select
			   |shop_id,
			   |month,
			   |m_amount,
			   |date_format(month,'yyyy') year,
			   |sum(m_amount) over(partition by date_format(month,'yyyy')) y_amount
			   |from
			   |(
			   |select
			   |shop_id,
			   |date_format(event_day,'yyyy-MM') month,
			   |sum(amount) m_amount
			   |from t1 group by shop_id,date_format(event_day,'yyyy-MM')
			   |) a) aa order by shop_id,month
			   |""".stripMargin).show()

结果


+-------+-------+----+--------+--------+------+
|shop_id|  month|year|m_amount|y_amount| ratio|
+-------+-------+----+--------+--------+------+
|      1|2022-01|2022|      30|     220|0.1364|
|      1|2022-02|2022|      10|     220|0.0455|
|      1|2022-03|2022|      30|     220|0.1364|
|      1|2022-05|2022|      10|     220|0.0455|
|      1|2022-06|2022|      20|     220|0.0909|
|      1|2023-01|2023|      30|     210|0.1429|
|      1|2023-02|2023|      10|     210|0.0476|
|      1|2023-03|2023|      30|     210|0.1429|
|      1|2023-05|2023|      10|     210|0.0476|
|      1|2023-06|2023|      20|     210|0.0952|
|     11|2022-01|2022|      30|     220|0.1364|
|     11|2022-02|2022|      10|     220|0.0455|
|     11|2022-03|2022|      30|     220|0.1364|
|     11|2022-05|2022|      20|     220|0.0909|
|     11|2022-06|2022|      30|     220|0.1364|
|     11|2023-01|2023|      40|     210|0.1905|
|     11|2023-02|2023|      10|     210|0.0476|
|     11|2023-03|2023|      20|     210|0.0952|
|     11|2023-05|2023|      10|     210|0.0476|
|     11|2023-06|2023|      30|     210|0.1429|
+-------+-------+----+--------+--------+------+

3.计算月环比
月环比计算公式=(本月-上月)/上月
为了方便说明分几步进行：
3.1 聚合本月数据（为了方便查看进行了order by正常不用的）

		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |date_format(event_day,'yyyy-MM') event_day,
			   |sum(amount) amount
			   |from t1 group by shop_id,date_format(event_day,'yyyy-MM') order by date_format(event_day,'yyyy-MM')
			   |""".stripMargin).createOrReplaceTempView("t2")

输出结果

+-------+---------+------+
|shop_id|event_day|amount|
+-------+---------+------+
|      1|  2022-01|    30|
|      1|  2022-02|    10|
|      1|  2022-03|    30|
|      1|  2022-05|    10|
|      1|  2022-06|    20|
|      1|  2023-01|    30|
|      1|  2023-02|    10|
|      1|  2023-03|    30|
|      1|  2023-05|    10|
|      1|  2023-06|    20|
|     11|  2022-01|    30|
|     11|  2022-02|    10|
|     11|  2022-03|    30|
|     11|  2022-05|    20|
|     11|  2022-06|    30|
|     11|  2023-01|    40|
|     11|  2023-02|    10|
|     11|  2023-03|    20|
|     11|  2023-05|    10|
|     11|  2023-06|    30|
+-------+---------+------+

3.2 聚合上个月的数据
因为我们计算月环比需要上个月的销售额，例如，2022-02需要2022-01月的销售额才能计算2022-02的月环比，即(2022-02销售额-2022-01销售额)/2022-01销售额，那么怎么获取2022-01销售额呢？这里采用的方式是join，所以就需要让上一个月和本月的日期一样，就是让2022-01变成2022-02，然后进行join即可。

		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |date_format(add_months(event_day,1),'yyyy-MM') event_day,
			   |sum(amount) amount
			   |from t1 group by shop_id,date_format(add_months(event_day,1),'yyyy-MM')
			   |order by shop_id,date_format(add_months(event_day,1),'yyyy-MM')
			   |""".stripMargin).createOrReplaceTempView("t3")

输出

+-------+---------+------+
|shop_id|event_day|amount|
+-------+---------+------+
|      1|  2022-02|    30|
|      1|  2022-03|    10|
|      1|  2022-04|    30|
|      1|  2022-06|    10|
|      1|  2022-07|    20|
|      1|  2023-02|    30|
|      1|  2023-03|    10|
|      1|  2023-04|    30|
|      1|  2023-06|    10|
|      1|  2023-07|    20|
|     11|  2022-02|    30|
|     11|  2022-03|    10|
|     11|  2022-04|    30|
|     11|  2022-06|    20|
|     11|  2022-07|    30|
|     11|  2023-02|    40|
|     11|  2023-03|    10|
|     11|  2023-04|    20|
|     11|  2023-06|    10|
|     11|  2023-07|    30|
+-------+---------+------+

3.3将本月和上个月的进行join
对于没有上个月的情况，输出为null，代表无意义，也可以将null转为其他值，依据具体需求而定。

		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |a_event_day,
			   |round((a_amount-b_amount)/b_amount,4) huanbi
			   |from
			   |(
			   |select
			   |t2.shop_id,
			   |t2.event_day a_event_day,
			   |t3.event_day b_event_day,
			   |t2.amount a_amount,
			   |t3.amount b_amount
			   |from t2 left join t3
			   |on t2.shop_id=t3.shop_id and t2.event_day=t3.event_day
			   |)  order by shop_id,a_event_day
			   |""".stripMargin).show()

输出

+-------+-----------+-------+
|shop_id|a_event_day| huanbi|
+-------+-----------+-------+
|      1|    2022-01|   null|
|      1|    2022-02|-0.6667|
|      1|    2022-03|    2.0|
|      1|    2022-05|   null|
|      1|    2022-06|    1.0|
|      1|    2023-01|   null|
|      1|    2023-02|-0.6667|
|      1|    2023-03|    2.0|
|      1|    2023-05|   null|
|      1|    2023-06|    1.0|
|     11|    2022-01|   null|
|     11|    2022-02|-0.6667|
|     11|    2022-03|    2.0|
|     11|    2022-05|   null|
|     11|    2022-06|    0.5|
|     11|    2023-01|   null|
|     11|    2023-02|  -0.75|
|     11|    2023-03|    1.0|
|     11|    2023-05|   null|
|     11|    2023-06|    2.0|
+-------+-----------+-------+

有些人会想到用快窗函数，然后使用lead或者lag获取上一行或者下一行，但是这总方式是有问题的，如果月份不连续，那么计算的就是错误的。

4 月同比
今年1月和去年1月进行比较。逻辑和环比一样只是12个月，不是1个月。

		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |date_format(add_months(event_day,12),'yyyy-MM') event_day,
			   |sum(amount) amount
			   |from t1 group by shop_id,date_format(add_months(event_day,12),'yyyy-MM')
			   |order by shop_id,date_format(add_months(event_day,1),'yyyy-MM')
			   |""".stripMargin).createOrReplaceTempView("t3")


		spark.sql(
			s"""
			   |select
			   |shop_id,
			   |a_event_day,
			   |round((a_amount-b_amount)/b_amount,4) tongbi
			   |from
			   |(
			   |select
			   |t2.shop_id,
			   |t2.event_day a_event_day,
			   |t3.event_day b_event_day,
			   |t2.amount a_amount,
			   |t3.amount b_amount
			   |from t2 left join t3
			   |on t2.shop_id=t3.shop_id and t2.event_day=t3.event_day
			   |)  order by shop_id,a_event_day
			   |""".stripMargin).show()

输出

+-------+-----------+-------+
|shop_id|a_event_day| tongbi|
+-------+-----------+-------+
|      1|    2022-01|   null|
|      1|    2022-02|   null|
|      1|    2022-03|   null|
|      1|    2022-05|   null|
|      1|    2022-06|   null|
|      1|    2023-01|    0.0|
|      1|    2023-02|    0.0|
|      1|    2023-03|    0.0|
|      1|    2023-05|    0.0|
|      1|    2023-06|    0.0|
|     11|    2022-01|   null|
|     11|    2022-02|   null|
|     11|    2022-03|   null|
|     11|    2022-05|   null|
|     11|    2022-06|   null|
|     11|    2023-01| 0.3333|
|     11|    2023-02|    0.0|
|     11|    2023-03|-0.3333|
|     11|    2023-05|   -0.5|
|     11|    2023-06|    0.0|
+-------+-----------+-------+