sql - 通过旋转将 Presto 列转换为行

Question

这是所需的输入和所需的输出。我不熟悉 SQL 或 Presto 中使用的术语，文档似乎指向使用 map_agg但我认为这里的问题是动态创建列，但很好奇这是否可能发生在 a, b, ...列是已知且有限的。

我很想知道在 SQL 或 Presto 中的正确功能，当然如果可能的话。最好以一种不涉及为每个所需行 => 列手动添加子句的方式。必须有一种方法可以自动执行此操作，或者通过提供值列表来过滤转换为标题的行(如下面的 'a' 如何移动为列标题)
table_a :

id | key | value

 0 |   'a'  |   1
 1 |   'b'  |   2

然后变成 desired :

id | 'a' | 'b' 
 0    1    2

我能得到的最接近的是使用 map_agg获得一套 key: values可以在输出中一次拉一个。然而，所需的解决方案是不必明确列出每个 key我想最终输出，而不是爆炸或推出 kvs 的所有键:

with subquery_A as (
  select 
    id,   
    map_agg(A.key, A.value) as "kvs"
  from A as a
  group by 1
)

select 
  sub_a.id,
  sub_a.kvs['a'],
  sub_a.kvs['b']

from subquery_A as sub_a

Answer 1

在几乎所有的数据库服务器中，查询都会返回一组固定的列。 RDBMS 需要知道它需要输出哪些列才能正确处理查询。

因此，以一种或另一种方式，您通常需要明确定义输出列。

您的解决方案似乎在 Presto DB 上运行良好。以防万一，您想将其与其他内容进行比较，这里是标准 SQL 中的一个典型解决方案，它使用条件聚合在一组(固定的)列上旋转数据。它不使用 CTE，而且大多数 RDBMS 都支持这种语法。

SELECT
    id,
    MAX(CASE WHEN key = 'a' THEN value END) AS a
    MAX(CASE WHEN key = 'b' THEN value END) AS b
FROM table_a 
GROUP BY id