SparkSQL 中g(shù)roup by、grouping sets尺栖、rollup和cube方法詳解

在平時的工作中嫡纠，經(jīng)常有按照不同維度篩選和統(tǒng)計數(shù)據(jù)的需求。拿視頻會員訂單數(shù)據(jù)來說吧延赌，運營人員要查看深圳市的成功下單數(shù)或則深圳市某一種產(chǎn)品的成功下單數(shù)或者某一種產(chǎn)品的所有成功下單數(shù)時除盏，每天的訂單數(shù)又很大，現(xiàn)查的話按照不同的維度去查詢又很慢挫以。此時本篇文章或許會幫助到你者蠕。

group by：主要用來對查詢的結(jié)果進行分組，相同組合的分組條件在結(jié)果集中只顯示一行記錄掐松□饴拢可以添加聚合函數(shù)。

grouping sets：對分組集中指定的組表達式的每個子集執(zhí)行g(shù)roup by大磺，group by A,B grouping sets(A,B)就等價于 group by A union group by B,其中A和B也可以是一個集合抡句，比如group by A,B,C grouping sets((A,B),(A,C))。

rollup：在指定表達式的每個層次級別創(chuàng)建分組集杠愧。group by A,B,C with rollup首先會對(A待榔、B、C)進行g(shù)roup by，然后對(A锐锣、B)進行g(shù)roup by腌闯，然后是(A)進行g(shù)roup by，最后對全表進行g(shù)roup by操作雕憔。

cube:為指定表達式集的每個可能組合創(chuàng)建分組集姿骏。首先會對(A、B斤彼、C)進行g(shù)roup by分瘦，然后依次是(A、B)畅卓，(A擅腰、C)，(A)翁潘，(B趁冈、C)，(B)拜马，( C)渗勘，最后對全表進行g(shù)roup by操作。

數(shù)據(jù)庫中會員訂單的數(shù)據(jù)映射的對象如下：

case class MemberOrderInfo(area:String,memberType:String,product:String,price:Int)

會員訂單表中的數(shù)據(jù)如下：

    import sqlContext.implicits._
    val orders=Seq(
      MemberOrderInfo("深圳","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員3個月",70),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員12個月",300),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員3個月",60),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員3個月",60),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員6個月",120),
      MemberOrderInfo("深圳","黃金會員","黃金會員1個月",15),
      MemberOrderInfo("深圳","黃金會員","黃金會員1個月",15),
      MemberOrderInfo("深圳","黃金會員","黃金會員3個月",45),
      MemberOrderInfo("深圳","黃金會員","黃金會員12個月",180),
      MemberOrderInfo("北京","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("北京","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("北京","鉑金會員","鉑金會員3個月",60),
      MemberOrderInfo("北京","黃金會員","黃金會員3個月",45),
      MemberOrderInfo("上海","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("上海","鉆石會員","鉆石會員1個月",25),
      MemberOrderInfo("上海","鉑金會員","鉑金會員3個月",60),
      MemberOrderInfo("上海","黃金會員","黃金會員3個月",45)
    )
    //把seq轉(zhuǎn)換成DataFrame
   val memberDF:DataFrame =orders.toDF()
    //把DataFrame注冊成臨時表
   memberDF.registerTempTable("orderTempTable")

接下來我們通過操作 orderTempTable 來看一下grouping sets俩莽、group by旺坠、rollup和cube具體如何使用。

1.group by

group by是SELECT語句的從句扮超，用來指定查詢分組條件取刃，主要用來對查詢的結(jié)果進行分組，相同組合的分組條件在結(jié)果集中只顯示一行記錄出刷。使用group by從句時候璧疗，通過添加聚合函數(shù)（主要有COUNT()、SUM馁龟、MAX()崩侠、MIN()等）可以使數(shù)據(jù)聚合。

 sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product")

執(zhí)行結(jié)果如下坷檩，可以看到結(jié)果中按照area,memberType,product來做聚合sum操作却音。

+----+----------+--------+---+
|area|memberType|product |total|
+----+----------+--------+---+
|深圳  |鉑金會員      |鉑金會員6個月 |120|
|深圳  |黃金會員      |黃金會員12個月|180|
|深圳  |鉆石會員      |鉆石會員3個月 |70 |
|深圳  |黃金會員      |黃金會員3個月 |45 |
|深圳  |鉆石會員      |鉆石會員12個月|300|
|北京  |黃金會員      |黃金會員3個月 |45 |
|深圳  |鉆石會員      |鉆石會員1個月 |50 |
|深圳  |黃金會員      |黃金會員1個月 |30 |
|深圳  |鉑金會員      |鉑金會員3個月 |120|
|北京  |鉆石會員      |鉆石會員1個月 |50 |
|北京  |鉑金會員      |鉑金會員3個月 |60 |
|上海  |黃金會員      |黃金會員3個月 |45 |
|上海  |鉆石會員      |鉆石會員1個月 |50 |
|上海  |鉑金會員      |鉑金會員3個月 |60 |
+----+----------+--------+---+

2.grouping sets

a.grouping sets是group by子句更進一步的擴展, 它讓你能夠定義多個數(shù)據(jù)分組。這樣做使聚合更容易, 并且因此使得多維數(shù)據(jù)分析更容易矢炼。
b.夠用grouping sets在同一查詢中定義多個分組

    sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product grouping sets(area,memberType,product)")

上面的語句輸出結(jié)果如下系瓢，可以看到使用grouping sets(area,memberType,product)會分別對這3個維度進行g(shù)roup by，也可以grouping sets ((area,memberType),(area,product)))此時相當于group by (area,memberType) union group by (area,product),也就是說grouping sets 后面可以指定你想要的各種維度組合句灌。

