如何在PostgreSQL的`tablefunc`查询中包含空值？ [英] How to include null values in `tablefunc` query in postgresql?

问题描述

我正在尝试在<中使用的变体：

  SELECT * FROM交叉表（
'SELECT zone_id，group_id，area 
 FROM ct 
 ORDER BY 1,2'
 
，'SELECT g FROM generate_series（1,8）g'- -！显式提供值
）
 AS ct（
 row_name整数
，g_1 float8，g_2 float8 
，g_3 float8，g_4 float8 
，g_5 float8，g_6 float8 
，g_7 float8，g_8 float8）; 
  

从而明确声明哪个值进入哪个输出列。因此该函数知道在何处填写 NULL 值。在这种情况下， generate_series（）可以派上用场，为8行提供数字1-8。 VALUES 表达式是一种替代选择：

 'VALUES（1） ，（2），（3），（4），（5），（6），（7），（8）'
  

此外，不要忘记第一个参数查询中的 ORDER BY 子句。

我在此相关答案中提供了详细的解释。

I'm trying to use the crosstab function in postgresql to create a pivot table. However, I'm having difficulty understanding how to structure my SQL within the query. My data consists of four columns and looks like this:

I create this table using the following code:

CREATE TABLE ct(id SERIAL, zone_id int, group_id int, area double precision);
INSERT INTO ct(zone_id, group_id, area) VALUES(1,2,6798.50754160784);
INSERT INTO ct(zone_id, group_id, area) VALUES(1,3,10197.7613124118);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,1,85708.8676744647);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,2,56006.5971338327);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,3,5584.33145616642);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,5,8611.99732832252);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,6,36103.5509183704);
INSERT INTO ct(zone_id, group_id, area) VALUES(2,8,9801.14541428806);
INSERT INTO ct(zone_id, group_id, area) VALUES(5,1,45796.0020793546);

And following the postgresql documentation closely, I use the following code in my crosstab query:

SELECT *
FROM crosstab(
  'select zone_id, group_id, area
   from ct
   ')
AS ct(row_name integer, 
      g_1 double precision, 
      g_2 double precision, 
      g_3 double precision, 
      g_4 double precision, 
      g_5 double precision, 
      g_6 double precision, 
      g_7 double precision, 
      g_8 double precision);

This results in the following table which is not what I want them to be:

For example, in row two, I want the following values:

85708.8676744647, 56006.5971338327, 5584.33145616642, NULL, 8611.99732832252, 36103.5509183704, NULL, 9801.14541428806

Instead the values are:

85708.8676744647, 56006.5971338327, 5584.33145616642, 8611.99732832252, 36103.5509183704, 9801.14541428806

However, it seems that the null values are ignored, so that my column names g1 to g8, do not correspond to the original groups.

解决方案

Use the crosstab() variant with two parameters:

SELECT * FROM crosstab(
   'SELECT zone_id, group_id, area
    FROM   ct
    ORDER  BY 1,2'

   ,'SELECT g FROM generate_series(1,8) g'  -- ! Provide values explicitly
   )
AS ct(
     row_name integer
   , g_1 float8, g_2 float8
   , g_3 float8, g_4 float8
   , g_5 float8, g_6 float8
   , g_7 float8, g_8 float8);

Thereby declaring explicitly which value goes in which output column. So the function knows where to fill in NULL values. In this case generate_series() comes in handy to provide 8 rows with the numbers 1-8. A VALUES expression would be an alternative:

'VALUES (1), (2), (3), (4), (5), (6), (7), (8)'

Also, don't forget the ORDER BY clause in the first parameter query.

I provided a detailed explanation in this related answer.

这篇关于如何在PostgreSQL的`tablefunc`查询中包含空值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！