未知行值的动态转置为 postgres 上的列名 [英] Dynamic transpose for unknown row value into column name on postgres

问题描述

我有这样的表:

<头>

customer_number	标签	value
1	地址	圣.约翰 1A
1	手机	111111111
1	电子邮件	[e-mail]
2	地址	圣.结婚231A
2	手机	222222222
2	电子邮件	[e-mail]

我想要新的表或视图，所以它变成了:

I want new table or view so it's become:

<头>

customer_number	地址	手机	电子邮件
1	圣.约翰 1A	111111111	[e-mail]
2	圣.结婚231A	222222222	[e-mail]

但未来可能会添加不同的标签，例如可能会有名为occupation的新标签.

but in the future there are possibility to add different label, for example there might be new label called occupation.

重要提示，我不知道标签列的值，所以它应该迭代到该列内的任何值.

Important to note, I don't know the value of the label column, so it's should iterate to any value inside that column.

有没有办法做到这一点?

Is there any way to do this?

推荐答案

你不能有一个动态"的数据透视因为查询的所有列的编号、名称和数据类型必须在查询实际执行之前(即在解析时) 为数据库所知.

You can't have a "dynamic" pivot as the number, names and data types of all columns of a query must be known to the database before the query is actually executed (i.e. at parse time).

我发现将内容聚合到 JSON 中更容易处理.

I find aggregating stuff into a JSON easier to deal with.

select customer_number,
       jsonb_object_agg(label, value) as props
from the_table
group by customer_number

如果您的前端可以直接处理 JSON 值，您可以到此为止.

If your frontend can deal with JSON values directly, you can stop here.

如果你真的需要一个每个属性只有一列的视图，你可以从 JSON 值中获取它们:

If you really need a view with one column per attribute, you can them from the JSON value:

select customer_number, 
       props ->> 'address' as address,
       props ->> 'phone' as phone,
       props ->> 'email' as email
from (       
  select customer_number,
         jsonb_object_agg(label, value) as props
  from the_table
  group by customer_number
) t

当添加新属性时，我发现这更容易管理.

I find this a bit easier to manage when new attributes are added.

如果你需要一个带有所有标签的视图，你可以创建一个存储过程来动态创建它.如果不同标签的数量变化不是太频繁，这可能是一个解决方案:

If you need a view with all labels, you can create a stored procedure to dynamically create it. If the number of different labels doesn't change too often, this might be a solution:

create procedure create_customer_view() 
as
$$
declare
  l_sql text;
  l_columns text;
begin
  select string_agg(distinct format('(props ->> %L) as %I', label, label), ', ')
    into l_columns
  from the_table;
  
  l_sql := 
    'create view customer_properties as 
     select customer_number, '||l_columns||' 
     from (
      select customer_number, jsonb_object_agg(label, value) as props
       from the_table 
       group by customer_number 
     ) t';
  execute l_sql;
end;
$$
language plpgsql;

然后使用以下方法创建视图:

Then create the view using:

call create_customer_view();  

在您的代码中只需使用:

And in your code just use:

select *
from customer_properties;

您可以安排该程序定期运行(例如，通过 Linux 上的 cron 作业)

You can schedule that procedure to run in regular intervals (e.g. through a cron job on Linux)

这篇关于未知行值的动态转置为 postgres 上的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！