主页 / dba / 问题 / 261433
Accepted
jgrocha
Asked: 2020-03-08 10:53:10 +0800 CST

通过将列转换为行来重塑表格

  • 772

问题

我正在尝试重塑包含许多列的表格。我正在尝试独立于特定表进行操作，因此我正在尝试对任何表进行操作。

让我们使用一个非常简单的表格foo。

CREATE TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

select * from foo;

| id|  a  |  b |  c  |
|---|-----|----|-----|
|  1| ant | cat|chimp|
|  2|grape|mint|basil|

我想将 columna和b行转换c为行。

此查询有效（对于此特定表）：

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS colvalue
FROM foo;

|id|colname|colvalue|
|--|-------|--------|
| 1|  a    |  ant   |
| 1|  b    |  cat   |
| 1|  c    | chimp  |
| 2|  a    | grape  |
| 2|  b    | mint   |
| 2|  c    | basil  |

但我想让它适用于任何有很多列的表。

我已经做了什么

要获取我想要在行中转换的所有列，我可以使用：

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name   = 'foo' and column_name ~ '^[a-z]$';

因此，使用前面的查询，我可以执行以下操作：

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( array[a, b, c] ) AS colvalue
FROM foo, tablecolumns;

但我无法array[a, b, c]用动态的东西代替。如果我使用：

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( tablecolumns.cols ) AS colvalue
FROM foo, tablecolumns;

结果不是列的值，而只是列的名称。

问题

我怎样才能unnest得到列的值？

postgresql

1 个回答

  1. Best Answer

    正如这个问题https://stackoverflow.com/questions/15800367/select-columns-with-particular-column-names-in-postgresql中所指出的，无法将列名作为列进行评估

    这样做的方法是使用to_jsonb，正如unnest all columns from a given table中所建议的那样，例如：

    select foo.id, x.*
from foo, jsonb_each_text(to_jsonb(foo)) as x(colname,colvalue)
where x.colname ~ '^[a-z]$';

    在我的具体情况下，我试图转换与 Corona Virus 相关的所有数据。原始数据位于：https ://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv 。我已将其导入为 table covid。

    它有许多以日期命名的列，例如：'1/22/20','1/23/20','1/24/20','1/25/20','1/26/20', .... 第一列与位置相关，因此我想将它们保留在重新调整的表格中。

    我使用的最后一个查询是：

    select id, geom, "province/state" , "country/region", to_date( reportdate, 'MM/DD/YY'), nofcases
from covid, jsonb_each_text(to_jsonb(covid)) as x(reportdate,nofcases)
where x.reportdate ~ '[0-9]+'
order by 5;
    • 1

相关问题