SQL/Knime - 使用“Group By”转置表格

Question

标题可能不太好，但这是我能想到的最好的。我有类似于以下内容的东西：

Country  Tag    Weight
-----------------------
1        1      20
1        2      30
1        3      77
2        1      10
2        2      11
2        3      100

或者，以人类可读的形式表达：

Country  Tag    Weight
-----------------------
USA      Rock   20
USA      Pop    30
USA      Metal  77
Spain    Rock   10
Spain    Pop    11
Spain    Metal  100

使用SQL（创建视图）或Knime中的数据操作工具，我需要将数据转换成以下形式：

Country  Rock   Pop   Metal
----------------------------
USA      20     30    77
Spain    10     11    100

基本上，标签条目（唯一的）成为列，国家（唯一的）成为行ID，其重量值与其国家/标签相关联。

我已经尝试了我所能想到的所有Knime中的方法，但没有一个原始的SQL查询可以解决这个问题。在Knime中，我成功地创建了所需矩阵的结构（按国家和标签），但不知道如何填充实际的权重值，它们都是问号。我的工作解决方案是将数据输出到CSV文件中，而不是数据库，以我想要的形式。但这很笨重，很难保持同步。有什么建议吗？

- colithium

Answer 1

您正在寻找一个称为“枢轴表”或“交叉表”的东西。我对Knime不是很了解，但这就是您应该在Google上搜索的技术。如果Knime没有这个功能可用，那么您可以将CSV数据放入MS Excel中并将其作为枢轴表。

- LesterDove

---

每当我阅读有关数据透视表的内容时，它总是非常强调聚合。我没有意识到它们可以解决这样的问题。其中一个元素的总和就是该元素本身... 傻眼了。 - colithium

Answer 2

使用ANSI SQL，您可以使用以下语句：

  SELECT t.country,
         MAX(CASE WHEN t.tag = 'Rock' THEN t.weight END) AS Rock,
         MAX(CASE WHEN t.tag = 'Pop' THEN t.weight END) AS Pop,
         MAX(CASE WHEN t.tag = 'Metal' THEN t.weight END) AS Metal
    FROM YOUR_TABLE t
GROUP BY t.country

LesterDove是正确的 - 你需要一个旋转查询，将行数据转换为列数据。

PIVOT（和UNPIVOT）是ANSI语法，但支持有点滞后 - 我只知道SQL Server 2005+，Oracle 11g+支持。这是下一个最广泛支持的ANSI方法。

- OMG Ponies

---

不幸的是，我正在使用MySQL。而且我必须自动生成查询，因为有大约250个国家和200个标签。幸运的是，Knime具有良好的透视支持，我只是没有意识到它适用于这种情况。 - colithium

@OMG Pnies：您能否也发布一下T-SQL语法？我猜还有一些人可能对Oracle感兴趣。 - PPC

@PPC：以上代码在SQL Server、Oracle和PostgreSQL上都可以直接使用，无需修改。基本上，任何支持CASE语法的数据库都可以使用。我已经更新了问题并添加了“pivot”标签——任何人都可以查找与其所选数据库相匹配的组合，对于PIVOT关键字的支持是比较近期的。 - OMG Ponies

Answer 3

你可以使用KNIME中的Pivoting节点来完成这个技巧。 只需将标签列选择为Pivot列，将国家列选择为Group列，在聚合部分中选择重量列，并将聚合方法设置为sum即可。 再见， Tobias

- Tobias