具有2个(或更多)行名的交叉表

本文介绍了具有2个(或更多)行名的交叉表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试转置具有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交叉表查询