Mysql, измените данные из длинного / высокий к широкому
Я имею данные в mysql таблице в длинном / высокий формат (описанный ниже) и хочу преобразовать его в широкий формат. Я могу сделать это использование просто sql?
Самый легкий объяснить с примером. Предположим, что у Вас есть информация о (страна, ключ, значение) для стран 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'
Существует ли путь в SQL для составления новой таблицы с данными в широком формате?
select distinct key from table;
//это получит меня все ключи.
1) Как я затем составляю таблицу с помощью этих основных элементов?
2) Как я затем заполняю значения таблицы?
Я вполне уверен, я могу сделать это с любым языком сценариев (мне нравится Python), но требуемый, чтобы знать, существует ли простой способ сделать это в mysql. Многим статистическим пакетам как R и STATA встроили эту команду, потому что это часто используется.
======
Чтобы быть более ясным, вот, желаемый ввод-вывод для простого случая:
Вход:
country attrName attrValue key (these are column names)
US President Obama 2
US Currency Dollar 3
China President Hu 4
China Currency Yuan 5
Вывод
country President Currency newPkey
US Obama Dollar 1
China Hu Yuan 2
3 ответа
Кросс-таблицы или сводные таблицы - вот ответ. Оттуда вы можете ВЫБРАТЬ ИЗ ... ВСТАВИТЬ В ... или создать ВИД из одного ВЫБРАТЬ.
Примерно так:
SELECT country,
MAX( IF( key='President', value, NULL ) ) AS President,
MAX( IF( key='Currency', value, NULL ) ) AS Currency,
...
FROM table
GROUP BY country;
Для получения дополнительной информации: http://dev.mysql.com/tech-resources/articles/wizard/index.html
Если бы вы использовали SQL Server, это было бы легко сделать с помощью UNPIVOT . Насколько мне известно, это не реализовано в MySQL, поэтому, если вы хотите сделать это (а я бы не советовал), вам, вероятно, придется генерировать SQL динамически, а это беспорядочно.
Думаю, я нашел решение, в котором используются VIEWS и INSERT INTO (как предлагает e4c5).
Вы должны сами получить свой список AttrNames / Keys, но MYSQL выполняет остальную тяжелую работу.
Для простого тестового примера, приведенного выше, создайте 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;
Если у вас больше 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;
Еще несколько советов
- : используйте NATURAL LEFT JOIN, и вам не нужно указывать предложение ON