sql - 使用交叉表功能透视 SQL 表

Question

我做了这个查询，列出了一个城市每年每平方米的所有房地产价格。它运作良好，但所有年份最终都排成一排，价格落后。我更愿意在单元格中看到列中的年份和价格。通过 stackoverflow，我找到了 crosstab 函数并对其进行了试验。不幸的是，我似乎无法让它发挥作用。如果有人可以查看查询，我会很高兴。

查询输出示例

city        year    avg_price_m2
Amsterdam   2016    4407,51
Amsterdam   2017    5015,75
Amsterdam   2018    5648,1
Amsterdam   2019    5904,91

想要的

city        2016    2017      2018    2019
Amsterdam   4407,51 5015,75   5648,1  5904,91

当前查询

SELECT city, 
       Extract(year FROM ondertekening_akte) AS year, 
       Round(Avg(transactieprijs_per_m2), 2) AS avg_price_m2 
FROM   transactiedata.transacties 
       JOIN bagactueel.gemeente 
         ON St_contains (bagactueel.gemeente.geovlak, 
            transactiedata.transacties.geopunt) 
WHERE  city = 'Amsterdam' 
       AND Extract(year FROM ondertekening_akte) > 2006 
GROUP  BY city, 
          year; 

旋转尝试

select * from crosstab (
    $$select city,
    extract(year from ondertekening_akte) as year,
    ROUND(AVG(transactieprijs_per_m2),2) as avg_price_m2
    from transactiedata.transacties
    JOIN bagactueel.gemeente ON ST_Contains (bagactueel.gemeente.geovlak, transactiedata.transacties.geopunt)
    where city = 'Amsterdam'
    and extract(year from ondertekening_akte) > 2006
    group by city, year$$,

    $$select distinct extract(year from ondertekening_akte) as year from transactiedata.transacties order by year$$

)
AS (
    "city" text,
    "2007" int,
    "2008" int,
    "2009" int,
    "2010" int,
    "2011" int,
    "2012" int,
    "2013" int,
    "2014" int,
    "2015" int,
    "2016" int,
    "2017" int,
    "2018" int,
    "2019" int
)
;

我收到这个错误:

ERROR:  invalid return type
DETAIL:  Query-specified return tuple has 14 columns but crosstab returns 17.

Answer 1

crosstab() 中的第二个查询返回超过 13 行(正好 16 行)。您可能应该添加条件:

select distinct extract(year from ondertekening_akte) as year 
from transactiedata.transacties 
where extract(year from ondertekening_akte) > 2006 --!!
order by year