athena presto-从长到宽的多列

ne5o7dgx 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(393)

我是雅典娜的新手，我正在尝试理解如何将多个栏目从长格式转换为宽格式。好像 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

sql presto window-functions pivot amazon-athena

来源：https://stackoverflow.com/questions/63142257/athena-presto-multiple-columns-from-long-to-wide

2条答案

按热度按时间

axr492tv1#

可以使用窗口函数和条件聚合。这要求您提前知道可能的字母，以及每个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

赞(0）回复(0）举报 2021-07-29

pw136qt22#

雅典娜需要在查询时知道这些列，但下一个最好的方法是使用一个Map，正如您在问题中所暗示的那样。
实现所需结果的一种方法是查询( 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}

我通过手动排序Map键来作弊，如果您运行查询，它们将以任意顺序结束，但我认为这样更容易看到所需的结果。
请注意，这是假设没有重复的字母/数字组合，如果有，我认为它是未定义的值将最终在结果中。
还要注意的是，雅典娜的Map输出格式是不明确的，在某些情况下，您可能会得到无法解析的结果（例如，当键或值包含等号或逗号时）。因此，我建议将Map转换为json，并在应用程序代码中使用json解析器，例如。 CAST(map_agg(…) AS JSON) .

赞(0）回复(0）举报 2021-07-29

我来回答

athena presto-从长到宽的多列

2条答案

相关问题

热门标签

最新问答