我是Athena的新手，我正在努力了解如何将多列从长格式转换为宽格式。似乎需要presto，但我只能成功地将map_agg应用于一个变量。我认为我下面的最终结果可以用multimap_agg实现，但不能完全发挥作用。
下面我浏览我的步骤和数据。如果你有什么建议或问题，请告诉我！
首先，数据开始如下：
id | letter | number | value
------------------------------------
123 | a | 1 | 62
123 | a | 2 | 38
123 | a | 3 | 44
123 | b | 1 | 74
123 | b | 2 | 91
123 | b | 3 | 97
123 | c | 1 | 38
123 | c | 2 | 98
123 | c | 3 | 22
456 | a | 1 | 99
456 | a | 2 | 33
456 | a | 3 | 81
456 | b | 1 | 34
456 | b | 2 | 79
456 | b | 3 | 43
456 | c | 1 | 86
456 | c | 2 | 60
456 | c | 3 | 59
然后，我使用where子句和joining:进行过滤，将数据转换为以下内容
id | letter | 1 | 2 | 3
----------------------------
123 | a | 62 | 38 | 44
123 | b | 74 | 91 | 97
123 | c | 38 | 98 | 22
456 | a | 99 | 33 | 81
456 | b | 34 | 79 | 43
456 | c | 86 | 60 | 59
对于最终结果，我想将其转换为以下内容：
id | a_1 | a_2 | a_3 | b_1 | b_2 | b_3 | c_1 | c_2 | c_3
--------------------------------------------------------------------------
123 | 62 | 38 | 44 | 74 | 91 | 97 | 38 | 98 | 22
456 | 99 | 33 | 81 | 34 | 79 | 43 | 86 | 60 | 59
您可以使用窗口函数和条件聚合。这需要您提前知道可能的字母，以及每个id/字母元组的最大行数：
select
id,
max(case when letter = 'a' and rn = 1 then value end) a_1,
max(case when letter = 'a' and rn = 2 then value end) a_2,
max(case when letter = 'a' and rn = 3 then value end) a_3,
max(case when letter = 'b' and rn = 1 then value end) b_1,
max(case when letter = 'b' and rn = 2 then value end) b_2,
max(case when letter = 'b' and rn = 3 then value end) b_3,
max(case when letter = 'c' and rn = 1 then value end) c_1,
max(case when letter = 'c' and rn = 2 then value end) c_2,
max(case when letter = 'c' and rn = 3 then value end) c_3
from (
select
t.*,
row_number() over(partition by id, letter order by number) rn
from mytable t
) t
group by id
实际上，如果number总是1、2、3，那么您甚至不需要窗口函数：
select
id,
max(case when letter = 'a' and number = 1 then value end) a_1,
max(case when letter = 'a' and number = 2 then value end) a_2,
max(case when letter = 'a' and number = 3 then value end) a_3,
max(case when letter = 'b' and number = 1 then value end) b_1,
max(case when letter = 'b' and number = 2 then value end) b_2,
max(case when letter = 'b' and number = 3 then value end) b_3,
max(case when letter = 'c' and number = 1 then value end) c_1,
max(case when letter = 'c' and number = 2 then value end) c_2,
max(case when letter = 'c' and number = 3 then value end) c_3
from mytable t
group by id
Athena需要在查询时知道列，但接下来最好的方法是使用映射，正如您在问题中所暗示的那样。
获得您想要的结果的一种方法是此查询(the_table指问题中的第一个表，即具有id、letter、number和value列的表(：
SELECT
id,
map_agg(letter || '_' || CAST(number AS varchar), value) AS letter_number_value
FROM the_table
GROUP BY id
这给出了这样的结果：
id | letter_number_value
----+-------------------------------------------------------------------------
123 | {a_1=62, a_2=38, a_3=44, b_1=74, b_2=91, b_3=97, c_1=38, c_2=98, c_3=22}
456 | {a_1=99, a_2=33, a_3=81, b_1=34, b_2=79, b_3=43, c_1=86, c_2=60, c_3=59}
我手动排序映射键有点作弊，如果你运行查询，它们将以任意顺序结束，但我认为这样更容易看到结果是所需的。
请注意，这是假设没有重复的字母/数字组合，如果有，我认为结果中会出现哪个值是未定义的。
还要注意，Athena的地图输出格式不明确，在某些情况下，您可能会得到不可解析的结果(例如，键或值包含等号或逗号(。因此，我建议将映射转换为JSON，并在应用程序代码中使用JSON解析器，例如CAST(map_agg(…) AS JSON)。