+----+----------+--------+-----+
|area|memberType|product |total|
+----+----------+--------+-----+
|null|null      |鉑金會員3個月 |240  |
|null|鉑金會員      |null    |360  |
|上海  |null      |null    |155  |
|null|鉆石會員      |null    |520  |
|null|null      |鉆石會員12個月|300  |
|null|null      |黃金會員12個月|180  |
|null|null      |鉆石會員3個月 |70   |
|null|null      |黃金會員3個月 |135  |
|深圳  |null      |null    |915  |
|null|null      |鉆石會員1個月 |150  |
|null|null      |黃金會員1個月 |30   |
|null|黃金會員      |null    |345  |
|北京  |null      |null    |155  |
|null|null      |鉑金會員6個月 |120  |
+----+----------+--------+-----+

3.rollup

rollup 是根據(jù)維度在數(shù)據(jù)結(jié)果集中進行的聚合操作夷陋。
group by A,B,C with rollup首先會對(A、B、C)進行g(shù)roup by肌稻，然后對(A、B)進行g(shù)roup by匕荸，然后是(A)進行g(shù)roup by爹谭，最后對全表進行g(shù)roup by操作。

    sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with rollup")

輸出結(jié)果中榛搔，可以group by A,B,C with rollup诺凡，的確是上述幾種group by的并集。

+----+----------+--------+-----+
|area|memberType|product |total|
+----+----------+--------+-----+
|上海  |null      |null    |155  |
|北京  |鉑金會員      |null    |60   |
|北京  |鉆石會員      |null    |50   |
|上海  |鉆石會員      |鉆石會員1個月 |50   |
|深圳  |黃金會員      |黃金會員1個月 |30   |
|深圳  |鉆石會員      |鉆石會員12個月|300  |
|北京  |黃金會員      |黃金會員3個月 |45   |
|深圳  |鉆石會員      |鉆石會員3個月 |70   |
|北京  |鉑金會員      |鉑金會員3個月 |60   |
|上海  |鉑金會員      |null    |60   |
|上海  |鉆石會員      |null    |50   |
|深圳  |黃金會員      |null    |255  |
|深圳  |null      |null    |915  |
|上海  |黃金會員      |黃金會員3個月 |45   |
|深圳  |鉑金會員      |鉑金會員3個月 |120  |
|深圳  |鉆石會員      |鉆石會員1個月 |50   |
|上海  |鉑金會員      |鉑金會員3個月 |60   |
|北京  |黃金會員      |null    |45   |
|深圳  |鉑金會員      |null    |240  |
|null|null      |null    |1225 |
|深圳  |鉆石會員      |null    |420  |
|北京  |null      |null    |155  |
|北京  |鉆石會員      |鉆石會員1個月 |50   |
|深圳  |黃金會員      |黃金會員12個月|180  |
|深圳  |鉑金會員      |鉑金會員6個月 |120  |
|深圳  |黃金會員      |黃金會員3個月 |45   |
|上海  |黃金會員      |null    |45   |
+----+----------+--------+-----+

