行转列crosstab使用心得

2019独角兽企业重金招聘Python工程师标准>>>

创建测试用表：

create table test1
(s_name varchar(10),
s_class varchar(10),
c_value numeric
);

插入测试数据：

insert into test1 values('张三','语文',78),('张三','数学',38),('张三','英语',89),('张三','化学',86),('张三','物理',68);
insert into test1 values('李四','语文',58),('李四','数学',67),('李四','英语',82),('李四','化学',56),('李四','物理',39);

一般查询及结果：

select s_name,s_class,c_value from test1;

行转列查询及结果：

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2')

as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric)

心得：

1，行转列每一个字段必须为text类型，如果表结构不是text就需要进行转换。

2，为了能方便阅读要给转换后的列写一个别名，这时需要注意的是，如果不排序查询结果是随机的，如上写成：

select * from crosstab('select s_name::text,s_class,c_value from test1')

as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric);

显然是不对的。

所以别名要按排序的结果写

3，分类个数宜多不宜少，少了数据丢失

少：

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2')

as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric);

显然这个丢失了”语文“,结果问题还不大，至少数据是正确的

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2')

as ct (姓名 text,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric);

这样结果完全是错误的

 

多：

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2')

as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric,历史 numeric);

对结果没有什么影响

现在，多加一条记录：

insert into test1 values('王五','语文',58),('王五','英语',82),('王五','化学',56),('王五','物理',39);

再查询：

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2') as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric,历史 numeric);

第三行出问题了

那么可以这么解决：

select * from crosstab('select s_name::text,s_class,c_value from test1 order by 1,2','select distinct s_class from test1 order by 1')
as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric);

显然这样比较准确，但是不是crosstab（text）输入一个参数的方法就没有必要了呢？

当然不，至少他的列数是可以随意变都没有报错，只要注意列的顺序且排前的列没有空值，还是有用处的。

crosstab这个用起来准确得多，当然限制也多一些，转换的列数必须与distinct个数一样，多或少都会报错。

总之，这函数用起来雷还是挺多的，要是能整合一下就更完美了。

转载于:https://my.oschina.net/u/204916/blog/190884