spark sql實現(xiàn)月銷售額占比以及月環(huán)比和月同比(大廠數(shù)倉sql面試題)

1.首先創(chuàng)造數(shù)據(jù)迄靠，數(shù)據(jù)有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")

數(shù)據(jù)如下：

+-------+--------+------+----------+
|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.計算月銷售額占比
通過窗口函數(shù)實現(xiàn)僚饭，首先聚合月銷售額，之后再根據(jù)月銷售額集合為年銷售額胧砰，最后計算占比即可鳍鸵。

        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()

結(jié)果


+-------+-------+----+--------+--------+------+
|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.計算月環(huán)比
月環(huán)比計算公式=(本月-上月)/上月
為了方便說明分幾步進行：
3.1 聚合本月數(shù)據(jù)（為了方便查看進行了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")

輸出結(jié)果

+-------+---------+------+
|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 聚合上個月的數(shù)據(jù)
因為我們計算月環(huán)比需要上個月的銷售額，例如尉间，2022-02需要2022-01月的銷售額才能計算2022-02的月環(huán)比偿乖，即(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轉(zhuǎn)為其他值毫别，依據(jù)具體需求而定。

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

有些人會想到用快窗函數(shù)典格，然后使用lead或者lag獲取上一行或者下一行岛宦，但是這總方式是有問題的，如果月份不連續(xù)耍缴，那么計算的就是錯誤的砾肺。

4 月同比
今年1月和去年1月進行比較。邏輯和環(huán)比一樣只是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|
+-------+-----------+-------+
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市本鸣，隨后出現(xiàn)的幾起案子疫衩，更是在濱河造成了極大的恐慌，老刑警劉巖荣德，帶你破解...
    沈念sama閱讀 222,681評論 6贊 517
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件闷煤，死亡現(xiàn)場離奇詭異，居然都是意外死亡涮瞻，警方通過查閱死者的電腦和手機鲤拿，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評論 3贊 399
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來署咽，“玉大人近顷，你說我怎么就攤上這事∧瘢” “怎么了窒升？”我有些...
    開封第一講書人閱讀 169,421評論 0贊 362
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長慕匠。 經(jīng)常有香客問我饱须，道長，這世上最難降的妖魔是什么台谊？ 我笑而不...
    開封第一講書人閱讀 60,114評論 1贊 300
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任蓉媳，我火速辦了婚禮，結(jié)果婚禮上锅铅，老公的妹妹穿的比我還像新娘酪呻。我一直安慰自己，他們只是感情好盐须，可當我...
    茶點故事閱讀 69,116評論 6贊 398
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布玩荠。 她就那樣靜靜地躺著，像睡著了一般。 火紅的嫁衣襯著肌膚如雪阶冈。 梳的紋絲不亂的頭發(fā)上屉凯，一...
    開封第一講書人閱讀 52,713評論 1贊 312
  • 城市分裂傳說
    那天，我揣著相機與錄音眼溶，去河邊找鬼。 笑死晓勇，一個胖子當著我的面吹牛堂飞，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播绑咱，決...
    沈念sama閱讀 41,170評論 3贊 422
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼绰筛，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了描融？” 一聲冷哼從身側(cè)響起铝噩，我...
    開封第一講書人閱讀 40,116評論 0贊 277
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎窿克，沒想到半個月后骏庸，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 46,651評論 1贊 320
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡年叮，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,714評論 3贊 342
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年具被，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片只损。...
    茶點故事閱讀 40,865評論 1贊 353
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡一姿，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出跃惫，到底是詐尸還是另有隱情叮叹，我是刑警寧澤，帶...
    沈念sama閱讀 36,527評論 5贊 351
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布爆存，位于F島的核電站蛉顽，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏终蒂。R本人自食惡果不足惜蜂林，卻給世界環(huán)境...
    茶點故事閱讀 42,211評論 3贊 336
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望拇泣。 院中可真熱鬧噪叙，春花似錦、人聲如沸霉翔。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,699評論 0贊 25
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至子眶，卻和暖如春瀑凝，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背臭杰。 一陣腳步聲響...
    開封第一講書人閱讀 33,814評論 1贊 274
  • 情欲美人皮
    我被黑心中介騙來泰國打工粤咪， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人渴杆。 一個月前我還...
    沈念sama閱讀 49,299評論 3贊 379
  • 代替公主和親
    正文 我出身青樓寥枝，卻偏偏與公主長得像，于是被迫代替她去往敵國和親磁奖。 傳聞我的和親對象是個殘疾皇子囊拜，可洞房花燭夜當晚...
    茶點故事閱讀 45,870評論 2贊 361

推薦閱讀更多精彩內(nèi)容