如何在AWS Athena中将行透视成列? [英] How to pivot rows into columns in AWS Athena?
本文介绍了如何在AWS Athena中将行透视成列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是AWS Athena的新手,它尝试将一些行转换为列,类似于此
I'm new to AWS Athena and trying to pivot some rows into columns, similar to the top answer in this StackOverflow post.
但是,当我尝试时:
SELECT column1, column2, column3
FROM data
PIVOT
(
MIN(column3)
FOR column2 IN ('VALUE1','VALUE2','VALUE3','VALUE4')
)
我收到错误消息:输入'('期望{',',')'}(}(服务:amazonathena;状态代码:400;错误代码:invalidrequestexception
有人知道如何完成我要在AWS Athena中实现的目标吗?
Does anyone know how to accomplish what I am trying to achieve in AWS Athena?
推荐答案
扩展@kadrach的答案.假设这样的表
Extending @kadrach 's answer. Assuming a table like this
uid | key | value1 | value2
----+-----+--------+--------
1 | A | 10 | 1000
1 | B | 20 | 2000
2 | A | 11 | 1001
2 | B | 21 | 2001
单列PIVOT的工作原理如下
Single column PIVOT works like this
SELECT
uid,
kv1['A'] AS A_v1,
kv1['B'] AS B_v1
FROM (
SELECT uid, map_agg(key, value1) kv1
FROM vtable
GROUP BY uid
)
结果:
uid | A_v1 | B_v1
----+------+-------
1 | 10 | 20
2 | 11 | 21
多列PIVOT的工作原理如下
Multi column PIVOT works like this
SELECT
uid,
kv1['A'] AS A_v1,
kv1['B'] AS B_v1,
kv2['A'] AS A_v2,
kv2['B'] AS B_v2
FROM (
SELECT uid,
map_agg(key, value1) kv1,
map_agg(key, value2) kv2
FROM vtable
GROUP BY uid
)
结果:
uid | A_v1 | B_v1 | A_v2 | B_v2
----+------+------+------+-----
1 | 10 | 20 | 1000 | 2000
2 | 11 | 21 | 1001 | 2001
这篇关于如何在AWS Athena中将行透视成列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文