presto - 在 presto 中将行扩展到列
问题描述
有什么方法可以有效地将行扩展到列?
我尝试分别用'where team = 1'和'where team = 2'过滤原始数据集,首先相应地获取数据集1和数据集2,然后在收入级别上加入两个数据集。但是,当income_level 具有太多不同的值时,会很不方便。有什么有效的方法可以得到我想要的结果吗?
解决方案
Prestodb 提供了一项map_agg
功能,可以帮助您将长数据转换为您正在寻找的宽格式。不幸的是,似乎没有一种方法可以动态创建列名,但是这种方法应该比加入每个团队更有效(并且输入更少:))。
WITH raw_data AS (
SELECT 1 AS team, 'a' AS income_level, 1 AS time, 11 AS ord
UNION
SELECT 1 AS team, 'b' AS income_level, 2 AS time, 12 AS ord
UNION
SELECT 1 AS team, 'c' AS income_level, 3 AS time, 13 AS ord
UNION
SELECT 2 AS team, 'a' AS income_level, 4 AS time, 14 AS ord
UNION
SELECT 2 AS team, 'b' AS income_level, 5 AS time, 15 AS ord
UNION
SELECT 2 AS team, 'c' AS income_level, 6 AS time, 16 AS ord
UNION
SELECT 3 AS team, 'a' AS income_level, 7 AS time, 17 AS ord
UNION
SELECT 3 AS team, 'b' AS income_level, 8 AS time, 18 AS ord
UNION
SELECT 3 AS team, 'c' AS income_level, 9 AS time, 19 AS ord
)
SELECT
income_level,
team_time[1] AS time_1,
team_ord[1] AS ord_1,
team_time[2] AS time_2,
team_ord[2] AS ord_2,
team_time[3] AS time_3,
team_ord[3] AS ord_3
FROM (
SELECT
income_level,
map_agg(team, time) AS team_time,
map_agg(team, ord) AS team_ord
FROM raw_data
GROUP BY income_level
);
输出:
| income_level | time_1 | ord_1 | time_2 | ord_2 | time_3 | ord_3 |
|--------------|--------|-------|--------|-------|--------|-------|
| a | 1 | 11 | 4 | 14 | 7 | 17 |
| b | 2 | 12 | 5 | 15 | 8 | 18 |
| c | 3 | 13 | 6 | 16 | 9 | 19 |
这个网站提供了另一个例子来说明如何做到这一点。
推荐阅读
- javascript - 如何从不同的组件调用反应组件中的内部函数?
- python - Streamlit 示例在虚拟 conda 环境中可以正常工作,但我的测试应用程序不能
- javascript - 从谷歌表中取出两列并将它们放入一个新数组中
- javascript - 仅提交最后一个值
- tensorflow - Keras 自定义损失函数仅在训练期间访问附加参数
- c# - 将xml文件中节点的值设置为C#中节点的另一个值
- python - 在 conda 中使用巧克力的“choco outdated”的等效命令来找出过时的 conda 包
- vim - 如何用 Vim 替换 () 和 &commentstring 注释整行
- python - 如何在 pandas 的帮助下创建函数来读取文件夹中的所有文件并为每个文件创建不同的数据框?
- mongoose - Strapi 查询对象数组中包含某个元素的所有对象