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

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

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

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

rollup：在指定表達(dá)式的每個(gè)層次級別創(chuàng)建分組集。group by A,B,C with rollup首先會對(A、B、C)進(jìn)行g(shù)roup by，然后對(A、B)進(jìn)行g(shù)roup by，然后是(A)進(jìn)行g(shù)roup by，最后對全表進(jìn)行g(shù)roup by操作。

cube:為指定表達(dá)式集的每個(gè)可能組合創(chuàng)建分組集。首先會對(A、B、C)進(jìn)行g(shù)roup by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后對全表進(jìn)行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個(gè)月",25),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員1個(gè)月",25),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員3個(gè)月",70),
      MemberOrderInfo("深圳","鉆石會員","鉆石會員12個(gè)月",300),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員3個(gè)月",60),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員3個(gè)月",60),
      MemberOrderInfo("深圳","鉑金會員","鉑金會員6個(gè)月",120),
      MemberOrderInfo("深圳","黃金會員","黃金會員1個(gè)月",15),
      MemberOrderInfo("深圳","黃金會員","黃金會員1個(gè)月",15),
      MemberOrderInfo("深圳","黃金會員","黃金會員3個(gè)月",45),
      MemberOrderInfo("深圳","黃金會員","黃金會員12個(gè)月",180),
      MemberOrderInfo("北京","鉆石會員","鉆石會員1個(gè)月",25),
      MemberOrderInfo("北京","鉆石會員","鉆石會員1個(gè)月",25),
      MemberOrderInfo("北京","鉑金會員","鉑金會員3個(gè)月",60),
      MemberOrderInfo("北京","黃金會員","黃金會員3個(gè)月",45),
      MemberOrderInfo("上海","鉆石會員","鉆石會員1個(gè)月",25),
      MemberOrderInfo("上海","鉆石會員","鉆石會員1個(gè)月",25),
      MemberOrderInfo("上海","鉑金會員","鉑金會員3個(gè)月",60),
      MemberOrderInfo("上海","黃金會員","黃金會員3個(gè)月",45)
    )
    //把seq轉(zhuǎn)換成DataFrame
   val memberDF:DataFrame =orders.toDF()
    //把DataFrame注冊成臨時(shí)表
   memberDF.registerTempTable("orderTempTable")

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

1.group by

group by是SELECT語句的從句，用來指定查詢分組條件，主要用來對查詢的結(jié)果進(jìn)行分組，相同組合的分組條件在結(jié)果集中只顯示一行記錄。使用group by從句時(shí)候，通過添加聚合函數(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個(gè)月 |120|
|深圳  |黃金會員      |黃金會員12個(gè)月|180|
|深圳  |鉆石會員      |鉆石會員3個(gè)月 |70 |
|深圳  |黃金會員      |黃金會員3個(gè)月 |45 |
|深圳  |鉆石會員      |鉆石會員12個(gè)月|300|
|北京  |黃金會員      |黃金會員3個(gè)月 |45 |
|深圳  |鉆石會員      |鉆石會員1個(gè)月 |50 |
|深圳  |黃金會員      |黃金會員1個(gè)月 |30 |
|深圳  |鉑金會員      |鉑金會員3個(gè)月 |120|
|北京  |鉆石會員      |鉆石會員1個(gè)月 |50 |
|北京  |鉑金會員      |鉑金會員3個(gè)月 |60 |
|上海  |黃金會員      |黃金會員3個(gè)月 |45 |
|上海  |鉆石會員      |鉆石會員1個(gè)月 |50 |
|上海  |鉑金會員      |鉑金會員3個(gè)月 |60 |
+----+----------+--------+---+

2.grouping sets

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

    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è)維度進(jìn)行g(shù)roup by，也可以grouping sets ((area,memberType),(area,product)))此時(shí)相當(dāng)于group by (area,memberType) union group by (area,product),也就是說grouping sets 后面可以指定你想要的各種維度組合。

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

