我正在尝试转置具有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个(或更多)行名的交叉表
发布评论