9.2.?crosstab(text)

crosstab(text sql)

crosstab函数被用来产生“pivot”显示,在其中数据被横布在页面上而不是直接向下列举。例如,可能有这样的数据:

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

而希望显示成这样:

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

crosstab函数接受文本参数,该参数是一个SQL查询,生成以第一种方式格式化的原始数据和以第二种方式格式化的表。

sql参数是一个生成源数据集的SQL语句。这个语句必须返回一个row_name列、一个category列和一个value列。

例如,所提供的查询是这样的一个集合:

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

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

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

这个示例产生这样一个集合:

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

FROM子句必须把输出定义为一个row_name列(具有SQL查询的第一个结果列的相同数据类型),其后跟随着N个value列(都具有SQL查询的第三个结果列的相同数据类型)。可以按照意愿设置劝拿庞蜗菲教ㄗ⒉嵬锯多的输出值列。而输出列的名称取决于你自己。

crosstab函数为具有相同row_name值的输入行的每一个连续分组产生一个输出行。它使用来自这些行的值域从左至右填充输出的值列。如果一个分组中的行比输出值列少,多余的输出列将被用空值填充。如果行更多,则多余的输入行会被跳过。

事实上,SQL查询应该总是指定ORDER BY 1,2来保证输入行被正确地排序,也就是说具有相同row_name的值会被放在一起并且在行内被正确地排序。注意crosstab本身并不关注查询结果的第二列,它放在那里只是为了被排序,以便控制出现在页面上的第三列值的顺序。

这是一个完整的示例:

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

为了避免总是要写出一个FROM子句来定义输出列有两种方法:

  1. 设置一个在其定义中硬编码所期望的输出行类型的自定义crosstab函数,详见下一节内容。

  2. 在视图定义中嵌入所需的FROM子句。

注意

uxsql中的\crosstabview命令也提供了和crosstab()类似的功能。

XML 地图 | Sitemap 地图