Início / dba / Perguntas / 261433
Accepted
jgrocha
Asked: 2020-03-08 10:53:10 +0800 CST

Remodelar a tabela convertendo colunas em linhas

  • 772

O problema

Estou tentando remodelar uma tabela com muitas colunas. Estou tentando fazer isso independentemente da tabela específica, então estou tentando fazer isso para qualquer tabela.

Vamos usar uma tabela muito simples 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|

Eu quero transformar a coluna ae bas clinhas.

Esta consulta funciona (para esta tabela específica):

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  |

Mas eu quero torná-lo genérico para qualquer tabela com muitas colunas.

O que eu já fiz

Para obter todas as colunas que quero transformar em linhas, posso usar:

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

Então, usando a consulta anterior, posso fazer o seguinte:

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;

Mas não consigo substituir array[a, b, c]por algo dinâmico. Se eu usar:

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;

o resultado não são os valores das colunas, mas apenas o nome das colunas.

Pergunta

Como posso unnestos valores das colunas?

postgresql

1 respostas

  1. Best Answer

    Não há como ter os nomes das colunas sendo avaliados como colunas, conforme apontado nesta pergunta https://stackoverflow.com/questions/15800367/select-columns-with-particular-column-names-in-postgresql

    A maneira de fazer isso é usando to_jsonb, como sugerido em desaninhar todas as colunas de uma determinada tabela , como:

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

    No meu caso específico, eu estava tentando converter todos os dados relacionados ao Corona Virus. Os dados originais estão disponíveis em: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv . Eu importei como table covid.

    Tem muitas colunas com o nome da data, como: '1/22/20','1/23/20','1/24/20','1/25/20','1/26/20', .... As primeiras colunas estão relacionadas com o local, então quero mantê-las na tabela reformulada.

    A consulta final que estou usando é:

    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

relate perguntas