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

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|
+-------+-----------+-------+

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,651评论 6赞 501
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,468评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,931评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,218评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,234评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,198评论 1赞 299
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,084评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,926评论 0赞 274
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,341评论 1赞 311
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,563评论 2赞 333
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,731评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,430评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,036评论 3赞 326
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,676评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,829评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,743评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,629评论 2赞 354

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

推荐阅读更多精彩内容