在 PostgreSQL 中动态生成交叉表的列 [英] Dynamically generate columns for crosstab in PostgreSQL
问题描述
我正在尝试在 PostgreSQL 中创建 crosstab
查询,以便它自动生成 crosstab
列而不是对其进行硬编码.我编写了一个函数,可以动态生成crosstab
查询所需的列列表.想法是使用动态 sql 替换 crosstab
查询中此函数的结果.
I am trying to create crosstab
queries in PostgreSQL such that it automatically generates the crosstab
columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my crosstab
query. The idea is to substitute the result of this function in the crosstab
query using dynamic sql.
我知道如何在 SQL Server 中轻松完成此操作,但我对 PostgreSQL 的有限了解阻碍了我的进步.我正在考虑将生成列的动态列表的函数的结果存储到变量中,并使用它来动态构建 sql 查询.如果有人可以指导我,那就太好了.
I know how to do this easily in SQL Server, but my limited knowledge of PostgreSQL is hindering my progress here. I was thinking of storing the result of function that generates the dynamic list of columns into a variable and use that to dynamically build the sql query. It would be great if someone could guide me regarding the same.
-- Table which has be pivoted
CREATE TABLE test_db
(
kernel_id int,
key int,
value int
);
INSERT INTO test_db VALUES
(1,1,99),
(1,2,78),
(2,1,66),
(3,1,44),
(3,2,55),
(3,3,89);
-- This function dynamically returns the list of columns for crosstab
CREATE FUNCTION test() RETURNS TEXT AS '
DECLARE
key_id int;
text_op TEXT = '' kernel_id int, '';
BEGIN
FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP
text_op := text_op || key_id || '' int , '' ;
END LOOP;
text_op := text_op || '' DUMMY text'';
RETURN text_op;
END;
' LANGUAGE 'plpgsql';
-- This query works. I just need to convert the static list
-- of crosstab columns to be generated dynamically.
SELECT * FROM
crosstab
(
'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2',
'SELECT DISTINCT key FROM test_db ORDER BY 1'
)
AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace ..
-- .. this static list with a dynamically generated list of columns ?
推荐答案
你可以使用提供的 C 函数 crosstab_hash
来解决这个问题.
You can use the provided C function crosstab_hash
for this.
手册在这方面不是很清楚.在关于 crosstab()
的章节末尾提到了两个参数:
The manual is not very clear in this respect. It's mentioned at the end of the chapter on crosstab()
with two parameters:
您可以创建预定义的函数以避免必须写出每个查询中的结果列名称和类型.参见示例上一节.这种crosstab
形式的底层C函数名为crosstab_hash
.
You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples in the previous section. The underlying C function for this form of
crosstab
is namedcrosstab_hash
.
例如:
CREATE OR REPLACE FUNCTION f_cross_test_db(text, text)
RETURNS TABLE (kernel_id int, key1 int, key2 int, key3 int)
AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;
调用:
SELECT * FROM f_cross_test_db(
'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2'
,'SELECT DISTINCT key FROM test_db ORDER BY 1');
请注意,您需要为每个具有不同返回类型的 crosstab
函数创建一个不同的 crosstab_hash
函数.
Note that you need to create a distinct crosstab_hash
function for every crosstab
function with a different return type.
相关:
你的生成列列表的函数比较复杂,结果不正确(kernel_id
后缺少int
),可以替换使用此 SQL 查询:
Your function to generate the column list is rather convoluted, the result is incorrect (int
missing after kernel_id
), it can be replaced with this SQL query:
SELECT 'kernel_id int, '
|| string_agg(DISTINCT key::text, ' int, ' ORDER BY key::text)
|| ' int, DUMMY text'
FROM test_db;
而且无论如何也不能动态使用.
And it cannot be used dynamically anyway.
这篇关于在 PostgreSQL 中动态生成交叉表的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!