Início / dba / Perguntas / 332921
Accepted
SpiderMan
Asked: 2023-11-09 14:50:06 +0800 CST

Crosstab de múltiplas colunas no postgresql

  • 772

Estou tentando combinar duas crosstab no postgres, mas travou, será ótimo se alguém me orientar.

-- rainfall CROSS TAB
SELECT * FROM
CROSSTAB(
'SELECT block_id, forecast_date, rainfall_mm
 FROM weather.imd_forecast 
    ORDER BY 1'
)
AS rainfall_crosstab(
    block_id int, rainfall_day_1 numeric, rainfall_day_2 numeric, 
    rainfall_day_3 numeric, rainfall_day_4 numeric, rainfall_day_5 numeric
);
    
-- temp_max CROSS TAB
SELECT * FROM
CROSSTAB(
'SELECT block_id, forecast_date, temp_max_deg_c 
 FROM weather.imd_forecast 
    ORDER BY 1'
)
AS temp_max_crosstab(
    block_id int, temp_max_day_1 numeric, temp_max_day_2 numeric, 
    temp_max_day_3 numeric, temp_max_day_4 numeric, temp_max_day_5 numeric);

insira a descrição da imagem aqui

postgresql

2 respostas

  1. Supondo que block_id e Forecast_date sejam campos comuns em ambos os resultados da tabela cruzada, você pode usar uma joinoperação para combiná-los com base nesses campos. Com base nessa suposição, primeiro, podemos usar expressões de tabela comuns (CTEs) para criar conjuntos de resultados temporários para rain_crosstab e temp_max_crosstab. Em seguida, realizamos uma operação JOIN nos campos block_ide forecast_datepara combinar os dois resultados da tabela cruzada.

    WITH rainfall_crosstab AS (
    SELECT *
    FROM CROSSTAB(
        'SELECT block_id, forecast_date, rainfall_mm
         FROM weather.imd_forecast 
         ORDER BY 1'
    ) AS rainfall_crosstab(
        block_id int, rainfall_day_1 numeric, rainfall_day_2 numeric, 
        rainfall_day_3 numeric, rainfall_day_4 numeric, rainfall_day_5 numeric
    )
), temp_max_crosstab AS (
    SELECT *
    FROM CROSSTAB(
        'SELECT block_id, forecast_date, temp_max_deg_c 
         FROM weather.imd_forecast 
         ORDER BY 1'
    ) AS temp_max_crosstab(
        block_id int, temp_max_day_1 numeric, temp_max_day_2 numeric, 
        temp_max_day_3 numeric, temp_max_day_4 numeric, temp_max_day_5 numeric
    )
)
SELECT 
    rc.block_id, 
    rc.rainfall_day_1, rc.rainfall_day_2, rc.rainfall_day_3, 
    rc.rainfall_day_4, rc.rainfall_day_5,
    tc.temp_max_day_1, tc.temp_max_day_2, tc.temp_max_day_3, 
    tc.temp_max_day_4, tc.temp_max_day_5
FROM rainfall_crosstab rc
JOIN temp_max_crosstab tc ON rc.block_id = tc.block_id AND rc.forecast_date = tc.forecast_date;
    • 0
  2. Best Answer

    Ambas as suas consultas exigem ORDER BY 1, 2, não apenas ORDER BY 1. Caso contrário, o resultado pode estar errado. Veja a demonstração no violino.

    Solução combinada:

    SELECT block_id
     , (t1).rainfall_mm AS rainfall_day1, (t1).temp_max_deg_c AS temp_day1
     , (t2).rainfall_mm AS rainfall_day2, (t2).temp_max_deg_c AS temp_day2
     , (t3).rainfall_mm AS rainfall_day3, (t3).temp_max_deg_c AS temp_day3
     , (t4).rainfall_mm AS rainfall_day4, (t4).temp_max_deg_c AS temp_day4
     , (t5).rainfall_mm AS rainfall_day5, (t5).temp_max_deg_c AS temp_day5
FROM   crosstab(
   'SELECT block_id, forecast_date, t
    FROM   weather.imd_forecast t
    ORDER  BY 1, 2'
    ) AS ct (   block_id int, t1 imd_forecast, t2 imd_forecast
           , t3 imd_forecast, t4 imd_forecast, t5 imd_forecast);

    violino

    Leia aqui:

    • 0

relate perguntas