时间戳

首页 > 解决方案 > 使用交叉表功能透视 SQL 表

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

问题描述

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

查询输出示例

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.

标签： sqlpostgresqlpivotcrosstab

解决方案

第二个查询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

推荐阅读