3.rollup

rollup 是根據(jù)維度在數(shù)據(jù)結(jié)果集中進(jìn)行的聚合操作。
group by A,B,C with rollup首先會對(A、B、C)進(jìn)行g(shù)roup by，然后對(A、B)進(jìn)行g(shù)roup by，然后是(A)進(jìn)行g(shù)roup by，最后對全表進(jìn)行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個(gè)月 |50   |
|深圳  |黃金會員      |黃金會員1個(gè)月 |30   |
|深圳  |鉆石會員      |鉆石會員12個(gè)月|300  |
|北京  |黃金會員      |黃金會員3個(gè)月 |45   |
|深圳  |鉆石會員      |鉆石會員3個(gè)月 |70   |
|北京  |鉑金會員      |鉑金會員3個(gè)月 |60   |
|上海  |鉑金會員      |null    |60   |
|上海  |鉆石會員      |null    |50   |
|深圳  |黃金會員      |null    |255  |
|深圳  |null      |null    |915  |
|上海  |黃金會員      |黃金會員3個(gè)月 |45   |
|深圳  |鉑金會員      |鉑金會員3個(gè)月 |120  |
|深圳  |鉆石會員      |鉆石會員1個(gè)月 |50   |
|上海  |鉑金會員      |鉑金會員3個(gè)月 |60   |
|北京  |黃金會員      |null    |45   |
|深圳  |鉑金會員      |null    |240  |
|null|null      |null    |1225 |
|深圳  |鉆石會員      |null    |420  |
|北京  |null      |null    |155  |
|北京  |鉆石會員      |鉆石會員1個(gè)月 |50   |
|深圳  |黃金會員      |黃金會員12個(gè)月|180  |
|深圳  |鉑金會員      |鉑金會員6個(gè)月 |120  |
|深圳  |黃金會員      |黃金會員3個(gè)月 |45   |
|上海  |黃金會員      |null    |45   |
+----+----------+--------+-----+

4.cube

