PostgreSQL Crosstab Query （交叉表）试用随记

PostgreSQL Crosstab Query

You can use the crosstab() function of the additional module tablefunc - which you have to installonce per database. Since PostgreSQL 9.1 you can use CREATE EXTENSION for that:

CREATE EXTENSION tablefunc;


http://stackoverflow.com/questions/3002499/postgresql-crosstab-query


初用PostgreSQL的交叉表，遇到问题，这篇文章帮助解决了问题。

特别注意：
1、extension的安装方法，最后使用pgAdminiii的扩展工具右键功能加上的。
2、crosstab方法在使用中一定要注意强制类型转换，此前好几次试验失败都是默认没加类型转换。例如row_name::text。
3、一定要注意croostab在行转列过程中不管列的排序问题，必须将ct（...）表达式中的值枚举与此前的select中排序顺序对应起来，否则会导致转列后的数值填充错位。资料的例子中是这么说的：

Proper answer

Install the additional module tablefunc which provides the function crosstab() once per database. Since PostgreSQL 9.1 you can use CREATE EXTENSION for that:

CREATE EXTENSION tablefunc;

Improved test case

CREATE TEMP TABLE t (
  section   text
 ,status    text
 ,ct        integer -- don't use "count" as column name.);INSERTINTO t VALUES('A','Active',1),('A','Inactive',2),('B','Active',4),('B','Inactive',5),('C','Inactive',7);-- no row for C with 'Active'

• count is a reserved word, don't use it as column name.

Simple form - not fit for missing attributes

SELECT*FROM   crosstab('SELECT section, status, ct
       FROM   t
       ORDER  BY 1,2')AS ct ("Section" text,"Active" int,"Inactive" int);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |

• No need for casting and renaming
• Note the incorrect result for C: the value 7 is filled in for the first column.

Safe form

SELECT*FROM crosstab('SELECT section, status, ct
        FROM   t
        ORDER  BY 1,2',$$VALUES('Active'::text),('Inactive')$$)AS ct ("Section" text,"Active" text,"Inactive" int);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7

• Note the correct result for C.

• The second parameter can be any query that returns one row per attribute in the appropriate order (VALUES expression in the example).
  Often you will want to query distinct attributes from the underlying table like this:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'

• I used dollar quoting in the second parameter query to make quoting easier.

按照croostab函数创建的相关视图脚本关键代码如下(环境为ArcGIS 10.1 + PostgreSQL 9.1.3，用到ArcGIS的空间视图和Query Layer特性)：

----------------------------------------------------------------------地市统计图表相关视图------------------------------------------------------------------------------------------

--生成地市交叉统计表

SELECT

         ct.cityid,

         COALESCE(ct."cata1" , 0)  as  cata1,

         COALESCE( ct."cata2",0)  as  cata2,

         COALESCE(ct."cata3",0 )  as  cata3,

         COALESCE(ct."cata4",0)  as  cata4,

         COALESCE(ct."cata5", 0)  as  cata5,

         COALESCE(ct."cata6",0)  as  cata6,

         COALESCE(ct."cata7",0)  as  cata7,

         COALESCE(ct."cata8" ,0)  as  cata8

 

FROM house.crosstab

         ('SELECT   

                   rpad(districtid,4) ||''00''::text as cityid,

                   housetype::text,

                   count(*)::int

         FROM        house.poi

         GROUP BY cityid, housetype

         ORDER BY 1,2'::text,

        

         'SELECT distinct housetype

         FROM  house.poi

         ORDER BY 1'::text

         )

ct(cityid text, "cata1" integer, "cata2" integer, "cata3" integer, "cata4" integer, "cata5" integer, "cata6" integer, "cata7" integer, "cata8" integer);

 

--生成带统计值的地市空间视图

SELECT *

  FROM house.city,house.v_sum

  where city.pac=v_sum.cityid;

----------------------------------------------------------------------地市统计图表相关视图------------------------------------------------------------------------------------------

 

 

 

----------------------------------------------------------------------县区统计图表相关视图------------------------------------------------------------------------------------------

--生成县区交叉统计表

SELECT ct.countyid, COALESCE(ct."cata1", 0) AS "cata1", COALESCE(ct."cata2", 0) AS "cata2", COALESCE(ct."cata3", 0) AS "cata3", COALESCE(ct."cata4", 0) AS "cata4",

 COALESCE(ct."cata5", 0) AS "cata5", COALESCE(ct."cata6", 0) AS "cata6", COALESCE(ct."cata7", 0) AS "cata7", COALESCE(ct."cata8", 0) AS "cata8"

   FROM house.crosstab('SELECT   

                   districtid::text as countyid,

                   housetype::text,

                   count(*)::int

         FROM        house.poi

         GROUP BY countyid, housetype

         ORDER BY 1,2'::text, 'SELECT distinct housetype

         FROM  house.poi

         ORDER BY 1'::text) ct(countyid text, "cata1" integer, "cata2" integer, "cata3" integer, "cata4" integer, "cata5" integer, "cata6" integer, "cata7" integer, "cata8" integer);

 

 

--生成带统计值的县区空间视图

SELECT *

   FROM house.county, house.v_county_sum v_sum

  WHERE county.pac= v_sum.countyid;

----------------------------------------------------------------------县区统计图表相关视图------------------------------------------------------------------------------------------