我不熟悉Postgres函数,因此遇到了困难。我有多个表格以这种格式导入Postgres:
id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
1 23 45 87 99
2 12 31 ...
我需要将其转换为以下格式:
id | year | value
_________________
1 1960 23
1 1961 45
1 1962 87
...
2 1960 12
2 1961 31
...
我会想象函数也是如此阅读:
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函数中做起来更清洁。
年份(开始和结束)因表而异。有时,我什至只能每隔五年左右就拥有几年...
一个完全动态的版本需要动态SQL。使用plpgsql函数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);
同样,传递一个实际数组:
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年一次):
SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));
根据要求输出。
该函数采用:
1.一个有效的表名-如果否则是非法的(如'"CaMeL"'
),则用双引号引起来。使用对象标识符类型regclass
来声明正确性并防止SQL注入。您可能希望对故事名称进行模式限定,以使其模棱两可(例如'public."CaMeL"'
)。更多的:
2. 与(双引号)列名称相对应的任何数字列表。
或以关键字开头的实际数组VARIADIC
。
列的数组不必以任何方式进行排序,但是表和列必须存在或引发异常。
输出按id
和year
(按integer
)排序。如果要根据输入数组的排序顺序对年份进行排序,请使其为just ORDER BY 1
。不能严格保证按数组排序的顺序,但可以在当前实现中使用。有关更多信息:
也适用于NULL
值。
带有示例的SQL Fiddle。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句