如何在PostgreSQL中取消透视表 [英] How to unpivot a table in PostgreSQL
问题描述
由于我不熟悉Postgres函数，因此我在编写它时遇到了困难.我有多个表格以这种格式导入Postgres:
I am having difficulties writing a Postgres function, as I am not familiar with it. I have multiple tables to import into Postgres with this format:
id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
1 23 45 87 99
2 12 31 ...
我需要将其转换为以下格式:
which I need to convert into this format:
id | year | value
_________________
1 1960 23
1 1961 45
1 1962 87
...
2 1960 12
2 1961 31
...
我也可以想象该函数也可以像这样读取:
I would imagine the function too to read like this:
SELECT all-years FROM imported_table;
CREATE a new_table;
FROM min-year TO max-year LOOP
EXECUTE "INSERT INTO new_table (id, year, value) VALUES (id, year, value)";
END LOOP;
但是，为此我很难写出具体细节.对我来说，用PHP进行操作会更容易，但是我坚信直接在Postgres函数中进行操作会更清洁.
However, I'm having real trouble writing the nitty-gritty details for this. Would be easier for me to do that in PHP, but I am convinced that it's cleaner to do it directly in a Postgres-function.
年份(开始和结束)因表格而异.有时候，我什至只能每隔五年左右就拥有几年的时间...
The years (start and end) vary from table to table. And sometimes, I can even have years only for every fifth year or so ...
推荐答案
完全动态版本需要动态SQL.将plpgsql函数与EXECUTE:
A completely dynamic version requires dynamic SQL. Use a plpgsql function with EXECUTE:
对于 Postgres 9.2或更早版本(在实施LATERAL之前):
CREATE OR REPLACE FUNCTION f_unpivot_years92(_tbl regclass, VARIADIC _years int[])
RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
RETURN QUERY EXECUTE '
SELECT id
, unnest($1) AS year
, unnest(ARRAY["'|| array_to_string(_years, '","') || '"]) AS val
FROM ' || _tbl || '
ORDER BY 1, 2'
USING _years;
END
$func$ LANGUAGE plpgsql;
对于 Postgres 9.3或更高版本(使用LATERAL):
CREATE OR REPLACE FUNCTION f_unpivot_years(_tbl regclass, VARIADIC _years int[])
RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
RETURN QUERY EXECUTE (SELECT
'SELECT t.id, u.year, u.val
FROM ' || _tbl || ' t
LEFT JOIN LATERAL (
VALUES ' || string_agg(format('(%s, t.%I)', y, y), ', ')
|| ') u(year, val) ON true
ORDER BY 1, 2'
FROM unnest(_years) y
);
END
$func$ LANGUAGE plpgsql;
关于VARIADIC:
致电任意年份:
SELECT * FROM f_unpivot_years('tbl', 1961, 1964, 1963);
相同，传递一个实际数组:
Same, passing an actual array:
SELECT * FROM f_unpivot_years('tbl', VARIADIC '{1960,1961,1962,1963}'::int[]);
一长串连续年份:
SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2014)));
对于具有固定间隔的长列表(例如每5年一次):
For a long list with regular intervals (example for every 5 years):
SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));
根据要求输出.
该函数需要:
1.有效的表名-如果它是非法的，则用双引号引起来(例如'"CaMeL"').使用对象标识符类型regclass声明正确性并防止SQL注入.您可能希望对故事名称进行模式限定(如'public."CaMeL"').更多:
The function takes:
1. A valid table name - double-quoted if it's otherwise illegal (like '"CaMeL"'). Using the object identifier type regclass to assert correctness and defend against SQL injection. You may want to schema-qualify the tale name to be unambiguous (like 'public."CaMeL"'). More:
2. 任何与(双引号)列名称相对应的数字列表.
或 是一个实际的数组，前缀为关键字VARIADIC.
2. Any list of numbers corresponding to (double-quoted) column names.
Or an actual array, prefixed with the keyword VARIADIC.
不必以任何方式对列数组进行排序，但是表和列必须存在，否则会引发异常.
The array of columns does not have to be sorted in any way, but table and columns must exist or an exception is raised.
输出按id和year排序(与integer相同).如果要根据输入数组的排序顺序对年份进行排序，则将其设置为ORDER BY 1.不能严格保证按数组排序的顺序，但可以在当前实现中使用.有关此的更多信息:
Output is sorted by id and year (as integer). If you want years to be sorted according to the sort order of the input array, make it just ORDER BY 1. Sort order according to array is not strictly guaranteed, but works in the current implementation. More about that:
也适用于NULL值.
SQL小提琴 和示例.
-
带有SQL的PostgreSQL generate_series()用作参数
这篇关于如何在PostgreSQL中取消透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
