9.4.?crosstab(text, text)

crosstab(text source_sql, text category_sql)

crosstab的单一参数形式的主要限制是它把一个组中的所有值都视作相似,并且把每一个值插入到第一个可用的列中。如果想要值列对应于特定的数据分类,或者因为某些分组没有关于某些分类的数据导致无法正常工作。crosstab的双参数形式通过提供一个对应于输出列的显式分类列表来处理这种情况。

source_sql是一个产生源数据集的SQL语句。这个语句必须返回一个row_name列、一个category列以及一个value列。也可以有一个或者多个“extra”列。row_name列必须是第一个。category和value列必须是按照这个顺序的最后两个列。row_name和category之间的任何列都被视作“extra”。对于具有相同row_name值的所有行,其“extra”列都应该相同。

例如,source_sql可能产生一组这样的东西:

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

category_sql是一个产生分类集合的SQL语句。这个语句必须只返回一列。它必须返回至少一行,如果是多行则不能重复,否则会生成错误。category_sql示例如下:

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

crosstab函数被声明为返回setof record,输出列的实际名称和类型必须在调用的SELECT语句的FROM子句中被定义,例如:

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

这将产生这样的结果:

                  <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

FROM子句必须定义正确数量的输出列以及正确的数据类型。如果在source_sql查询的结果中有N列,其中的前N-2列必须匹配前N-2个输出列。剩余的输出列必须具有source_sql查询结果的最后一列的类型,并且并且它们的数量必须正好和category_sql查询结果中的行数相同。

crosstab函数为具有相同row_name值的输入行形成的每一个连续分组产生一个输出行。输出的row_name列外加劝拿庞蜗菲教ㄗ⒉嵬锯一个“extra”列都是从分组的第一行复制而来。输出的value列被使用具有匹配的category值的行中的value域填充。如果一个行的category不匹配category_sql查询的任何输出,它的value会被忽略。匹配的分类不出现于分组中任何输出行中的的输出列会被用空值填充。

事实上,source_sql查询应该总是指定ORDER BY 1来保证具有相同row_name的值会被放在一起。但是,一个分组内分类的顺序并不重要。还有,确保category_sql查询的输出的顺序与指定的输出列顺序匹配是非常重要的。

这里有两个完整的示例:

示例一:

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

示例二:

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt     | temperature | test_result |test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |42       | PASS |                      | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |53      | FAIL|  Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

创建预定义的函数可以避免在每个查询中都必须写出结果列的名称和类型,参考前一节中的示例。用于这种形式的crosstab的底层C函数被命名为crosstab_hash。

XML 地图 | Sitemap 地图