Advertisement

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

阅读量：

首先创造数据，数据有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|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-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-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-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-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-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|

    +-------+-----------+-------+

全部评论 (0)

还没有任何评论哟~

相关文章推荐

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

首先创造数据，数据有4列分别是店铺id、订单id、订单金额、订单日期。 spark.createDataFrameSeq 1,11,10,20230101, 1,22,20,20230102, 1,3...

tableau -- 月销售额年同比增长

tableau——月销售额年同比增长 为方便以后制作数据分析报告，特此留下数据图表化的操作步骤，以便日后翻阅查询，如果不对，欢迎留言 同比增长=（本期同期）/同期 准备数据 数据背景：连锁超市的订单信...

通过门店销售明细表用SQL得到每月每个门店的销冠和按月的同比环比数据

假设我在Snowflake里有销售表，包含ID主键、门店ID、日期、销售员姓名和销售额，需要统计出每个月所有门店和各门店销售额最高的人，不一定是一个人，以及他所在的门店ID和月总销售额。 统计每个月份...

sql计算周月同比

方法一：当面对全量表，使用自关联笛卡尔积 先看一下笛卡尔积的结果 首先看一下全量表的明细数据 对明细数据进行聚合 select from selectcountascnt,appid,downdate...

python数据处理实例之月销售额时序变化及月环比可视化展现

理论基础知识可以看我之前的博客： 1、python之Numpy知识点详细总结 2、python最最最重要的数据分析工具之pandas 3、pandas之表连接与高级查询 也可以进入我的专栏：欢迎订阅哦...

【数据分析面试】7. 不同产品的月销售额（SQL）

题目：不同产品的月销售额 给定一个包含每月销售数据的表，编写一个查询以找到每个产品在每个月销售的总金额，并将每个产品作为输出表中的单独列。 示例： 输入： monthlysales表格 monthpr...

SQL实现占比、同比、环比指标分析

一、数据准备工作 表信息和表字段 / SourceServerType:MySQL SourceHost:localhost:3306 DATABASE :test01 / Tablestructur...

tableau实现年累计，月累计投放以及同比环比

1、创建参数：[日期参数] 2、创建新的字段：[近两年]放在筛选器上选True,逻辑为： 说明：根据日期参数，过滤出近两年的发放日期 [发放日期]=dateDATETRUNC'year',DATEAD...

sql 查询每月的销售金额

sql数据分月统计，表中只有每天的数据，现在要求求一年中每个月的统计数据（一条sql） SELECT MONTH那个日期的字段 , SUM需要统计的字段，比如销售额什么的 FROM 表 WHERE Y...

MYSQL求月份同比数据和环比数据

需求题目如下 1.使用MonthlyTotals子查询计算每个账户在每个月的总交易金额。这里使用了substr函数将transactiondate字段的前7个字符提取为年份和月份，并使用SUM函数计算...