如何使用聚合在配置单元中透视数据
如何使用聚合在配置单元中透视数据
提问于 2018-09-06 02:42:41
我有一个像下面这样的表数据，我想用聚合来透视数据。
ColumnA ColumnB ColumnC
1 complete Yes
1 complete Yes
2 In progress No
2 In progress No
3 Not yet started initiate
3 Not yet started initiate 想要像下面这样透视
ColumnA Complete In progress Not yet started
1 2 0 0
2 0 2 0
3 0 0 2我们有没有办法在hive或Impala中实现这一点？
回答 2
Stack Overflow用户
发布于 2018-09-06 03:12:50
将case与sum聚合一起使用：
select ColumnA,
sum(case when ColumnB='complete' then 1 else 0 end) as Complete,
sum(case when ColumnB='In progress' then 1 else 0 end) as In_progress,
sum(case when ColumnB='Not yet started' then 1 else 0 end) as Not_yet_started
from table
group by ColumnA
order by ColumnA --remove if order is not necessary
;Stack Overflow用户
发布于 2018-09-07 04:40:44
这就是在spark scala中实现这一点的方法。
val conf = spark.sparkContext.hadoopConfiguration
val test = spark.sparkContext.parallelize(List( ("1", "Complete", "yes"),
("1", "Complete", "yes"),
("2", "Inprogress", "no"),
("2", "Inprogress", "no"),
("3", "Not yet started", "initiate"),
("3", "Not yet started", "initiate"))
).toDF("ColumnA","ColumnB","ColumnC")
test.show()
val test_pivot = test.groupBy("ColumnA")
.pivot("ColumnB")
.agg(count("columnC"))
test_pivot.na.fill(0)show(false)
}和输出
|ColumnA|Complete|Inprogress|Not yet started|
+-------+--------+----------+---------------+
|3 |0 |0 |2 |
|1 |2 |0 |0 |
|2 |0 |2 |0 |
+-------+--------+----------+---------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52191603
复制相关文章
相似问题