具有2个(或更多)行名的交叉表 [英] crosstab with 2 (or more) row names

问题描述

我正在尝试转置具有2个行名的表.
Postgres文档提到crosstab()函数只能处理1个行名，但是我有2个行名，例如名字和姓氏.

I'm trying to transpose a table which has 2 row names.
Postgres documentation mentions the crosstab() function being able to only handle 1 row name, but I have 2 row names, like first name and last name.

我的初始表格是:

fn |  ln |  file_type |attribute
--------------------------------
A  |  1  |  cat1     |abc
A  |  2  |  cat1     |gth
A  |  1  |  cat2     |fgh
B  |  1  |  cat2     |gth

并且我希望我的最终表具有2个初始行并转置了file_type

and I want my final table to be with 2 initial rows and the file_type transposed

 fn |  ln |  cat1 | cat2
--------------------------------
 A  |   1 |  abc  | fgh
 A  |   2 |  gth  | 
 B  |   1 |       | gth

我无法在functools中找到所需的东西...

I have not been able to find what I need in functools ...

版本是Postgres 9.3

version is Postgres 9.3

推荐答案

使用附加模块tablefunc中的crosstab().

Use crosstab() from the additional module tablefunc.

特定困难在这里是行名"由两列组成.我出于查询目的而进行连接，但不在最后显示连接的列.
假设fn和ln是NOT NULL.未经测试:

The specific difficulty here is that the "row name" consists of two columns. I concatenate for the purpose of the query and do not display the concatenated column at the end.
Assuming fn and ln are NOT NULL. Untested:

SELECT fn, ln, cat1, cat2
FROM crosstab(
       'SELECT fn || ln AS row_name, fn, ln, file_type, attribute
        FROM   t
        ORDER  BY fn, ln, file_type'

      ,$$VALUES ('cat1'::text), ('cat2')$$)
AS t (row_name text, fn text, ln int, cat1 text, cat2 text);

另一个选择是使用dense_rank()之类的窗口函数添加代理行名"，并将定义的两列视为额外列".示例:

Another option would be to add a surrogate "row name" with a window function like dense_rank() and treat the defining two columns as "extra columns". Example:

• 具有多个行名"的Postgresql交叉表查询;列

基础:

• PostgreSQL交叉表查询

这篇关于具有2个(或更多)行名的交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！