在Apache Spark Java中获取整个数据集或仅列的摘要 [英] Getting the Summary of Whole Dataset or Only Columns in Apache Spark Java
问题描述
对于以下数据集,要获取 Col1 的总摘要值,我做了
For below Dataset, to get Total Summary values of Col1 , I did
import org.apache.spark.sql.functions._
val totaldf = df.groupBy("Col1").agg(lit("Total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"))
然后与
df.union(totaldf).orderBy(col("Col1"), col("Col2").desc).show(false)
df.
+-----------+-------+--------+--------------+
| Col1 | Col2 | price | displayPrice |
+-----------+-------+--------+--------------+
| Category1 | item1 | 15 | 14 |
| Category1 | item2 | 11 | 10 |
| Category1 | item3 | 18 | 16 |
| Category2 | item1 | 15 | 14 |
| Category2 | item2 | 11 | 10 |
| Category2 | item3 | 18 | 16 |
+-----------+-------+--------+--------------+
合并后.
+-----------+-------+-------+--------------+
| Col1 | Col2 | price | displayPrice |
+-----------+-------+-------+--------------+
| Category1 | Total | 44 | 40 |
| Category1 | item1 | 15 | 14 |
| Category1 | item2 | 11 | 10 |
| Category1 | item3 | 18 | 16 |
| Category2 | Total | 46 | 44 |
| Category2 | item1 | 16 | 15 |
| Category2 | item2 | 11 | 10 |
| Category2 | item3 | 19 | 17 |
+-----------+-------+-------+--------------+
现在,我想要以下所示的Whole Dataset的摘要,它将Col1摘要作为总计,并且具有All Col1和Col2的数据. 必需.
Now I want summary of Whole Dataset as Below , which will have Col1 Summary as Total and has the Data of All Col1 and Col2. Required.
+-----------+-------+-------+--------------+
| Col1 | Col2 | price | displayPrice |
+-----------+-------+-------+--------------+
| Total | Total | 90 | 84 |
| Category1 | Total | 44 | 40 |
| Category1 | item1 | 15 | 14 |
| Category1 | item2 | 11 | 10 |
| Category1 | item3 | 18 | 16 |
| Category2 | Total | 46 | 44 |
| Category2 | item1 | 16 | 15 |
| Category2 | item2 | 11 | 10 |
| Category2 | item3 | 19 | 17 |
+-----------+-------+-------+--------------+
我如何能够获得上述结果?
How Can I be able to achieve the above result?
推荐答案
从totaldf
创建第三个数据帧为
val finalTotalDF= totaldf.select(lit("Total").as("Col1"), lit("Total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"))
,然后将其用于union
作为
df.union(totaldf).union(finalTotalDF).orderBy(col("Col1"), col("Col2").desc).show(false)
您应该具有最终要求 dataframe
已更新
如果订购对您而言很重要,则应执行以下操作
If ordering matters to you then you should be changing T
of Total
in Col2
column to t
as total
by doing the following
import org.apache.spark.sql.functions._
val totaldf = df.groupBy("Col1").agg(lit("total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"))
val finalTotalDF= totaldf.select(lit("Total").as("Col1"), lit("total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"))
df.union(totaldf).union(finalTotalDF).orderBy(col("Col1").desc, col("Col2").desc).show(false)
您应该得到
+---------+-----+-----+------------+
|Col1 |Col2 |price|displayPrice|
+---------+-----+-----+------------+
|Total |total|90 |82 |
|Category2|total|46 |42 |
|Category2|item3|19 |17 |
|Category2|item2|11 |10 |
|Category2|item1|16 |15 |
|Category1|total|44 |40 |
|Category1|item3|18 |16 |
|Category1|item2|11 |10 |
|Category1|item1|15 |14 |
+---------+-----+-----+------------+
如果评论中提到的订购对您而言确实很重要
If ordering really matters to you as mentioned in the comment
我希望总数据量能达到最高水平,所以我希望排在最前,这实际上是我的要求
I want the total Data as prioirity,So I want that to be at the Top, which is actuall the requirement for me
然后您可以创建另一列以
Then you can create another column for sorting as
import org.apache.spark.sql.functions._
val totaldf = df.groupBy("Col1").agg(lit("Total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"), lit(1).as("sort"))
val finalTotalDF= totaldf.select(lit("Total").as("Col1"), lit("Total").as("Col2"), sum("price").as("price"), sum("displayPrice").as("displayPrice"), lit(0).as("sort"))
finalTotalDF.union(totaldf).union(df.withColumn("sort", lit(2))).orderBy(col("sort"), col("Col1"), col("Col2")).drop("sort").show(false)
您应该获得
+---------+-----+-----+------------+
|Col1 |Col2 |price|displayPrice|
+---------+-----+-----+------------+
|Total |Total|90 |82 |
|Category1|Total|44 |40 |
|Category2|Total|46 |42 |
|Category1|item1|15 |14 |
|Category1|item2|11 |10 |
|Category1|item3|18 |16 |
|Category2|item1|16 |15 |
|Category2|item2|11 |10 |
|Category2|item3|19 |17 |
+---------+-----+-----+------------+
这篇关于在Apache Spark Java中获取整个数据集或仅列的摘要的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!