雅典娜/普雷斯托-UNNEST MAP至专栏 [英] Athena/Presto - UNNEST MAP to columns

问题描述

假设我有一个这样的表，

Assume i have a table like this,

表格:"qa_list"

table: qa_list

    id   |  question_id |  question  | answer    |  
---------+--------------+------------+-------------
    1    |  100         |  question1 | answer    |  
    2    |  101         |  question2 | answer    |  
    3    |  102         |  question3 | answer    |  
    4    |  ... 
    ...  |  ...

和给出以下结果的查询(因为我找不到直接的方式来转置表格)，

and a query that gives below result (since I couldn't find a direct way to transpose the table),

表格:qa_map

   id   |  qa_map
--------+---------
    1   | {question1=answer,question2=answer,question3=answer, ....}

qa_map是任意数量的问题和答案的map_agg的结果.

Where qa_map is the result of a map_agg of arbitrary number of questions and answers.

是否可以将UNAEST qa_map映射为任意数量的列，如下所示?

Is there a way to UNNEST qa_map to an arbitrary number of columns as shown below?

    id   |  Question_1 |  Answer_1 |  Question_2 |  Answer_2 |  Question_3 | ....
---------+-------------+-----------+-------------+-----------+-------------+
    1    |  question   | answer    |  question   | answer    |  question   | ....

AWS Athena/Presto-0.172

AWS Athena/Presto-0.172

推荐答案

否，无法编写查询来根据数据生成不同数量的列.在开始执行查询之前，必须知道这些列.您所拥有的地图与您将要获得的地图非常接近.

No, there is no way to write a query that results in different number of columns depending on the data. The columns must be known before query execution starts. The map you have is as close as you are going to get.

如果您包括想要这样做的动机，则可能还有其他方法可以帮助您实现最终目标.

If you include your motivation for wanting to do this there may be other ways we can help you achieve your end goal.

这篇关于雅典娜/普雷斯托-UNNEST MAP至专栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！