4.cube

group by A,B,C with cube践惑，則首先會對(A腹泌、B、C)進行g(shù)roup by尔觉，然后依次是(A凉袱、B)，(A侦铜、C)专甩，(A)，(B钉稍、C)涤躲，(B)，( C)贡未，最后對全表進行g(shù)roup by操作种樱。

    sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube")

+----+----------+--------+-----+
|area|memberType|product |total|
+----+----------+--------+-----+
|深圳  |null      |黃金會員12個月|180  |
|深圳  |null      |鉆石會員3個月 |70   |
|深圳  |null      |黃金會員3個月 |45   |
|null|null      |鉑金會員3個月 |240  |
|北京  |null      |鉑金會員3個月 |60   |
|null|鉑金會員      |null    |360  |
|上海  |null      |null    |155  |
|北京  |鉑金會員      |null    |60   |
|null|鉆石會員      |null    |520  |
|北京  |鉆石會員      |null    |50   |
|上海  |鉆石會員      |鉆石會員1個月 |50   |
|深圳  |黃金會員      |黃金會員1個月 |30   |
|null|null      |鉆石會員12個月|300  |
|深圳  |鉆石會員      |鉆石會員12個月|300  |
|null|黃金會員      |黃金會員12個月|180  |
|null|鉑金會員      |鉑金會員6個月 |120  |
|null|黃金會員      |黃金會員3個月 |135  |
|深圳  |null      |鉆石會員1個月 |50   |
|深圳  |null      |黃金會員1個月 |30   |
|北京  |黃金會員      |黃金會員3個月 |45   |
|null|null      |黃金會員12個月|180  |
|上海  |null      |鉑金會員3個月 |60   |
|null|null      |鉆石會員3個月 |70   |
|深圳  |鉆石會員      |鉆石會員3個月 |70   |
|null|null      |黃金會員3個月 |135  |
|北京  |鉑金會員      |鉑金會員3個月 |60   |
|北京  |null      |黃金會員3個月 |45   |
|上海  |鉑金會員      |null    |60   |
|上海  |鉆石會員      |null    |50   |
|深圳  |黃金會員      |null    |255  |
|null|黃金會員      |黃金會員1個月 |30   |
|深圳  |null      |null    |915  |
|null|鉆石會員      |鉆石會員12個月|300  |
|上海  |黃金會員      |黃金會員3個月 |45   |
|深圳  |鉑金會員      |鉑金會員3個月 |120  |
|null|null      |鉆石會員1個月 |150  |
|深圳  |鉆石會員      |鉆石會員1個月 |50   |
|null|null      |黃金會員1個月 |30   |
|北京  |null      |鉆石會員1個月 |50   |
|上海  |鉑金會員      |鉑金會員3個月 |60   |
|上海  |null      |黃金會員3個月 |45   |
|null|鉆石會員      |鉆石會員3個月 |70   |
|深圳  |null      |鉑金會員6個月 |120  |
|null|黃金會員      |null    |345  |
|北京  |黃金會員      |null    |45   |
|深圳  |null      |鉑金會員3個月 |120  |
|深圳  |鉑金會員      |null    |240  |
|null|null      |null    |1225 |
|深圳  |鉆石會員      |null    |420  |
|北京  |null      |null    |155  |
|null|鉑金會員      |鉑金會員3個月 |240  |
|上海  |null      |鉆石會員1個月 |50   |
|null|鉆石會員      |鉆石會員1個月 |150  |
|深圳  |null      |鉆石會員12個月|300  |
|北京  |鉆石會員      |鉆石會員1個月 |50   |
|深圳  |黃金會員      |黃金會員12個月|180  |
|深圳  |鉑金會員      |鉑金會員6個月 |120  |
|深圳  |黃金會員      |黃金會員3個月 |45   |
|null|null      |鉑金會員6個月 |120  |
|上海  |黃金會員      |null    |45   |
+----+----------+--------+-----+