group by A,B,C with cube，則首先會對(A、B、C)進(jìn)行g(shù)roup by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后對全表進(jìn)行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個(gè)月|180  |
|深圳  |null      |鉆石會員3個(gè)月 |70   |
|深圳  |null      |黃金會員3個(gè)月 |45   |
|null|null      |鉑金會員3個(gè)月 |240  |
|北京  |null      |鉑金會員3個(gè)月 |60   |
|null|鉑金會員      |null    |360  |
|上海  |null      |null    |155  |
|北京  |鉑金會員      |null    |60   |
|null|鉆石會員      |null    |520  |
|北京  |鉆石會員      |null    |50   |
|上海  |鉆石會員      |鉆石會員1個(gè)月 |50   |
|深圳  |黃金會員      |黃金會員1個(gè)月 |30   |
|null|null      |鉆石會員12個(gè)月|300  |
|深圳  |鉆石會員      |鉆石會員12個(gè)月|300  |
|null|黃金會員      |黃金會員12個(gè)月|180  |
|null|鉑金會員      |鉑金會員6個(gè)月 |120  |
|null|黃金會員      |黃金會員3個(gè)月 |135  |
|深圳  |null      |鉆石會員1個(gè)月 |50   |
|深圳  |null      |黃金會員1個(gè)月 |30   |
|北京  |黃金會員      |黃金會員3個(gè)月 |45   |
|null|null      |黃金會員12個(gè)月|180  |
|上海  |null      |鉑金會員3個(gè)月 |60   |
|null|null      |鉆石會員3個(gè)月 |70   |
|深圳  |鉆石會員      |鉆石會員3個(gè)月 |70   |
|null|null      |黃金會員3個(gè)月 |135  |
|北京  |鉑金會員      |鉑金會員3個(gè)月 |60   |
|北京  |null      |黃金會員3個(gè)月 |45   |
|上海  |鉑金會員      |null    |60   |
|上海  |鉆石會員      |null    |50   |
|深圳  |黃金會員      |null    |255  |
|null|黃金會員      |黃金會員1個(gè)月 |30   |
|深圳  |null      |null    |915  |
|null|鉆石會員      |鉆石會員12個(gè)月|300  |
|上海  |黃金會員      |黃金會員3個(gè)月 |45   |
|深圳  |鉑金會員      |鉑金會員3個(gè)月 |120  |
|null|null      |鉆石會員1個(gè)月 |150  |
|深圳  |鉆石會員      |鉆石會員1個(gè)月 |50   |
|null|null      |黃金會員1個(gè)月 |30   |
|北京  |null      |鉆石會員1個(gè)月 |50   |
|上海  |鉑金會員      |鉑金會員3個(gè)月 |60   |
|上海  |null      |黃金會員3個(gè)月 |45   |
|null|鉆石會員      |鉆石會員3個(gè)月 |70   |
|深圳  |null      |鉑金會員6個(gè)月 |120  |
|null|黃金會員      |null    |345  |
|北京  |黃金會員      |null    |45   |
|深圳  |null      |鉑金會員3個(gè)月 |120  |
|深圳  |鉑金會員      |null    |240  |
|null|null      |null    |1225 |
|深圳  |鉆石會員      |null    |420  |
|北京  |null      |null    |155  |
|null|鉑金會員      |鉑金會員3個(gè)月 |240  |
|上海  |null      |鉆石會員1個(gè)月 |50   |
|null|鉆石會員      |鉆石會員1個(gè)月 |150  |
|深圳  |null      |鉆石會員12個(gè)月|300  |
|北京  |鉆石會員      |鉆石會員1個(gè)月 |50   |
|深圳  |黃金會員      |黃金會員12個(gè)月|180  |
|深圳  |鉑金會員      |鉑金會員6個(gè)月 |120  |
|深圳  |黃金會員      |黃金會員3個(gè)月 |45   |
|null|null      |鉑金會員6個(gè)月 |120  |
|上海  |黃金會員      |null    |45   |
+----+----------+--------+-----+

5.應(yīng)用

經(jīng)過group by、grouping sets、rollup和cube處理過后的數(shù)據(jù)可以直接存儲到MySQL等數(shù)據(jù)庫中。文章開頭中提到的需求，可以根據(jù)運(yùn)營提出的篩選維度進(jìn)行cube分析，就可以得到各種維度組合下的訂單統(tǒng)計(jì)結(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")
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • pyspark.sql模塊 模塊上下文 Spark SQL和DataFrames的重要類： pyspark.sql...
    mpro閱讀 9,920評論 0贊 13
  • 專業(yè)考題類型管理運(yùn)行工作負(fù)責(zé)人一般作業(yè)考題內(nèi)容選項(xiàng)A選項(xiàng)B選項(xiàng)C選項(xiàng)D選項(xiàng)E選項(xiàng)F正確答案 變電單選GYSZ本規(guī)程...
    小白兔去釣魚閱讀 10,576評論 0贊 13
  • group by 用來在原始數(shù)據(jù)上創(chuàng)建聚合來將數(shù)據(jù)轉(zhuǎn)化為有用的信息。 基本的group by 列出個(gè)個(gè)部門的名稱...
    貓貓_tomluo閱讀 1,329評論 0贊 6
  • 實(shí)現(xiàn)效果 JS疊加、移動(dòng)、旋轉(zhuǎn)div 實(shí)現(xiàn)方式對比 1、添加div2、通過rotate插件實(shí)現(xiàn)旋轉(zhuǎn)3、通過鍵盤監(jiān)聽...
    過橋閱讀 2,346評論 0贊 1
  • 入選時(shí)間：2016年8月16日 入選級別：季級 入選理由：袁美鳳，女，企業(yè)工作人員。進(jìn)入時(shí)間管理名人堂一直是她最大...
    周助人閱讀 243評論 0贊 0

友情鏈接更多精彩內(nèi)容