无需硬编码即可在 Amazon Athena 中逆向旋转列 [英] Unpivot Columns inside of Amazon Athena without hardcoding

问题描述

我正在 AWS Athena 内部编写查询.原始表类似于:

I am writing a query inside of AWS Athena. The Origianl Table is something like:

employee|manager1|manager2|manager3|... | manager10
   12345|A .     |B .     |C .     |... | (null)
   54321|I .     |II .    |III .   |... | X

结果应该是这样的:

employee | manager
12345 .  | A
12345 .  | B
12345 .  | C
54321    | I
54321 .  | II
54321 .  |...
54321 .  | X

我尝试在 Athena 中使用 unpivot，但遇到了一些语法错误.此外，使用 unpivot 意味着我需要对所有这些列进行硬编码.

I tried to use unpivot inside of Athena but got some syntax errors. Also, with unpivot it means i need to hardcoding all these columns.

这是我第一次与 Athena 合作，我不知道 Athena 能否实现.如果是，正确的方法是什么?

This is my first time with Athena and I don't know if Athena can achieve it or not. And if yes, what's the correct way?

推荐答案

我刚刚在 Athena 中使用数组进行了逆透视.我的案例没有可变数量的列，因此您可能需要对此进行调整，但这至少应该可以为您提供一个逆轴:

I just did an unpivot in Athena with arrays. My case didn't have a variable number of columns so you may need to adjust for that, but this should at least work to give you an unpivot:

WITH dataset AS (
  SELECT
    employee,
    ARRAY[manager1, manager2, manager3] AS managers
  FROM
    (SELECT 'A' AS employee, '1' AS manager1, '2' AS manager2, '3' AS manager3)
)
SELECT employee, manager FROM dataset
CROSS JOIN UNNEST(managers) as t(manager)

这会给你输出:

查看文档以获取更多信息和示例:https://docs.aws.amazon.com/athena/latest/ug/creating-arrays.html

Check the documentation for more info and examples: https://docs.aws.amazon.com/athena/latest/ug/creating-arrays.html

这篇关于无需硬编码即可在 Amazon Athena 中逆向旋转列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！