@
woodytang pg 下,如果可以用程序生成返回列然后拼接 SQL ,那么很简单
//创建测试表
create table test3("user" text,amount int,datetime timestamp);
insert into public.test3 (user, amount, datetime)
values ('A', 10, '2021-02-12 10:00:00.000000'),
('A', 20, '2021-03-12 10:00:00.000000'),
('B', 30, '2021-05-12 10:00:00.000000'),
('B', 10, '2021-06-12 10:00:00.000000'),
('C', 10, '2021-06-12 10:00:00.000000'),
('C', 20, '2021-06-12 10:00:00.000000'),
('C', 5, '2021-05-12 10:00:00.000000');
//使用 crosstab 进行 pivot
select *
from crosstab($$select "user",date_trunc('month',datetime)::date::text,sum(amount)
from test3 group by 1,2 order by 1,2 $$,
'select distinct date_trunc(''month'',datetime)::date::text from test3 order by 1') as ct(
"user" text,
"2021-02" text,
"2021-03" text,
"2021-05" text,
"2021-06" text
);
//结果
user | 2021-02 | 2021-03 | 2021-05 | 2021-06
------+---------+---------+---------+---------
A | 10 | 20 | |
B | | | 30 | 10
C | | | 5 | 30
如果要动态生成返回列名,那么确实是相当的麻烦,因为 pg 的查询必须显式制定列,只能用非常别扭的方式
-- 创建一个函数,实际查询的 SQL ,可以反复使用
create or replace function crosstabquery(_t anyelement) returns setof anyelement as
$$
declare
column_list text;
begin
select string_agg(format('%I text', d), ',')
into column_list
from (select distinct date_trunc('month', datetime)::date::text d from test3 order by 1) r;
return query execute ($b$select *
from crosstab($a$select "user",date_trunc('month',datetime)::date::text,sum(amount)
from test3 group by 1,2 order by 1,2 $a$,
'select distinct date_trunc(''month'',datetime)::date::text from test3 order by 1') as ct("user" text,$b$ ||
column_list||')' );
end ;
$$
language plpgsql;
-- 创建返回类型,每次使用前调用
do
$$
declare
column_list text;
begin
select string_agg(format('%I text', d), ',')
into column_list
from (select distinct date_trunc('month', datetime)::date::text d from test3 order by 1) r;
drop type if exists __t;
execute format('create type __t as ("user" text, %s)', column_list);
end
$$ language plpgsql;
-- 返回实际结果
select * from crosstabquery(null::__t);
user | 2021-02-01 | 2021-03-01 | 2021-05-01 | 2021-06-01
------+------------+------------+------------+------------
A | 10 | 20 | |
B | | | 30 | 10
C | | | 5 | 30
(3 rows)
不过如果可以动态生成 sql ,就别用下面这个方案,实在是别扭