5.應用

經(jīng)過group by、grouping sets俊卤、rollup和cube處理過后的數(shù)據(jù)可以直接存儲到MySQL等數(shù)據(jù)庫中嫩挤。文章開頭中提到的需求，可以根據(jù)運營提出的篩選維度進行cube分析瘾蛋，就可以得到各種維度組合下的訂單統(tǒng)計結(jié)果俐镐。比如要獲取總的定單數(shù)〔负撸可以如下處理:

    val cubeDF = sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube")
cubeDF .registerTempTable("resultTempTable")
//下面SQL執(zhí)行的結(jié)果就是所有訂單的總數(shù)
 sqlContext.sql("select * from resultTempTable where area=null and memberType=null and product =null")

最后編輯于：2020.10.13 15:47:38

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末佩抹，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子取董，更是在濱河造成了極大的恐慌棍苹，老刑警劉巖，帶你破解...
沈念sama閱讀 222,104評論 6贊 515
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件茵汰，死亡現(xiàn)場離奇詭異枢里，居然都是意外死亡，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,816評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門栏豺，熙熙樓的掌柜王于貴愁眉苦臉地迎上來彬碱，“玉大人，你說我怎么就攤上這事奥洼∠锾郏” “怎么了？”我有些...
開封第一講書人閱讀 168,697評論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵灵奖，是天一觀的道長嚼沿。經(jīng)常有香客問我，道長瓷患，這世上最難降的妖魔是什么骡尽？我笑而不...
開封第一講書人閱讀 59,836評論 1贊 298
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮擅编，結(jié)果婚禮上攀细，老公的妹妹穿的比我還像新娘。我一直安慰自己爱态，他們只是感情好辨图，可當我...
茶點故事閱讀 68,851評論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著肢藐，像睡著了一般故河。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上吆豹，一...
開封第一講書人閱讀 52,441評論 1贊 310
城市分裂傳說
那天鱼的，我揣著相機與錄音，去河邊找鬼痘煤。笑死凑阶，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的衷快。我是一名探鬼主播宙橱，決...
沈念sama閱讀 40,992評論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼蘸拔！你這毒婦竟也來了师郑？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,899評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤调窍，失蹤者是張志新（化名）和其女友劉穎宝冕，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體邓萨，經(jīng)...
沈念sama閱讀 46,457評論 1贊 318
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡地梨，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,529評論 3贊 341
?白月光啟示錄
正文我和宋清朗相戀三年菊卷，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片宝剖。...
茶點故事閱讀 40,664評論 1贊 352
活死人
序言：一個原本活蹦亂跳的男人離奇死亡洁闰，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出万细，到底是詐尸還是另有隱情渴庆，我是刑警寧澤，帶...
沈念sama閱讀 36,346評論 5贊 350
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布雅镊，位于F島的核電站，受9級特大地震影響刃滓，放射性物質(zhì)發(fā)生泄漏仁烹。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 42,025評論 3贊 334
男人毒藥：我在死后第九天來索命
文/蒙蒙一咧虎、第九天我趴在偏房一處隱蔽的房頂上張望卓缰。院中可真熱鬧，春花似錦砰诵、人聲如沸征唬。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,511評論 0贊 24
一樁弒父案茁彭，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽总寒。三九已至，卻和暖如春理肺，著一層夾襖步出監(jiān)牢的瞬間摄闸，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,611評論 1贊 272
情欲美人皮
我被黑心中介騙來泰國打工妹萨，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留年枕，地道東北人。一個月前我還...
沈念sama閱讀 49,081評論 3贊 377
代替公主和親
正文我出身青樓乎完，卻偏偏與公主長得像熏兄，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子树姨，可洞房花燭夜當晚...
茶點故事閱讀 45,675評論 2贊 359

SparkSQL 中g(shù)roup by座每、grouping sets前鹅、rollup和cube方法詳解