Postgres Crosstab在具有未知值的双列上

Question

所以我的Postgres v.10 DB 中有一个这样的表

CREATE TABLE t1(id integer primary key, ref integer,v_id integer,total numeric, year varchar, total_lastyear numeric,lastyear varchar ) ;
INSERT INTO t1 VALUES

(1,  2077,15,10000,2020,9000,2019),
(2,  2000,13,190000,2020,189000,2019),
(3,  2065,11,10000,2020,10000,2019),
(4,  1999,14,2300,2020,9000,2019);

从t1中选择*=

id  ref    v_id total   year    total_lastyear  lastyear
1   2077    15  10000   2020       9000          2019
2   2000    13  190000  2020       189000        2019
3   2065    11  10000   2020       10000         2019
4   1999    14  2300    2020       9000          2019

现在我想透视这个表，这样我就有2020和2019作为列，总金额作为值。

我的问题：

1. 我不知道在同一个查询中两个列是如何透视的，这可能吗？或者你必须做两个步骤吗？

2. 2020年和2019年是动态的，可以从一天变到另一天。列中的年份在每一行都是相同的。

3. 因此，基本上我需要将lastyear和year中的年份保存在某个变量中，并将其传递给Crosstab查询。

到目前为止，我自己做到了，但我只调整了一年，2019年和2020年都是硬编码的。演示

Answer 1

您可以使用WITH一次调整一个。

WITH xd1 AS (
SELECT * FROM crosstab('SELECT ref,v_id,year,total FROM t1 ORDER BY 1,3',
'SELECT DISTINCT year FROM t1 ORDER BY 1') AS ct1(ref int,v_id int,"2020" int)
), xd2 AS (
SELECT * FROM crosstab('SELECT ref,v_id,lastyear,total_lastyear FROM t1 ORDER BY 1,3',
'SELECT DISTINCT lastyear FROM t1 ORDER BY 1') AS ct2(ref int,v_id int,"2019" int)
)
SELECT xd1.ref,xd1.v_id,xd1."2020",xxx."2019"
FROM xd1
LEFT JOIN xd2 AS xxx ON xxx.ref = xd1.ref AND xxx.v_id = xd1.v_id;

这并不能防止CCD_ 2和CCD_。您仍然需要知道年份查询将返回，因为您必须定义交叉表返回的记录。

您可以将它封装在EXECUTE format()中，使其更具动态性并处理一些字符串。这里提到了这个问题。