我正在尝试使用crosstab http://www.postgresql.org/docs/9.1/static/tablefunc.html函数于postgresql
创建一个pivot table
。但是,我很难理解如何在查询中构建 SQL。我的数据由四列组成,如下所示:
我使用以下代码创建此表:
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);
并遵循postgresql
密切关注文档,我在我的中使用以下代码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);
这导致下表不是我想要的结果:
例如,在第二行中,我想要以下值:
85708.8676744647, 56006.5971338327, 5584.33145616642, NULL, 8611.99732832252, 36103.5509183704, NULL, 9801.14541428806
相反,这些值是:
85708.8676744647, 56006.5971338327, 5584.33145616642, 8611.99732832252, 36103.5509183704, 9801.14541428806
然而,似乎null
值被忽略,因此我的列名g1
to g8
,不对应于原始组。