PostgreSQL获取数据作为数据透视表。

Question

我有一个查询返回以下数据。

我希望将这个结果转换为下面的数据透视表。

Customer  Unit Oct2015  Nov2015  Dec2015  Jan2016
customer4  A4           3423
TEST       A3           4762.6
customer2  A2           1000

我希望使用日期字段来筛选数据。因此，如果我选择2015年10月至2016年1月，则会出现上述结果。

我尝试了以下查询：

CREATE EXTENSION tablefunc;


SELECT * FROM crosstab(
$$ select customer_name,date_part('month', breakup_date) as month,amount from (
select se.enquiry_id,s.ref_no,customer_name,ppd.property,breakup_date,land+construction+service_tax+vat as amount
from work_sheet_detail wsd
left join sales_estimate s on wsd.estimate_id=s.ref_no
left join sales_enquiry se on s.enquiry_id=se.enquiry_id
left join project_property_details ppd on s.property_id=ppd.property_id
where s.status_id=1
) as data order by 1 $$,
$$ SELECT m FROM generate_series(1,12) m $$
) AS (
  customer_name character varying, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int
); 

但它不能为客户每月的金额总和提供数据。此外，透视表按月份排列，而我需要年份和月份两列。

我已经做出了以下查询。但是我得到的是按月份和年份汇总的总数，但无法为其创建系列。

CREATE EXTENSION IF NOT EXISTS tablefunc ;


SELECT * FROM crosstab(
$$ select customer_name,breakup_date as month,amount from (
with sum_amount as (
select se.enquiry_id,to_char(breakup_date,'Mon YYYY') as breakup_date,s.ref_no,sum(land+construction+service_tax+vat) as amount
from work_sheet_detail wsd
left join sales_estimate s on wsd.estimate_id=s.ref_no
left join sales_enquiry se on s.enquiry_id=se.enquiry_id
where s.status_id=1
group by 1,2,3
)
select se.enquiry_id,customer_name,ppd.property,breakup_date,amount
from sum_amount sm
left join sales_estimate s on sm.ref_no=s.ref_no
left join sales_enquiry se on s.enquiry_id=se.enquiry_id
left join project_property_details ppd on s.property_id=ppd.property_id
where s.status_id=1

) as data order by 1 $$,
$$ SELECT m FROM generate_series(1,12) m $$
) AS (
  customer_name character varying, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int
);

- Akhilesh

---

@Vivek.S 这是一个打字错误。已经更新了问题。 - Akhilesh

既然你已经明确表达了你想要的，为什么不尝试提出一个问题，并可能提供一些你尝试过但未能如预期般工作的东西呢？ - Eggplant

Answer 1

如果您在透视表中只显示一年比较灵活，那么这个选择可能会有所帮助：

select customer, unit
    , sum(case date_part('MONTH', b_date) when 1 then amount else 0 end) as Jan
    , sum(case date_part('MONTH', b_date) when 2 then amount else 0 end) as Feb
    , sum(case date_part('MONTH', b_date) when 3 then amount else 0 end) as Mar
    , sum(case date_part('MONTH', b_date) when 4 then amount else 0 end) as Apr
    , sum(case date_part('MONTH', b_date) when 5 then amount else 0 end) as May
    , sum(case date_part('MONTH', b_date) when 6 then amount else 0 end) as June
    , sum(case date_part('MONTH', b_date) when 7 then amount else 0 end) as July
    , sum(case date_part('MONTH', b_date) when 8 then amount else 0 end) as Aug
    , sum(case date_part('MONTH', b_date) when 9 then amount else 0 end) as Sept
    , sum(case date_part('MONTH', b_date) when 10 then amount else 0 end) as Oct
    , sum(case date_part('MONTH', b_date) when 11 then amount else 0 end) as Nov
    , sum(case date_part('MONTH', b_date) when 12 then amount else 0 end) as Dec
from customers
    where date_part('YEAR', b_date) = 2015
group by name, property;

- Ana