Mysql，重塑从长/高到宽的数据

I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql?

我在长/高格式的mysql表中有数据（如下所述），并希望将其转换为宽格式。我可以只使用sql吗？

Easiest to explain with an example. Suppose you have information on (country, key, value) for M countries, N keys (e.g. keys can be income, political leader, area, continent, etc.)

最简单的解释一个例子。假设您有关于M个国家（国家，关键，价值）的信息，N个密钥（例如，密钥可以是收入，政治领导者，地区，大陆等）

Long format has 3 columns: country, key, value
  - M*N rows.
  e.g. 
  'USA', 'President', 'Obama'
   ...
  'USA', 'Currency', 'Dollar'

Wide format has N=16 columns: county, key1, ..., keyN
  - M rows
example: 
   country, President, ... , Currency
   'USA', 'Obama', ... , 'Dollar'

Is there a way in SQL to create a new table with the data in the wide format?

在SQL中有没有办法用宽格式的数据创建一个新表？

select distinct key from table;

// this will get me all the keys.

//这会给我所有的钥匙。

1) How do I then create the table using these key elements?

1）如何使用这些关键元素创建表格？

2) How do I then fill in the table values?

2）如何填写表格值？

I'm pretty sure I can do this with any scripting language (I like python), but wanted to know if there is an easy way to do this in mysql. Many statistical packages like R and STATA have this command built in because it is often used.

我很确定我可以用任何脚本语言（我喜欢python）来做这个，但是想知道在mysql中是否有一种简单的方法可以做到这一点。许多统计软件包（如R和STATA）都内置了此命令，因为它经常被使用。

======

To be more clear, here is the desired input output for a simple case:

更清楚的是，这是一个简单案例的理想输入输出：

Input:

输入：

country    attrName    attrValue     key  (these are column names)
US         President   Obama         2
US         Currency    Dollar        3
China      President   Hu            4
China      Currency    Yuan          5

Output

产量

country    President    Currency    newPkey
US         Obama        Dollar      1
China      Hu           Yuan        2

3 个解决方案

#1

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

交叉表或数据透视表就是答案。从那里你可以SELECT FROM ... INSERT INTO ...或者从单个SELECT创建一个VIEW。

Something like:

就像是：

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

For more info: http://dev.mysql.com/tech-resources/articles/wizard/index.html

有关更多信息：http：//dev.mysql.com/tech-resources/articles/wizard/index.html

#2

If you were using SQL Server, this would be easy using UNPIVOT. As far as I am aware, this is not implemented in MySQL, so if you want to do this (and I'd advise against it) you'll probably have to generate the SQL dynamically, and that's messy.

如果您使用的是SQL Server，那么使用UNPIVOT会很容易。据我所知，这不是在MySQL中实现的，所以如果你想这样做（我建议反对它），你可能必须动态生成SQL，这很麻烦。

#3

I think I found the solution, which uses VIEWS and INSERT INTO (as suggested by e4c5).

我想我找到了解决方案，它使用VIEWS和INSERT INTO（如e4c5所示）。

You have to get your list of AttrNames/Keys yourself, but MYSQL does the other heavy lifting.

你必须自己获得你的AttrNames / Keys列表，但是MYSQL做了另一个繁重的工作。

For the simple test case above, create the new_table with the appropriate columns (don't forget to have an auto-increment primary key as well). Then

对于上面的简单测试用例，使用适当的列创建new_table（不要忘记也有自动增量主键）。然后

CREATE VIEW a
AS SELECT country, attrValue
WHERE attrName="President";

CREATE VIEW b
AS SELECT country, attrValue
WHERE attrName="Currency";


INSERT INTO newtable(country, President, Currency)
SELECT a.country, a.attrValue, b.attrValue
FROM  a
INNER JOIN b  ON a.country=b.country;

If you have more attrNames, then create one view for each one and then adjust the last statement accordingly.

如果您有更多的attrNames，则为每个创建一个视图，然后相应地调整最后一个语句。

INSERT INTO newtable(country, President, Currency, Capital, Population)
SELECT a.country, a.attrValue, b.attrValue, c.attrValue, d.attrValue
FROM  a
INNER JOIN b  ON a.country=b.country
INNER JOIN c  ON a.country=c.country
INNER JOIN d  ON a.country=d.country;

Some more tips

更多提示

use NATURAL LEFT JOIN and you don't have to specify the ON clause
使用NATURAL LEFT JOIN，您不必指定ON子句

#1